How to set up a Node/Express JS project with Sequelize-CLI

Introduction

As a developer, I can guess the first question you asked yourself when you heard of Node JS was, ‘Why should I learn Node?‘ or ‘What is Node JS‘. If you were lucky enough to get the answers, the next task was to struggle with Node documentation or tutorials, which were not easy to understand. Maybe later you learnt that there were many other things you were to know about Node such as express JS, sequelize and sequelize-cli.

Well, in this tutorial, you will get a complete stepwise approach to setting up a node application with the express framework using sequelize-cli. Specifically, we will address the following concerns;-

  • What is Node?
  • What is express JS?
  • How to set up a project with sequelize-cli
  • How to create sequelize models
  • How to handle a many-to-many relationship in sequelize models – ‘junction model pattern’
  • How to seed a database with faker package in sequelize
  • How to run transactions in sequelize
  • How to run a specific migration in Sequelize
  • How to use express JS router
  • How to setup express JS controllers.

Prerequisite

  • A code editor (I will be using Visual Studio Code (VS Code))
  • A basic understanding of JavaScript.
  • Basic knowledge of Postman.
  • A basic understanding of types of relationships in relational DBMS
  • MySQL/MariaDB DBMS installed in your computer (You can use MySQL bundles in XAMPP).

The application we will build in this tutorial

Think of a fictitious application (blog app), which allows a user to create a blog post. A post has tags and that one tag can appear in different posts. This application can best be modelled using the ERD shown below. If you get stuck while following this tutorial, a complete source code is available in this GitHub repository.


An ERD modelling a sample application

It can be observed that a user can add many posts and a post can have many tags. Additionally, one tag can appear in many different posts.

What is Node?

We know that for a long time, JavaScript code could only run in the browser and not in the server.

However, with node,  JavaScript can run in a server environment.

That is why you have been hearing that node is an environment, which enables JavaScript to run in a server environment. Now you know, Node is not a framework, but an environment.

You’ve heard of Google’s V8 (built by Google to run JavaScript in Google Chrome browser)? Well, it is the engine that was extended to create Node.

Installing Node

For you to write node applications, you must have node installed on your computer. Download node from here and install it. The video below should guide you if you run into trouble.

How to Install Node JS in Windows 10

Remember that NPM (Node Package Manager) will be installed along with Node. If you have never used NPM or heard of it, the following 2-minute video will enable you to understand the use of NPM.

What is NPM? 

Initialize a node application and install needed packages

Create a directory (I will call it decodedapp) anywhere in your machine and navigate into it with cmd.

If you’re using VS Code, and it is open, click on the Terminal menu, then New Terminal to pull the terminal from the lower part of the editor.

Run npm init

npm init

You will be asked some questions such as the name of the app, version, description, entry point of the app, test command etc… You can input your own details but you will notice that npm already has suggestions for you (the suggestions are put in brackets). Just hit enter to accept the suggestions.

Once you are done, your directory will have one file as shown

decodedapp
    -package.json

The package.json file contains details entered above plus it is going to have information about the packages, which we shall install using npm.

Notice that the entry point of our application is index.js.

Installing the packages needed from npm

There are two types of packages we shall install

  1. Packages required both during development and in production
  2. Packages required only during development

Run the following command to install 6 packages needed both during development and production. Notice the –save option. The names of packages are separated by space.

npm install express body-parser faker mysql2 sequelize dotenv --save
Packages needed both during development and production. Note that you may not need faker in the production environment.

Run the following command to install 2 packages only needed during development. Notice the –save-dev option. Additionally, option -g means that packages have been installed globally and thus are available to any project created on your computer.

npm install -g nodemon sequelize-cli --save-dev

