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.
Codebrains Newsletter
Get weekly dev news and tutorials.
About The Author: James Coonce
James Coonce is a programmer from Tampa Fl that has a love for golf, travel and language learning. When he's not hitting golf balls, he's probably watching foreign movies.
More posts by James Coonce