Migrations in NestJS - Step-by-Step Execution

Concept Flow - Migrations

Write Migration Script

↓

Run Migration Command

↓

Migration Tool Reads Script

↓

Apply Changes to Database

↓

Update Migration History Table

↓

Migration Complete

This flow shows how a migration script is written, run, applied to the database, and recorded to keep track of changes.

Execution Sample

NestJS

import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddUsersTable1680000000000 implements MigrationInterface {
  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));`);
  }
  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP TABLE users;`);
  }
}

This migration creates a 'users' table when applied and removes it when rolled back.

Execution Table

Step	Action	QueryRunner Method	Database Change	Migration History Update
1	Start migration	N/A	No change yet	No update
2	Run up()	queryRunner.query()	Create 'users' table	Record migration as applied
3	Migration complete	N/A	'users' table exists	Migration history updated
4	Run down() to rollback	queryRunner.query()	Drop 'users' table	Remove migration record
5	Rollback complete	N/A	'users' table removed	Migration history updated
6	Exit	N/A	Database matches migration state	Migration tracking consistent

💡 Migration process ends after applying or rolling back changes and updating history.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
Database Schema	Empty	Has 'users' table	No 'users' table	Matches migration state
Migration History	Empty	Contains AddUsersTable record	Empty	Consistent with schema

Key Moments - 2 Insights

Why do we need both up() and down() methods in a migration?

The up() method applies changes, while down() reverts them. This allows safe rollbacks if needed, as shown in steps 2 and 4 of the execution_table.

What happens if the migration history is not updated after applying changes?

Without updating migration history (step 3), the system won't know the migration ran, risking repeated runs or conflicts.

Visual Quiz - 3 Questions

Look at the execution_table, what database change happens at step 2?

A Drop 'users' table

B No change

C Create 'users' table

D Update migration history only

💡 Hint

Refer to the 'Database Change' column in step 2 of the execution_table.

At which step is the migration recorded as applied?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Check the 'Migration History Update' column for when the migration is recorded.

If the down() method is not implemented, what impact is seen in the execution_table?

A Step 4 would fail to drop the table

B Step 2 would not create the table

C Migration history would not update at step 3

D No impact, migration runs normally

💡 Hint

Look at step 4 where down() runs queryRunner.query() to drop the table.

Concept Snapshot

Migrations in NestJS with TypeORM:
- Use MigrationInterface with up() and down() methods
- up() applies DB changes (e.g., create table)
- down() reverts changes (e.g., drop table)
- Run migrations via CLI to update DB and migration history
- Keeps DB schema in sync and allows safe rollbacks

Full Transcript

Migrations in NestJS help manage database changes safely. You write a migration class with up() to apply changes and down() to undo them. When you run the migration command, the system executes up(), updates the database schema, and records the migration in a history table. This history prevents running the same migration twice. If needed, you can rollback by running down(), which reverts the changes and updates the history. This process keeps your database structure consistent with your code. The execution table shows each step: starting migration, applying changes, updating history, and rolling back if necessary. Variables like the database schema and migration history change accordingly. Remember, both up() and down() are important for safe migrations.

Practice

1. What is the main purpose of migrations in a NestJS project using TypeORM?

easy

A. To handle HTTP requests and responses

B. To safely update the database schema step-by-step

C. To write business logic for the application

D. To manage user authentication

Solution

1. Step 1: Understand what migrations do

   Migrations help change the database structure safely without losing data.

2. Step 2: Identify the correct purpose in NestJS context

   They are not for business logic or HTTP handling but for database schema updates.

3. Final Answer:

   To safely update the database schema step-by-step -> Option B

4. Quick Check:

   Migrations = safe database updates [OK]

Hint: Migrations change database structure safely [OK]

Common Mistakes:

• Confusing migrations with business logic
• Thinking migrations handle HTTP requests
• Assuming migrations manage authentication

2. Which command correctly runs all pending migrations in a NestJS project using TypeORM?

easy

A. npx typeorm migration:create

B. npx typeorm migration:revert

C. npx typeorm migration:generate

D. npx typeorm migration:run

Solution

1. Step 1: Recall commands for migrations

   migration:run applies pending migrations to the database.

2. Step 2: Differentiate from other commands

   migration:create creates a new migration file, migration:generate generates migration from schema changes, and migration:revert undoes the last migration.

3. Final Answer:

   npx typeorm migration:run -> Option D

4. Quick Check:

   Run migrations = migration:run [OK]

Hint: Run migrations with migration:run command [OK]

Common Mistakes:

• Using migration:create to run migrations
• Confusing migration:generate with migration:run
• Using migration:revert to apply migrations

3. Given this migration class, what will happen when up() is run?

export class AddAgeColumn1678901234567 {
  async up(queryRunner) {
    await queryRunner.query(`ALTER TABLE users ADD COLUMN age int`);
  }
  async down(queryRunner) {
    await queryRunner.query(`ALTER TABLE users DROP COLUMN age`);
  }
}

