Sequelize Integration with Express, Typescript and NestJS

In a previous tutorial, we used MongoDB with ExpressJS and NestJS. Sometimes, you make not want to use a NoSQL DB, and instead a relational database is a better choice. This is where Sequelize comes in. Sequelize is an ORM that offers compatibility with various Relational Databases such as MySQL, PostgreSQL, and MSSQL. Review some of our video courses here if you’re interested in learning more. You can gain access to the GitHub account and code example here.

Project Setup

Setting up this project is similar to the MongoDB application. First, you must create a new NestJS application.

nest new sequelize-todo

Nest you must install all of the dependencies for Sequelize and your database provider of choice. In this tutorial well be using PostgreSQL.

npm install --save pg sequelize sequelize-typescript

Setting up Sequelize Postgres Database Connection

In order for NestJS to connection with our database, we must first specify the connection details. We’ll create a module and provider for this.

nest generate module database
nest generate provider database

import { Sequelize } from 'sequelize-typescript';
import { Todo } from '../todos/entity/todo.entity';

export const databaseProviders = [
    {
        provide: 'SequelizeToken',
        useFactory: async () => {
            const sequelize = new Sequelize({
              dialect: 'postgres',
              host: 'localhost',
              port: 5432,
              username: 'jamescoonce',
              password: '',
              database: 'nestTodo',
            });
            sequelize.addModels([Todo]);
            await sequelize.sync();
            return sequelize;
        },
    },
];

import { Module } from '@nestjs/common';
import { databaseProviders } from './database.providers';

@Module({
    providers: [...databaseProviders],
    exports: [...databaseProviders],
})
export class DatabaseModule { }

You can see that we add an entity model to our DB connection. Sequelize uses this to generate queries based on this model.

Creating our Entity and Services

We currently don’t have a model so we will have to create one. Along with it, we’ll have to create services to be able to easily access reusable database requests. This also cleans up our code.

nest generate module todos 
nest generate controller todos
nest generate service todos

Inside of the todos folder, we will also create an entity folder which will hold our todo.entity.ts file.

import { Table, Column, Model, DataType, CreatedAt, UpdatedAt, DeletedAt } from 'sequelize-typescript';
import { IDefineOptions } from 'sequelize-typescript/lib/interfaces/IDefineOptions';

const tableOptions: IDefineOptions = { timestamps: true } as IDefineOptions;
@Table(tableOptions)
export class Todo extends Model<Todo> {
  @Column({
    type: DataType.INTEGER,
    autoIncrement: true,
    primaryKey: true,
    unique: true,
    field: 'ID',
  })
  id: number;

  @Column
  text: string;

  @Column
  complete: boolean;

  @CreatedAt
  creationDate: Date;

  @UpdatedAt
  updatedOn: Date;

  @DeletedAt
  deletionDate: Date;
}

In this class, we have special decorators that specify that the properties in the model represent a Database Table and it’s columns. We’ll also create a todos.providers.ts that allow for us to access the Todo model and its Sequelize methods by an alias ‘TodosRepository’.

The next step is to create a DTO (Data Transfer Object). Under the dto folder, create createTodo.dto.ts. You may also want to add swagger support with ‘@nest/swagger’

import { ApiModelProperty } from '@nestjs/swagger';

export class CreateTodoDto {
  @ApiModelProperty()
  readonly id: number;

  @ApiModelProperty()
  readonly text: string;

  @ApiModelProperty()
  readonly complete: boolean;
}

Building our NestJS Todo Service

Inside of our todos.services.ts file, we’ll create an injectable service that we can use throughout our application.

import { Injectable, Inject} from '@nestjs/common';
import { CreateTodoDto } from './dto/createTodo.dto';
import { Todo } from './entity/todo.entity';

@Injectable()
export class TodosService {
  constructor(
    @Inject('TodosRepository') private readonly todosRepository: typeof Todo,
  ) {}

  async findAll(): Promise<Todo[]> {
    return await this.todosRepository.findAll<Todo>();
  }

  async findById(ID: number): Promise<Todo> {
    return await this.todosRepository.findById(ID);
  }
  async create(createTodoDto: CreateTodoDto): Promise<Todo> {
    return await this.todosRepository.create<Todo>(createTodoDto);
  }

  async update(id: number, newValue: CreateTodoDto): Promise<Todo | null> {

    let todo = await this.todosRepository.findById<Todo>(id);

    if (!todo.id) {
      // tslint:disable-next-line:no-console
      console.error('user doesn\'t exist');
    }

    todo = this._assign(todo, newValue);

    return await todo.save({ returning: true });
  }

  public async delete(ID: number): Promise<number> {

    return await this.todosRepository.destroy({
      where: { ID },
    });
  }

  private _assign(todo: CreateTodoDto, newValue: CreateTodoDto): Todo {
    // tslint:disable-next-line:no-string-literal
    for (const key of Object.keys(todo['dataValues'])) {
      if (todo[key] !== newValue[key]) {
        //
        todo[key] = newValue[key];
      }
    }
    return todo as Todo;
  }
}

Even though we’ve created the service, we can’t use it until we register it in our module file along with our providers.

import { Module } from '@nestjs/common';
import { TodosController } from './todos.controller';

import { DatabaseModule } from '../database/database.module';
import { TodosService } from './todos.service';
import { todosProviders } from './todos.providers';
@Module({
  imports: [DatabaseModule],
  controllers: [TodosController],
  providers: [TodosService, ...todosProviders ],
})
export class TodosModule {}

Creating our NestJS Controller

The last step that we have is creating our NestJS Controller. We’ll Inject our service and then use our service methods to perform our CRUD functions. If you choose to, you can also decorate your API for Swagger.

import { Controller, Get, Response, HttpStatus, Param, Body, Post, Request, Patch, Delete } from '@nestjs/common';
import { TodosService } from './todos.service';
import { CreateTodoDto } from './dto/createTodo.dto';
import { ApiUseTags, ApiResponse } from '@nestjs/swagger';

@ApiUseTags('todos')
@Controller('todos')
export class TodosController {
    constructor(private readonly todosService: TodosService) { }

    @Get()
    public async getTodos(@Response() res) {
        const todos = await this.todosService.findAll();
        return res.status(HttpStatus.OK).json(todos);
    }

    @Get('/:id')
    public async getTodo(@Response() res, @Param() param) {
        const todos = await this.todosService.findById(param.id);
        return res.status(HttpStatus.OK).json(todos);
    }

    @Post()
    @ApiResponse({ status: 201, description: 'The record has been successfully created.' })
    @ApiResponse({ status: 403, description: 'Forbidden.' })
    public async createTodo(@Response() res, @Body() createTodoDTO: CreateTodoDto) {

        const todo = await this.todosService.create(createTodoDTO);
        return res.status(HttpStatus.OK).json(todo);
    }

    @Patch('/:id')
    public async updateTodo(@Param() param, @Response() res, @Body() body) {

        const todo = await this.todosService.update(param.id, body);
        return res.status(HttpStatus.OK).json(todo);
    }

    @Delete('/:id')
    public async deleteTodo(@Param() param, @Response() res) {

        const todo = await this.todosService.delete(param.id);
        return res.status(HttpStatus.OK).json(todo);
    }
}

Conclusion

Adding Sequelize support to NestJS is fairly easy. It’s similar to adding MongoDB but it offers the ability to integrate with your current relational database.  In the next tutorial, we’re going to add TypeORM to NestJS. It’s similar to Sequelize but is written from the ground up in Typescript and offers the ability to choose between Active Record and Data Mapper patterns. If you’re interested in learning more, check out some of our video courses here.