How to configure NestJS TypeORM Integration with MySQL?

In this post, we will look at how to configure NestJS TypeORM to work with MySQL database.

NestJS is database agnostic. In other words, it allows us to integrate with any database whether SQL or NoSQL. There are a number of options available depending on the user’s preference.

In case you are interested, I have written a detailed post on NestJS MongoDB Integration with Mongoose.

However, over the years, I’ve built and released several applications using the MySQL database. It is a wonderful database that’s suitable for a wide-variety of use-cases and organization levels.

NestJS also seamlessly integrates with any general-purpose database integration library such as Sequelize, TypeORM, Knex.js or MikroORM.

However, in the case of Sequelize and TypeORM, Nest provides an even stronger integration. In this post, I will use that integration to build a small application using NestJS and MySQL with the TypeORM package.

1 – NestJS TypeORM and SQL Package Installation

TypeORM is written in Typescript. It is also one of the most mature ORM framework available for Typescript.

To make the integration easier, Nest provides the @nestjs/typeorm package. To begin, we have to first install the packages as below:

$ npm install --save @nestjs/typeorm typeorm mysql2

Basically, we are installing the @nestjs/typeorm package and the typeorm package. Also, we install mysql2 package since we are trying to use NestJS with MySQL for our example.

We can also setup a MySQL server by using Docker. For this example, we use the standard mysql image available on Docker Hub. In case you are new to Docker and wish to know more about it, we have detailed post on getting started with Docker.

docker run --name demo-mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password -d mysql:latest

You can also use a normal MySQL installation instead of Docker container.

2 – NestJS SQL Configuration with TypeORM

Once the packages are installed and the MySQL container is up and running, we need to start wiring up the pieces in our application.

The first step is to configure the connection of our NestJS application with MySQL using TypeORM. We can do so via the app.module.ts file.

See the below code:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { Book } from './library/entities/book.entity';
import { LibraryModule } from './library/library.module';

@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'root',
      password: 'password',
      database: 'library',
      entities: [Book],
      synchronize: true,
      dropSchema: true
    }), LibraryModule
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Basically, we use the forRoot() method available as part of TypeOrmModule to pass the configuration parameters. These parameters are the type of database and the host plus port details. Other details include the credentials for connection.

The forRoot() method supports all configuration properties that are part of createConnection() function. Notice the entities attribute in the configuration object. Basically, this tells NestJS about the entities we will have in our application. We will be creating the entity in the next section.

Also, you can ignore the LibraryModule in the imports array for the time being. We will be creating that soon.

In addition, there are several extra properties available as below:

retryAttempts	Number of attempts to connect to the database
retryDelay	Delay between connection retry attempts
autoLoadEntities	If true, entities will be loaded automatically
keepConnectionAlive	If true, connection will not be closed on application shutdown

3 – NestJS Entity and Repository for MySQL DB

The next step is to create a repository.

Basically, NestJS supports the popular Repository design pattern. In other words, each entity has its own repository.

For the example, we will be creating a Library application. Therefore, we will create a Book entity as below:

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

@Entity()
export class Book {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    bookName: string;

    @Column()
    authorName: string;

    @Column()
    publishYear: number;

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

We will keep the above Book entity within the library folder. Basically, we want it to be a part of the Library module. It is always good practice to keep entities close to the overall domain.

In the above example, the decorator @Entity is important. It basically tells NestJS to register this class as an entity and enable a repository to access the same. Also, we have already added the Book entity to the entities array in the app.module.ts

4 – Creating the NestJS Module

Modules are a great way to isolate domain functionalities within the application. We have a detailed post about NestJS Module system in case you wish to know more about them.

In our example case, we will simply be creating the LibraryModule as below. If you remember, we have also added the LibraryModule to the App Module configuration.

import { Module } from "@nestjs/common";
import { TypeOrmModule } from "@nestjs/typeorm";
import { LibraryController } from "./controllers/library.controller";

import { Book } from "./entities/book.entity";
import { LibraryService } from "./services/library.service";

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

This module uses the forFeature() method to define which repositories are registered in the current scope. We need this piece of configuration to inject the repository into the service.

See below code for the service class.

import { Injectable } from "@nestjs/common";
import { InjectRepository } from "@nestjs/typeorm";
import { Repository } from "typeorm";
import { Book } from "../entities/book.entity";

@Injectable()
export class LibraryService {
    constructor(
        @InjectRepository(Book)
        private bookRepository: Repository<Book>
    ){}

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

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

    createBook(book: Book): Promise<Book> {
        return this.bookRepository.save(book);
    }
}

Notice that we use the @InjectRepository() decorator to inject the Book repository into the service.

Next, we use the repository to write a few functionalities such as fetching data and creating a new record. You can read more about NestJS Services in this post.

5 – Creating the NestJS Controller

A NestJS controller is basically a bunch of request handlers to process incoming requests. You can read more about them in our detailed post on NestJS Controllers.

For our example application, we simply create a controller as below:

import { Body, Controller, Get, HttpStatus, Param, Post, Res } from "@nestjs/common";
import { Book } from "../entities/book.entity";
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
        })
    }

}

Basically, we use the LibraryService to carry out read and write operations on the Book repository.

If you start the application now, you can fire requests to create a new book. The data will be stored in the MySQL database.

It will also be accessible using the /books endpoint. All you have to do is use the appropriate method i.e. POST or GET.

Conclusion

With this, we have successfully created our NestJS MySQL application with TypeORM.

We looked at various configuration options and also used the repository pattern to create an entity and use it to carry out various operations.

The code for this post is available on Github.

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