Setting up Express JS REST API, Postgres, and Sequelize ORM with ES6+

Developing with express js (node js), Postgres and Sequelize ORM has been fun for me. I enjoy working with ORMs such as Sequelize as it aids me in building projects faster and efficiently. I want to share with you how I set up my Express js project with Postgres and Sequelize to code in ES6 and above.

This tutorial assumes you have a fair knowledge of the JavaScript language.

Setup

To begin, install Node JS on your computer. It comes with a package manager which you'll use for command-line operations.

• Create a folder where your project will reside, you can give it any name you want. we'll call it node-project

• Open your command line and change directory into it.

cd node-project

• Initialize a NodeJS application by running the following command:

npm init -y

This will create a package.json file with a basic config. You can manually input the configuration by omitting the -y flag.

Express JS Setup

To install express js, run npm i express on your command line within the project folder.

• Create an index.js file.

• Add the following codes in the newly created index.js file
  

const express = require('express');

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Create a catch-all route for testing the installation.
app.get('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})

• Run node ./index.js on the terminal
• Visit http://localhost:5000 on your browser to view your express API server. Any route will display the same welcome message because of the catch-all route we created.

When we make changes, we'll have to kill the process and restart to see the effect. So, we'll install a package that will save us that stress: Nodemon.

• Run npm i -D nodemon

• Edit scripts in package.json file to look like this:
  

"scripts": {
    "dev": "nodemon index.js"
  },

Kill your current process and run npm run dev to start the server. Going forward, when you start the server this way, you won't need to restart it to see changes.

The folder structure should look like this:

node-project
├── node_modules
├── index.js
├── package.json
├── package-lock.json

Babel Setup for ES6

To code in ES6 and above, you need to install babel packages,
Run the following command to install the necessary packages for our project:

npm i @babel/core @babel/node @babel/preset-env

• Create a .babelrc file in the folder and populate with the following:

{
  "presets": [
    [
      "@babel/preset-env", {
        "targets": {
          "node": "current"
        }
      }
    ]
  ]
}

• Edit the package.json file script command to use babel

 "scripts": {
    "dev": "nodemon --exec babel-node index.js"
  },

The package.json file should look like this:

{
  "name": "project",
  "version": "1.0.0",
  "description": "",
  "main": "index.js",
  "scripts": {
    "dev": "nodemon --exec babel-node index.js"
  },
  "author": "",
  "license": "ISC",
  "dependencies": {
    "express": "^4.17.1",
    "@babel/core": "^7.12.10",
    "@babel/node": "^7.12.10",
    "@babel/preset-env": "^7.12.11"
  },
  "devDependencies": {
    "nodemon": "^2.0.7"
  }
}

The index.js file should look like this:

import express from 'express';

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

// Create a catch-all route for testing the installation.
app.get('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})

• Restart your server and you are ready to code your node js API with ES6 syntax

Sequelize Setup in Express JS App

We will be using Postgres DB in this tutorial but you can use any DB you are comfortable with such as MySQL, SQLite, etc.

To get started with Sequelize ORM with Postgres, you need a Postgres DB which could be remote or on your local machine. Visit this link to install Postgres on your computer.

To use Sequelize in your node app with Postgres, you can install the Command Line Tool (CLI) package globally on your computer or in the app. You just need to know how to call it.

In this tutorial, we will install it globally and in the project as well.

Run the command to install it locally and globally

npm install -g sequelize-cli

npm install sequelize-cli

Depending on which approach you choose, if globally installed, you will always make the command as sequelize do:something, if local, it'll be ./node_modules/.bin/sequelize inside the project folder.

• Install Sequelize and Postgres packages by running the command:

npm i sequelize pg

• Install babel to work with sequelize

npm i @babel/register

• Create .sequelizerc file and populate with the following configuration

require("@babel/register");

const path = require('path');

module.exports = {
  "config": path.resolve('./src/config', 'config.json'),
  "models-path": path.resolve('./src/models'),
  "seeders-path": path.resolve('./src/database/seeders'),
  "migrations-path": path.resolve('./src/database/migrations')
};

The sequelizerc file contains how the folders for Sequelize will be organized. If you don't have it, it'll still work but everything will be placed in the root folder. The configuration will be in src/config, Sequelize models will reside in src/models folder, while seeders file and migration will be in src/database folder.

• Next, we initialize Sequelize in the project.

sequelize init

The command creates the necessary folders and files for Sequelize ORM.

• If you look at src/models/index.js, it is not written in ES6. Let's refactor that and it'll become:

import fs from 'fs';
import path from 'path';
import Sequelize from 'sequelize';
import enVariables from '../config/config.json';

const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = enVariables[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
  sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
  sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs
  .readdirSync(__dirname)
  .filter(file => (file.indexOf('.') !== 0) && (file !== basename) && (file.slice(-3) === '.js'))
  .forEach(file => {
    // eslint-disable-next-line global-require,import/no-dynamic-require
    const model = require(path.join(__dirname, file)).default(sequelize, Sequelize.DataTypes);
    db[model.name] = model;
  });

Object.keys(db).forEach(modelName => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

export default db;

• Create a DB and update the config/config.json file accordingly:

{
  "development": {
    "username": "postgres",
    "password": "password",
    "database": "node-project",
    "port": "5434",
    "host": "127.0.0.1",
    "dialect": "postgres"

  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

All is now set to create models and migration.

Let's create a model and migration for users.

Run the command:

sequelize model:generate --name User --attributes name:string,email:string,phone:string,password:string,status:string,last_login_at:date,last_ip_address:string

This command creates a User model and migration table in the corresponding folders. The attributes are the fields we want to have on the table.

The user model looks like this:

'use strict';
const {
  Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    phone: DataTypes.STRING,
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

And migrations like this:

'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      phone: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

Let's refactor the generated migration and model to:

import { Model } from 'sequelize';

export default (sequelize, DataTypes) => {
  class User extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: DataTypes.STRING,
    phone: DataTypes.STRING,
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

and

export default {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        type: Sequelize.STRING
      },
      phone: {
        type: Sequelize.STRING
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

I like renaming the model name to use uppercase first and in some cases camel case.

Since it's a user model we created and we have some protected fields, I'll quickly add a method to hide the fields on JSON response.

Let's add some validations to the fields for user authentication with node js (express js).

The model and migration will now look like this:

import { Model } from 'sequelize';

const PROTECTED_ATTRIBUTES = ['password'];

export default (sequelize, DataTypes) => {
  class User extends Model {
    toJSON() {
      // hide protected fields
      const attributes = { ...this.get() };
      // eslint-disable-next-line no-restricted-syntax
      for (const a of PROTECTED_ATTRIBUTES) {
        delete attributes[a];
      }
      return attributes;
    }
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate(models) {
      // define association here
    }
  };
  User.init({
    name: DataTypes.STRING,
    email: {
      type: DataTypes.STRING,
      allowNull: {
        args: false,
        msg: 'Please enter your email address',
      },
      unique: {
        args: true,
        msg: 'Email already exists',
      },
      validate: {
        isEmail: {
          args: true,
          msg: 'Please enter a valid email address',
        },
      },
    },
    phone: {
      type: DataTypes.STRING,
      unique: true,
    },
    password: DataTypes.STRING,
    status: DataTypes.STRING,
    last_login_at: DataTypes.DATE,
    last_ip_address: DataTypes.STRING
  }, {
    sequelize,
    modelName: 'User',
  });
  return User;
};

and

export default {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      name: {
        type: Sequelize.STRING
      },
      email: {
        allowNull: false,
        unique: true,
        type: Sequelize.STRING,
      },
      phone: {
        type: Sequelize.STRING,
        unique: true,
      },
      password: {
        type: Sequelize.STRING
      },
      status: {
        type: Sequelize.STRING
      },
      last_login_at: {
        type: Sequelize.DATE
      },
      last_ip_address: {
        type: Sequelize.STRING
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  }
};

We have to refactor any model and migration we create in the future to look like this.

• Next, we run the migration to create the DB tables:

sequelize db:migrate

Our database is now created.

Route and Controllers

Let's create route and controllers to be able to interact with our database.

• Create a controllers folder in src and add AuthController.js file to it. That's where our user management logic will reside.

Add the following code to create user sign up controller logic:

import { Op } from 'sequelize';
import model from '../models';

const { User } = model;

export default {
  async signUp(req, res) {
    const {email, password, name, phone} = req.body;
    try {
      const user = await User.findOne({where: {[Op.or]: [ {phone}, {email} ]}});
      if(user) {
        return res.status(422)
        .send({message: 'User with that email or phone already exists'});
      }

      await User.create({
        name,
        email,
        password,
        phone,
      });
      return res.status(201).send({message: 'Account created successfully'});
    } catch(e) {
      console.log(e);
      return res.status(500)
      .send(
        {message: 'Could not perform operation at this time, kindly try again later.'});
    }
  }
}

Ideally, you will have to encrypt (hash) the user's password before storing it in the DB.

Create the route folder with index.js file inside and add the following code:

import AuthController from '../controllers/AuthController'

export default (app) => {
  app.post('/register', AuthController.signUp);

// Create a catch-all route for testing the installation.
app.all('*', (req, res) => res.status(200).send({
  message: 'Hello World!',
}));
};

Notice that we have transferred the first route we created here.
Our top level index.js file will now look like this:

import express from 'express';
import route from './src/routes'

const app = express();

app.use(express.urlencoded({ extended: true }));
app.use(express.json());

route(app);

const port = 5000;

app.listen(port, () => {
  console.log('App is now running at port ', port)
})

Restart the server and use postman to test the API.

That's basically how I setup Express JS REST API, Postgres, and Sequelize ORM with ES6+.

See the code on github here.

If you have any concerns, kindly raise them in the comment section below.

I wrote this tutorial as a foundation for an article I want to publish on how to implement dynamic Role-based Access Control (RBAC) in Express JS where an admin can assign multiple roles to a user and a role can have multiple permissions.

Update: How to implement dynamic Role-based Access Control (RBAC) in Express JS REST API

Comments

[Iyere Handsome(Beau) Omogbeme]

I have gotten an error whenever "import postRoute from './routes/postRoutes.js'" I get a lengthy error node:internal/modules/cjs/loader:585
throw e;
^

Error: Package subpath './types/query-types' is not defined by "exports" in /home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/sequelize/package.json
at new NodeError (node:internal/errors:399:5)
at exportsNotFound (node:internal/modules/esm/resolve:266:10)
at packageExportsResolve (node:internal/modules/esm/resolve:597:13)
at resolveExports (node:internal/modules/cjs/loader:579:36)
at Function.Module._findPath (node:internal/modules/cjs/loader:648:31)
at Function.Module._resolveFilename (node:internal/modules/cjs/loader:1076:27)
at Function.Module._load (node:internal/modules/cjs/loader:934:27)
at Module.require (node:internal/modules/cjs/loader:1157:19)
at require (node:internal/modules/helpers:119:18)
at Object. (/home/beau/Desktop/Sequelize Projects/Project_2/backend/models/taskmodel.js:3:1)
at Module._compile (node:internal/modules/cjs/loader:1275:14)
at Module._compile (/home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/pirates/lib/index.js:136:24)
at Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
at Object.newLoader as .js
at Module.load (node:internal/modules/cjs/loader:1133:32)
at Function.Module._load (node:internal/modules/cjs/loader:972:12)
at Module.require (node:internal/modules/cjs/loader:1157:19)
at require (node:internal/modules/helpers:119:18)
at Object. (/home/beau/Desktop/Sequelize Projects/Project_2/backend/controllers/taskControllers.js:1:1)
at Module._compile (node:internal/modules/cjs/loader:1275:14)
at Module._compile (/home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/pirates/lib/index.js:136:24)
at Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
at Object.newLoader as .js
at Module.load (node:internal/modules/cjs/loader:1133:32)
at Function.Module._load (node:internal/modules/cjs/loader:972:12)
at Module.require (node:internal/modules/cjs/loader:1157:19)
at require (node:internal/modules/helpers:119:18)
at Object. (/home/beau/Desktop/Sequelize Projects/Project_2/backend/routes/taskRoutes.js:2:1)
at Module._compile (node:internal/modules/cjs/loader:1275:14)
at Module._compile (/home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/pirates/lib/index.js:136:24)
at Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
at Object.newLoader as .js
at Module.load (node:internal/modules/cjs/loader:1133:32)
at Function.Module._load (node:internal/modules/cjs/loader:972:12)
at Module.require (node:internal/modules/cjs/loader:1157:19)
at require (node:internal/modules/helpers:119:18)
at Object. (/home/beau/Desktop/Sequelize Projects/Project_2/backend/server.js:13:1)
at Module._compile (node:internal/modules/cjs/loader:1275:14)
at Module._compile (/home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/pirates/lib/index.js:136:24)
at Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
at Object.newLoader as .js
at Module.load (node:internal/modules/cjs/loader:1133:32)
at Function.Module._load (node:internal/modules/cjs/loader:972:12)
at Function.runMain (node:internal/modules/run_main:83:12)
at Object. (/home/beau/Desktop/Sequelize Projects/Project_2/backend/node_modules/@babel/node/src/_babel-node.ts:223:12)
at Module._compile (node:internal/modules/cjs/loader:1275:14)
at Object.Module._extensions..js (node:internal/modules/cjs/loader:1329:10)
at Module.load (node:internal/modules/cjs/loader:1133:32)
at Function.Module._load (node:internal/modules/cjs/loader:972:12)
at Function.executeUserEntryPoint as runMain {
code: 'ERR_PACKAGE_PATH_NOT_EXPORTED'
}
what does this errors mean ?

[Iyere Handsome(Beau) Omogbeme]

This article is currently helping me and I am enjoying it although the your migrations and mine are going to be different tho.

[Mohamed Shamoon]

Can you explain why not using jwt?

[Imo-owo Nabuk]

It's beyond the scope of the article.

I didn't want to go into authentication and authorization.

I wrote an article that covers authentication and authorization