Database migrations explained, from first table to production

#What a database migration is

Your application code and your database schema change together. When you add a role field to the user model, the users table needs a matching column, and every database that runs your code needs it too: your laptop, a teammate's laptop, the CI database, staging and production.

A migration is the unit that carries that change. It is a small file of SQL statements, such as CREATE TABLE or ALTER TABLE ... ADD COLUMN, with a timestamp in its name so migrations always run in the same order. Applying all migrations from an empty database rebuilds the current schema exactly. Applying only the new ones upgrades an existing database without touching its data.

In kastor, the source of truth for the shape you want is your TypeScript schema, written with table() and the t column builders. The migration files are the source of truth for how each database got there. You need both.

#Schema drift and why it hurts

Schema drift is when two databases that should match do not. It usually starts innocently: someone adds an index by hand in production to fix a slow page, or runs an ALTER TABLE from a SQL console during an incident, or a feature branch changes a column and is then abandoned after its change was applied to staging.

Drift causes bugs that are hard to reproduce. A query works locally and fails in production because a column is nullable in one place and not the other. A migration that runs fine in CI fails on deploy because the index it creates already exists.

The cure is discipline more than tooling: every schema change goes through a migration file, and no one changes a shared database by hand. If an emergency fix does happen in production, write a migration that matches it right away, so the history and reality agree again.

#The migration workflow, step by step

The kastor workflow has four commands. First point kastor.config.ts at your schema and a migrations folder:

import { defineConfig } from 'kastor-orm'
import { postgres } from 'kastor-orm/postgres'
import pg from 'pg'

export default defineConfig({
  schema: './src/db/schema.ts',
  adapter: () => postgres(new pg.Pool({ connectionString: process.env.DATABASE_URL })),
  migrations: './kastor/migrations',
})

The day to day loop:

1. Change the schema in src/db/schema.ts. Types update as you type, there is no generate step.
2. Run migrate dev with a descriptive name (leave out --name and it suggests one). kastor first applies any pending migrations, for example from a teammate's branch, then diffs the TypeScript schema against your local database, writes kastor/migrations/<timestamp>_<name>/migration.sql and applies it. With --create-only it stops after writing the file.
3. Read the SQL and commit it together with the schema change, in the same pull request, so reviewers see both.
4. Run migrate deploy in CI or on release. It applies pending migrations in order and never generates anything new.

npx kastor migrate dev --name add_posts
git add src/db/schema.ts kastor/migrations
npx kastor migrate status
npx kastor migrate deploy

migrate status lists every migration as applied, pending or modified for the database your config connects to, flags applied migrations whose folder is missing, and warns when the schema has changes no migration covers yet. It exits with code 1 when anything is pending, modified or missing, and --json prints the same report for scripts.

#Readable SQL files and the migrations table

Each migration is a folder named <timestamp>_<name>, with the timestamp in UTC, and a single migration.sql inside. It is ordinary SQL for your database with a commented header that lists the changes, so you can read it in review and run it in your head:

-- Migration 20261013120000_add_posts
-- Written by kastor 0.1.0 for postgres on 2026-10-13 12:00:00 UTC.
-- Applied by `kastor migrate deploy`. Edit it before it is applied if you need to;
-- after that, the checksum in _kastor_migrations must keep matching.
--
-- Changes:
--   + Create table posts

-- Create table posts
CREATE TABLE "posts" (
  "id" uuid DEFAULT gen_random_uuid() NOT NULL,
  "title" varchar(200) NOT NULL,
  "body" text,
  "published" boolean DEFAULT false NOT NULL,
  "authorId" integer NOT NULL,
  CONSTRAINT "posts_pkey" PRIMARY KEY ("id"),
  CONSTRAINT "posts_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "users" ("id") ON DELETE CASCADE ON UPDATE NO ACTION
);
CREATE INDEX "posts_authorId_idx" ON "posts" ("authorId");

Next to the folders, migration_lock.toml records which database the files were written for. Commit it too: migrate dev and migrate deploy refuse to run these files against a different kind of database.

Because the file is plain SQL, you are allowed to edit it before it is applied. Plain migrate dev applies the file right away, so when you know you want to edit, run it with --create-only, change the SQL, then run migrate dev again: it applies pending files before diffing. Keep the file and the TypeScript schema consistent: if you add something by hand that the schema does not know about, the next diff may try to remove it.

