How to manage a schema with migrations

Short answer: sqlx migrate add <name> writes a timestamped .sql file into a migrations/ folder, and sqlx migrate run applies every file the database has not recorded yet. The file is plain SQL because sqlx never parses it — it sends the bytes to Postgres and writes a checksum into a bookkeeping table.

The previous page ends with a CREATE TABLE typed straight into psql. That works once and then evaporates: nothing about it lives in the repository, and a teammate — or CI, or a new machine — gets a database with no tables in it. Migrations fix that by making the schema a set of files that ship with the code.

The CLI

The migration commands live in sqlx-cli, installed separately from the crate:

cargo install sqlx-cli --no-default-features --features rustls,postgres

The features decide what the binary can talk to: postgres here, with rustls for TLS. Without --no-default-features it also compiles MySQL and SQLite support, which the build does not need and will not use. Check the install with sqlx --version, which prints sqlx-cli 0.9.0 at the version this page was written against.

One migration is one file

sqlx migrate add create_dishes

Creating migrations/20261030130349_create_dishes.sql

The number is a timestamp, so yours will differ — the timestamp is the migration’s version, and the file never gets renamed after it exists. The migrations/ folder is created if it does not exist, and the file starts out empty. Put the SQL in it:

CREATE TABLE dishes (
    id          INT PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INT NOT NULL
);

That is the whole migration. No struct, no builder, no schema DSL — sqlx reads the file and sends it to the server, so anything Postgres accepts works, including statements a schema tool has no verb for. The trade is that nothing checks the SQL before it runs: a typo in CREATE TABLE fails at migrate run, not in an editor.

Running them

sqlx migrate run reads DATABASE_URL. Point it at an empty database — on the server from the previous page, CREATE DATABASE shop makes one:

export DATABASE_URL="postgres://postgres:dev@localhost:5432/shop"
sqlx migrate run

Applied 20261030130349/migrate create dishes (1.472167ms)

The duration is from this run; yours will differ. A second migration works the same way — sqlx migrate add seed_dishes, fill the file with INSERT statements, sqlx migrate run again. Once every file is applied, running a third time prints nothing and exits: applied migrations are recorded, and nothing is pending.

The record lives in a table sqlx creates, _sqlx_migrations. Reading it directly shows what the tool is actually storing:

PGPASSWORD=dev psql -h 127.0.0.1 -U postgres -d shop \
  -c "SELECT version, description, success FROM _sqlx_migrations"

    version     |  description  | success
----------------+---------------+---------
 20261030130349 | create dishes | t
 20261030130413 | seed dishes   | t
(2 rows)

sqlx migrate info reads the same table from the other side and labels every file installed or pending:

20261030130349/installed create dishes
20261030130413/installed seed dishes

Editing an applied migration does not work

Along with the version and the description, sqlx stores a checksum of each file’s contents. Change a file after it has been applied and the next run stops with:

error: migration 20261030130349 was previously applied but has been modified

That error is the point of the checksum: the database’s schema was built by the old file, and quietly rewriting history would leave the two disagreeing in ways nobody notices for months. The fix is always a new migration — alter forward, never edit backward. Editing a file is only legitimate while the migration is still unapplied.

Reversible migrations

sqlx migrate add -r creates a pair of files instead of one:

sqlx migrate add -r add_name_index

Creating migrations/20261030130444_add_name_index.up.sql
Creating migrations/20261030130444_add_name_index.down.sql

The up file holds the change; the down file undoes it:

CREATE INDEX dishes_name_idx ON dishes (name);

DROP INDEX dishes_name_idx;

migrate run only ever looks at up files. To undo the latest one:

sqlx migrate revert

Applied 20261030130444/revert add name index (1.601292ms)

sqlx migrate info now reports that migration pending, and the next run applies it again:

20261030130349/installed create dishes
20261030130413/installed seed dishes
20261030130444/pending add name index

Down migrations deserve the caveat printed in the tool’s own help: reverting is destructive, and because normal development never executes down files, they rot — the up file changes and the down file no longer matches. Write them for the cases you actually need to roll back; a plain migration is the honest default.

Why the file is plain SQL

Because sqlx adds nothing on top of it. The tool reads files in timestamp order, skips the versions already recorded, sends the rest to the server, and stores version, description, and checksum in _sqlx_migrations. There is no intermediate representation to learn, no codegen step to forget to run, and no lock-in: the same files load through psql on a machine that has never heard of sqlx. What sqlx contributes is exactly the bookkeeping — order, once-only application, and the checksum that notices when history has been rewritten.

The schema now travels with the code. The next step is making the compiler check queries against that schema — query! and sqlx prepare, which is the next page in this chapter.