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 step wise approach to setting up a node application with express framework using sequelize-cli. Specifically, we will address the following concerns;-

  • What is Node?
  • What is express JS?
  • How to setup 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.  
  • A basic knowledge of Postman.
  • A basic understanding 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 modeled using the the ERD shown below. If you get stuck while following this tutorial, a complete source code is available in GitHub repository.

An ERD modeling 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 extend to create node.

Installing Node

For you to write node applications, you must have node installed in your computer. Download node from here and install it. The video below should guide you if you ran 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 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 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 name of 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 production environment.

Run the following command to install 2 packages only needed during development. Notice the --save-dev option. Additionally,  the option -g means that packages have been installed globally and thus are available to any project created in 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 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 a 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 and environment separate from code. See the 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 need to setup 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 models 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, out app can connect to 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 the 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 models 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 a 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 constraint to a column. For instance, I can make 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, 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 spending several hours to input 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 the 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 users 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 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 users table. See the code below.

You can observe that in line 2, we import faker package, then we create an array users in line 5, which will hold 100 objects as seen in line 6 - 11 logic. The users 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 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 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 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 recorded 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.
exports.newPost = async (req, res, next) =>{
    let userId = req.params.userId;
    let post_title = req.body.post_title;
    let post_text = req.body.post_text;
    let status = true;
    let tag_text = req.body.tag_text;

    //t is a transaction
    let t;
    //a javascript object to hold the message to be sen to the client
    let message = {};
    try {
        //Start transaction 
        t = await model.sequelize.transaction();
        //insert a record in post, but results will not be saved permanently 
        const postResult = await model.Post.create({
            post_title:post_title,
            post_text:post_text,
            status:status,
            userId:userId
        },{
            transaction:t
        });
        //insert a record in tags, but results will not be saved permanently 
        const tagResult = await model.Tag.create({
            tag_text:tag_text
        },
        {
            transaction:t 
        });
        post_id = postResult.id;
        tag_id = tagResult.id;
        //insert a record in posttags, but results will not be saved permanently 
        const postTagResult = await model.PostTag.create({
            post_id: post_id,
            tag_id: tag_id
        },{
            transaction:t  
        });

        await t.commit();
        message ['message'] = 'Records added';
        res.status(201).json(message);
    } catch (error) {
        console.log(error);
        t.rollback(); 
        message ['message'] = 'Records not added';
        res.send(message);
    }
}
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 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 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 diagram below:

Lastly, reading a user post with its tags

How do we read data in a may-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

exports.getPost = (req, res, next) =>{
    let userId = req.params.userId;
    model.Post.findOne({
        where: {userId:userId},
        attributes: ['post_title','post_text','status'],
        include: [{
            model:model.Tag,
            as:'tags',
            attributes: ['id','tag_text']
        }]
    }).then (result=>{
        res.status(200).send(result);
    }).catch (error=>{
        console.log(error)
        res.status(500).send(error);
    });
}

Notice the use of 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 postman interface bellow.

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 GitHub repository.  

Please leave comments or questions.

See you in the next tutorial.  

You've successfully subscribed to Decoded For Devs
Welcome back! You've successfully signed in.
Great! You've successfully signed up.
Your link has expired
Success! Your account is fully activated, you now have access to all content.