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

7 min readJul 7, 2023

Welcome to the tutorial on how to create an Azure SQL database and connect it to NestJS using the @nestjs/typeorm library.

Microsoft Azure is a reliable cloud service that offers various database tools to meet the needs of application development. In this tutorial, we will show you how to create an Azure SQL database and connect it to NestJS using the @nestjs/typeorm.

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 @nestjs/typeorm

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

npm install @nestjs/typeorm typeorm mssql

Step 5: Configure the @nestjs/typeorm

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 { TypeOrmModule } from '@nestjs/typeorm';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: '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',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      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 an Entity

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

import { Entity, Column, PrimaryGeneratedColumn } from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  id: number;
  @Column()
  name: string;
  @Column()
  email: string;
}

The “User” class is decorated with the “@Entity” decorator, which indicates that this class represents a database table. Each attribute of the class is decorated with the “@Column” decorator, which defines the columns of the table.

The “id” attribute is decorated with the “@PrimaryGeneratedColumn()” decorator, which indicates that this is the primary key of the table and will be automatically generated by TypeORM.

Step 7: Using the 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 { CreateUserDto } from './dto/create-user.dto';
import { InjectRepository } from '@nestjs/typeorm';
import { User } from './entities/user.entity';
import { Repository } from 'typeorm';

@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User)
    private readonly userRepository: Repository<User>,
  ) {}
  async create(createUserDto: CreateUserDto) {
    return this.userRepository.save(createUserDto);
  }

  async findAll() {
    return this.userRepository.find();
  }

  async findOne(id: number) {
    return this.userRepository.findOneBy({ id });
  }

  async update(id: number, updateUserDto:UpdateUserDto){
    await this.userRepository.update(id, updateUserDto)
    return this.findOne(id); 
  }

  async delete(id:number) {
    await this.userRepository.delete(id);
  }
}

This code defines a service in NestJS for interacting with User entities stored in a database. Here’s a breakdown:

The @Injectable() decorator is a special function from the NestJS framework that allows the class to be managed by the NestJS Dependency Injection system.
CreateUserDto is a Data Transfer Object (DTO) that defines how the data will be sent over the network. It's not shown in this code, but it usually contains properties that match the User entity but with additional validation rules.
@InjectRepository(User) is a decorator that tells NestJS to inject a repository for the User entity into the UsersService class. This repository is then used to interact with the User table in the database.
async create(createUserDto: CreateUserDto) is a method that takes a DTO of the user data and uses the repository's save method to create a new user in the database.
async findAll() is a method that uses the repository's find method to fetch all users from the database.
async findOne(id: number) is a method that takes an ID and uses the repository's findOneBy method to fetch a specific user by ID from the database.

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.

Conclusion

In conclusion, in this tutorial, we covered the steps required to create an Azure SQL database and connect it to a NestJS application using the @nestjs/typeorm library.

We started by creating an account on Azure and setting up an Azure SQL database. We then went through the process of obtaining connection strings for the database.

In the application development side, we installed the necessary packages, set up TypeORM to connect with the Azure SQL database, and created an entity to map to the database.

We implemented a service that utilizes the TypeORM repository for performing CRUD operations, and finally, we created a controller to handle HTTP requests and call the service methods.

This basic setup gives you a starting point to further expand your application. From here, you can add more entities, services, and controllers as needed for your application. You can also add validation, error handling, logging, and more, to make your application robust and production-ready.

Remember, while working with databases and data, always ensure the security and integrity of the data by following best practices and guidelines.