When the database and code drift apart
You just added a verified_at column to your users table in the database. You restart your Rust app, and it crashes. The code expects a column that didn't exist yesterday. Or worse, you're on a team. Your teammate updated the schema on their machine, pushed the code, and now your CI pipeline is failing because the test database is out of sync. You need a way to version your database schema just like you version your code.
Migrations solve this problem. They are version control for your database schema. Just like Git tracks changes to your source code, migrations track changes to your tables, columns, and indexes. Diesel doesn't manage this inside the Rust crate itself. It uses a separate command-line tool, diesel_cli, to generate, run, and roll back these changes. The workflow is explicit: you write SQL scripts to apply changes and SQL scripts to reverse them. The tool runs them in order and remembers what has already been applied.
How Diesel manages schema changes
Diesel migrations live in a directory structure that the CLI tool controls. Each migration is a folder containing two SQL files: up.sql and down.sql. The up.sql file contains the SQL to apply the change. The down.sql file contains the SQL to revert it. Diesel executes these files against your database and tracks the state in a special table called diesel_schema_migrations.
The CLI tool handles the heavy lifting. It finds pending migrations, runs them in chronological order, and updates the tracking table. If you need to undo a change, the tool runs the down.sql file. This separation keeps your Rust code free of database administration logic. You write Rust for business logic and SQL for schema evolution.
Generating and running a migration
Install the Diesel CLI tool with the features matching your database backend. The tool is separate from the diesel crate you use in your application.
# Install the CLI. Features must match your database driver.
# Use --features postgres for PostgreSQL, --features mysql for MySQL.
cargo install diesel_cli --no-default-features --features sqlite
# Create the migrations directory. Diesel expects this folder at the project root.
mkdir -p migrations
# Generate a new migration named 'create_users'.
# This creates a timestamped folder with up.sql and down.sql inside.
diesel migration generate create_users
# Run pending migrations against the database.
# Diesel reads DATABASE_URL from the environment to find the database.
diesel migration run
The diesel migration generate command creates a directory named with a timestamp prefix, like 20231027120000_create_users. The timestamp ensures migrations run in the order they were created. The name after the underscore is for humans. Diesel ignores it when ordering.
Convention aside: The community standard is to keep the migrations folder at the project root, not inside src. Diesel looks for migrations by default. If you put it elsewhere, you have to pass --migration-dir every time, which breaks the muscle memory of other developers.
Inside the migration directory
Open the generated folder. You'll see two empty files. Write your SQL in them.
-- migrations/20231027120000_create_users/up.sql
-- Define the schema change. This runs when you apply the migration.
-- Use standard SQL syntax for your database backend.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT NOT NULL UNIQUE,
email TEXT NOT NULL
);
-- migrations/20231027120000_create_users/down.sql
-- Reverse the change. This runs when you roll back the migration.
-- If the migration creates a table, the down script must drop it.
DROP TABLE users;
The up.sql script applies the change. The down.sql script reverts it. Diesel requires both files to exist. If you leave down.sql empty, you can still run the migration, but you won't be able to roll it back safely. Treat down.sql as a contract. If you can't write the rollback, the migration is too risky.
The tracking table
When you run diesel migration run, Diesel connects to the database and checks for a table named diesel_schema_migrations. If the table doesn't exist, Diesel creates it. The table stores the version ID of every applied migration and the timestamp when it ran.
Diesel compares the versions in the tracking table with the migration folders in your directory. If a folder exists but its version is missing from the table, Diesel marks it as pending. It then executes the up.sql file for each pending migration in order. After each successful execution, Diesel inserts a row into the tracking table.
This tracking table is the source of truth. Diesel doesn't inspect your database schema to guess what's applied. It trusts the table. If you manually create a table in the database without running a migration, Diesel won't know about it. The Rust code generated by Diesel will also miss that table. Always use migrations to change the schema.
Realistic workflow: adding columns and rolling back
Migrations aren't just for creating tables. You use them for every schema change. Here's how you add a column and then roll it back.
-- migrations/20231028090000_add_age_to_users/up.sql
-- Add a new column to an existing table.
-- SQLite supports ADD COLUMN, but has limits on other ALTER operations.
ALTER TABLE users ADD COLUMN age INTEGER;
-- migrations/20231028090000_add_age_to_users/down.sql
-- Remove the column.
-- SQLite does not support DROP COLUMN in older versions.
-- If you use SQLite, you may need to recreate the table in down.sql.
ALTER TABLE users DROP COLUMN age;
Run the migration with diesel migration run. Diesel executes the ALTER TABLE and records the version. If you realize the column is a mistake, run diesel migration revert. Diesel finds the last applied migration, executes its down.sql, and removes the version from the tracking table. The database returns to its previous state.
The SQLite trap and database-specific SQL
Diesel migrations run raw SQL. Diesel does not translate your SQL to be database-agnostic. This is a feature, not a bug. It gives you full control over the database. It also means you hit database-specific limits.
SQLite has strict restrictions on ALTER TABLE. In versions before 3.25.0, you can only add a column. You cannot rename a column, drop a column, or change a column type. If you write a migration that renames a column, it works on PostgreSQL but crashes on SQLite.
-- This works on PostgreSQL.
-- This fails on SQLite < 3.25.0.
ALTER TABLE users RENAME COLUMN email TO address;
If you support multiple backends, you must test migrations on all of them. Diesel doesn't protect you from SQL syntax errors or unsupported operations. The database is the boss. If the SQL is invalid, the migration fails, and Diesel stops.
Convention aside: When writing migrations for SQLite, check the version limits for ALTER TABLE. If you need to rename a column on older SQLite, you have to recreate the table in the migration. Copy the data to a new table, drop the old one, and rename the new one. It's verbose, but it works.
Editing a migration after it runs
You can't just edit a migration file and re-run diesel migration run. Diesel checks the tracking table and sees the version is already applied. It skips the migration. You need to revert and re-apply.
Use diesel migration redo to roll back the last migration and run it again. This executes down.sql followed by up.sql. It's the standard way to fix a mistake in a migration that has already been applied.
# Revert the last migration and run it again.
# Useful when you edit a migration file after it has been applied.
diesel migration redo
If you need to revert multiple migrations, run diesel migration revert multiple times. Diesel always reverts the most recent applied migration first. You can't revert a specific migration in the middle of the chain. The order is strict.
Pitfalls and error handling
Migrations fail for predictable reasons. The CLI tool reports errors clearly.
If the DATABASE_URL environment variable is missing or invalid, Diesel prints "Error: Database URL must be set" and exits. Always export the variable before running the CLI.
# Set the database URL for the CLI session.
export DATABASE_URL="sqlite://./dev.db"
diesel migration run
If you try to run a migration that modifies a table that doesn't exist, the database returns an error like "table users does not exist". Diesel propagates this error and stops. It doesn't apply the migration. The tracking table remains unchanged. You can fix the SQL and run again.
If you have a migration folder with a version that conflicts with an existing one, Diesel detects the duplicate and fails. This usually happens when you generate two migrations with the same timestamp because your system clock jumped back. Rename the folder to fix the conflict.
If you run diesel migration run and there are no pending migrations, Diesel prints "No migrations to run" and exits with success. This is normal. It means your database is up to date.
When to use Diesel migrations
Use Diesel migrations when you are building a Diesel-based application and want a seamless way to manage schema evolution. Use Diesel migrations when your team needs a standard way to apply and roll back schema changes without writing custom scripts. Use Diesel migrations when you want the CLI tool to handle the tracking table and ordering automatically.
Reach for raw SQL execution when you are managing the database outside of Rust and only need the application to read the schema. Pick a different migration tool when you are using a database library other than Diesel, as Diesel's CLI is tightly coupled to its own conventions.
Keep migrations small and atomic. One table, one migration. One column, one migration. Large migrations that do everything are hard to roll back and hard to reason about. Trust the borrow checker for your Rust code. Trust the migration tool for your database schema.