Reylo Migrate

Put your schema in a SQL file, and Reylo Migrate writes the migrations

Model your schema in schema.sql, write each change into a migration with reylo migrate new, and run it with reylo migrate dev:

How the migration commands move between schema.sql, the migrations and the database. reylo migrate new writes a migration from schema.sql into the migrations folder, whose reylo.lock records their order. reylo migrate dev and reylo migrate deploy run the migrations on the database. Off to the sides, skipping the migrations: reylo migrate init writes schema.sql from an existing database, and reylo db push changes the database straight from schema.sql.schema.sqlmigrations/reylo.lockDatabasereylo migrate newreylo migrate {dev|deploy}reylo migrate initreylo db push

Basic usage

Install the reylo command:

brew install reylosql/tap/reylo
npm install --global reylo
curl -fsSL https://reylo.dev/install.sh | sh
irm https://reylo.dev/install.ps1 | iex
scoop bucket add reylo https://github.com/reylosql/scoop-bucket
scoop install reylo

Start your migrations from your database:

$ reylo migrate init
✓ Started reylo/ from postgres://localhost:5432/app
  reylo/
  ├── schema.sql          this database's schema
  ├── migrations/
  │   └── init.sql        the same, recorded as run here
  └── reylo.lock          the order the migrations run in

This produces a reylo directory. This path can be modified with the dir config in .reylo.yaml. It should be committed to version control.

To change your schema, edit schema.sql:

schema.sql
create table users (
  id integer primary key,
  email text not null
  email text not null, 
  bio text
);

Write the migration:

$ reylo migrate new add_bio
✓ Wrote reylo/migrations/20261104140347_add_bio.sql

It's plain SQL, yours to edit:

20261104140347_add_bio.sql
alter table users add column bio text;

Run it on your development database:

$ reylo migrate dev
Applying to postgres://localhost:5432/app
  ✓ 20261104140347_add_bio.sql  applied · 4 ms
✓ Wrote reylo/reylo.lock

Commit it with the lock, and deploy:

$ reylo migrate deploy
Applying to postgres://db.example.com/app
  ✓ 20261104140347_add_bio.sql  applied · 12 ms
✓ Applied 1 migration

Workflows

Trying a change

Push changes to your development database, then write the migration:

# take the database to schema.sql, with no migration
$ reylo db push

# or push again on every save
$ reylo db push --watch

# write the migration; dev records it without running it again
$ reylo migrate new add_bio
$ reylo migrate dev

Dropping and renaming

A drop, or a rename that also changes a type, is refused until you name it:

# drop a column, and its data
$ reylo migrate new drop_bio --drop users.bio

# rename a column whose type changes too, keeping its rows
$ reylo migrate new rename_bio --rename users.bio=about

Switching branches

After a switch, pull or merge, run reylo migrate dev:

# rewind what feature-a ran, and run what's new on feature-b
$ git switch feature-b
$ reylo migrate dev
Applying to postgres://localhost:5432/app
  ↺ 20261104120000_add_tags.sql   rewound: it isn't in the migrations
    ! Dropped tags
  ✓ 20261104130000_add_posts.sql  applied · 3 ms
✓ Wrote reylo/reylo.lock

# after a conflict on reylo/reylo.lock, keep main's lines, then add yours back after them
$ reylo migrate dev

Deploying

Try the deploy, run it, then confirm it:

# in CI, before you deploy: on shadow databases, running nothing
$ reylo migrate deploy --dry-run

# when you deploy
$ reylo migrate deploy

# after: exits 1 unless the database has run every migration
$ reylo migrate status

Starting over

Rebuild your development database from your migrations, or empty it:

# drop everything and run every migration
$ reylo db reset

# keep the schema, delete every row, and load your seed
$ reylo db wipe && reylo run --file seed.sql

Keeping a change made by hand

Keep a change made on production by hand:

# print what the database has, and copy the change into reylo/schema.sql
$ reylo db introspect --url $PROD_URL

# write its migration, run it in development, and record it on production
$ reylo migrate new users_email
$ reylo migrate dev
$ reylo migrate history push --url $PROD_URL

Schema schema.sql

