DEV Community

Cover image for Setting up Express JS REST API, Postgres, and Sequelize ORM with ES6+
Imo-owo Nabuk
Imo-owo Nabuk

Posted on • Updated on

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
Enter fullscreen mode Exit fullscreen mode

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)
})
Enter fullscreen mode Exit fullscreen mode
  • 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"
  },
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Create a .babelrc file in the folder and populate with the following:
{
  "presets": [
    [
      "@babel/preset-env", {
        "targets": {
          "node": "current"
        }
      }
    ]
  ]
}

Enter fullscreen mode Exit fullscreen mode
  • Edit the package.json file script command to use babel
 "scripts": {
    "dev": "nodemon --exec babel-node index.js"
  },
Enter fullscreen mode Exit fullscreen mode

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"
  }
}

Enter fullscreen mode Exit fullscreen mode

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)
})

Enter fullscreen mode Exit fullscreen mode
  • 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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
  • Install babel to work with sequelize
npm i @babel/register
Enter fullscreen mode Exit fullscreen mode
  • 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')
};
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • 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"
  }
}

Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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');
  }
};
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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');
  }
};
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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');
  }
};
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.'});
    }
  }
}
Enter fullscreen mode Exit fullscreen mode

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!',
}));
};

Enter fullscreen mode Exit fullscreen mode

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)
})
Enter fullscreen mode Exit fullscreen mode

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

Top comments (4)

Collapse
 
beauspot profile image
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 ?

Collapse
 
beauspot profile image
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.

Collapse
 
mohashamoon profile image
Mohamed Shamoon

Can you explain why not using jwt?

Collapse
 
richienabuk profile image
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