The schema drift trap
You push a new feature to production. The code expects a column named status on the orders table. The database doesn't have it. The server crashes on the first request. You added the column locally, but the deployment script didn't update the schema. Your teammate's machine is fine. The staging server is fine. Production is broken because the database structure drifted from the code.
This is the classic schema drift problem. In languages with heavy ORMs, the framework might try to auto-create columns, which hides errors until runtime or creates messy schema state. Rust prefers explicit control. sqlx treats database schema changes as code artifacts. You write the SQL, you version it, and sqlx ensures every instance of your application applies the same changes in the same order.
Migrations turn your database schema into a reproducible sequence of operations. They are version control for your data structure. Once a migration is committed, it becomes part of the application's history. Every new database instance replays that history to reach the current state.
Migrations as a replay log
Think of migrations like a recipe for your database. The first migration creates the table. The second adds a column. The third adds an index. sqlx keeps a ledger of which recipes have been executed. When you start the application, sqlx checks the ledger, finds the missing steps, and runs them one by one.
The ledger lives in a table named sqlx_migrations. sqlx creates this table automatically when you run the first migration. It stores the version, description, installation time, success status, and a checksum of the SQL file. The checksum is the safety mechanism. If you edit a migration file after it has been applied, the checksum changes. sqlx detects the mismatch and refuses to run. This prevents accidental edits to history that could desynchronize databases.
Convention aside: the community keeps the migrations directory at the project root. sqlx looks there by default. Moving it requires passing --source to the CLI, which adds friction for no gain. Stick to the root.
The sqlx migration workflow
The workflow revolves around two commands: add and run. You use add to create a new migration file. You use run to apply pending migrations to a database.
First, ensure your Cargo.toml includes the migrate feature. Without this feature, the CLI commands won't work, and the Rust API won't compile.
[dependencies]
sqlx = { version = "0.7", features = ["runtime-tokio-rustls", "postgres", "migrate"] }
Run the add command to generate a new file. sqlx creates a file with a timestamp prefix and your name. The timestamp ensures migrations run in chronological order.
sqlx migrate add create_users_table
This creates migrations/20231027120000_create_users.sql. Open the file and write your SQL.
-- migrations/20231027120000_create_users.sql
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
Run the migration against your database. sqlx connects using the DATABASE_URL environment variable, checks the sqlx_migrations table, and executes the new file.
sqlx migrate run
If DATABASE_URL is not set, sqlx fails immediately. The error is a configuration error, not a compiler error. Set the variable before running the command.
export DATABASE_URL="postgres://user:pass@localhost/mydb"
sqlx migrate run
Convention aside: name migrations with a verb. create_users_table tells you exactly what happens. users is ambiguous. add_email_to_users is clear. Use the imperative mood for the name.
Embedding migrations in production
Running the CLI command works for development. In production, you usually want the application to manage migrations automatically. sqlx provides a Migrator struct that embeds all migration files from the migrations directory into your binary at compile time.
When you compile your crate, sqlx reads the SQL files and generates Rust code that contains the migration payloads. The binary becomes self-sufficient. It doesn't need the migrations directory at runtime. You can deploy a single binary, and it knows how to update the database.
Use the Migrator::run() method to apply pending migrations. This is an async function that takes a connection pool.
use sqlx::migrate::Migrator;
/// Runs all pending migrations against the database.
async fn run_migrations(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {
// The Migrator struct is generated at compile time from the migrations directory.
// It contains the SQL for every migration file.
Migrator::run(pool).await
}
Call this function during application startup, before you start serving requests. If a migration fails, Migrator::run() returns an error. Handle the error and shut down the application. A failed migration usually means the database is in an inconsistent state. Continuing to run risks data corruption.
The Migrator struct also supports blocking execution. If you are using a blocking runtime or need to run migrations in a synchronous context, use Migrator::run_blocking().
use sqlx::migrate::Migrator;
/// Runs migrations in a blocking context.
fn run_migrations_blocking(pool: &sqlx::PgPool) -> Result<(), sqlx::Error> {
Migrator::run_blocking(pool)
}
Embed the migrator. Your binary should be self-sufficient. Don't rely on external scripts to update the schema in production.
The sqlx_migrations table
Understanding the sqlx_migrations table helps you debug issues. The table schema looks like this:
CREATE TABLE sqlx_migrations (
version BIGINT PRIMARY KEY,
description TEXT NOT NULL,
installed_on TIMESTAMPTZ NOT NULL DEFAULT now(),
success BOOLEAN NOT NULL,
checksum BYTEA NOT NULL
);
The version column holds the timestamp from the filename. sqlx uses this to determine order. The checksum column stores the hash of the SQL file content. When sqlx runs, it compares the checksum of the file on disk with the checksum in the table. If they differ, sqlx aborts with a checksum mismatch error.
This behavior protects you from editing committed migrations. If you need to change a migration that has already been applied, you must create a new migration to fix the schema. Never edit a past migration file. Treat the migration directory like a log. Append only.
If you accidentally edit a past migration and hit the checksum error, you have two options. You can revert the migration, edit the file, and re-run it. Or you can create a new migration that undoes the change and applies the correct change. The second option is safer because it preserves the history.
Pitfalls and error patterns
Migrations introduce specific failure modes. Knowing these patterns saves time when things go wrong.
If you forget the migrate feature in Cargo.toml, the compiler rejects your code. You get E0599 (no function named run found for struct Migrator) or E0432 (unresolved import) depending on how you use the API. Add the feature and rebuild.
If your DATABASE_URL points to a database that doesn't exist, sqlx fails with a connection error. The error message includes the driver-specific details. Check the URL format and credentials.
If you run a migration that contains invalid SQL, the migration fails. sqlx rolls back the transaction for that migration. The sqlx_migrations table records the failure. You must fix the SQL and run the migration again. sqlx does not retry failed migrations automatically.
If you use sqlx in offline mode, you need to prepare the migration queries. Offline mode allows sqlx to check queries at compile time without a database connection. Run sqlx prepare to generate the query data. If you add a new migration, run sqlx prepare again to update the offline data.
Convention aside: sqlx wraps each migration in a transaction by default for databases that support it. This means if a migration fails halfway through, the entire migration rolls back. The database stays in the previous state. This is a safety feature. You don't need to write transaction blocks in your migration files.
Choosing your migration strategy
Migrations support reversible changes. You can create a migration that has both an up script and a down script. The up script applies the change. The down script reverts it.
Use sqlx migrate add --reversible to create a reversible migration. This creates two files: up.sql and down.sql.
sqlx migrate add --reversible add_status_to_orders
This creates:
migrations/20231027120000_add_status_to_orders/up.sqlmigrations/20231027120000_add_status_to_orders/down.sql
Write the forward change in up.sql and the reverse change in down.sql.
-- up.sql
ALTER TABLE orders ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';
-- down.sql
ALTER TABLE orders DROP COLUMN status;
You can revert the last migration using the CLI.
sqlx migrate revert
This runs the down.sql script and updates the sqlx_migrations table. Reversible migrations are useful during development when you need to reset the database state. They are risky in production. Dropping columns or tables can destroy data. Use reversible migrations carefully.
Decision matrix for migration tools:
Use sqlx migrate add when you need to create a new schema change. This generates the file structure and timestamp. Use sqlx migrate run when you want to apply pending migrations from the command line. This is useful for CI/CD pipelines or local development. Use Migrator::run() when you want the application to manage migrations automatically. This embeds the migrations in the binary and applies them at startup. Use --reversible flag when you need to roll back changes during development. This creates up and down scripts for safe reverts. Use sqlx migrate revert when you need to undo the last migration in a safe environment. This runs the down script and updates the ledger. Use offline mode when you want compile-time validation of migration SQL. This requires running sqlx prepare after each change.
Treat the migration hash as a fingerprint. If it changes, sqlx refuses to run. This protects you from accidental edits to history.