SQLx Migrations in Rust and Running Them in CI and Prod

A database schema is code that has to run in order, exactly once per environment. SQLx ships a small migration system that does that with plain SQL files and no framework. It is the part of the toolkit that production teams use on day one. This post follows the SQLx explained guide.

sqlx-cli 0.8, PostgreSQL 17, Rust 1.90.

Install and create

cargo install sqlx-cli --no-default-features --features rustls,postgres
sqlx migrate add create_users

The CLI creates migrations/20250101000000_create_users.sql with a timestamp prefix. Write the SQL:

CREATE TABLE users (
    id         BIGSERIAL PRIMARY KEY,
    email      TEXT NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Apply

sqlx database create
sqlx migrate run
sqlx migrate info

migrate info lists each file with its applied status. The CLI reads DATABASE_URL. SQLx creates the _sqlx_migrations table to track version, description, checksum and execution time.

Embed and run in the program

use sqlx::PgPool;

pub async fn migrate(pool: &PgPool) -> Result<(), sqlx::migrate::MigrateError> {
    sqlx::migrate!("./migrations").run(pool).await
}

migrate! reads the folder at compile time and embeds the SQL. A change to the folder does not trigger a rebuild by default; add a build.rs with println!("cargo:rerun-if-changed=migrations"); so new files are noticed. SQLx takes an advisory lock while migrating, so two instances starting together do not race; the second waits and finds nothing to do.

The checksum error

Edit a file that has already run, and the next start fails:

Error: migration 20250101000000 was previously applied but has been modified

The fix is to restore the file and put the change in a new migration. In a development database you can drop and recreate it, never in a shared one.

Rolling out safely

Migrations and code ship in an order. The rule that avoids downtime: every migration must work with the previous release of the code. So add a nullable column first, deploy the code that writes it, then backfill, then add the constraint in a later migration. Dropping a column in the same release that stops using it breaks the instances still running during the rollout.

Reversible files

sqlx migrate add -r name creates name.up.sql and name.down.sql. Down files help in development; production rollbacks of schema changes are rare and risky, because data may already depend on the new shape.

Testing against migrations

The #[sqlx::test] attribute creates a fresh database per test and applies your migrations, which keeps tests honest. The general layout of such tests is in integration tests in Rust.

Limits

SQLx migrations are forward only SQL. Data transformations that need Rust code, large backfills and long locks need care: use batches and avoid ALTER TABLE operations that rewrite the table on a busy system. The CLI and macro are documented under sqlx::migrate.