Mastering Database Migrations in NestJS

This is fourth story of series Building a Robust Backend: A Comprehensive Guide Using NestJS, TypeORM, and Microservices. Our purpose is to build an email sync system for Gmail, with basic Skelton in its place, lets resolve entities issue we faced last time by using migrations.

By definition, migrations refer to a set of operations that help us manage our database schema. Migrations allow us to version your database, make changes to its structure, and then apply or revert these changes in a controlled way. This is especially useful in larger projects where multiple developers might be working on the same database, or when deploying changes to production.

We are using TypeORM for as ORM, and TypeORM offers migrations support, about which further can be read from its official documentation. For our setup, we will first disable entities auto sync by setting synchronize: false in TypeOrmModule.forRoot configuration in app.module.ts. Then we will set migration table name to keep record of migrations that have been run and set migration run to false to disable migrations auto run on application start. Also lets setup logging to true to visualize the queries being made to DB, it isn’t necessary but becomes handy when debugging issues at database level. Now the updated properties will look like:

    TypeOrmModule.forRoot({
      type: 'postgres',
      host: 'localhost',
      port: 5432,
      username: 'postgres',
      password: 'admin',
      database: 'gmail-sync',
      entities: [__dirname + '/**/*.entity{.ts,.js}'],
      logging: true,
      synchronize: false,
      migrationsTableName: 'typeorm_migrations',
      migrationsRun: false,
    }),

Now since migrations won’t run with application so how will we run these migrations? We will use datasource to run the migrations, to setup datasource lets create a datasource.ts file in shared/utils. Then we will initialize datasource from typeorm and pass database properties to it. The code of datasource file is:

import { DataSource } from 'typeorm';
import { join } from 'path';

export const connectionSource = new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'admin',
  database: 'gmail-sync',
  logging: true,
  entities: [__dirname + '/../**/*.entity{.ts,.js}'],
  migrations: [join(__dirname, '/../../', 'database/migrations/**/*{.ts,.js}')],
  synchronize: false,
  migrationsTableName: 'typeorm_migrations',
  migrationsRun: false,
});

Finally, lets add scripts in package.json to create, run and revert migrations.

"migration:create": "cross-var ts-node node_modules/typeorm/cli.js migration:create ./src/database/migrations/$npm_config_name",
"migrate": "ts-node node_modules/typeorm/cli.js migration:run -d src/shared/utils/datasource.ts",
"migration:down": "ts-node node_modules/typeorm/cli.js migration:revert -d src/shared/utils/datasource.ts"

In above command there is particular package being used, name cross-var. This allows us to set migration file name.

So, we are ready to create and run the migrations, but first what’s the point of setting environment variables if we aren’t going to use them? Let’s setup DB creds from local.env, and set values for host, port, username, password and database name, the values in local.env will be:

DB_HOST=localhost
DB_PORT=5432
DB_USERNAME=postgres
DB_PASSWORD=admin
DB_NAME=gmail-sync

And the following properties in both datasource.ts and app.module.ts will be replaced by following:

  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,

Now let's create our first migrations file, we will run:

npm run migration:create --name=gmail-accounts

This will create database/migrations folder and {ts}-create-users.ts where {ts} is the timestamp at which the migration is created. At the moment the migration will have boilerplate code, lets add the code below to set column names and their type:

import { MigrationInterface, QueryRunner, Table } from 'typeorm';

export class GmailAccounts1698406058495 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'gmail_accounts',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isPrimary: true,
            default: 'uuid_generate_v4()',
            isGenerated: true,
            generationStrategy: 'uuid',
          },
          {
            name: 'full_name',
            type: 'varchar',
          },
          {
            name: 'email',
            type: 'varchar',
          },
          {
            name: 'access_token',
            type: 'varchar',
            isNullable: true,
          },
          {
            name: 'refresh_token',
            type: 'varchar',
            isNullable: true,
          },
          {
            name: 'token_type',
            type: 'varchar',
            isNullable: true,
          },
          {
            name: 'scope',
            type: 'varchar',
            isNullable: true,
          },
          {
            name: 'expiry_date',
            type: 'bigint',
            isNullable: true,
          },
        ],
      }),
      true,
    );
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropTable('gmail_accounts');
  }
}

P.S in last story we set table name as gmail-accounts, correct it to gmail_accounts.
Finally, let’s clear our database manually and run:

npm run migrate

This will create the gmail_accounts and typeorm table in DB as shown in image below:

Querry logs

DB tables

Let’s start our server and execute API we created last time. This will add record in DB.

We have finally setup migrations for our NestJS application. Next, we will be configuring Gmail OAuth2 to allow us access our users databases. s usual, this story code is available on GitHub in feature/add-migrations branch. If you appreciate this work, please show your support by clapping for the story and starring the repository.

Before we conclude, here’s a handy toolset you might want to check out: The Dev’s Tools. It’s not directly related to our tutorial, but we believe it’s worth your attention. The Dev’s Tools offers an expansive suite of utilities tailored for developers, content creators, and digital enthusiasts:

• Image Tools: Compress single or multiple images efficiently, and craft custom QR codes effortlessly.
• JSON Tools: Validate, compare, and ensure the integrity of your JSON data.
• Text Tools: From comparing texts, shuffling letters, and cleaning up your content, to generating random numbers and passwords, this platform has got you covered.
• URL Tools: Ensure safe web browsing with the URL encoder and decoder.
• Time Tools: Calculate date ranges and convert between Unix timestamps and human-readable dates seamlessly.

It’s a treasure trove of digital utilities, so do give it a visit!