medium

A. Creates a new table called 'age'

B. Removes the 'age' column from the 'users' table

C. Adds a new integer column 'age' to the 'users' table

D. Throws a syntax error because of missing semicolons

Solution

1. Step 1: Analyze the up() method

   The up() method runs SQL to add a column named 'age' of type integer to the 'users' table.

2. Step 2: Understand the effect of the query

   This modifies the existing 'users' table by adding the new column; it does not remove columns or create new tables.

3. Final Answer:

   Adds a new integer column 'age' to the 'users' table -> Option C

4. Quick Check:

   up() adds column 'age' [OK]

Hint: up() applies changes like adding columns [OK]

Common Mistakes:

• Confusing up() with down() method
• Thinking it creates a new table
• Assuming syntax error due to semicolons

4. You wrote this migration but get an error when running npx typeorm migration:run. What is the likely problem?

export class RemoveEmailColumn1678901234568 {
  async up(queryRunner) {
    await queryRunner.query(`ALTER TABLE users DROP email`);
  }
  async down(queryRunner) {
    await queryRunner.query(`ALTER TABLE users ADD email varchar(255)`);
  }
}

medium

A. The SQL syntax for dropping a column is incorrect

B. The migration class name is invalid

C. Missing async keyword in down() method

D. The migration file is not saved

Solution

1. Step 1: Check the SQL in up() method

   The query ALTER TABLE users DROP email is incorrect SQL syntax for dropping a column.

2. Step 2: Correct SQL syntax for dropping a column

   The correct syntax is ALTER TABLE users DROP COLUMN email. Missing 'COLUMN' causes the error.

3. Final Answer:

   The SQL syntax for dropping a column is incorrect -> Option A

4. Quick Check:

   DROP COLUMN keyword required in SQL [OK]

Hint: Use DROP COLUMN to remove columns in SQL [OK]

Common Mistakes:

• Omitting COLUMN keyword in DROP statement
• Assuming class name causes error
• Forgetting async keyword (not the cause here)

5. You want to create a migration that renames a column 'username' to 'user_name' in the 'accounts' table and can be reverted. Which up() and down() methods are correct?

hard

A. async up(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts RENAME COLUMN username TO user_name`);
}
async down(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts RENAME COLUMN user_name TO username`);
}

B. async up(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts CHANGE username user_name varchar(255)`);
}
async down(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts CHANGE user_name username varchar(255)`);
}

C. async up(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts DROP COLUMN username`);
await queryRunner.query(`ALTER TABLE accounts ADD COLUMN user_name varchar(255)`);
}
async down(queryRunner) {
await queryRunner.query(`ALTER TABLE accounts DROP COLUMN user_name`);
await queryRunner.query(`ALTER TABLE accounts ADD COLUMN username varchar(255)`);
}

D. async up(queryRunner) {
await queryRunner.query(`RENAME COLUMN username TO user_name`);
}
async down(queryRunner) {
await queryRunner.query(`RENAME COLUMN user_name TO username`);
}

Solution

1. Step 1: Identify correct SQL syntax for renaming columns

   The standard SQL syntax supported by many databases is ALTER TABLE table_name RENAME COLUMN old_name TO new_name.

2. Step 2: Check each option's SQL correctness and revert logic

   async up(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts RENAME COLUMN username TO user_name`);
   }
   async down(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts RENAME COLUMN user_name TO username`);
   }

   uses correct syntax for both up() and down() to rename and revert the column name.

   async up(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts CHANGE username user_name varchar(255)`);
   }
   async down(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts CHANGE user_name username varchar(255)`);
   }

   uses MySQL-specific CHANGE syntax which may not be portable.

   async up(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts DROP COLUMN username`);
     await queryRunner.query(`ALTER TABLE accounts ADD COLUMN user_name varchar(255)`);
   }
   async down(queryRunner) {
     await queryRunner.query(`ALTER TABLE accounts DROP COLUMN user_name`);
     await queryRunner.query(`ALTER TABLE accounts ADD COLUMN username varchar(255)`);
   }

   drops and adds columns, losing data.

   async up(queryRunner) {
     await queryRunner.query(`RENAME COLUMN username TO user_name`);
   }
   async down(queryRunner) {
     await queryRunner.query(`RENAME COLUMN user_name TO username`);
   }

   misses the table name in the query, causing error.

3. Final Answer:

   ALTER TABLE accounts RENAME COLUMN username TO user_name -> Option A

4. Quick Check:

   Rename column with ALTER TABLE RENAME COLUMN [OK]

Hint: Use ALTER TABLE RENAME COLUMN for renaming columns [OK]

Common Mistakes:

• Dropping and adding columns instead of renaming
• Omitting table name in ALTER TABLE
• Using database-specific syntax without checking compatibility