How to Run Postgres Migrations in a TypeScript API

Every TypeScript backend with Postgres needs a way to change its schema over time, and the answer is always some form of versioned migrations: files in the repository, applied in order, with a table in the database recording which ones have run. The tools differ in who writes the SQL, you or a generator, and in who is responsible for running it on deploy.

That second part is where most incidents come from. A migration that runs after the new code is already serving, a failed migration that leaves the schema half changed, or a constraint that locks a busy table for minutes. This guide covers the main options for TypeScript and the patterns that keep a schema change from breaking the code that is still running.

TypeScript migration tools at a glance

Each option is covered below, starting with Encore.

Applying migrations on every deploy with Encore

Encore is a tool that works alongside your framework for TypeScript and Go, with a platform that provisions and deploys into your own AWS or GCP account. A database is declared in code with a migrations folder, and Encore applies the migrations when you run the app locally and before new code starts on every deploy, in your own AWS or GCP account. A billing service with an invoices table starts with one migration:

-- billing/migrations/1_create_invoices.up.sql
CREATE TABLE invoices (
    id BIGSERIAL PRIMARY KEY,
    customer TEXT NOT NULL,
    amount_cents BIGINT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The service declares the database next to the endpoints that use it:

// billing/billing.ts
import { api } from "encore.dev/api";
import { SQLDatabase } from "encore.dev/storage/sqldb";

// Encore applies billing/migrations in order on encore run and on every deploy.
const db = new SQLDatabase("billing", { migrations: "./migrations" });

interface Invoice {
  id: number;
  customer: string;
  amountCents: number;
  status: "open" | "paid" | "void";
}

export const create = api(
  { expose: true, method: "POST", path: "/invoices" },
  async (p: { customer: string; amountCents: number }): Promise<Invoice> => {
    const row = await db.queryRow<Invoice>`
      INSERT INTO invoices (customer, amount_cents)
      VALUES (${p.customer}, ${p.amountCents})
      RETURNING id, customer, amount_cents AS "amountCents", status`;
    return row!;
  },
);

export const markPaid = api(
  { expose: true, method: "POST", path: "/invoices/:id/paid" },
  async ({ id }: { id: number }): Promise<Invoice> => {
    const row = await db.queryRow<Invoice>`
      UPDATE invoices SET status = 'paid' WHERE id = ${id}
      RETURNING id, customer, amount_cents AS "amountCents", status`;
    return row!;
  },
);

Files are named <number>_<description>.up.sql and run in order. Encore records the last applied version in a schema_migrations table, and the database docs describe the format in full. encore run starts a local Postgres and applies everything, so a new migration is tested the moment you save it. If a migration fails, Encore rolls it back, and during a cloud deploy it aborts the deploy, so the previous version keeps serving traffic against the previous schema.

Now the invoices need a status. The tempting version adds a nullable column, backfills it and sets NOT NULL in one go. The safer version is a single statement:

-- billing/migrations/2_add_status.up.sql
-- A constant default fills existing rows without rewriting the table
-- (Postgres 11+), and code that does not know about status keeps working.
ALTER TABLE invoices ADD COLUMN status TEXT NOT NULL DEFAULT 'open';

Then the allowed values become a constraint. Adding a CHECK constraint normally scans the whole table while holding a lock that blocks writes. NOT VALID skips the scan for existing rows, and validating in a separate migration checks them under a lighter lock:

-- billing/migrations/3_status_check.up.sql
-- NOT VALID adds the constraint for new writes without scanning existing rows.
ALTER TABLE invoices ADD CONSTRAINT invoices_status_check
    CHECK (status IN ('open', 'paid', 'void')) NOT VALID;

CREATE INDEX invoices_status_idx ON invoices (status);

-- billing/migrations/4_validate_status_check.up.sql
-- Checks existing rows under a lock that still allows reads and writes.
ALTER TABLE invoices VALIDATE CONSTRAINT invoices_status_check;

We applied these four migrations to Encore's local Postgres and confirmed that a row created before migration 2 reads open, that new rows get the default, and that an update to an unknown status is rejected by the constraint.

Encore records applied migrations in the schema_migrations table, so once a migration number has been applied, editing that file does not run it again. Treat applied migrations as immutable and add a new file for every change. Locally, encore db reset billing drops the database and replays all migrations when you do want a clean start.

For coding agents, the loop is short: the agent writes a migration, encore run applies it locally, and the deploy aborts if it fails in the cloud. Cloud access sits with Encore's IAM role, not the agent.

The connect your cloud account and deployment guides cover how migrations run as part of a deploy to your own AWS or GCP account.

Drizzle Kit

Drizzle defines tables in TypeScript, and Drizzle Kit compares that schema with the previous snapshot to generate SQL migration files with drizzle-kit generate. In your pipeline, drizzle-kit migrate applies pending files and records them in its own table. drizzle-kit push skips the files and syncs the schema directly, which is handy for prototypes and risky for production.

Read every generated file before it ships. Drizzle Kit can generate a rename as a drop and an add, or a NOT NULL column without a default. With Encore you keep the same generation step and let Encore apply the files, by declaring the database with migrations: { path: "migrations", source: "drizzle" } as shown in the Drizzle guide.

Prisma Migrate

Prisma generates migrations from schema.prisma. prisma migrate dev creates and applies them locally, and prisma migrate deploy applies pending ones in production. A migration that fails is recorded as failed, and later deploys stop until you fix the database and mark it with prisma migrate resolve. Encore supports Prisma's folder with source: "prisma".

node-pg-migrate

node-pg-migrate is the choice when you want migrations as code without an ORM. Each migration is a JS or TS file with up and down functions that call a builder, or raw SQL through pgm.sql. It runs each migration in a transaction by default and records them in a pgmigrations table. The trade-off is that the migration is code rather than plain SQL, so reviewing it means reading the builder calls.

Schema changes that do not break running code

Migrations run before the new code starts, so for a short time the old code runs against the new schema. Every migration has to be compatible with the code currently deployed:

• Adding a column: add it nullable or with a constant default, never as NOT NULL without a default.
• Renaming a column: add the new column, write to both, backfill, switch reads, then drop the old one in a later deploy.
• Dropping a column: stop reading and writing it in one deploy, then drop it in the next.
• Constraints: add them NOT VALID and validate separately on large tables.
• Indexes on large tables: a plain CREATE INDEX blocks writes while it builds. CREATE INDEX CONCURRENTLY avoids that but cannot run inside a transaction, so check if your tool wraps migrations in one.

How to choose

Use Encore when you want migrations applied locally and on every deploy to your own AWS or GCP account, with failed migrations stopping the deploy. Keep Drizzle Kit or Prisma for generating migrations if you already use those ORMs, and let Encore apply the output. Use node-pg-migrate or one of the ORM tools on their own when you deploy elsewhere and already have a pipeline that runs a migration step.

Related guides

• How to run Postgres migrations in a Go API
• How to add a database and deploy a Go API with an AI agent
• Best database setup for AI coding agents
• TypeScript backend stack in 2026