The schema drift trap
You just added an email column to your users table in your local database. You commit the SQL script, push to production, and restart the server. The app crashes immediately with a "column does not exist" error. Or a new developer clones your repo, runs cargo run, and stares at a stack trace because their database is empty and missing every table. Manual schema changes don't scale. You need a way to version your database structure just like you version your code.
Migrations as version control for your database
Database migrations are version-controlled scripts that transform your database schema over time. Think of them as patches for your database. When you update your Rust code, you commit the changes to Git. When you update your database structure, you write a migration script. The migration tool tracks which scripts have already run and applies the new ones in order. This keeps your local, staging, and production databases in sync with your code.
The tool maintains a ledger table inside the database, usually called schema_migrations or sqlx_migrations, to record what's been applied. This ledger is the source of truth. The tool compares the ledger against your migration files and executes only the missing ones. Your database evolves predictably, and every environment stays consistent with the codebase.
Don't run SQL manually in production. Let the tool track the state.
Running your first migration
Rust projects rely on CLI tools to manage migrations safely. The two most common ecosystems are Diesel and SQLx. Both provide commands to generate, run, and revert migrations.
# Create a new migration using Diesel's CLI.
# This generates a directory with up.sql and down.sql files.
# The timestamp in the directory name ensures correct ordering.
diesel migration generate add_email_to_users
# Run the migration against the database.
# The tool reads diesel.toml for the connection string.
diesel migration run
For SQLx, the workflow is similar but uses a different command structure.
# Create a new migration using SQLx's CLI.
# This generates a single file in the migrations directory.
# SQLx uses sequential numbering or timestamps based on configuration.
sqlx migrate add add_email_to_users
# Run the migration against the database.
# SQLx reads the DATABASE_URL environment variable by default.
sqlx migrate run
Convention aside: Diesel generates a directory per migration with separate up.sql and down.sql files. SQLx generates a single file per migration by default, though you can configure it to split them. Stick to the default for your tool to avoid confusing other developers.
Commit the migration file to Git. The database doesn't care about your local state.
What happens under the hood
When you run the migration command, the tool connects to the database and checks its ledger table. It compares the list of applied migrations against the files in your migrations/ directory. For every file that hasn't been applied, the tool executes the SQL inside. If the SQL succeeds, the tool records the migration version in the ledger. If the SQL fails, the tool stops immediately and rolls back the transaction. Your database stays consistent. You never end up with a half-applied schema.
The transaction wrapper is your safety net. Trust it.
A realistic project setup
A production-ready project separates configuration from code and keeps migrations organized. Here's how a Diesel project typically looks.
# diesel.toml
# Configuration tells Diesel where to find the database.
# Use separate profiles for dev and production to avoid leaking credentials.
[print_schema]
file = "src/schema.rs"
[migrations_directory]
dir = "migrations"
-- migrations/20231001000000_add_users/up.sql
-- Create the users table with a primary key and constraints.
-- The down.sql file reverses this by dropping the table.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL UNIQUE,
created_at TIMESTAMP DEFAULT NOW()
);
-- migrations/20231001000000_add_users/down.sql
-- Reverse the up migration.
-- Dropping the table removes the data, so only use this in development.
DROP TABLE users;
Convention aside: Diesel's diesel migration run command updates src/schema.rs automatically. This file maps your database schema to Rust types. If you skip this step, your Rust code won't compile against the new schema. Always run the migration command after generating a new migration.
Keep your migrations small and focused. One migration per logical change.
The ledger table and why you can't delete files
The ledger table stores the version of every applied migration. When you run a migration, the tool inserts a row into this table. When you query the ledger, the tool sees the row and skips that migration. This means you can't just delete a migration file and expect the database to forget about it. The ledger remembers.
If you need to remove a migration, you must write a new migration that reverses the changes and deletes the ledger entry, or use the tool's reset command in development. Deleting files breaks the history and causes errors when other developers try to run migrations. The ledger is the source of truth. Your migration files are just instructions.
Treat the ledger as immutable history. Never delete applied migration files.
Writing reversible migrations
Every migration should have a reverse. The up script applies the change. The down script reverts it. If you add a column in up, you drop it in down. If you create a table in up, you drop it in down. This lets you roll back a migration if it causes problems. Not all tools require down migrations, but you should write them anyway. Production databases don't forgive mistakes.
Write the down migration before you write the up migration. It forces you to think about reversibility.
Idempotency and transaction safety
Idempotency means running the same operation multiple times has the same effect as running it once. Migrations are usually applied once, so strict idempotency isn't required for the up script. However, your deployment scripts might retry migrations on failure. If a migration fails halfway through, the tool rolls back the transaction, so the next retry starts fresh. You don't need to write CREATE TABLE IF NOT EXISTS in your migrations. The transaction wrapper handles the safety.
Rely on the transaction wrapper. Don't clutter your migrations with defensive checks.
Running migrations in production
In production, you don't run migrations interactively. You need an automated way to apply them. The standard pattern is to run migrations before starting the application server. You can do this by creating a separate binary in your Rust project that runs migrations and exits. Or you can use a deployment script that invokes the CLI tool. Never run migrations inside the main application loop unless you have a robust locking mechanism. If your app crashes during a migration, you don't want the restart to try to run the same migration again and fail.
Separate migration execution from application logic. Keep your server fast and your schema changes safe.
Pitfalls and errors
You write the migration, run it locally, but forget to commit the SQL file to Git. Your CI pipeline runs the migration against a fresh database and fails because the file is missing. Always commit the migration file alongside the code that depends on it.
Two instances of your app try to run migrations at the same time. You might get duplicate key errors or schema corruption. Most tools handle this with database locks, but you should still configure your deployment to run migrations before starting the app server. Use a deployment orchestrator to ensure only one instance runs migrations.
SQLx offline mode causes build failures. If you enable offline mode for compile-time verification, you must run sqlx prepare or sqlx migrate run locally to generate the query data. Otherwise, the build fails with a "query not found in query data" error. Update the query data whenever you change the schema.
SQLx rejects the build with a "query not found in query data" error if you enable offline mode but haven't prepared the queries.
Offline mode moves database verification to compile time. Catch errors before you deploy.
Choosing your migration tool
Use Diesel when you want an ORM that generates Rust types from your schema automatically. Diesel's migration tool integrates tightly with its schema discovery, so running diesel migration run updates your schema.rs file and keeps your Rust code in sync with the database.
Use SQLx when you prefer writing raw SQL and want compile-time verification of your queries. SQLx's migration tool is lightweight and works with any database supported by the crate. It also supports offline mode, which lets you verify migrations and queries at compile time without a database connection.
Use a custom solution when you have unique requirements that existing tools can't handle, such as running migrations inside a specific transaction context or integrating with a proprietary database system. Building your own migration runner requires managing the ledger table, handling locks, and ensuring idempotency yourself.
Pick the tool that matches your query style. The migration runner is just a helper.