Performing Database Operations on PostgreSQL Database Using Node.js and Sequelize Object Relational Mapping (ORM)

In this article, we will implement the database operations on PostgreSQL using Node.js and Sequelize ORM. PostgreSQL is an Open-Source, powerful object-relational database system. This database supports various features. The detailed features of this database can be read from this link. 

This article is written for the audience those have basic knowledge of Node.js and Express.

What is Object-Relational Mapping (ORM)?    

ORM is a technique of mapping the data schema from the database with the Object-Oriented representation in incompatible systems. The application that needs to connect to the database for performing various database operations, can use these objects to perform read/write operations instead of using typical database syntax like select, insert, update, delete queries. Various server-side technologies like JAVA, .NET, .NET Cors use various ORM frameworks like Hibernate, EntityFramework, etc. to map with database schemas (table) and perform read/write operations. While using Node.js on the server-side, we can make use of Sequelize ORM to work with database read/write operations.

What is Sequelize?

Sequelize is a promise-based Node.,js ORM which we can use for MySQL, PostgreSQL, MariaDB, Microsoft SQL Server, etc. for performing database operations. Sequelize supports database-first and code-first approaches. In the database-first approach, the JavaScript Models are generated based on database tables and in the code-first approach, database tables are generated from JavaScript Models. More information about Sequelize can be read from this link.

Let's create a database using PostgreSQL

In the beginning, I have already written that we will be using PostgreSQL for database creation. PostgreSQL can be downloaded from this link.  

To create a database and tables, I have created a new user in PostgreSQL using the script shown in listing 1. This script will create a user of the name myuser with a password as P@ssw01rd.  This user has the privilege of creating a database. 

Create Role myuser With Login PASSWORD 'P@ssw01rd';

ALTER ROLE myuser CREATEDB;

Listing 1: The Script to create user      

Create a database of name Business and table of name Department by connecting to the PostgreSQL using the Username created using the script provided in listing 1. The script for creating the database and table is provided in listing 2

Create Database Business;

-- Create Table

Create Table Department (

  DeptNo smallint Not Null,

  DeptName varchar(100) Not Null,

  Location varchar(100) Not Null,

  PRIMARY KEY (DeptNo)

);

Listing 2: The Database and Table creation script 

We have created a Business database and the Department table in it.

 Creating Node.js and Sequelize application

Step 1: Create a folder of name nodeorm on the machine and open this folder in Visual Studio Code (VSCode).  Open the Command Prompt (or Terminal window if using Linux or MacOS) and navigate to the nodeorm folder. 

Step 2: This application uses Express for creating REST APIs to accept data from the client. So, let's install  packages in the application by running the following command from the command prompt

npm install -g pg pg-hstore sequelize sequelize-auto sequelize-cli

We need to install pg package to use PostgreSQL and Sequelize packages to use Sequelize and its command line to generate JavaScript Models from tables so that Node.js application can use it.

Run the following command to generate package.json in the project

npm init -y

Run the following command to install packages for the project

npm install --save express pg pg-hstore sequelize sequelize-auto sequelize-cli

Step 3: To generate JavaScript Models from the database so that the Node.js application can use it, run the following command from the command prompt

 sequelize-auto -h localhost -d business -u maheshadmin

-x P@ssw0rd_ -p 5433  --dialect postgres -o models

-t department -l esm

The sequelize-auto command accepts the following switches

• h: the host machine where the database is running 
• d: the database name
• u: user name
• x: password
• p: The port (Note: PostgreSQL rung default on port 5432, but since I have multiple PostgreSQL instances I have used port 5433)
• dialect: The Database provider, currently we are using Postgres
• o: the output path in the project where JavaScript Model files will be created. 
• t: table (or tables) those will be used to generate JavaScript Model classes
• l esm: This switch is used to generate ES 6 JavaScript module files

Once the above command is executed successfully, the models folder will be added to the project. This folder will contain department.js that has department class and init-models.js with the model initialization.  The department class is derived from the Model class. The Model class contains asynchronous methods to perform read/write operations some of the methods are as follows

• findAll(), to read all records from the table
• create(), to create new record
• update(), to update record
• destroy(), to delete the record       

Step 4: In the project add a new folder and name it as dal. In this folder add a new file and name it as deptdal.js. In this file, we will write the logic for connecting to the database and then perform read/write operations

First of all import packages Sequelize, DataTypes so that we can start using the Sequelize object model in the application. The code is shown in listing 3

import {Sequelize} from 'sequelize';

import pkg from 'sequelize';

const {DataTypes} = pkg;

import department from './../models/department.js';

Listing 3: The package import 

Here we need, DataTypes to make sure that Database mapped datatypes are supported by the Node.js application. The department model is imported from the department.js. This will be used to perform CRUD operations.

Now, let's connect to the Postgres Database using the Sequelize instance. We will pass, hostname, database name, credentials, port to the constructor so that a connection will be established. Once a database connection is established, we will authenticate the application to make sure that those credentials used to connect to the database are valid. The code is shown in listing 4

//  connect to the database

