Connecting an Azure SQL Database with a NestJS Application using sequelize: A Step-by-Step Guide

9 min readJul 11, 2023

In this tutorial, we will explore the process of integrating Azure SQL Database with a NestJS application using the Sequelize library. Azure SQL Database is a reliable cloud-based service offered by Microsoft, while NestJS is a powerful Node.js framework for building scalable and efficient server-side applications. By leveraging Sequelize as our ORM (Object-Relational Mapping) library, we can easily connect, query, and manipulate data in our Azure SQL Database from within our NestJS application.

Step 1: Create an Azure Account

The first step is to create an Azure account. If you already have an account, you can skip this step. Otherwise, go to https://azure.microsoft.com/ and create a free account.

Step 2: Create an Azure SQL Database

After creating an Azure account, log in to the Azure portal and create an SQL Database. This database will be used to create a proof of concept (POC) connection, which you can do by following these steps:

On the Azure portal homepage, select “Create a resource”.

In the “New” menu, select “SQL Database”.

Set the Resource Group and click on creating a new server (Server):

Create the server with the region settings (Location), the server name (Server Name), and for testing purposes use SQL Authentication:

Set the name of your database in (Database name), choose no in the option “Want to use SQL Elastic pool”, in “Workload environment” set your database as development (Development). Choose “Compute + storage” as Basic 2GB:

In the “Networking” tab, set the network connection (Connect Method) as Public endpoint and in the “add current client IP address” option as yes (Yes). This will make the server available to the public IP of whoever created the resource on the internet:

Click on “Review + create” and then on “Create”, and the database will be created.

Step 3: Connection Strings of the Azure SQL

Obtain the Connection Strings of the Azure SQL Database To connect NestJS to the Azure SQL Database, we need to obtain the database connection string. You can do this by following these steps:

In the Azure portal, select your database. On the database page, select “Connection Strings”. As there is no direct documentation for JavaScript, we will use the variables from Go to create our connections.

Step 4: Install the dependencies

Package To use @nestjs/sequelize, you need to install it using npm. To do this, run the following command:

npm install --save @nestjs/sequelize sequelize sequelize-typescript mssql

Step 5: Configure the @nestjs/sequelize

Now, we need to configure the @nestjs/typeorm in our NestJS project. To do this, open the app.module.ts file and add the following lines of code:

import { Module } from '@nestjs/common';
import { SequelizeModuleOptions } from '@nestjs/sequelize';
@Module({
  imports: [
    SequelizeModule.forRoot({
      dialect: 'mssql',
      host: '{Your Azure SQL Server}',
      port: {Your Azure SQL Server Port},
      username: '{Your Azure SQL Username }',
      password: '{Your Azure SQL Password}',
      database: '{Your Azure SQL Database Name}',
      models: [__dirname + '/**/*.model{.ts,.js}'],
      autoLoadModels: true,
      synchronize: true, //use this with development enviroment
    }),
  ],
})
export class AppModule {}

Remember to replace ‘Your Azure SQL Server’, ‘Your Azure SQL Server Port’, ‘Your Azure SQL Username’, ‘Your Azure SQL Password’, and ‘Your Azure SQL Database Name’ with your actual Azure SQL Server details.

The “synchronize” parameter indicates whether TypeORM should automatically synchronize the database with the defined entities. This parameter should be set to “true” only during development. In production, it is recommended to disable it.

Step 6: Creating a model

Let’s create a modelto represent a user in the database. To do this, create a new file “user.entity.ts” with the following code:

import { Model, Column, Table, DataType, PrimaryKey, AutoIncrement } from 'sequelize-typescript';

@Table
export class User extends Model<User> {
  @PrimaryKey
  @AutoIncrement
  @Column(DataType.INTEGER)
  id: number;

  @Column(DataType.STRING)
  name: string;

  @Column(DataType.STRING)
  email: string;
}

Step 7: Using the model Repository

Now, we can use the repository to perform CRUD operations in the database. Let’s create a service that uses the repository to create a new user in the database. Create a new file “user.service.ts” with the following code:

import { Injectable } from '@nestjs/common';
import { InjectModel } from '@nestjs/sequelize';
import { User } from './user.model';

@Injectable()
export class UserService {
  constructor(@InjectModel(User) private userModel: typeof User) {}

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

  async findOne(id: number): Promise<User> {
    return this.userModel.findByPk(id);
  }

  async create(user: Partial<User>): Promise<User> {
    return this.userModel.create(user);
  }

  async update(id: number, user: Partial<User>): Promise<[number, User[]]> {
    return this.userModel.update(user, { where: { id } });
  }

  async remove(id: number): Promise<void> {
    const user = await this.findOne(id);
    await user.destroy();
  }
}

This code demonstrates a UserService class in a NestJS application that utilizes the @nestjs/sequelize library to interact with the Azure SQL Database using Sequelize.

