Setup database migration in NestJS with Sequelize and MySQL

When building a system that uses a database, you will certainly encounter a situation where you need to change the structure of the database. It can be changing, adding, modifying, deleting columns of a table or creating new foreign key links to another table...

This is when we need to write commands to migrate the database. This article will introduce you to how to write migration using Sequelize and MySQL in NestJS. Let's get started now.

1. Create new NestJS project

Use Nest CLI to create a new project as follows:

# Install nest cli if you have not installed it before
$ npm i -g @nestjs/cli

# Create a new project named database-migration
$ nest new database-migration

2. Install modules

$ npm install @nestjs/sequelize sequelize sequelize-typescript mysql2 dotenv sequelize-cli

3. Create an env file

# Database
DATABASE_USERNAME=root
DATABASE_PASSWORD=Abcd@1234
DATABASE_HOST=localhost
DATABASE_PORT=3306
DATABASE_NAME=databse-migration
DATABASE_DIALECT=mysql

4. Create database module

In the src folder, create a new folder database to configure the connection to our database. Inside the database folder will include the following files/folders:

├── src
│   ├── database
│   │   ├── constant
│   │   │   ├── index.ts
│   │   ├── models
│   │   │   ├── user.entity.ts
│   │   ├── database.config.ts
│   │   ├── database.module.ts
│   │   ├── database.provider.ts
│   │   ├── sequelize.config.js

index.ts

export const SEQUELIZE = 'SEQUELIZE'

user.entity.ts

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

@Table
export class User extends Model<User> {
  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  name: string

  @Column({
    type: DataType.STRING,
    unique: true,
    allowNull: false,
  })
  phone: string
}

database.config.ts

const dotenv = require('dotenv')
dotenv.config()

import { SequelizeOptions } from 'sequelize-typescript'
import { Dialect } from 'sequelize'

export const sequelizeConfig: SequelizeOptions = {
  dialect: 'mysql' as Dialect,
  host: process.env.DATABASE_HOST || 'localhost',
  port: Number(process.env.DATABASE_PORT) || 3306,
  username: process.env.DATABASE_USERNAME || 'root',
  password: process.env.DATABASE_PASSWORD || '',
  database: process.env.DATABASE_NAME || 'your_database',
  logging: false,
}

database.provider.ts

import { Logger } from '@nestjs/common'
import { Sequelize } from 'sequelize-typescript'
import { SEQUELIZE } from './constant'
import { sequelizeConfig } from './database.config'
import { User } from './models/user.entity'

export const databaseProviders = [
  {
    provide: SEQUELIZE,
    useFactory: async () => {
      const sequelize = new Sequelize({
        ...sequelizeConfig,
        logging: Logger.log,
        define: {
          timestamps: true,
          underscored: true,
          paranoid: true,
          createdAt: 'created_at',
          updatedAt: 'updated_at',
          deletedAt: 'deleted_at',
          charset: 'utf8mb4',
        },
        retry: {
          max: 20,
          match: [
            /ConnectionError/,
            /SequelizeConnectionError/,
            /SequelizeConnectionRefusedError/,
            /SequelizeHostNotFoundError/,
            /SequelizeHostNotReachableError/,
            /SequelizeInvalidConnectionError/,
            /SequelizeConnectionTimedOutError/,
            /SequelizeConnectionAcquireTimeoutError/,
            /Connection terminated unexpectedly/,
          ],
        },
      })
      sequelize.addModels([User])
      await sequelize.sync()

      return sequelize
    },
  },
]

database.module.ts

import { Module } from '@nestjs/common'
import { databaseProviders } from './database.provider'

@Module({
  providers: [...databaseProviders],
  exports: [...databaseProviders],
})
export class DatabaseModule {}

sequelize.config.js

// src/config/sequelize-config.js
require('ts-node/register')
const { sequelizeConfig } = require('./database.config.ts')

module.exports = sequelizeConfig

Now you need to import DatabaseModule into AppModule

app.module.ts

import { Module } from '@nestjs/common'
import { AppController } from './app.controller'
import { AppService } from './app.service'
import { DatabaseModule } from './database/database.module'

@Module({
  imports: [DatabaseModule],
  controllers: [AppController],
  providers: [AppService],
})
export class AppModule {}

5. Create migration file

First you need to create a .sequelizerc file to declare the paths to the database configuration file.

.sequelizerc

const path = require('path')

module.exports = {
  config: path.resolve('src/database/sequelize.config.js'),
  'migrations-path': path.resolve('src/database/migrations'),
  'seeders-path': path.resolve('src/database/seeders'),
}

For convenience, we will add the following 3 commands to the package.json file

    "db:migrate:up": "npx sequelize-cli db:migrate",
    "db:migrate:down": "npx sequelize-cli db:migrate:undo",
    "db:migrate:new": "npx sequelize-cli migration:generate"

Now try to create a new migration file with the following command:

npm run db:migrate:new --name user-add-email

After running the above command, a javascript file will be generated in the migrations folder. You need to change the file format from .js to .ts.

Suppose in this case we want to add the email column to the users table, we will write the code as follows:

import { QueryInterface, DataTypes } from 'sequelize'

module.exports = {
  up: (queryInterface: QueryInterface): Promise<void> =>
    queryInterface.sequelize.transaction(async (transaction) => {
      await queryInterface.addColumn('users', 'email', {
        type: DataTypes.STRING,
      })
    }),

  down: (queryInterface: QueryInterface): Promise<void> =>
    queryInterface.sequelize.transaction(async (transaction) => {
      // here go all migration undo changes
      await queryInterface.removeColumn('users', 'email')
    }),
}

6. Run the migration file

Type the following command in the terminal:

npm run db:migrate:up

And the result after running the command will be:

> database-migration@0.0.1 db:migrate:up
> npx sequelize-cli db:migrate


Sequelize CLI [Node: 20.17.0, CLI: 6.6.2, ORM: 6.37.3]

Loaded configuration file "src\database\sequelize.config.js".
== 20240929140925-user-add-email: migrating =======
== 20240929140925-user-add-email: migrated (0.107s)

Finally, you just need to update the user model, add the email field and you're done

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

@Table
export class User extends Model<User> {
  @Column({
    type: DataType.STRING,
    allowNull: false,
  })
  name: string

  @Column({
    type: DataType.STRING,
    unique: true,
    allowNull: false,
  })
  phone: string

  @Column({
    type: DataType.STRING,
  })
  email: string
}

In case you want to cancel the previously run migration file and return to the old state, you can run the following command:

npm run db:migrate:down

So I have guided you how to create a new migration file and execute it with just one command line. The entire sample code of the project is published on Github, you can refer to and use them. If you find it interesting, please share and support me.