Photo by Tracy Adams on Unsplash

TypeORM and MySql Configuration for NestJS

Recently I started working on the NestJS to get comfortable with the server-side node js framework and learn Typescript. I have decided to write down the learning as I implement them. I am in no way an expert in either NextJS or Typescript. So, please bear with me if I make any mistakes.

Introduction

Today I will try to explain different approaches to take while configuring the MySQL database with NestJS using typeorm. To get a head start on the configuration section, you can go through their official database documentation.

Pre-requisite

Before you start with anything related to nest js, make sure you have installed the NestJS cli on your local machine. If you have worked before with the Laravel framework before then, you will be very comfortable with its usage.

$ npm i -g @nestjs/cli

Setup

Setting up a project

$ nest new project

The successful execution of the above command will create the minimum folder structure needed for starting the NestJS server
Or You can take the approach given below

$ git clone <https://github.com/nestjs/typescript-starter.git> project
$ cd project
$ yarn install
$ yarn run start
$ yarn @nestjs/core @nestjs/common rxjs reflect-metadata

For this article, I have taken the @nestjs/cli approach. The successful execution of the nest new nest-db-config command will create the minimum folder structure needed for starting the NestJS server.

Now we can start the server by running yarn start:dev command, which will run the NestJS server in watch mode.

Database setup

Let’s create a database and users related to the database in MySQL.

// create database
mysql> create database if not exists nest_db_config;
Query OK, 1 row affected (0.01 sec)
// create user
mysql> create user 'nest_db_config'@'localhost' identified by 'nest_DB_c0nf!g';
Query OK, 0 rows affected (0.02 sec)
// Grant user all privileges to database created above
mysql> grant all privileges on nest_db_config.* to 'nest_db_config'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

Now log into MySQL with the newly created database and user and ensure that the user can access it.

Install typeorm

The NestJS framework provides tight integration with Typeorm and Sequalize ORM. Here I am using Typeorm for database configuration examples. Install the required library needed for typeorm with NestJS

$ yarn add @nestjs/typeorm typeorm mysql

Quick Database connection

To quickly test the database connection, make the following changes to app.module.ts

import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    TypeOrmModule.forRoot({
      type: 'mysql',
      host: 'localhost',
      port: 3306,
      username: 'nest_db_config',
      password: 'nest_DB_c0nf!g',
      database: 'nest_db_config',
      entities: [],
      synchronize: true
    })
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

You only need the above snippet to test the database connection with NestJS. If you want to understand how it works, then change the above configuration and observe what happens at the console level. You will notice the following error in the console.

[11:33:52 AM] File change detected. Starting incremental compilation...
[11:33:53 AM] Found 0 errors. Watching for file changes.
[Nest] 16874   - 01/07/2021, 11:33:55 AM   [NestFactory] Starting Nest application...
[Nest] 16874   - 01/07/2021, 11:33:55 AM   [InstanceLoader] TypeOrmModule dependencies initialized +172ms
[Nest] 16874   - 01/07/2021, 11:33:55 AM   [InstanceLoader] AppModule dependencies initialized +1ms
[Nest] 16874   - 01/07/2021, 11:33:55 AM   [TypeOrmModule] Unable to connect to the database. Retrying (1)... +9ms
Error: ER_ACCESS_DENIED_ERROR: Access denied for user 'nest_db_config'@'localhost' (using password: YES)
...

This approach is clean and makes it easily readable for newcomers. But it has the following downsides.

Cons

• Since database credentials are part of the code, they are available to anyone who gets access to the code.
• This approach forces us to use similar credentials for all development environments. We lose flexibility.

Solution

We can improve on the above solution by making a few changes in the code.

Please create a new file called ormconfig.json at the root of the project and remove the configuration object from TypeOrmModule.forRoot and add it to the ormconfig.json file. As shown below.

app.module.ts
import { Module } from '@nestjs/common';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    TypeOrmModule.forRoot()
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}
-------
ormconfig.json
{
    "type": "mysql",
    "host": "localhost",
    "port": 3306,
    "username": "nest_db_config",
    "password": "nest_DB_c0nf!g",
    "database": "nest_db_config",
    "entities": [],
    "synchronize": true
}

Once done with the changes, then restart the local server so that it can pick up the ormconfig.json file.

In almost all of the cases, the above solution will work smoothly. But there is one caveat, as mentioned on the NestJS website

WARNING
Note that the ormconfig.json file is loaded by the typeorm library. Thus, any of the extra properties described above (which are supported internally by way of the forRoot() method - for example, autoLoadEntities and retryDelay) won't be applied.

What the above warning means is ormconfig.json will be used for the configurations by TypeORM package instead of TypeORM module for NestJS. This package is implemented as a NestJS module, which internally uses the original TypeORM package. The NestJS’s module defines its configuration properties, which are ignored when we define ormconfig.json. If you are not going to use those properties, you don’t have to worry about it.

But what if you want to take the benefit of the properties defined in NestJS’s TypeORM module? Do we have to go back to defining configuration in the code? And answer to that is NO.

NestJS’s config module comes to our Rescue. Install it by running the following command

$ yarn add @nestjs/config

NestJS’s config module internally uses the dotenv package to read .env files. So, rather than defining database configurations in ormconfig.json we can define them in the .env file placed at the root of the project.

.env
DB_PORT=3306
DB_HOST=localhost
DB_USER=nest_db_config
DB_PASSWORD=nest_DB_c0nf
DB_NAME=nest_db_config

Now make the following updates toapp.module.ts file.

app.module.ts
import { Module } from '@nestjs/common';
import { ConfigModule } from '@nestjs/config';
import { AppController } from './app.controller';
import { AppService } from './app.service';
import { TypeOrmModule } from '@nestjs/typeorm';
@Module({
  imports: [
    ConfigModule.forRoot(),
    TypeOrmModule.forRootAsync({
      useFactory: () => ({
        type: 'mysql',
        host: process.env.DB_HOST,
        port: parseInt(process.env.DB_PORT) || 3306,
        username: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: false
      })
    })
  ],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

Let’s go through the changes step by step

• Import ConfigModule from @nestjs/config
• Add ConfigModule.forRoot() to imports array. This line will automatically load the .env file from the root of the project
• Replace TypeOrmModule.forRoot method with TypeOrmModule.forRootAsync which accepts the object with useFactory method.

Now Please rename the ormconfig.js file to something else; otherwise, TypeORM will use it for the configuration.

Now restart the server, and it will use the configuration values defined in .env file.

I prefer defining all environment based application-level configurations in a single place, like .env over saving database configurations in ormconfig.json and other configuration values in .env . You are free to choose any approach you like.

Please feel free to point out the mistakes; don’t hesitate to recommend a better approach in the comments section.

Thanks and Bye!

UPDATE

You can find the code on the Github repository here. Also, I have written one more article regarding the typeorm and NestJS with a few advanced techniques. You can read more about it here.