Backend cheatsheetTypeORM migrationsShip the schema safely

TypeORM
Migrations

Generate, create, run and revert, then the PostgreSQL rules that keep a busy table online while it changes: short lock timeouts, batched backfills and concurrent indexes.

Set up the CLI Check the locks

By Shree Kumar SharmaTypeORM 0.3PostgreSQL7 min read

Set up the CLI

One DataSource file that both the app and the CLI read, and four scripts you will run every week.

SetupDataSourceCLIts-node

data-source.tsTypeScript

export default new DataSource({
  type: 'postgres',
  url: process.env.DATABASE_URL,
  entities: ['dist/**/*.entity.js'],
  migrations: ['dist/migrations/*.js'],
  migrationsTableName: 'migrations',
  migrationsTransactionMode: 'each', // one transaction per migration, not one for all
  synchronize: false,                // never true outside a throwaway database
});

Why it matters: the CLI and the app agree on one config, and synchronize can never silently drop a column in production.

package.jsonJSON

{
  "scripts": {
    "typeorm": "typeorm-ts-node-commonjs -d src/data-source.ts",
    "migration:generate": "npm run typeorm -- migration:generate",
    "migration:run": "npm run typeorm -- migration:run",
    "migration:revert": "npm run typeorm -- migration:revert",
    "migration:show": "npm run typeorm -- migration:show"
  }
}

Why it matters: the data source flag lives in one place, so nobody runs a migration against the wrong config.

Command	What it does
`migration:generate src/migrations/AddPhone`	Diffs entities against the database and writes the SQL for you
`migration:create src/migrations/Backfill`	An empty migration you fill by hand; needs no database
`migration:run`	Runs every pending migration, oldest first
`migration:revert`	Runs down() of the last executed migration, one at a time
`migration:show`	Lists migrations, with [X] for executed

Anatomy of a migration

A class with a timestamp in its name, an up that moves forward and a down that undoes exactly that.

BasicsQueryRunnerup and downmigrations table

1727600000000-AddPhoneE164.tsTypeScript

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

  async up(q: QueryRunner): Promise<void> {
    await q.query(`SET LOCAL lock_timeout = '3s'`);
    await q.query(`ALTER TABLE contacts ADD COLUMN phone_e164 text`);
  }

  async down(q: QueryRunner): Promise<void> {
    await q.query(`ALTER TABLE contacts DROP COLUMN phone_e164`);
  }
}

Why it matters: the timestamp orders migrations, and a down that truly reverses up is what makes revert safe.

migrations tableid1timestamp1727600000000name'AddPhoneE1641727600000000'

one row per executed migrationrevert deletes the last row

TerminalTerminal

$ npm run migration:show
[X] 1 CreateContacts1727000000000
[X] 2 AddPhoneE1641727600000000
[ ] 3 ContactsPhoneNotNull1727700000000
# one pending migration: run it before deploying code that needs it

Generate or create

Generate when entities changed and you want the diff. Create when the change is data, or needs care the diff cannot know.

WorkflowgeneratecreateReview

Generatemigration:generate AddPhoneCompares entities with the live schema and writes the SQL. Always read it: it happily drops and recreates.

Createmigration:create BackfillAn empty class. For backfills, CONCURRENTLY indexes, CHECK constraints and anything order sensitive.

TerminalTerminal

$ npm run migration:generate -- src/migrations/AddPhoneE164
Migration /app/src/migrations/1727600000000-AddPhoneE164.ts has been generated successfully.
$ npm run migration:generate -- src/migrations/Nothing
No changes in database schema were found - cannot generate a migration.
# generate only works when entities and schema differ

Generated SQL looks like	It means	Do instead
`DROP COLUMN` then `ADD COLUMN`	You renamed a property	Expand and contract, or a hand written RENAME
`ALTER COLUMN ... TYPE`	A type change that may rewrite the table	New column, dual write, backfill, swap
`SET NOT NULL`	A full scan under ACCESS EXCLUSIVE	CHECK NOT VALID, VALIDATE, then SET NOT NULL
`CREATE INDEX`	Writes blocked while it builds	CREATE INDEX CONCURRENTLY in its own migration

Know the lock

Most ALTERs finish in milliseconds. The danger is the lock they wait for, and the queue that forms behind them.

PostgreSQLMust knowlock_timeoutLock queue

Long SELECTholds ACCESS SHARE for 40s
Your ALTERwaits for ACCESS EXCLUSIVE
Every new queryqueues behind the ALTER
Pool exhaustedthe site looks down

Locks are granted in order, so one migration waiting behind a slow report makes every later query wait too. Set a short lock_timeout in every migration so it gives up quickly, lets the queue drain, and can be retried.

Change	Lock	Work	Safe way on a busy table
ADD COLUMN, nullable	ACCESS EXCLUSIVE	Instant	Safe with lock_timeout
ADD COLUMN with constant DEFAULT	ACCESS EXCLUSIVE	Instant on PG 11+	Safe with lock_timeout
ADD COLUMN with volatile DEFAULT	ACCESS EXCLUSIVE	Rewrites table	Add nullable, backfill, then SET DEFAULT
ALTER COLUMN TYPE int to bigint	ACCESS EXCLUSIVE	Rewrites table and indexes	New column, dual write, backfill, swap
SET NOT NULL	ACCESS EXCLUSIVE	Full scan	CHECK NOT VALID, VALIDATE, SET NOT NULL
CREATE INDEX	SHARE	Blocks writes while building	CREATE INDEX CONCURRENTLY
ADD FOREIGN KEY	SHARE ROW EXCLUSIVE, both tables	Scans rows	NOT VALID, then VALIDATE CONSTRAINT
RENAME or DROP COLUMN	ACCESS EXCLUSIVE	Instant	Only after no running code uses it