The functions/use of the installed packages are as follows:

  • express – it is a node framework, which does some heavy lifting for you, thus making your work easier when handling HTTP requests.
  • sequelize –  it is a powerful JavaScript Object-Relational Mapper (ORM) for node applications. Sequelize provides easy access to MySQL, MariaDB, SQLite, PostgreSQL etc…
  • mysql2 – it is the MySQL DBMS drive, which sequelize uses to access the MYSQL database. Sequelize requires mysql2 to be installed for it to work.
  • body-parser – it is an HTTP request wrapper that enables easier extraction of data sent in an HTTP request.
  • faker – it is a package, which enables one to generate massive data for testing purposes. It is useful, especially in API design.
  • dotenv – used to load environment variables from a .env file into process.env thus helps in storing configuration in an environment separate from the code. See The Twelve-Factor App methodology.
  • nodemon – it is used to automatically restart the node server when files have changed so that the developer does not have to restart the server manually every time changes are made in the source code.
  • sequelize-cli – this is a Command Line Interface for sequelize. It requires sequelize to be installed for it to work.

Bootstrapping node js app structure using sequelize-cli

Now that sequelize-cli is installed, run the following command to create an empty project.

npx sequelize-cli init

Resultant project directory structure:

decodedapp
   -config
     -config.json
   -migrations
   -models
     -index.js
   -node_modules
   -seaders
   -package.json
   -package-lock.json

Setting up config variables and Creating Database

An application’s config are variables, which are likely to vary between deploys i.e. production, staging, production etc.. and in our case, it includes the following:-

  • server port number
  • database name.
  • database user
  • database user password
  • database server

Some of these variables are contained in the config.json file.

The dotenv package we installed earlier will help us in this.

Proceed as follows:

  1. Create a file .env in project’s root directory
decodedapp
  -config
    -config.json
  -migrations
  -models
    -index.js
  -node_modules
  -seaders
  -.env
  -package.json
  -package-lock.json

2. Add the following contents into the .env file

DB_USERNAME=root
DB_NAME=decoded
DB_HOST=localhost
DB_PASSWORD= 
API_PORT=3000
//Change the values above to suit your database and server port environment
.env file contents. Normally, if you are using git, this file is ignored. Another environment needs to set up this file with values relevant to that environment. 

3. Rename the config.json to config.js and change its contents as follows

require('dotenv').config();
module.exports =  {
    "development": {
      "username": process.env.DB_USERNAME,
      "password": process.env.DB_PASSWORD,
      "database": process.env.DB_NAME,
      "host": process.env.DB_HOST,
      "dialect": "mysql"
     },
     "test": {
         ....
     },
     "production": {
         ....
     }
} 

It can be observed that the config.js file will read values as set in the .env file. For it to work, you must import the dotenv package as seen in the first line of that file.

require('dotenv').config();
Shows how to import dotenv into config.js 

4. Open index.js found inside the model’s folder and change the following line from:

const config = require(__dirname + '/../config/config.json')[env];

to:

const config = require(__dirname + '/../config/config.js')[env];

Notice that we changed config.json to config.js

At this point, our app can connect to the MySQL database (see dialect in config.js), but you can use a different database such as PostgreSQL if you wish.

All you need to do is create a database by name decoded  (you can use a database name of your choice) in your DBMS or run the command below from your terminal to create the database specified in your .env file.

npx sequelize db:create

This command creates the database you specify in .env file so you will not have to create it yourself.

Creating sequelize models

We are going to create User, Post and Tags models (remember the ERD we created earlier) using sequelize-cli.

Creating User model:

npx sequelize-cli model:generate --name User --attributes 'name:string, email:string'

Notice that in the attributes, you include the column name and the Data Type. More sequelize data types explained here.

When sequelize model is created two files are created; one in the models folder and the other in the migrations folder.

The file in the model’s folder stores the structure of the model whereas the one in migrations is the one that will be executed to create the table in your database.

We must also indicate the relationship of a model to another model inside the associate function (see code below).

Also, notice the naming format of the migration file (20200729005825-create-user.js). The first part (made up of digits) is a timestamp. It is also worth noting that migrations files will appear in the order in which they were created and will be executed in that order when migration is run.

