Using Sequelize with ExpressJS and PostgreSQL

Today we will learn how we can connect to a PostgreSQL database from an ExpressJS application and How we can setup Sequelize ORM inside the project.

First we need a database to connect with. You can take help from some cloud provider like AWS RDS or we can spin up a local database instance.

We have talked about how we can spin up a local database instance with the help of docker easily in a previous article

Our starting docker-compose.yml file will look like this

version: "3"

services:
  database-layer:
    image: postgres
    container_name: database-layer
    environment:
      - POSTGRES_USER=dbuser
      - POSTGRES_PASSWORD=dbpassword
      - POSTGRES_DB=dbname

    volumes:
      - database-volume:/var/lib/postgresql/data
    ports:
      - "5432:5432"
    networks:
      - shared-network
    restart: on-failure
  express-typescript-boilerplate:
    depends_on:
      - database-layer
    environment:
      - NODE_ENV=development
    build:
      context: .
      dockerfile: Dockerfile.dev
    volumes:
      - ./:/usr/src/app
    container_name: express-typescript-boilerplate
    expose:
      - "4000"
    ports:
      - "4000:4000"
    command: npm run dev
    networks:
      - shared-network
    restart: on-failure
  adminer:
    image: adminer
    container_name: adminer-docker
    depends_on:
      - database-layer
    ports:
      - "8080:8080"
    networks:
      - shared-network
    restart: on-failure

volumes:
  database-volume:

networks:
  shared-network:

And put this file inside your express-project and run the following command.

docker-compose up

It will spin up a local database instance of postgres and the credentials are

username: dbuser
password: dbpassword
database name: dbname
host: database-layer

Why Sequelize?

There are multiple ORM for us to choose from. TypeORM is a good alternative. But in terms of feature Sequelize is best. It's also widely popular. So we are choosing this.

But we are not going to use the base package. As that doesn't provide enough support for Typescript. So we will use a special package named sequelize-typescript

First install that

yarn add sequelize-typescript

As we are using PostgreSQL so we need to install the client for that as well.

yarn add pg

Create a model for the database

Let's create a model for the User table in the database.

import { Table, Model, Column, DataType } from 'sequelize-typescript';

@Table({
  timestamps: false,
  tableName: 'users',
})
export class User extends Model {
  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  name!: string;

  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  email!: string;

  @Column({
    type: DataType.STRING,
    allowNull: true,
    defaultValue: '',
  })
  password!: boolean;
}

Configure the connection

Then create our connection for the database

import { Sequelize } from "sequelize-typescript";
import config from "../config/Config";
import { User } from "../models/User";

const connection = new Sequelize({
  dialect: "postgres",
  host: config.dbHost,
  username: config.dbUser,
  password: config.dbPassword,
  database: config.dbName,
  logging: false,
  models: [User],
});

export default connection;

Connect to database

Finally connect to database from the index.ts file

import connection from './services/SequelizeClient';

const startServer = async () => {
  try {
    dbClient = await connection.sync(); // See here!
    server = app.listen(PORT, (): void => {
      console.log(`Connected successfully on port ${PORT}`);
    });
  } catch (error: any) {
    console.error(`Error occurred: ${error.message}`);
  }
};

startServer();

If everything goes well you should have the connection to database up and running.

Create a repository

Let's isolate all our queries into the repository class

import { User } from "../models/User";

export default class UserRepository {
  createUser = async (name: string, email: string, password: string): Promise<User> => {
    const user = User.build({ name, email, password });
    return await user.save();
  };

  findByEmail = async (email: string): Promise<User | null> => {
    return await User.findOne({ where: { name: email } });
  };

  getAllUsers = async (): Promise<User[]> => {
    return await User.findAll();
  };
}

These are some examples that you can use quickly.

Github Repository

https://github.com/Mohammad-Faisal/professional-express-sequelize-docker-boilerplate

Using Sequelize with ExpressJS and PostgreSQL

Why Sequelize?

Create a model for the database

Configure the connection

Connect to database

Create a repository

Github Repository

Resources:

Share this post

Read more articles...

Express and Database with Docker for Local Development

Dockerize a Express-Typescript Application

Request Validation in ExpressJS

Who I am