Declare your schema in plain SQL, as you would on the database. Reylo Migrate compares and migrates:

  • Tables, columns, constraints and indexes
  • Generated columns, and identity columns (Postgres)
  • Enums (Postgres and MySQL), domains, composite and range types (Postgres)
  • Views, and materialized views (Postgres)
  • Triggers, functions (Postgres and MySQL), and procedures and events (MySQL)
  • Partitions (Postgres and MySQL)
  • Row level security policies, sequences, extensions and schemas (Postgres)
  • Grants and comments (Postgres and MySQL)
  • STRICT and WITHOUT ROWID tables (SQLite)

Enums and domains

create type mood as enum ('sad', 'ok', 'happy');
create domain email as text check (value like '%@%');

Generated columns

create table line_items (
  price numeric not null,
  qty integer not null,
  total numeric generated always as (price * qty) stored
);

Exclusion constraints

create table bookings (
  room integer not null,
  during tstzrange not null,
  exclude using gist (room with =, during with &&)
);

Partial indexes

create index users_unverified on users (created_at) where verified_at is null;

Materialized views

create materialized view room_totals as
  select room, sum(total) as total from bookings group by room;
create unique index room_totals_room on room_totals (room);

Functions and triggers

create function touch() returns trigger language plpgsql as $$
begin new.updated_at := now(); return new; end $$;

create trigger users_touch before update on users
  for each row execute function touch();

Row level security

alter table documents enable row level security;
create policy documents_own on documents
  using (owner_id = current_setting('app.user_id')::integer);

Grants and comments

grant select on room_totals to reporting;
comment on column users.verified_at is 'When they confirmed their email';

A change to any of them is a migration:

schema.sql
create policy docs_read on docs for select to reader using (owner_name = current_user); 
create policy docs_read on docs for select to reader, writer using (owner_name = current_user or body is null); 
20261106120000_writers_read.sql
drop policy docs_read on docs;
create policy docs_read on docs for select to reader, writer using (owner_name = current_user or body is null);

Text search configurations, security labels and a few other kinds are compared by name only, or not at all: write their changes by hand.

Schemaless mode

Delete schema.sql, and your migrations are your schema.

The same diagram without schema.sql: the file is deleted, so reylo migrate init and reylo db push, which touch it, are grayed out. reylo migrate new still writes the next migration, now from the database, up into the migrations folder, beside reylo migrate dev and reylo migrate deploy, which run the migrations on the database.schema.sqlmigrations/reylo.lockDatabasereylo migrate {dev|deploy}reylo migrate newreylo migrate initreylo db push

Write migrations by hand, or change your development database and let reylo migrate new write the migration:

$ reylo run 'alter table users add column bio text'
✓ alter table users add column bio text

$ reylo migrate new add_bio
✓ Wrote reylo/migrations/20261104140347_add_bio.sql

$ reylo migrate dev
Applying to postgres://localhost:5432/app
  + 20261104140347_add_bio.sql  recorded: already in place
✓ Wrote reylo/reylo.lock

What changes:

  • Checks against schema.sql are skipped.
  • Your development database stands in for schema.sql, so reylo migrate new refuses until it has run every migration.
  • There's nothing to push, so reylo db push refuses.

You lose the one file that shows your whole schema.

Ordering

Your migrations run in one order: the lockfile's, or their names'.

Lockfile reylo.lock

The lockfile lists your migrations in order, each with a checksum chained to the one before. A migration it doesn't list yet is a draft, free to edit:

reylo.lock
schema: c7bf681b
migrations:
  - 20261104120000_users.sql c86e9de4
  - 20261104140347_add_bio.sql 78ad08c0

Two branches that each add a migration conflict on the lock in git. Keep main's lines, then run reylo migrate dev to add yours after them.

Lock-free mode

Delete reylo.lock, and your migrations run in name order.

The same diagram without the lock: reylo.lock is deleted and grayed out, and the migrations run in name order. Every command remains.schema.sqlmigrations/reylo.lockDatabasereylo migrate newreylo migrate {dev|deploy}reylo migrate initreylo db push

Status says so:

