Skip to main content

Database Migrations

Overview​

Database migrations provide a systematic way to manage database schema changes over time. The starter kit integrates TypeORM's migration system to help you manage schema changes safely.

Migration Principles​

  1. Version Control: Migrations should be committed to version control to track database changes
  2. Forward Only: Each migration should be designed to run only once
  3. Incremental Changes: Break complex schema changes into smaller, manageable migrations
  4. Reversibility: When possible, include both up and down migrations for rollbacks

Configuration​

TypeORM Migration Configuration​

The starter kit configures migrations in the database module and ormconfig.js:

// ormconfig.js
module.exports = {
type: process.env.DB_TYPE || 'postgres',
host: process.env.DB_HOST || 'localhost',
port: parseInt(process.env.DB_PORT, 10) || 5432,
username: process.env.DB_USERNAME || 'postgres',
password: process.env.DB_PASSWORD || 'postgres',
database: process.env.DB_DATABASE || 'nest_starter',
entities: [__dirname + '/dist/**/*.entity{.ts,.js}'],
migrations: [__dirname + '/dist/migrations/**/*{.ts,.js}'],
cli: {
migrationsDir: 'src/migrations',
},
};

NPM Scripts​

The starter kit includes npm scripts to simplify migration commands:

{
"scripts": {
"migration:generate": "npm run build && typeorm migration:generate -n",
"migration:run": "npm run build && typeorm migration:run",
"migration:revert": "npm run build && typeorm migration:revert",
"migration:create": "typeorm migration:create -n"
}
}

Creating Migrations​

Automatic Migration Generation​

TypeORM can automatically generate migrations by comparing your entity definitions to the current database schema:

npm run migration:generate -- CreateUsers

This generates a migration file like TIMESTAMP-CreateUsers.ts in the src/migrations directory:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class CreateUsers1647443300405 implements MigrationInterface {
name = 'CreateUsers1647443300405';

public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`
CREATE TABLE "users" (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"email" character varying NOT NULL,
"password" character varying NOT NULL,
"firstName" character varying NOT NULL,
"lastName" character varying NOT NULL,
"isActive" boolean NOT NULL DEFAULT false,
"role" character varying NOT NULL DEFAULT 'user',
"createdAt" TIMESTAMP NOT NULL DEFAULT now(),
"updatedAt" TIMESTAMP NOT NULL DEFAULT now(),
CONSTRAINT "UQ_users_email" UNIQUE ("email"),
CONSTRAINT "PK_users" PRIMARY KEY ("id")
)
`);
}

public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.query(`DROP TABLE "users"`);
}
}

Manual Migration Creation​

For more complex migrations, you can create a migration manually:

npm run migration:create -- AddUserProfileData

This creates a skeleton migration file that you can fill in:

import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddUserProfileData1647443400123 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<void> {
// Add your migration logic here
await queryRunner.query(`
ALTER TABLE "users"
ADD COLUMN "profilePicture" character varying,
ADD COLUMN "bio" text
`);
}

public async down(queryRunner: QueryRunner): Promise<void> {
// Add your rollback logic here
await queryRunner.query(`
ALTER TABLE "users"
DROP COLUMN "profilePicture",
DROP COLUMN "bio"
`);
}
}

Running Migrations​

Apply Pending Migrations​

To run all pending migrations:

npm run migration:run

Revert Migrations​

To revert the most recent migration:

npm run migration:revert

Complex Migration Examples​

Adding Foreign Keys​

public async up(queryRunner: QueryRunner): Promise<void> {
// Create the posts table
await queryRunner.query(`
CREATE TABLE "posts" (
"id" uuid NOT NULL DEFAULT uuid_generate_v4(),
"title" character varying NOT NULL,
"content" text NOT NULL,
"authorId" uuid,
"createdAt" TIMESTAMP NOT NULL DEFAULT now(),
"updatedAt" TIMESTAMP NOT NULL DEFAULT now(),
CONSTRAINT "PK_posts" PRIMARY KEY ("id")
)
`);

// Add the foreign key
await queryRunner.query(`
ALTER TABLE "posts"
ADD CONSTRAINT "FK_posts_users"
FOREIGN KEY ("authorId")
REFERENCES "users"("id")
ON DELETE CASCADE
`);
}