If you run a migration for a table with a foreign key before a table with the corresponding primary key, you will run into an error!

Additionally, there are three columns that were added automatically i.e. id as a primary key added in the model and migration file and createdAt and updatedAt added in the migration file as timestamps showing when a table record was inserted and updated respectively.

The contents of user migration and model files are as shown:

For each model, a migration file is created.

It is also worth noting that, you can add a constraint to a column. For instance, I can make an email column to be unique. See more sequelize validations and constraints.

email: {
    type: Sequelize.STRING,
    unique: true,
    allowNull: false
},
Sequelize Validations & Constraints e.g. uniqu

Creating a Post model:

npx sequelize-cli model:generate --name Post --attributes 'post_title:string, post_text:text, status:boolean, userId:integer'

The post.js model file will have the following contents:

Creating the Tag model:

npx sequelize-cli model:generate --name Tag --attributes 'tag_text:string'

The tag.js model file will have the following contents:

Creating PostTag model:

npx sequelize-cli model:generate --name PostTag --attributes 'post_id:integer,tag_id:integer'

The posttag.js model file will have the following contents:

Why are awe creating PostTag model and yet it does not appear in or ERD?

Well, the type of relationship between User and Post is one-to-many, hence, in User, we indicate hasMany and in Post we indicate belongsTo.

However, the type of relationship between Post and Tag is a many-to-many. It is not possible to model this relationship and we must create an extra model (PostTag), which only contains the primary keys from Post and Tag. In the association function of both models (Post and Tag), we use belongsToMany, through the model, PostTag, using the respective foreign keys.

For instance, inside the associate function of the Post model, you see this:

Post.belongsToMany(models.Tag,{
    through: models.PostTag,
    foreignKey:'tag_id'
});

Creating sequelize seeders and seeding database tables

When designing APIs, it will not make sense to spend several hours inputting data into your database tables for testing purposes. Sequelize allows you to create files, which can be used to input massive data into your tables with a single command.

To generate massive data, we can use libraries built for this purpose. In this tutorial, we will use faker package to achieve this (we already installed it).

Let’s follow the following steps to seed the user’s table and posts table.

You will notice that if you do not change anything, sequelize model names are in singular (e.g. Post), their names in migration files and seeders are in plural (e.g. Posts) and names of tables in the database are lower case and in plural (e.g. posts)

  1. Create a seeder file for User
npx sequelize-cli seed:generate --name demo-users

This command creates a file in the seeders folder, that looks like the one below.

'use strict';
module.exports = {
     up: async (queryInterface, Sequelize) => {
         /**
         * Add seed commands here.
         *
         * Example:
         * await queryInterface.bulkInsert('People', [{
         *   name: 'John Doe',
         *   isBetaMember: false
         * }], {});
         */
     },
     down: async (queryInterface, Sequelize) => {
         /**
         * Add commands to revert seed here.
         * 
         * Example:
         * await queryInterface.bulkDelete('People', null, {});
         */
     }
};

It can be observed that the file has two parts, the up part responsible for bulky insert and the down part responsible for bulky delete. There is also an example provided to help you understand how it works. People is the name of the table where data is to be inserted and the second parameter is the data to be inserted (formatted as an array of objects, where the object properties are the columns of your table). Additionally, you can see that the example will only insert one record! But we need many.

We need to change the contents of the file to be able to insert, say, 100 records in our user’s table. See the code below.

You can observe that in line 2, we import faker package, then we create array users in line 5, which will hold 100 objects as seen in line 6 – 11 logic. The user’s array is what we supply to the bulkInsert method.

2. Create a seeder file for Post

The posts table has a foreign key from the users table. Thus, we need to insert in posts values that are only found in users’ id columns. This will answer the question, How to seed a sequelize table with a foreign key using faker?.

Run this command to create a seeder table for the posts table.

 npx sequelize-cli seed:generate --name demo-post

Change the contents of the seeder table as shown below:

You can observe that the only new thing in this seed files is that, in lines 6 and 7, we write a query to read all id in the users table, then pick from it randomly as seen in line 15. If you tried to insert an id not present in users’ table, it will fail because of referential integrity constraints.

You should’ve guessed correctly that when we run the seeder files, users seed has to run first then post’s seed file.

Something else worth noting is the use of async and await. Because node is non-blocking, we do not want the code execution to continue before we get all the user ids in line 6. Hence we use await in line 6 and as a result, async has to be put in the function starting at line 4.

If you have understood this, you can seed tags and posttags tables yourself.

3. Seeding the tables

Now that the seeders table are ready, run the command below and you will have the needed data in your database tables.

npx sequelize-cli db:seed:all  

N/B: If you find that the data you get from fake data is not appropriate, you can seed your tables by reading from an external file. Find out how to read a text files line-by-line here.

If you want to run specific seed files, sequelize-cli provides options you can append to the above command to achieve that as shown below.

npx sequelize-cli db:migrate --from starting-file-is-not-included.js --to another-file.js

The above command means that the file where you start from (i.e. starting-file-is-not-included.js) will not be included in the seeding operation.

Creating application Controllers

Now that our models are ready, it is time to think about our controllers (I believe you at least know something about MVC design pattern).

Express controller action methods listen and receive http requests from clients and then responds to clients after processing.

Let us see how we can create a controller with an action method, which receives user details and then inserts them into the users table. Lets us follow these steps:

  1. Create a folder, controllers, and add a file userControllers.js.
decodedapp
   -config
      -controllers
         -userController.js
   -migrations
   -models
   -node_modules
   -seaders
   -.env
   -package.json
   -package-lock.json

2. Add the following code in userControllers.js

const model = require('../models');

exports.newUser = (req, res, next) =>{
   let name = req.body.name;
   let email = req.body.email;

   model.User.create({
       name: name,
       email:email
   }).then (result=>{
       res.status(200).send(result);
   }).catch(error=>{
       req.status(500).send("An error occured");
   });
}

We have imported all models to this file and it can be observed that there is a special method on the User model, create, which receives an object, which has the data to be stored in the database table.

The then block will have the results of an execution (in this case the record, which was inserted) when everything is successful whereas the catch block will be executed when an error is encountered and the error information will be contained in the error variable.

Did you notice that we used JavaScript arrow functions?

Another observation is the newUser function is exported because we shall need it in the routing file.

Additionally, notice how we extract the data sent in the request body. In this case, the data is sent in form of form-data or x-www-form-urlencoded.

let name = req.body.name;

Note that data can reach your application as JSON and as part of the URL or as URL query string.

If the data you need is part of the url e.g. /user/3/posts, and that you name 3 as id in your url, then receive it as follows:

let userId = req.params.id

On the other hand, if the data is part of a URL query string (e.g. /user?id=3), then receive it as follows:

let userId = req.query.id

It is advisable to maintain a controller for each model

Creating Express application routing

Routing refers to how an application’s endpoints (or URIs) respond to client requests.

Matching and handling incoming requests can be done using routes in a separate file.

Let us use the following steps to add routing to our application:

  1. Create a folder, routes, and in it, create a file userRoutes.js
decodedapp
   -config
   -controllers
   -migrations
   -models
   -node_modules
   -routes
      -userRoutes.js
   -seaders
   -.env
   -package.json
   -package-lock.json

2. Add the following code in userRoutes.js

In line 3, we import the controllers so that the action methods are available in the routes file as shown in line 7 (see the method newUser from the userController.js).

A ‘POST’ request from a url ‘/user’, will match this route and the request will be processed by newUser method in the userController.js. In this file, you can add all routes related to the User model.

Creating Application Entry File (index.js) and Starting Express/Node Server

To test our first route, we will need to create an entry point file for this application. This is where we start the node server from so that we can listen to incoming HTTP requests.

Remember when we created our node app, we specified that the entry to our app is index.js and this information can be found inside package.json file.