$ reylo migrate status
Migrations on postgres://localhost:5432/app
  ✓ init.sql                 applied 2026-11-04 12:00 UTC
  ✓ 20261105090000_tags.sql  applied 2026-11-05 09:00 UTC
✓ Unchanged since its last migration
» With no lock, migrations run in name order
  To write one:
    reylo migrate lock
» With no lock to hold its checksum, reylo/schema.sql is checked against the migrations by a dry run
  To check it:
    reylo migrate deploy --dry-run

Without a lock, a merged migration can sort before one a database already ran. Then reylo migrate dev and reylo migrate deploy replay both orders on a shadow database, and refuse it where they end differently:

$ reylo migrate deploy
✗ Migration 20261105080000_no_nick.sql sorts before 20261105090000_nick.sql, which this database already ran, and the two orders end differently
  + a fresh database: column users.nick text
  Give 20261105080000_no_nick.sql a later name, so that every database runs it after 20261105090000_nick.sql

Nothing was changed.

You give up:

  • The same data everywhere. No check can show that data written in two orders matches.
  • Checksums. A migration edited before any database ran it goes unseen.

Only a missing lock turns it on. A reylo.lock that is there and can't be read is refused by every command.

To turn the lock back on, run reylo migrate lock.

CLI reylo migrate

Every command and flag:

Usage: reylo <group> <command> [flags]

Commands:
  migrate init             Start from this database
    --schema <name>        A schema to read (Postgres; public by default)
  migrate new <name>       Write the next migration, from schema.sql or this database
    --drop <names>         Accept what it drops: tables and columns
    --rename <old>=<new>   Say a table or column was renamed
    --blocking             Accept a statement that blocks writes while it reads every row
  migrate dev              Take this database to the migrations, and lock them
  migrate deploy           Run what the lock lists, with no shadow database
    --dry-run              Try it on a shadow database, running nothing here
  migrate status           Check that this database and the files are in step
  migrate lock             Write the lock from this database's history
  migrate history push     Record the migrations this database's schema has
  migrate history pull     Write the migrations this database ran into files
  db push                  Take this database to schema.sql, unrecorded
    --drop <names>         Accept what it drops: tables and columns
    --rename <old>=<new>   Say a table or column was renamed
    --force                Accept everything it drops
    --watch                Push again on each save
  db reset                 Rebuild this database from the migrations
  db wipe                  Delete every row, keeping the schema
  db introspect            Print this database's schema as SQL
    --schema <name>        A schema to read (Postgres; public by default)

Flags:
  --url <url>              The database, in place of direct_url
  --json                   Print the result as one JSON object
  -h, --help               Print this help

init

Starts your migrations from an existing database:

$ reylo migrate init
✓ Started reylo/ from postgres://localhost:5432/app
  reylo/
  ├── schema.sql          this database's schema
  ├── migrations/
  │   └── init.sql        the same, recorded as run here
  └── reylo.lock          the order the migrations run in

On an empty database:

$ reylo migrate init
✓ Started reylo/ for an empty database
  reylo/
  ├── schema.sql          empty: declare your tables here
  ├── migrations/
  └── reylo.lock
» There are no migrations yet
  To write the first:
    reylo migrate new <name>

new

Writes the next migration, from your change to schema.sql.

  • It replays your migrations on a shadow database and writes the difference to schema.sql. It never reads your database's schema.
  • It first checks that your development database ran each migration as it now reads. An edited or deleted one, such as another branch's, is refused until reylo migrate dev settles it.
  • With no schema.sql, it writes what your development database has that your migrations don't (Schemaless mode).
  • Edit the file freely, and add any data changes.
  • On Postgres, new constraints and indexes never block writes, and a table rewrite needs --blocking. Each statement gives up a busy lock after half a second and retries.

To accept a table rewrite:

$ reylo migrate new widen_amount --blocking

A drop, or a type change that loses values, is refused until you name it:

schema.sql
create table users (
  id integer primary key,
  email text not null, 
  email text not null
  bio text
);
$ reylo migrate new drop_bio
✗ This migration drops the column users.bio and its data

  To drop it:
    reylo migrate new drop_bio --drop users.bio

Nothing was changed.

A column replaced by one just like it is a rename, which keeps its rows:

