Migrations

Generated versus hand-written migrations in TypeORM 1.x, and how to run them during a deploy rather than at boot.

Syntaxtypeorm migration:generate -d <path/to/data-source> <path/to/migrations>/<Name>

A migration is a timestamped class with up() and down() methods that TypeORM runs once and records in a migrations table. It exists so that the schema in production changes in reviewed, repeatable steps instead of being inferred from your entity classes at startup.

Generated versus hand-written

migration:generate diffs your entities against the connected database and writes the SQL for you. migration:create writes an empty up/down pair for changes a diff cannot express: backfilling data, renaming a column without losing it, or creating an index CONCURRENTLY. Always read a generated file before committing it; a diff cannot tell a rename from a drop plus an add.

Where the DataSource lives

The CLI loads a standalone DataSource file, not your Nest application. Migrations therefore get no dependency injection: no providers, no ConfigService, only process.env. Set synchronize: false there, and keep migrationsRun: false so that booting an extra replica never races to alter tables.

Shorthand and longhand

npm run migration:run

# the same command, unwrapped
npx typeorm-ts-node-esm migration:run -d src/database/data-source.ts

Running them on deploy

Run migration:run once per release, as a separate step before the new pods take traffic, against the compiled dist DataSource. migration:show prints [X] for applied and [ ] for pending, and changes nothing, so it is safe against production.

Example

// src/database/data-source.ts
// Loaded by the TypeORM CLI, not by Nest. There is no injector here,
// so configuration comes from the environment.
import 'dotenv/config';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';
import { DataSource } from 'typeorm';
import { Customer } from '../customers/customer.entity.js';
import { Order } from '../orders/order.entity.js';
import { OrderItem } from '../orders/order-item.entity.js';

// __dirname does not exist in an ESM project; in a CommonJS one, use it directly.
const here = dirname(fileURLToPath(import.meta.url));

export default new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: [Customer, Order, OrderItem],
  // Both extensions: .ts when run through ts-node, .js from dist.
  migrations: [join(here, 'migrations', '*{.ts,.js}')],
  synchronize: false, // mandatory once migrations own the schema
  migrationsRun: false, // do not migrate on boot; a release step does it
  migrationsTableName: 'migrations',
  migrationsTransactionMode: 'all', // 'all' | 'each' | 'none'
});

When to use it

Adding a column to a table that already has production rows, without downtime.
Setting up a migration workflow for a project that was started with synchronize: true.
Running pending migrations once per release, before the new version takes traffic.
Checking from a shell which migrations a given environment has already applied.

More examples

The scripts, and what each command does

Generation needs your entities and a live database to diff against, so it belongs on a developer machine. Running needs only the compiled migration files, so it belongs in the deploy. Keep the two scripts separate: a pipeline that calls migration:generate can silently invent a migration nobody reviewed.

// package.json (excerpt)
{
  "scripts": {
    // Development: TypeScript DataSource, TypeScript migrations.
    "migration:generate": "typeorm-ts-node-esm migration:generate -d src/database/data-source.ts",
    "migration:create": "typeorm migration:create",
    "migration:run": "typeorm-ts-node-esm migration:run -d src/database/data-source.ts",
    "migration:revert": "typeorm-ts-node-esm migration:revert -d src/database/data-source.ts",
    "migration:show": "typeorm-ts-node-esm migration:show -d src/database/data-source.ts",

    // Deploy: compiled DataSource, compiled migrations.
    "migration:deploy": "typeorm migration:run -d dist/database/data-source.js"
  }
}

// Usage, with -- passing the positional argument through npm:
//
//   npm run migration:generate -- src/database/migrations/AddOrderStatus
//   npm run migration:create   -- src/database/migrations/BackfillOrderTotals
//   npm run migration:show
//   npm run migration:run
//   npm run migration:revert     # runs down() on the most recent migration, once
//
// In a release pipeline, after the build:
//
//   npm run migration:deploy && node dist/main.js

A hand-written migration with a data backfill

Three statements in one migration, in an order that works on a table that already has rows: add nullable, fill, then constrain. Write migrations by hand whenever data has to move, and remember that queryRunner.query is raw SQL against the database being migrated, so it must be valid for that one dialect.

// src/database/migrations/1764412800000-BackfillOrderTotals.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class BackfillOrderTotals1764412800000 implements MigrationInterface {
  // up() must leave the database in the new shape.
  async up(queryRunner: QueryRunner): Promise<void> {
    // 1. add the column as nullable, so the statement does not need a default
    await queryRunner.query(
      `ALTER TABLE "order" ADD "totalCents" integer`,
    );

    // 2. backfill from existing rows: the part a generated diff cannot write
    await queryRunner.query(`
      UPDATE "order" o
         SET "totalCents" = COALESCE((
               SELECT SUM(i."quantity" * i."unitPriceCents")
                 FROM "order_item" i
                WHERE i."orderId" = o."id"
             ), 0)
    `);

    // 3. only now can the column be made NOT NULL
    await queryRunner.query(
      `ALTER TABLE "order" ALTER COLUMN "totalCents" SET NOT NULL`,
    );
  }

  // down() must undo exactly what up() did.
  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`ALTER TABLE "order" DROP COLUMN "totalCents"`);
  }
}

Opting a migration out of the wrapping transaction

By default TypeORM wraps the whole run in one transaction (--transaction all). Statements that cannot run in a transaction need --transaction each or none plus transaction = false on the class. Reach for this when adding an index to a large, live table, where a plain CREATE INDEX would lock out writes.

// src/database/migrations/1764499200000-AddOrderCustomerIndex.ts
import { MigrationInterface, QueryRunner } from 'typeorm';

export class AddOrderCustomerIndex1764499200000 implements MigrationInterface {
  // Postgres refuses CREATE INDEX CONCURRENTLY inside a transaction.
  // This property only takes effect in the 'each' or 'none' transaction mode.
  transaction = false;

  async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(
      `CREATE INDEX CONCURRENTLY "order_customer_id_idx" ON "order" ("customerId")`,
    );
  }

  async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`DROP INDEX CONCURRENTLY "order_customer_id_idx"`);
  }
}

// Run it with per-migration transactions so the flag is honoured:
//   typeorm migration:run -d dist/database/data-source.js --transaction each
//
// The same choice as a DataSource option: migrationsTransactionMode: 'each'.

Discussion

Be the first to comment on this lesson.