const sequelize = new Sequelize('business',

'myuser', 'P@ssw01rd', {

    host: 'localhost',

    port: 5433,

    dialect: 'postgres'

});

// make sure that the connection is established

sequelize.authenticate().then(authenticate =>{

    console.log('====================================');

    console.log('connected');

    console.log('====================================');

}, (error)=>{

 console.log('====================================');

 console.log('Error Occured');

 console.log('====================================');

});

 

Listing 4: Connected to the database and authenticate  

 

Once we have authenticated, now it's time for us to perform CRUD operations. We will be implementing this logic by creating DepartmentDataAccess class. This class will contain a constructor. This constructor will initialize the department model so that it will map to the department table of the database to perform CRUD Operations. The class contains methods for CRUD operations. The code for the class is shown in listing 5

// the data access class for CRUD operations

//with Department table

class DepartmentDataAccess {

    //  Initialize the  department model for

//mapping with table, so that read/write operations

//can be performed

    constructor(){

        department.init(sequelize,DataTypes);

    }

    // Method to read all records

    async getDepartments(req,resp){

        //  connect to database using sequelize object

        // {force:false} will make sure that if the

//table is already exist, it won't

        // be overridden

        await sequelize.sync({force:false});

        //  Read all records

        let records = await department.findAll();

        //  Generate response

        if(records) {

            return resp.status(200).send(records);

        }

        return resp.status(500).send({message:

'Some Error Occured'});

    }

    async postDepartment(req,resp){

        await sequelize.sync({force:false});

        let dept = await department.create(req.body);

        if(dept) {

            return resp.status(200).send (dept);

        }

        return resp.status(500).send

({msg:'Some Error Occured in create'});

    }

    async putDepartment(req,resp){

        await sequelize.sync({force:false});

        console.log(req.params.id);

        let dept = await department.update({

            deptno: req.body.deptno,

            deptname: req.body.deptname,

            location: req.body.location,

            capacity: req.body.capacity

        },{where:{deptno:parseInt(req.params.id)}});

       

        if(dept) {

            return resp.status(200).send

({message: `Record Updated Successfully`,

data:JSON.stringify(dept)});

        }

        return resp.status(500).send

({msg:'Some Error Occured in create'});

    }

    async deleteDepartment(req,resp){

        await sequelize.sync({force:false});

        console.log(req.params.id);

        let result =

await department.destroy(

{where:{deptno:parseInt(req.params.id)}});

       

        if(result) {

            return resp.status(200).send

({message:'Record Deleted Successfully',

data:result});

        }

        return resp.status(500).send

({msg:'Some Error Occured in create'});

    }

}

export default DepartmentDataAccess;

Listing 5: The DepartmentDataAccess class    

 

In, the code shown in Listing 6, contains various methods. But, one common code statement in each method is as follows 

await sequelize.sync({force:false});

This mans that, sequelize will establish onnection to database and then will overwrite tables in database, the {force:false}, will make sure that, tables will not be overwritten. One more important feture of the DepartmentDataAccess class is tht all methods ate asynchronous and each method accepts request and resonse object. The reason behind the asyc methods is that all these methods are performing awaitable CRUD operations using sequelize object and department model. The request and response object will be passed to these methods using express Http pipeline which we will be crating in forthcomming steps. The request object will be used to read posted data by the client application and also header parameters. We will be using the posted values from the requestobjct to create new record and update an existing record.  Once the database CRUD operations are successfully completed, response object will be used to return this data. The DepartmentDataAccess class is exported as a Node.js ES 6 module so that it will be imported by other module in the application.

Step 5: In the project add  a new file and name it as server.js. In this filw we will use the Express module to create REST API. We will be using Http methods of the express object to create REST Endpoints. These endpoints will invokes methds from DepartmentDataAccess class so that values posted by the client to REST endpoints will be passed to these methods. The code of the REST API endpoints is showin in listing 6

import express from 'express';

import DepartmentDataAccess  from './dal/deptdal.js';

const port = process.env.PORT || 9091;

const instance  = express();

instance.use(express.urlencoded({extended:false}));

instance.use(express.json());

const deptDal = new DepartmentDataAccess();

instance.get('/api/departments',

    deptDal.getDepartments);

instance.post('/api/departments',

    deptDal.postDepartment);

instance.put('/api/departments/:id',

    deptDal.putDepartment);

instance.delete('/api/departments/:id',

    deptDal.deleteDepartment);

instance.listen(port, ()=>{

    console.log(`Server Started on port ${port}`);

});

Listing 6: The REST APIs code 

We have configured the 9091 port for REST Endpoints.

To run the application, run the following command from the command prompt

node server.js

This command will expose the REST Endpoints on port 9091. 

You can test the application using browser by enter address as http://localhost:9091/api/departments. The result will be as shown in figure 1

Figure 1: The result 

Likewise, using Postmon, Advanced REST Client (ARC) or by creating your client application, you can test POST, PUT and DELETE requests.

The code for this article is available on this link. 

Conclusion: The Sequelize provides great feature for using ORM in Node.js to make it as first class technology for development of Data Centric Applications using JavaScript.