To know what has been applied, kastor keeps a bookkeeping table named _kastor_migrations in each database. Every row records the migration folder name, a checksum of its migration.sql and when it was applied. migrate deploy compares the folders on disk with those rows and runs only what is missing. If an applied file has changed since, both migrate dev and migrate deploy stop with a MIGRATION_DRIFT error. That is why you should treat a migration as frozen once a database has applied it: fix mistakes with a new migration instead of rewriting history. On your own machine, migrate reset rebuilds the database from the files if you need a clean start.

#Renames and destructive changes

The riskiest diff is a rename. To a naive differ, renaming name to fullName looks like "drop column name, add column fullName", which deletes every value in that column. When migrate dev sees a column vanish and a compatible one appear in the same table, it asks whether you renamed it, and it does the same for tables. Pick "Renamed to fullName" and the migration uses ALTER TABLE "users" RENAME COLUMN "name" TO "fullName", so the data stays. Without a terminal to ask in, it stops with an error unless you pass --rename users.name:fullName (or --rename users:accounts for a table).

Changes that drop data for real, such as removing a column, get a second question: migrate dev and db push ask you to confirm before writing them, and refuse outside a terminal unless you pass --accept-data-loss. The header of the migration file marks those steps with [data loss].

If you only want a nicer property name in TypeScript, skip the migration entirely: keep the database column and map it with .map('name') on the new property.

Expand and contract

Some changes are destructive no matter what: dropping a column, making a column NOT NULL, shrinking a type, or splitting one column into two. During a deploy, old and new versions of your code run side by side for a while, so a single step change can break the version that is still serving traffic. The safe pattern is expand and contract:

1. Expand. Add the new column as nullable. Old code ignores it.
2. Write both. Ship code that writes the old and the new column.
3. Backfill. Fill the new column for existing rows, in the migration or in batches for big tables.
4. Switch reads to the new column and deploy.
5. Contract. In a later migration, add the NOT NULL constraint and drop the old column.

The backfill can live in the expand migration itself. Write it with npx kastor migrate dev --create-only --name add_display_name, add the UPDATE by hand, then apply it with migrate dev:

-- Migration 20261014090000_add_display_name
-- (header lines trimmed)

-- Add column users.displayName
ALTER TABLE "users" ADD COLUMN "displayName" varchar(100);

-- added by hand: backfill before the column becomes required
UPDATE "users"
SET "displayName" = COALESCE("name", split_part("email", '@', 1))
WHERE "displayName" IS NULL;

It takes two or three deploys instead of one, but no request fails and no data disappears.

#db push for prototyping, db pull for existing databases

db push is fast because it skips the history. That is exactly why it does not belong on shared databases: nothing records what changed. A good habit is to push freely while an idea is taking shape, then create one clean migration once the schema settles. Because a pushed database already matches the schema, migrate dev would find nothing to write, so first run migrate reset, which asks for confirmation, drops every table, reapplies the committed migrations and runs the seed when one is configured, and then migrate dev.

db pull is the starting point for an existing project. It reads the live tables, columns, indexes and foreign keys and writes a schema file you can tidy up, with relations guessed from the foreign keys. It writes to the schema path of your config (or --out), and --print shows the result without writing anything. Running migrate dev right after a pull should report that the database is already in sync, so your migration history starts at that point, and from then on every schema change goes through migrate dev like in any other project.

#Running migrations in CI

In CI you want two things: tests run against a schema built only from committed migrations, and production receives exactly those migrations. Both come down to migrate deploy.

name: deploy
on:
  push:
    branches: [main]
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 22
      - run: npm ci
      - run: npx kastor migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
      - run: npx kastor migrate status
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

The order matters: migrate status exits with code 1 while migrations are pending, so before the deploy it would fail the job on every release that brings a new migration. After the deploy it is a cheap check that nothing is pending, modified or missing.

A few rules keep this boring, which is what you want:

• Run migrations as their own step before the new application version starts, and only once per deploy, not from every server instance on boot.
• Use a database user that is allowed to change the schema for this step, and a more limited user for the running app.
• In the test job, start an empty database, run migrate deploy, then run the tests. If a migration only works on your laptop, this is where you find out.
• Keep every migration backward compatible with the code that is currently live, using expand and contract for anything destructive.

Next, fill fresh databases with the database seeding guide or wire migrations into a Next.js deploy. If the CLI cannot reach your database, run npx kastor doctor.

#Questions people ask

#Related guides