Setup a REST API with Sequelize and Express.js
Introduction
In this tutorial all steps for building a REST API are detailed. On that purpose we are going to use mainly two Node.js modules, namely Express.js and Sequelize. The first one is a lightweight web framework while the second one is an ORM for, as its name suggests, SQL based database.
In a separated tutorial we discuss about the implementation of the same API as the one in the present article using GraphQL.
A bit of theory
What is a REST API ? A REST API is a set of URIs performing precise actions on data.
What kind of actions ? The actions are the so-called CRUD actions, namely Create, Read, Update, Delete. The main idea of REST is to associate the HTTP protocol with CRUD actions mentioned above. Indeed HTTP has distinguished kind of HTTP request, among others POST, GET, PUT, DELETE.
POST corresponds to Create, GET to Read, PUT to Update and DELETE to Delete in CRUD model.
The HTTP header of an a HTTP request contains the type of the HTTP request called the method, also known under HTTP verb.
REST provides thus an uniformed way to build interface between applications, what is commonly called an API. REST has been designed by Fielding in his PHD thesis.
In this little project we are going to make use of an ORM which is an abstraction layer on top of the database. Thanks to that layer, we do not need to write any SQL statements. These statements are generated by the ORM.
Starting point
Since REST performes actions on data, the starting point would be to look at the data structure of your service. For the sake of this tutorial we’ll use a very simple database schema.
Environment setup
You need to install two independent software for this tutorial. Node.js and MySQL. Regarding the first one, it’s recommended to install it through nvm. As for the second one, you can for example install XAMPP whose stack includes MySQL.
Project setup
Run the following commands in the terminal :
> cd whatever_path
> mkdir restApiNodeDemo && cd restApiNodeDemo
> npm init -y
> npm i express sequelize mysql2 body-parser --save
> npm i nodemon sequelize-cli babel-cli babel-preset-env babel-preset-stage-3 faker lodash.times lodash.random --save-devNow do open the generated package.json file and do add the following scripts under the scripts key and remove the generated "test" one :
"start": "npm run init-db && npx nodemon --exec babel-node server.js",
"sequelize-skeleton": "npx sequelize init:models; npx sequelize init:config",
"init-db": "cd DIR_WHERE_MYSQL_BIN_IS && echo 'DROP DATABASE IF EXISTS api_node_demo; CREATE DATABASE api_node_demo CHARACTER SET utf8 COLLATE utf8_general_ci' | ./mysql -u root && cd -"The snippet above consists of three scripts for respectively launching the server, creating a skeleton for the ORM and creating a database. Do replace in the last script DIR_WHERE_MYSQL_BIN_IS by the location of the mysql bin in your system. If you installed it through XAMPP, the path should be /opt/lampp/bin on a GNU/Linux based OS.
Now do launch mysql server and run the second script :
npm run sequelize-skeletonEventually do edit the generated config.json file located under the config folder and do change the database name like this : "database": "api_node_demo".
Lastly, in order for us to use the ES2015 syntax for the module importation in Nodejs and not the CommonJS one, by means of Babel, do create a .babelrc at the root of the project and put the following :
{
"presets": ["env", "stage-3"]
}Project hierarchy
Let’s create our ORM data models
Under the models directory, do create the models Post and Author in two separated files post.js, author.js and do paste in them the following content :
module.exports = (sequelize, DataTypes) => {
const Post = sequelize.define('post', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
title: DataTypes.STRING,
content: {
type: DataTypes.TEXT,
allowNull: false
},
},
{
freezeTableName: true,
}
);
Post.associate = (models) => {
Post.belongsTo(models.author);
};
return Post;
}module.exports = (sequelize, DataTypes) => {
const Author = sequelize.define('author', {
id: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
firstName: DataTypes.STRING,
lastName: DataTypes.STRING
},
{
freezeTableName: true,
}
);
Author.associate = (models) => {
Author.hasMany(models.post);
};
return Author;
}You can see how to express relationships between models with the associate function.
Let’s create our API
Do create a folder app at the root of the project. Under this app folder, do create a folder called api. Under that api folder do create the files post.js,author.js with the following content :
module.exports = (app, db) => {
app.get( "/posts", (req, res) =>
db.post.findAll().then( (result) => res.json(result) )
);
app.get( "/post/:id", (req, res) =>
db.post.findByPk(req.params.id).then( (result) => res.json(result))
);
app.post("/post", (req, res) =>
db.post.create({
title: req.body.title,
content: req.body.content
}).then( (result) => res.json(result) )
);
app.put( "/post/:id", (req, res) =>
db.post.update({
title: req.body.title,
content: req.body.content
},
{
where: {
id: req.params.id
}
}).then( (result) => res.json(result) )
);
app.delete( "/post/:id", (req, res) =>
db.post.destroy({
where: {
id: req.params.id
}
}).then( (result) => res.json(result) )
);
}module.exports = (app, db) => {
app.get( "/author/:id", (req, res) =>
db.author.findByPk(req.params.id).then( (result) => res.json(result))
);
}These two modules, depending upon the method of the HTTP request, retrieve the data passed in its body, using the req object in the callback, and in any case query the database through the ORM. As an exercise you might complete the author.js API.
Let’s create our server
Do create a server.js file at the root of the project having the following content :
import express from "express";
import bodyParser from "body-parser";
import faker from "faker";
import times from "lodash.times";
import random from "lodash.random";
import db from "./models";
import apiPost from "./app/api/post";
import apiAuthor from "./app/api/author";
const app = express();
app.use(bodyParser.json());
app.use(express.static("app/public"));
apiPost(app, db);
apiAuthor(app, db);
db.sequelize.sync().then(() => {
// populate author table with dummy data
db.author.bulkCreate(
times(10, () => ({
firstName: faker.name.firstName(),
lastName: faker.name.lastName()
}))
);
// populate post table with dummy data
db.post.bulkCreate(
times(10, () => ({
title: faker.lorem.sentence(),
content: faker.lorem.paragraph(),
authorId: random(1, 10)
}))
);
app.listen(8080, () => console.log("App listening on port 8080!"));
});In this file an express instance is declared. Then that instance is extended by appending to it the APIs created above. Finally the database connection as well as the tables are created and besides the tables are populated with some dummy data. Let’s run the server :
> npm run startLet’s consume the API
We are in the last step. Do create a folder public under the app folder created previously. Under that folder do create a index.html file having the following content :
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Post overview</title>
<style type="text/css">
section {
border-bottom: 1px solid grey;
padding-bottom: 10px;
}
section p:first-of-type>strong {
color: rgb(2.8%, 11.1%, 47.6%);
}
section p:nth-of-type(2)>strong {
color: rgb(29.1%, 30.8%, 38.2%);
}
</style>
</head>
<body>
<h2>Posts and their author</h2>
<script>
document.addEventListener('DOMContentLoaded', function () {
const renderPost = (body, post) => {
const section = document.createElement('section');
const domString = `
<p>
<strong>Post: </strong>${post.title}
</p>
<p>
<strong>Author: </strong>${post.author.firstName}
</p>
`;
section.innerHTML = domString;
body.appendChild(section);
};
const promisePosts = fetch("/posts")
.then((response) => response.json());
const promiseAuthors = promisePosts
.then((posts) => {
return Promise.all(
posts.map(
(post) => fetch(`/author/${post.authorId}`)
.then((r) => r.json())
)
);
});
const body = document.body;
Promise.all([promisePosts, promiseAuthors])
.then(([posts, authors]) => {
authors.forEach((author, index) => {
if (posts[index]) {
const aggregatedPost = {...posts[index], author};
renderPost(body, aggregatedPost);
}
})
})
.catch((e) => console.error(e));
});
</script>
</body>
</html>In the script above first a GET request for fetching the posts is done, which returns a promise. That promise is chained for fetching the author of those posts, which in turn returns a promise (Promise.all returns indeed a promise). Eventually once both promises are resolved their data are combined and rendered in Vanilla JS. Do enter http://localhost:8080 in your browser.
You may use an HTTP client like Postman for quick testing the other endpoints of the API.
Conclusions
Feel free to clone the repo created for the sake of this tutorial.
Based on this little REST API we can see some of the limitations of such an API :
- On the client-side we need to transform the data structure before we actually can start to render it (see the line using the spread operator).
- Some data sent by the server are not used client-side, e.g. the
lastNameof the author. - REST does not have any standard to specify how the data need to be delivered, e.g. alphabetically order by post title.
- Finally on the server-side we have multiple endpoints which is hard to maintain.
- We did many HTTP requests, actually 1 (all posts)+10 (author)=11. This is the so-called n+1 problem.
We could have created an additional endpoint like/posts/authorfor addressing this issue, but we would then have more endpoints to manage.
If you are concerned about the above listed issues, you may have a look at the related article implementing the same API using GraphQL.