"main": "index.js",

Proceed as follows:

  1. Create index.js in the project root directory
decodedapp
   -config
   -controllers
   -migrations
   -models
   -node_modules
   -routes  -seaders
   -.env
   -index.js
   -package.json
   -package-lock.json

2. Add the following code in index.js file.

Note the following:

  • In line 2, we import the route files
  • adding the body-parser before the routes
  • In line 7, the express server listens to the port we set in .env file (in this case port 3000)

3. Start the server

nodemon index

The express/node server is now running on port 3000 and is listening to any incoming http requests.

Sending our first HTTP request to the express/node server

At this point, we have a route, a controller action method and our server is running.

Using Postman, we can construct our http request with the following characteristics:

URL: http://localhost:3000/user
Method: POST 
headers: No modification needed
body:
    type: form-data or x-www-form-urlencoded
    data: key value pair
    name: Derdus
    email: axy@gmail.com	

See below; the request was successful and the response is the record that was inserted in the database table. You can check in your user’s table to see that a record was indeed inserted.

Testing an insertion operation in the users table

You could read and delete records from tables using findAll and destroy methods respectively on sequelize models. Read more on sequelize documentation.

Running Database Transactions with sequelize in Express/Node Js

A good example of thinking about database transactions is when you want to insert data into multiple tables simultaneously and all the operations must be successful or if any of them fails, all must fail – all or nothing property. You can read more about database transactions here or here.

More often, this happens if the data to be inserted are related in some way. In other words, all the queries must belong to the same transaction.

In our case, if we were to add a post, then we end up posting data in 3 different tables in which case all the insertions must be successful. Otherwise, the data will be erroneous.

The tables, which will be affected are:

  • posts – it will take userId, post_title, post_text and status
  • tags – it will take tag_text
  • posttags –  it will take post_id and tag_id

Proceed as follows:

  1. Add the following controller action method (newPost) in userController.js file as shown.
Controller action method for adding a new post

The following can be observed from the function:

  • t = await model.sequelize.transaction(); marks the start of our transaction
  • t is passed as a parameter to any query, which must belong to this transaction
  • If all the queries are successful, we commit t.commit(); inside the try block.
  • otherwise, we rollback in the catch block with, t.rollback();
  • Since we must have post_id and tag_id before inserting a record in posttags table, we must wait to receive results from postResult and tagResult, hence the use of await keyword, and as a result, our newPost function must have the async keyword.

2. Add the route below in userRoutes.js file

route.post('/user/:userId/post', userController.newPost);

3. Start node/express server from the terminal (or VS Code terminal)

nodemon index

4. Test your code by sending required data using Postman client

We construct a postman request with the following characteristics

  • Method: POST
  • URL: http://localhost:3000/user/203/post (203 is a user id, which must be valid i.e. present is your users table)
  • headers: no modification required
  • body: post_title, post_text, status, tag_text
  • body type: form-data or x-www-form-urlencoded

See the diagram below:

Lastly, reading a user post with its tags

How do we read data in a many-to-many relationship with sequelize?

To read one post belonging to a user, together with its tags, follow the following procedure:

  1. Add the following router in the userRoutes.js.
route.get('/user/:userId/post',userController.getPost);

2. Add the following controller action method in userController.js

Notice the use of the findOne method, but we could use findAll to fetch all the records that match the criteria.

If you do not need all the column data, you can specify the columns to be returned by adding attributes property.

3. Start your node/express server

nodemon index

4. Test your code by sending a request with the following characteristics:

  • URL: http://localhost:3000/user/1/post (replace 1 with a valid user id in your users’ table)
  • Method: GET
  • headers: no modification needed
  • body: not body data needed

See the postman interface below.

And we are done!

I hope you enjoyed reading this walk-through and implementing the code.

If you have encountered errors, which you cannot figure out, find the complete code from the GitHub repository.

Please leave comments or questions.

See you in the next tutorial.  

6 Shares:
You May Also Like