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 Key terms in plain words

By Shree Kumar SharmaTypeORM 0.3PostgreSQL7 min read

Key terms in plain words

New to databases or migrations? Start here. Every word below shows up later on this page, each compared to a records office that has to stay open while its ledgers are re-ruled. Underlined words in the modules link back to these cards.

What you're changing Migrations themselves Why a quick change can stall a site Changing a busy table safely Shipping it

22 terms in 5 groups. Hover an underlined word anywhere on the page for a quick reminder, or click it to jump here.

What you're changing

The database is a busy records office. Its ledgers are the tables, and the floor plan says which ledgers exist and how each is ruled.

Database: Think of it as the whole records office, open to customers all day; The program and the stored data your app reads and writes. This page uses PostgreSQL, a popular free one. It is shared by every copy of your app, so a change to it affects them all at once.
Table: Think of it as one ledger, ruled into columns, with one line per record; Where data lives: a grid of rows and columns about one kind of thing. Each row is one record, such as one contact, and each column is one piece of information about it, such as phone.; For example The contacts table has a row per contact and a column per detail.
Schema: Think of it as the office floor plan: which ledgers exist and how each one is ruled; The shape of the database: its tables, their columns, the type of each column and the rules on them. A migration is how you change the schema.
ORM: Think of it as an interpreter who turns your app's requests into the office's own forms; Object relational mapper: a library that lets your code work with objects while it writes the SQL, the database's language, for you. TypeORM is the ORM used on this page, and it also runs migrations.
Entity: Think of it as the architect's drawing of what one ledger should look like; A class in your code that describes a table: its name, its columns and their types. TypeORM can compare your entities with the real database and write the SQL to close the gap.

Migrations themselves

Numbered work orders for the office, plus the logbook that says which ones are done.

Migration: Think of it as a numbered work order: "add a phone column to the contacts ledger"; A small file of code that changes the database schema one step. Its name starts with a timestamp, so every machine applies the same work orders in the same order.; For example 1727600000000-AddPhoneE164.ts
up and down: Think of it as the work order, and the instructions for undoing it; Every migration has two methods. up() makes the change and down() reverses exactly that change. Both receive a QueryRunner, the object that sends SQL to the database.
Migrations table: Think of it as the logbook where each finished work order is signed off; A table TypeORM keeps in your database with one row per migration that has run. That's how it knows which ones are still pending.
DataSource and the CLI: Think of it as the one address book that both the office staff and the contractors use; The DataSource file holds the database address and settings. The CLI (command line tool, a program you run by typing in a terminal) reads the same file, so your app and your migration commands never disagree about which database they mean.
Generate or create: Think of it as letting the architect draft the work order, or writing it out by hand; migration:generate compares your entities with the database and writes the SQL for you, so read it before trusting it. migration:create gives you an empty migration to fill in yourself, for data changes and careful steps the comparison can't know about.

Why a quick change can stall a site

A one-second edit can freeze every visitor if it has to wait its turn at the counter.

Transaction: Think of it as a batch of edits that are all signed off together, or all torn up; A group of database changes that succeed or fail as one, so a half finished migration never sticks. TypeORM wraps each migration in one by default.
Lock: Think of it as a "closed for re-ruling" sign hung on one ledger; A claim a statement takes on a table so others can't change it at the same moment. Locks come in strengths. ACCESS EXCLUSIVE, the strongest, blocks even reads, and most ALTER TABLE changes need it.
Lock queue: Think of it as a line forming behind a clerk who is waiting for one ledger; Locks are handed out in order. If your migration waits behind a slow report, every query after it waits too, the app's connection pool (its small set of open database connections) runs dry, and the site looks down.
lock_timeout: Think of it as "if the ledger isn't free in three seconds, come back later"; A setting that makes a statement give up if it can't get its lock in time. The migration fails fast, the queue behind it drains, and you retry later.; For example SET LOCAL lock_timeout = '3s'
Index: Think of it as the alphabetical index at the back of a ledger; A sorted list that points straight to matching rows, so lookups don't read the whole table. A normal build blocks writes; CREATE INDEX CONCURRENTLY keeps the table open but can't run inside a transaction.

Changing a busy table safely

Splitting one risky change into small steps the office can absorb while customers keep coming.

Backfill: Think of it as going back through old ledger lines to fill in the new column; Writing values into a new column for rows that existed before it. Done in small batches, each in its own short transaction, so no lock is held for long and a rerun carries on where it stopped.
NOT NULL and CHECK NOT VALID: Think of it as a rule that every line must have this box filled, checked in stages; NOT NULL means a column must always have a value. Adding it directly scans the whole table under the strongest lock. Adding a CHECK rule as NOT VALID first, then validating it, does the slow scan under a weak lock instead.
Expand and contract: Think of it as opening the new counter before closing the old one; Change a table in safe steps spread over releases: add the new column, write to both, backfill, switch reads over, and only then drop the old one. Old and new code both keep working at every step.

Shipping it

Running the work orders in the right order on release day, and knowing how to back out.

Deploy order: Think of it as re-ruling the ledger before the staff are told to use the new column; A deploy, or release, puts a new version of the app live. Migrations run first, so the code that needs a column never arrives before the column does.
Revert: Think of it as carrying out the last work order's undo instructions; migration:revert runs down() for the newest migration that has run, one per call. It's only safe when down() truly reverses up().
synchronize: Think of it as a contractor who rebuilds the office to match the drawing without asking; A TypeORM setting that alters the database to match your entities every time the app starts. It can drop a column and its data silently, so keep it false anywhere but a throwaway database.
Idempotent: Think of it as a work order that's harmless to carry out twice; Safe to run again with the same result. IF NOT EXISTS makes a step skip itself when it's already done, which rescues a migration that half applied.

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`