A required column

Nullable first, backfill in batches, then NOT NULL with a validated CHECK doing the slow part under a weak lock.

RecipeBackfillCHECK NOT VALIDPG 12+

ExpandADD COLUMN phone_e164 text
Dual writeapp writes both columns
Backfillbatches of 5,000
EnforceCHECK, VALIDATE, NOT NULL
Contractdrop phone next release

1727700000000-ContactsPhoneNotNull.tsTypeScript

async up(q: QueryRunner) {
  await q.query(`SET LOCAL lock_timeout = '3s'`);
  // instant: only rows written from now on are checked
  await q.query(`ALTER TABLE contacts ADD CONSTRAINT phone_e164_nn CHECK (phone_e164 IS NOT NULL) NOT VALID`);
  // full scan, but under SHARE UPDATE EXCLUSIVE: reads and writes continue
  await q.query(`ALTER TABLE contacts VALIDATE CONSTRAINT phone_e164_nn`);
  // instant on PG 12+: the valid CHECK already proves it
  await q.query(`ALTER TABLE contacts ALTER COLUMN phone_e164 SET NOT NULL`);
  await q.query(`ALTER TABLE contacts DROP CONSTRAINT phone_e164_nn`);
}

Why it matters: the only step that reads every row runs under a lock that lets traffic through.

backfill.sqlSQL

UPDATE contacts c SET phone_e164 = normalize_e164(c.phone)
WHERE c.id IN (
  SELECT id FROM contacts WHERE phone_e164 IS NULL
  ORDER BY id LIMIT 5000
  FOR UPDATE SKIP LOCKED
);

Why it matters: each batch is a short transaction, and running it again just picks up where it stopped.

Before you start, estimate how long the backfill will take. Run this with your own row count.

backfill-estimate.jsJavaScript

const rows = 12_480_211;
const batch = 5_000;
const msPerBatch = 180; // measure one batch on a replica first
const pauseMs = 200;    // room for replicas and autovacuum

const batches = Math.ceil(rows / batch);
const minutes = (batches * (msPerBatch + pauseMs)) / 60000;
console.log('batches:', batches.toLocaleString('en-IN'));
console.log('estimated minutes:', minutes.toFixed(1));
console.log('rows per second:', Math.round(batch / ((msPerBatch + pauseMs) / 1000)).toLocaleString('en-IN'));

TerminalOutput

$ node backfill-estimate.js
batches: 2,497
estimated minutes: 15.8
rows per second: 13,158

Why it matters: knowing it takes 16 minutes, not 16 seconds, decides whether it runs in the deploy or as a job.

Indexes and transactions

CREATE INDEX CONCURRENTLY cannot run in a transaction, and TypeORM wraps migrations in one. Turn it off for that migration only.

IndexesCONCURRENTLYtransaction = falseInvalid index

1727800000000-ContactsPhoneIndex.tsTypeScript

export class ContactsPhoneIndex1727800000000 implements MigrationInterface {
  transaction = false; // needs migrationsTransactionMode: 'each'

  async up(q: QueryRunner) {
    await q.query(`CREATE INDEX CONCURRENTLY IF NOT EXISTS contacts_phone_e164_idx ON contacts (phone_e164)`);
  }

  async down(q: QueryRunner) {
    await q.query(`DROP INDEX CONCURRENTLY IF EXISTS contacts_phone_e164_idx`);
  }
}

Why it matters: writes never stop while the index builds, and down is just as gentle as up.

migrationsTransactionMode	Behaviour	Use when
`all` (default)	Every pending migration in one transaction	Small projects; a failure rolls back the whole batch
`each`	One transaction per migration; `transaction = false` opts out	Production, and any CONCURRENTLY index
`none`	No transactions at all	Rarely; a failure leaves partial changes

psqlTerminal

$ SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
 contacts_phone_e164_idx
# a cancelled concurrent build leaves this behind: drop it concurrently and run again

Ship it

Migrations run before the code that needs them, contracts wait a full release, and every failure has a known next step.

ReleaseCI/CDRevertTroubleshooting

Picture it: the deploy order

Error	Cause	Fix
`canceling statement due to lock timeout`	Something held the table longer than 3s	Retry off peak; find the blocker in pg_stat_activity
`No changes in database schema were found`	Entities already match the schema	Use migration:create for data changes
`CREATE INDEX CONCURRENTLY cannot run inside a transaction block`	The migration is in a transaction	transaction = false with mode each
`relation "..." already exists`	synchronize ran, or a half applied migration	Turn synchronize off; make up idempotent with IF NOT EXISTS
`column "..." does not exist` after deploy	Code shipped before its migration	Run migrations first in the pipeline

TerminalTerminal

$ npm run migration:revert
Migration ContactsPhoneIndex1727800000000 has been reverted successfully.
# revert undoes one migration per run, newest first

Which one do I need?

Find your change, then follow the column on the right. When in doubt, split it into two releases.

I want to	Do	Command
Add an optional column	One migration with lock_timeout	`migration:generate`
Add a required computed column	Nullable, backfill, CHECK, NOT NULL	`migration:create` twice
Add an index to a big table	CONCURRENTLY, transaction off	`migration:create`
Rename a column	New column, dual write, swap, drop later	Several releases
Change a column type	New column, backfill, swap	Several releases
See what is pending	Check before every deploy	`migration:show`
Undo the last migration	Only if down is truly reversible	`migration:revert`