Migrations

my-app/
  migrations/
    0001_init.sql
    0002_add_posts.sql

runlot pg migrate

0001_init
0002_add_posts

If there is nothing to apply, the CLI tells you so.

File naming rules

File names must follow the NNNN_name.sql format, and the number must be a positive integer. The default directory is <project>/migrations, which you can change with the --migrations option.

Applied history

Applied migrations are recorded in the following table in your database.

CREATE TABLE IF NOT EXISTS schema_migrations (
  version    int  PRIMARY KEY,
  name       text NOT NULL,
  sha256     text NOT NULL,
  applied_at timestamptz NOT NULL DEFAULT now()
)

You can check which migration was applied when directly in SQL.

runlot pg execute -c "select version, name, applied_at from schema_migrations order by version"

Each file is applied in a single transaction

The DDL in a file and the history record for it run in the same transaction. If an error occurs partway through, the file's contents and the history entry roll back together, so nothing is left half-applied.

A mismatch stops the run

In the following situations nothing is applied and the run stops.

• The contents of an already-applied file have changed (sha256 mismatch)
• A migration is in the history but its file is missing
• A new file appears with a number lower than one already applied

Do not edit migration files you have already deployed. Add your changes in a file with a new number instead.

Using ORM migration tools alongside this

Use prisma migrate and drizzle-kit only to generate SQL. Move the generated SQL to migrations/NNNN_*.sql and apply it with runlot pg migrate.

# Prisma
npx prisma migrate diff --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma --script > migrations/0003_posts.sql

# Drizzle
npx drizzle-kit generate
cp drizzle/0003_*.sql migrations/0003_posts.sql

You cannot run prisma migrate deploy inside a worker, because Prisma's migration engine is not available there.

TypeORM's synchronize and Sequelize's sync({ force: true }) do work, since they run DDL at runtime. They are convenient during development, but do not use them as your production migration strategy.

When you need to run SQL just once

runlot pg execute -c "alter table posts add column pinned boolean not null default false"
runlot pg execute -f ./fix.sql

SQL run with this command is not recorded in the applied history. We recommend managing schema changes as migration files.