How to setup a NestJS Sequelize PostgreSQL Integration?

In this post, we will look at how to setup a NestJS Sequelize PostgreSQL Integration.

NestJS is a database-agnostic framework. Basically, we can integrate it with any database whether SQL or NoSQL. As a result, there are numerous options to choose from.

NestJS also works with any general-purpose database integration library such as Sequelize, TypeORM, Knex.js or MikroORM. However, special integration is provided with Sequelize and TypeORM.

We have a detailed post on NestJS TypeORM integration with MySQL. Also, from NoSQL perspective, we have earlier looked at building a NestJS MongoDB application using Mongoose.

Here, we will specifically look at Sequelize and PostgreSQL integration.

1 – NestJS Sequelize Package Installation

First step is to install the required dependencies.

Use the below npm install commands for the same:

$ npm install --save @nestjs/sequelize sequelize sequelize-typescript pg
$ npm install --save-dev @types/sequelize

Since, we are dealing with PostgreSQL, we install the pg package. If we were using MySQL, we need to use the mysql2 package.

Next, we need to get a PostgreSQL server up and running to connect with our application. I would recommend using Docker for the same. If Docker is already setup on your machine, you can simply use the below command to start up PostgreSQL container.

docker run --name demo-postgres -p 5432:5432 -e POSTGRES_PASSWORD=password -d postgres

We will be able to access our PostgreSQL server on port 5432.

2 – NestJS Sequelize PostgreSQL Configuration

Now that the setup part is out of the way, we can start focusing on writing the actual application.

The first step is to establish the connection between our application and the database. We can do so by adding the necessary configuration in the app.module.ts.

See below:

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { LibraryModule } from './library/library.module';

@Module({
  imports: [SequelizeModule.forRoot({
    dialect: 'postgres',
    host: 'localhost',
    port: 5432,
    username: 'postgres',
    password: 'password',
    database: 'library',
    autoLoadModels: true,
    synchronize: true
  }), LibraryModule],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

We use the forRoot() method to supply the necessary configuration. Basically, the forRoot() method supports all the configuration parameters that are part of the Sequelize constructor. In addition, NestJS introduces some additional options as below:

retryAttempts	Number of attempts to connect to database. Default value is 10
retryDelay	Delay between connection retry attempts. Default value is 3000 ms
autoLoadModels	If true, models are loaded automatically
keepConnectionAlive	If true, connection will be closed on application shutdown
synchronize	If true, automatically loaded models will be synchronized

It is useful have the autoLoadModels turned on. This actually helps create the schema and tables in the PostgreSQL during application start-up. Also, by using this option, we need not manually specify the models within our application context. NestJS will automatically scan our application code and find the classes decorated with the @Table annotation.

3 – Creating the Sequelize Model

The next step is to create the Sequelize Model class. Sequelize basically uses the Active Record pattern. In other words, you can directly use the model classes to interact with the database.

We will create one model to demonstrate the format.

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

@Table
export class Book extends Model {
    @Column
    bookName: string;

    @Column
    authorName: string;

    @Column
    publishYear: number;

    @Column({ defaultValue: true})
    isAvailable: boolean;
}

As you can see, the Book class extends the Sequelize Model class. Also, we use decorators such as @Table and @Column to define the various aspects of our model class.

Since we are creating a Library application, we will keep the model classes within a directory named library. Basically, we are trying to keep domain classes closer to the domain. In this case, the domain is the LibraryModule.

4 – Creating the Library Module

Let’s create the LibraryModule as well.

import { Module } from "@nestjs/common";
import { SequelizeModule } from "@nestjs/sequelize";
import { LibraryController } from "./controllers/library.controller";
import { Book } from "./models/book.model";
import { LibraryService } from "./services/library.service";

@Module({
    imports:[SequelizeModule.forFeature([Book])],
    providers: [LibraryService],
    controllers: [LibraryController]
})
export class LibraryModule {}

This module uses the forFeature() method to define which models are registered in the current context. Also, we specify the LibraryService as a provider and LibraryController as a controller. We will get to those in the next section.

Also, we have already imported the Library Module in the app.module.ts file of the application.

5 – Creating the Service and Controller

The next step is to finally create the service (or provider) and the controller.

We have detailed posts on NestJS Providers and NestJS Controllers in case you want to know more about them. However, for the purpose of this post, we will create simplified versions.

Below is the service class.

import { Injectable } from "@nestjs/common";
import { InjectModel } from "@nestjs/sequelize";
import { Book } from "../models/book.model";

@Injectable()
export class LibraryService {
    constructor(
        @InjectModel(Book)
        private bookModel: typeof Book
    ) {}

    async findAll(): Promise<Book[]> {
        return this.bookModel.findAll();
    }

    findOne(id: string): Promise<Book> {
        return this.bookModel.findOne({
            where: {
                id,
            },
        });
    }

    async createBook(book: Book): Promise<Book> {
        return this.bookModel.create(book);
    }
}

We inject the Book model in the constructor using the @InjectModel() decorator. Then, we can use the bookModel to perform various operations. Basically, we implement the findAll(), findOne() and createBook() methods.

Next, we have the controller class where we implement a few request handlers to call the methods in the service class. See below:

import { Body, Controller, Get, HttpStatus, Param, Post, Res } from "@nestjs/common";
import { Book } from "../models/book.model";
import { LibraryService } from "../services/library.service";

@Controller('books')
export class LibraryController {
    constructor(private readonly libraryService: LibraryService){}

    @Post()
    async createBook(@Res() response, @Body() book: Book) {
        const newBook = await this.libraryService.createBook(book);
        return response.status(HttpStatus.CREATED).json({
            newBook
        })
    }

    @Get()
    async fetchAll(@Res() response) {
        const books = await this.libraryService.findAll();
        return response.status(HttpStatus.OK).json({
            books
        })
    }

    @Get('/:id')
    async findById(@Res() response, @Param('id') id) {
        const book = await this.libraryService.findOne(id);
        return response.status(HttpStatus.OK).json({
            book
        })
    }
}

Here, we basically inject the LibraryService and implement the request handlers to create, fetchAll and findById.

If we start the application now, we can access the API endpoints at http://localhost:3000/books to create a book and then fetch the records from the database.

Conclusion

With this, we have successfully completed the NestJS Sequelize PostgreSQL integration. We have looked at the various configuration options and created appropriate request handlers to create and read data.

The code for this post is available on Github.

If you have any comments or queries, please feel free to write in the comments section below.