Injectable decorator: This decorator is imported from the @nestjs/common package and is used to define the UserService class as an injectable provider within the NestJS application.
InjectModel decorator: This decorator is imported from the @nestjs/sequelize package and is used to inject the Sequelize model, User, into the UserService class. The User model is defined in the user.model.ts file.
Constructor: The constructor of the UserService class accepts an instance of the User model as a parameter, injected using @InjectModel(User). This allows us to access the Sequelize model within the class.
findAll() method: This method retrieves all users from the Azure SQL Database by calling findAll() on the userModel. It returns a promise that resolves to an array of User instances.
findOne(id: number) method: This method retrieves a user by their ID from the Azure SQL Database using the findByPk() method on the userModel. It returns a promise that resolves to a single User instance.
create(user: Partial<User>) method: This method creates a new user in the Azure SQL Database using the create() method on the userModel. It accepts a Partial<User> object as a parameter, which represents the user data to be created. It returns a promise that resolves to the created User instance.
update(id: number, user: Partial<User>) method: This method updates an existing user in the Azure SQL Database using the update() method on the userModel. It accepts the ID of the user to be updated and a Partial<User> object representing the updated user data. It returns a promise that resolves to an array containing the number of affected rows and an array of the updated User instances.
remove(id: number) method: This method removes a user from the Azure SQL Database. It first calls findOne() to retrieve the user by ID, and then uses the destroy() method to delete the user from the database.

Overall, the UserService class provides methods for performing common CRUD (Create, Read, Update, Delete) operations on the Azure SQL Database using Sequelize and exposes these operations as async functions that return promises for efficient and asynchronous handling of database operations in the NestJS application.

Step 8: Create a controller

The provided code is a UserController in NestJS for handling HTTP requests related to User entities. Here’s a breakdown:

import { Controller, Get, Post, Body, Param } from '@nestjs/common';
import { UserService } from './user.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}
  @Post()
  async create(@Body() createUserDto: CreateUserDto) {
    await this.userService.create(createUserDto);
  }
  @Get()
  async findAll() {
    return this.userService.findAll();
  }
  @Get(':id')
  async findOne(@Param('id') id: string) {
    return this.userService.findOne(+id);
  }
  @Patch(':id')
  async update(@Param('id') id: string, @Body() updateUserDto: UpdateUserDto) {
    return this.userService.update(+id, updateUserDto);
  }
  @Delete(':id')
  async delete(@Param('id') id: string) {
    return this.userService.delete(+id);
  }
}

@Controller('users') is a decorator that tells NestJS that this class is a controller and that it should map HTTP requests to the class's methods based on the provided route, in this case, 'users'.
constructor(private readonly userService: UserService) {} is a constructor that creates an instance of the UserService. The private readonly keywords make this service available throughout the UserController class.
@Post() is a decorator that tells NestJS to call the create method when a POST request is made to the '/users' endpoint. The method creates a new User using the data in the request body.
@Get() is a decorator that maps GET requests for the '/users' endpoint to the findAll method. This method returns a list of all users.
@Get(':id') is a decorator that maps GET requests for '/users/:id' to the findOne method. This method returns the user with the provided ID.
@Patch(':id') is a decorator that maps PATCH requests for '/users/:id' to the update method. This method should update the user with the provided ID with the data in the request body. However, your current implementation is calling findOne method which will not perform any update operation. It should call update method in the userService.
@Delete(':id') is a decorator that maps DELETE requests for '/users/:id' to the delete method. This method deletes the user with the provided ID.

Step 8: Import to UserModel

Import User into UserModule:

import { Module } from '@nestjs/common';
import { SequelizeModule } from '@nestjs/sequelize';
import { User } from './user.model';

@Module({
  imports: [SequelizeModule.forFeature([User])],
})
export class UserModule {}

This code represents the configuration of the UserModule in a NestJS application using the @nestjs/sequelize library.

Module decorator: This decorator is imported from the @nestjs/common package and is used to define the UserModule class as a module within the NestJS application.
SequelizeModule import: This import statement brings in the SequelizeModule from the @nestjs/sequelize package. This module is responsible for integrating Sequelize with the NestJS application.
User import: This import statement brings in the User model from the user.model.ts file. The User model represents the database schema for the users table.
imports property: Inside the @Module decorator, the imports property is used to specify the dependencies required for the UserModule. In this case, we include SequelizeModule.forFeature([User]).
SequelizeModule.forFeature([User]): This method is called with an array containing the User model. It configures the Sequelize module to make the User model available for use within the UserModule, allowing us to perform database operations on the User entity.

By configuring the UserModule with SequelizeModule.forFeature([User]), we ensure that the User model is accessible within the module, enabling us to utilize Sequelize methods to interact with the Azure SQL Database and perform various CRUD operations on user data.

This configuration sets up the necessary infrastructure for the UserModule to utilize Sequelize and work with the User model, making it a cohesive unit for managing user-related functionality within the NestJS application.

Conclusion

In conclusion, this tutorial has provided an overview of how to integrate an Azure SQL Database with a NestJS application using the Sequelize library. We started by understanding the benefits of using Azure SQL Database and NestJS, highlighting their strengths in application development and data management.

We then proceeded with the setup process, including creating an Azure SQL Database and obtaining the necessary connection details. Next, we explored the migration from TypeORM to Sequelize, adjusting the code to use Sequelize decorators and syntax for defining models.

The tutorial then focused on the UserService class, demonstrating how to utilize Sequelize and the @nestjs/sequelize package for performing common CRUD operations on the Azure SQL Database. We discussed methods for retrieving users, creating new users, updating existing users, and deleting users, all while leveraging the powerful features provided by Sequelize.

Furthermore, we explored the configuration of the UserModule, where we imported the User model and specified the necessary dependencies using SequelizeModule.forFeature(). This ensured that the User model was accessible within the module, allowing us to seamlessly work with the Azure SQL Database in a modular and organized manner.

By following this tutorial, you should now have a solid understanding of how to connect an Azure SQL Database to a NestJS application using Sequelize. You can continue to build upon this foundation to develop robust, scalable, and efficient applications that leverage the capabilities of Azure SQL Database and take advantage of the rich ecosystem provided by NestJS and Sequelize.