schema.sql
create table users (
  id integer primary key,
  email text not null,
  bio text
  about text
);
$ reylo migrate new rename_bio
✓ Wrote reylo/migrations/20261104150000_rename_bio.sql
  ! Written as a rename, keeping its rows: users.bio → about
    If they're different columns, delete the file, then:
      reylo migrate new rename_bio --drop users.bio

Where the new column differs too, say which it is:

$ reylo migrate new tidy_columns
✗ These 2 look like renames, but each changes more than its name

    posts.title → heading  text → varchar(100)
    users.bio   → about    text → varchar(200)

  If they are renames, keeping their rows:
    reylo migrate new tidy_columns \
      --rename posts.title=heading \
      --rename users.bio=about
  If they are new, dropping the old ones and their data:
    reylo migrate new tidy_columns --drop posts.title,users.bio

Nothing was changed.

With no schema change, the migration is empty, for a data change:

$ reylo migrate new backfill
✓ Wrote reylo/migrations/20261104160000_backfill.sql, empty: reylo/schema.sql has nothing new, so it's for a change to data

dev

Takes your development database to your migrations and writes the lock:

$ reylo migrate dev
Applying to postgres://localhost:5432/app
  ↺ 20261104120000_tags.sql   rewound: it isn't in the migrations
    ! Dropped tags (3 rows)
  ✓ 20261104130000_posts.sql  applied · 3 ms
✓ Wrote reylo/reylo.lock

Each time, it takes these steps:

  1. Compareyour database

    Reads what your database ran, and keeps it as far as it agrees with your migrations, in the lock's order.

  2. Replaya shadow database

    Runs the migrations it kept on a shadow database, to read the schema they make.

  3. Rewindyour database

    Undoes what your database ran past that point, by the change from what it has to that schema.

  4. Runyour database

    Runs the migrations it hasn't run, as reylo migrate deploy will.

  5. Lockyour files

    Writes reylo.lock, listing what ran, in order.

It refuses a change made outside schema.sql and your migrations:

$ reylo migrate dev
✗ This database was changed outside reylo/schema.sql and its migrations

    alter table users add column hotfix bigint;

  To keep it, add it to reylo/schema.sql, then write its migration:
    reylo migrate new <name>
  To undo it:
    reylo run 'alter table users drop column hotfix;'
  To rebuild this database from the migrations, losing its rows:
    reylo db reset

Nothing was changed.

When nothing changed since its last run, it skips the shadow databases.

deploy

Runs the lock's pending migrations, each in one transaction with its history row. On MySQL, where a schema change commits on its own, each migration holds one change. A statement in MySQL's executable comments, as mysqldump writes them (/*!50001 … */), runs as written, and is checked as every version of MySQL reads it, with each comment's text run or skipped.

$ reylo migrate deploy
Applying to postgres://db.example.com/app
  ✓ 20261104140347_add_bio.sql  applied · 12 ms
✓ Applied 1 migration

It needs no shadow database. It refuses to run:

  • On an edited schema file. A schema.sql that doesn't match the lock's checksum.
  • On a database changed by hand. Each migration stores a fingerprint of the schema, and a database that no longer matches it is refused, a hand-added index included.
$ reylo migrate deploy
✗ This database was changed outside its migrations

  + column users.hotfix bigint

  If a migration makes this change, record it here, running nothing:
    reylo migrate history push
  If none does, write one and run it in development, then record it here:
    reylo migrate new <name>
    reylo migrate dev
    reylo migrate history push
  To see which migrations it matches:
    reylo migrate deploy --dry-run

Nothing was changed.

With --dry-run, it runs nothing and tries everything on shadow databases instead. Run it in CI:

$ reylo migrate deploy --dry-run
✓ This database is as its migrations left it
✓ The migrations match reylo/schema.sql
✓ Tried the pending migration on a shadow database:
    · 20261104140347_add_bio.sql

A failed migration rolls back and records nothing. Fix the file, run reylo migrate dev, and deploy again. Where a database already ran the old version, write a new migration instead.

A migration and the build that needs it go live at different moments, and the old build's queries fail on a renamed or dropped column. So rename in steps. To rename users.email to contact, add the new column and copy its values, then deploy code that reads contact and writes both:

20261110120000_add_contact.sql
alter table users add column contact text;
update users set contact = email;

Once it runs everywhere, copy what the old build wrote meanwhile, and drop the old column:

20261112120000_drop_email.sql
update users set contact = email where contact is null or contact <> email;
alter table users drop column email;

Two things the fingerprint leaves out:

  • Comments. One added in a dashboard stops no deploy.
  • Upgrades. After a new Reylo or a new major version of your database, the next deploy stores a new fingerprint and says so.

status

Lists what your database ran and what's pending, and checks the files. It changes nothing and needs no shadow database:

$ reylo migrate status --url $PROD_URL
Migrations on postgres://db.example.com/app
  ✓ init.sql                    applied 2026-11-04 12:00 UTC
  ✓ 20261104140347_add_bio.sql  applied 2026-11-04 15:12 UTC
  · 20261105090000_tags.sql     pending
✓ Unchanged since its last migration
✓ The migration files match reylo/reylo.lock
✓ The migrations match reylo/schema.sql
» This database hasn't run 20261105090000_tags.sql
  To run it in development:
    reylo migrate dev
  To run it in production:
    reylo migrate deploy

It exits 1 for anything out of step: a pending, edited or deleted migration, a change made by hand, or a schema.sql changed with no migration:

$ reylo migrate status
Migrations on postgres://localhost:5432/app
  ✓ init.sql                    applied 2026-11-04 12:00 UTC
  ✓ 20261104140347_add_bio.sql  applied 2026-11-04 15:12 UTC
✓ Unchanged since its last migration
✓ The migration files match reylo/reylo.lock

✗ The schema changed since the lock was written
  The lock holds a checksum of reylo/schema.sql from when the migrations last matched it.

  If the schema changed, write its migration and run it:
    reylo migrate new <name>
    reylo migrate dev
  If only formatting or comments changed, check it again:
    reylo migrate dev

In CI, run it after a deploy. Before one, run reylo migrate deploy --dry-run.

lock

Writes the lock from your database's history, to turn the lock back on (Lock-free mode) or replace a lost one. It refuses where a lock exists:

$ reylo migrate lock
✓ Wrote reylo/reylo.lock: 2 migrations, in the order this database ran them
✓ The migrations match reylo/schema.sql

history push

Records your migrations as run on a database that already has their schema, running nothing:

$ reylo migrate history push --url $PROD_URL
Recording on postgres://db.example.com/app
  + 20261104160000_users_email.sql  recorded: already in place
✓ Recorded 1 migration, running nothing

history pull

Writes your migration files from your database's history: a deleted migration comes back, an edited one is written over, and a lost lock is written again:

$ reylo migrate history pull --url $PROD_URL
Pulling from postgres://db.example.com/app
  ↓ 20261104140347_add_bio.sql  written over: it differed from what ran
Not run on this database, and left as they are: 20261105090000_tags.sql

A database changed by hand is refused.

db push

Takes your development database to schema.sql, with no migration, for trying a change:

schema.sql
create table users (
  id integer primary key,
  email text not null,
  bio text
  bio text, 
  nick text
);
$ reylo db push
Pushing reylo/schema.sql to postgres://localhost:5432/app
  + alter table users add column nick text;
✓ This database matches reylo/schema.sql

As with reylo migrate new, a drop or a type change that loses values is refused until you name it:

schema.sql
create table users (
  id integer primary key,
  email text not null,
  bio text, 
  nick text
  bio text
);
$ reylo db push
✗ This push drops the column users.nick and its data

  To drop it:
    reylo db push --drop users.nick
  To accept every destructive change:
    reylo db push --force

Nothing was changed.

Name a rename with --rename, or accept every drop with --force:

$ reylo db push --rename users.bio=about
$ reylo db push --force

With --watch, it pushes again each time you save:

$ reylo db push --watch

It refuses until reylo migrate dev has run. Until you write the migration, reylo migrate dev says so:

$ reylo migrate dev
✓ Up to date with the migrations
» The schema is ahead of the migrations
  To write the changes to a migration file:
    reylo migrate new <name>

Once you write it, reylo migrate dev records it without running it again. An empty schema.sql is refused: to start over, use reylo db reset.

