Building RESTful APIs with Node, Sequelize & PostgresQL

Ifyou’ve landed here, hopefully you’re looking for help getting data flowing from a Node.js and Express web application, to a PostgreSQL database with Sequelize as the O.R.M. This guide will assist you with setting up a Node.js project, and installing Sequelize to allow for object relational database mapping between a Postgres database.

What is a ORM?

The simplest way to think of Object Relational Mapping is as a process for accessing a relational database — Postgres in our instance — from an object-oriented language like JavaScript. Sequelize allows us to interact with a Postgres database using JavaScript instead of SQL.

“Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server.” — sequelize.org

While my goal is to help you configure Sequelize with Postgres, in the process we will be building a RESTful API. Once we have data flowing properly, you can expand on this app’s build however you’d like — adding more routes or developing the front-end.

Those of us who have only dabbled with the back-end, may have done a few tutorials using a non-relational database like MongoDB. And I can attest I’ve learned a ton by using MongoDB with Node.js — seeing data flowing and testing endpoints with insomnia. Postgres itself is an “object-relational database system that uses and extends the SQL language,” according to the official website. Its language is designed to perform CRUD (Create, Read, Update, Delete) operations on a database, it scales vertically and can perform complex queries across multiple tables of data. Without Sequelize, we’d have to execute SQL statements in order for us to interact with Postgres and perform CRUD on our databases. Sequelize handles this translation for us, allowing us to interact with a Postgres database using JavaScript instead of SQL.

We need to tell Sequelize CLI how to connect to the database. To make that happen, open the default config file. Add the dotenv requirement to the top of the file, and destructure your .env variable names from process.env

const express = require("express");
const bodyParser = require("body-parser");
const cors = require("cors");

const app = express();

var corsOptions = {
  origin: "http://localhost:8081"
};

app.use(cors(corsOptions));

// parse requests of content-type - application/json
app.use(express.json());

// parse requests of content-type - application/x-www-form-urlencoded
app.use(express.urlencoded({ extended: true }));

// simple route
app.get("/", (req, res) => {
  res.json({ message: "Welcome to your application!" });
});

// set port, listen for requests
const PORT = process.env.PORT || 8080;
app.listen(PORT, () => {
  console.log(`Server is running on port ${PORT}.`);
});

// config.js
require('dotenv').config();
const { DB_HOST, DB_USERNAME, DB_PASSWORD } = process.env;

Configure PostgreSQL database & Sequelize

In the app folder, we create a separate config folder for configuration with db.config.js file like this:

module.exports = {
  HOST: "localhost",
  USER: "postgres",
  PASSWORD: "123",
  DB: "testdb",
  dialect: "postgres",
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000
  }
};

First five parameters are for PostgreSQL connection.
pool is optional, it will be used for Sequelize connection pool configuration:

• max: maximum number of connection in pool

• min: minimum number of connection in pool

• idle: maximum time, in milliseconds, that a connection can be idle before being released

• acquire: maximum time, in milliseconds, that pool will try to get connection before throwing error

For more details, please visit API Reference for the Sequelize constructor.

Initialize Sequelize

We’re gonna initialize Sequelize in app/models folder that will contain model in the next step.

Now create app/models/index.js with the following code:

const dbConfig = require("../config/db.config.js");

const Sequelize = require("sequelize");
const sequelize = new Sequelize(dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD, {
  host: dbConfig.HOST,
  dialect: dbConfig.dialect,
  operatorsAliases: false,

  pool: {
    max: dbConfig.pool.max,
    min: dbConfig.pool.min,
    acquire: dbConfig.pool.acquire,
    idle: dbConfig.pool.idle
  }
});

const db = {};

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

db.tutorials = require("./tutorial.model.js")(sequelize, Sequelize);

module.exports = db;

Don’t forget to call sync() method in server.js:

...
const app = express();
app.use(...);

const db = require("./app/models");
db.sequelize.sync();

...

Define the first Model & Migration

In models folder, create tutorial.model.js file like this: To actually get data into our database, let’s create a model (table) for our database named User, and provide a few attributes (columns) — firstName, lastName, email.

module.exports = (sequelize, Sequelize) => {
  const User = sequelize.define("user", {
    firstName: {
      type: Sequelize.STRING
    },
    lastName: {
      type: Sequelize.STRING
    },
    email: {
      type: Sequelize.STRING
    }
  });

  return User;
};

This Sequelize Model represents users table in PostgreSQL database. These columns will be generated automatically: id, firstName, lastName, email, createdAt, updatedAt.

After initializing Sequelize, we don’t need to write CRUD functions, Sequelize supports all of them:

• create a new User: create(object)

• find a User by id: findByPk(id)

• get all Users: findAll()

• update a User by id: update(data, where: { id: id })

• remove a User: destroy(where: { id: id })

• remove all Users: destroy(where: {})

• find all Users by firstName: findAll({ where: { firstName: ... } })

Wrapping up

We went over how to create a RESTful API using Node.js and Express, which can transmit data to a Postgres database by utilizing Sequelize to translate JavaScript into equatable SQL commands.

From here, maybe you create more tables to really see how relational DB’s work, or build out pages that have forms gathering or verifying data. Imagine a form for creating a new account and another for logging in, that way you’ll be able to test the two-way process of storing and accessing data.

My hope is that this guide benefits both newcomers and experts. We all learn and grow uniquely from the wisdom our teachers & mentors provide. What’s important is that we continue ‘forever learning’. Happy coding!