public async down(queryRunner: QueryRunner): Promise<void> {
// Drop the foreign key first
await queryRunner.query(`
ALTER TABLE "posts"
DROP CONSTRAINT "FK_posts_users"
`);

// Then drop the table
await queryRunner.query(`DROP TABLE "posts"`);
}

Data Migrations​

public async up(queryRunner: QueryRunner): Promise<void> {
// Create new role column with enum type
await queryRunner.query(`
CREATE TYPE "user_role_enum" AS ENUM ('admin', 'moderator', 'user')
`);

// Add the new column
await queryRunner.query(`
ALTER TABLE "users"
ADD COLUMN "roleEnum" "user_role_enum" NOT NULL DEFAULT 'user'
`);

// Migrate data from string role to enum role
await queryRunner.query(`
UPDATE "users"
SET "roleEnum" = 'admin'::"user_role_enum"
WHERE "role" = 'admin'
`);

await queryRunner.query(`
UPDATE "users"
SET "roleEnum" = 'moderator'::"user_role_enum"
WHERE "role" = 'moderator'
`);

// Drop old column
await queryRunner.query(`
ALTER TABLE "users"
DROP COLUMN "role"
`);

// Rename new column
await queryRunner.query(`
ALTER TABLE "users"
RENAME COLUMN "roleEnum" TO "role"
`);
}

Index Management​

public async up(queryRunner: QueryRunner): Promise<void> {
// Add indexes for better performance
await queryRunner.query(`
CREATE INDEX "IDX_users_email" ON "users"("email")
`);

await queryRunner.query(`
CREATE INDEX "IDX_posts_author" ON "posts"("authorId")
`);

await queryRunner.query(`
CREATE INDEX "IDX_posts_created_at" ON "posts"("createdAt")
`);
}

public async down(queryRunner: QueryRunner): Promise<void> {
// Drop indexes
await queryRunner.query(`DROP INDEX "IDX_posts_created_at"`);
await queryRunner.query(`DROP INDEX "IDX_posts_author"`);
await queryRunner.query(`DROP INDEX "IDX_users_email"`);
}

Migration Best Practices​

1. Testing Migrations​

Always test migrations in a development or staging environment before applying them to production:

// In your test files
describe('Migrations', () => {
it('should run all migrations without errors', async () => {
const connection = await createConnection({
...ormConfig,
database: 'test_migrations_db',
synchronize: false,
migrationsRun: true,
dropSchema: true,
});

expect(connection.isConnected).toBe(true);
await connection.close();
});
});

2. Database Backups​

Always back up your database before running migrations in production:

# For PostgreSQL
pg_dump -U username -d database_name -f backup.sql

# Restore if needed
psql -U username -d database_name -f backup.sql

3. Breaking Changes​

When making breaking schema changes, consider these strategies:

  1. Add before remove:

    • First migration: Add new columns/tables
    • Code update: Handle both old and new schemas
    • Second migration: Remove old columns/tables once code is updated
  2. Temporary columns:

    • Create temporary columns for transitions
    • Migrate data between columns
    • Remove temporary columns after successful migration

4. Performance Considerations​

  1. Large Tables: For large tables, consider running migrations during off-hours
  2. Locks: Be aware of table locks during migrations that could affect application performance
  3. Batching: Split large data migrations into smaller batches
public async up(queryRunner: QueryRunner): Promise<void> {
// Add new column
await queryRunner.query(`
ALTER TABLE "large_table" ADD COLUMN "new_column" text
`);

// Batch processing for large data migrations
const batchSize = 1000;
let processed = 0;
let hasMore = true;

while (hasMore) {
const result = await queryRunner.query(`
UPDATE "large_table"
SET "new_column" = "old_column"
WHERE "new_column" IS NULL
LIMIT ${batchSize}
`);

processed += result.affected || 0;
hasMore = result.affected === batchSize;

// Optional: Add a small delay to reduce database load
if (hasMore) {
await new Promise(resolve => setTimeout(resolve, 100));
}
}
}