db reset

Rebuilds your development database from your migrations.

It checks your files first, then drops everything, empties the history and runs every migration. A platform's own objects, such as Supabase's auth.users, stay:

$ reylo db reset
Resetting postgres://localhost:5432/app
  ! Dropped users (2 rows) and emptied the history
  ✓ init.sql                    applied · 5 ms
  ✓ 20261104140347_add_bio.sql  applied · 4 ms
✓ Rebuilt from 2 migrations

db wipe

Deletes every row, keeping the schema and the history.

It leaves a platform's own tables alone, such as Supabase's auth.users:

$ reylo db wipe
✓ Deleted every row of 2 tables on postgres://localhost:5432/app; the schema and the history stay

db introspect

Prints your database's schema as SQL, checked on a shadow database to make exactly what your database has:

$ reylo db introspect
create table users (
  id integer not null,
  email text not null,
  constraint users_pkey primary key (id)
);

In a terminal, a spinner shows while it reads, and the SQL is coloured; a pipe or a redirect gets plain SQL. Where it can't check the schema, it prints the database's own dump instead.

To save it over schema.sql, write another file first, so a failed run can't empty it:

$ reylo db introspect > schema.new.sql && mv schema.new.sql reylo/schema.sql

Configuration

Every setting is optional. These are the defaults:

.reylo.yaml
# the directory of schema.sql, the migrations, the lock and the cache
dir: reylo

# the database to migrate
direct_url: ${DATABASE_URL}

# a server for shadow databases; null uses your database's own
shadow_url: null

To migrate another database, pass --url, which on Turso Cloud is its libsql:// URL with your auth token (Turso):

$ reylo migrate status --url $PROD_URL

Most commands use shadow databases: throwaway databases where Reylo Migrate replays your migrations to read the schema they make. A deploy needs none:

Needs a shadow databaseNeeds none
reylo migrate initreylo migrate deploy
reylo migrate newreylo migrate status
reylo migrate devreylo db introspect
reylo migrate deploy --dry-runreylo db wipe
reylo migrate lock
reylo migrate history push
reylo migrate history pull
reylo db push
reylo db reset

Each database makes them its own way:

  • Postgres: on your database's server, with create database, so your role needs CREATEDB. A local superuser has it, and so do the roles Neon and Supabase give you. Reylo Migrate keeps up to 32 as a cache, named reylo_cache_…, so a run replays only the migrations that are new. On Azure Database for PostgreSQL, list each extension your migrations create in the server's azure.extensions parameter first, or no shadow can create it. Timescale Cloud refuses create database, so point shadow_url at a server with TimescaleDB and timescaledb_toolkit, such as the timescale/timescaledb-ha image.
  • MySQL: on your database's server, with create database, so your user needs the CREATE privilege on every database. PlanetScale's Vitess refuses create database, so point shadow_url at a plain MySQL 8.4 server.
  • SQLite: in memory. There's nothing to grant. A Turso database's shadows run on libSQL's own engine, as Turso Cloud does (Turso).

A migration that would reach past its shadow is refused: one that changes a role, a database, a tablespace or a server setting, and on MySQL one that names a database, as in app.users. Code inside a do block or a function isn't checked, so keep it from changing those.

On Supabase, each shadow also gets Supabase's extensions and schemas, such as auth and storage, so a migration that references auth.users replays as it runs.

Where your role can't create a database, point shadow_url at a server that can, running the same version:

.reylo.yaml
# a local Postgres 18, for a hosted Postgres 18
shadow_url: postgres://localhost:5433/postgres

Every setting, for migrations and the rest of Reylo, is in the .reylo.yaml reference.

API

Run your migrations from code:

server.ts
import { migrate } from "reylo";

// reylo migrate deploy, in this directory, on direct_url's database
const ran = await migrate();

It resolves to the names of the migrations it ran, and rejects with the deploy's problems. Pass url for another database:

await migrate({ url: process.env.PRODUCTION_URL });

A Cloudflare Durable Object runs its migrations on itself, from its constructor (Durable Objects).

Released under the MIT License. Copyright © 2026 Colin McDonnell.

On this page

GitHub