Using Sequelize and PostgreSQL in a Node.js app

In this tutorial, you will connect your Node.js app to a PostgresQL database using Sequelize as an ORM.

You will have an overview on

What's relational databases?
What's PostgreSQL?
What's an ORM?
What's Sequelize?

What you need for this tutorial

A computer, obviously
Node.js installed on your computer
PostgresQL

What is a relational database?

A relational database is a type of database(where data is stored) that organises data into tables and create a relationship between one or more tables.

For example, A course has relationships with a teacher and the students. Relational databases include the popular MYSQL, SQL Server, POSTGRESQL and others. For this article, we will be using POSTGRES, a flavour of SQL

What is PostgreSQL?

PostgreSQL is an open-source relational relational database management system.

What is an ORM?

ORM is an acronym for Object Relational Mapping. ORMs allow you to query your database in a programming language of your choice(in this case, Javascript) without writing pure SQL queries.

ORMs make things better without wondering about SQLishh queries. However, It is not a bad idea to have a hang of SQL.

To connect a Node.js app to a SQL database, there are different ORMS you can use including Prisma, Sequelize etc.

For this article, we will be using Sequelize. It supports Postgresql, SQL etc

What is Sequelize?

According to the docs,

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more.

Let's get started!

Step 1 - Creating a project directory for your work

Create a folder for your project, if you don't have one yet.

mkdir postgrestut

Initialise a node project by running npm init --y to create package.json. Follow the prompt

npm init --y

Step 2 - Installing the needed packages

You need to install some packages as dependencies. Run

npm install dotenv pg pg-hstore sequelize

A brief explanation of the packages

dotenv is to secure your environment variables like password, connection string etc.

pg is a Node.js client for PostgreSQL

pg-hstore is a node package for serializing and deserializing JSON data to hstore format

sequelize is a Node.js ORM for relational databases.

As extras, you need to install some devDependencies

npm install sequelize-cli --save -dev

sequelize-cli is a command line interface to use sequelize.

Step 3 - Creating a new project in Sequelize

To create a new project in Seqelize, you will need to run the init command

npx sequelize-cli init

This will create the following folders in your project directory

Config
Models
Migrations
Seeders

Step 4 - Configuring the Database

We need to configure how to connect our app to Postgres Database. Open the config.json file in the config folder. You should have something similar to this

{
  "development": {
    "username": "postgres",
    "password": "",
    "database": "postgrestut_dev",
    "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"
  }
}

For now, you should only make changes to development. Put in your own database details. Since we are using Postgres, change the dialect to postgres

To create the database, run

npx sequelize-cli db:create

Step 5 - Creating Models

You will create a Model for User with attributes username, first name, last name and email with the associated data types Run

npx sequelize-cli model:generate --name User --attributes userName:string,firstName:string,lastName:string,email:string

This will create the model and migration file for User in the models and migration folders respectively. PS: It is the migration file that hits the database, hence to see the User table in the DB, run

npx sequelize-cli db:migrate

Step 6 - Seeding your Database

You can populate your database with dummy data for the User Table by running

npx sequelize-cli seed:generate --name User

This will create a file in the seeders folder.
Make changes in the seed file to look like this

To commit the seed file to the Database, run

npx sequelize-cli db:seed:all

That's all for now

In another article, you will be able to create a simple crud API with Postgres and Node.js/Express.

Thanks for reading🙏

Oyin Olatunji