The schema drift disaster
You deploy a new feature. The code expects a verified_at column on the users table. The production database doesn't have it. The server crashes on the first request. You didn't break the code. You broke the contract between code and data. This is schema drift. Migrations fix this by versioning your database structure alongside your code.
Rust does not include a built-in migration command. The language focuses on memory safety and performance. Database management depends on the specific database you use. PostgreSQL, SQLite, and MySQL all have different SQL dialects and capabilities. Rust delegates this to the ecosystem. You pick a tool that matches your database and your workflow.
What a migration actually is
A migration is a script that changes the database schema. It has a version number and a direction. The up direction applies the change. The down direction reverts it. Migration tools track which scripts have run. They execute only the pending ones in order.
Think of a migration like a renovation checklist. You have a list of tasks. Task 1: Paint walls. Task 2: Install lights. You check off Task 1. Next time you walk in, you see the checkmark and skip to Task 2. If you hire a new contractor, they look at the checklist, not the walls. They trust the list. Migration tools work the same way. They trust the metadata table in the database, not the files on your disk.
This separation is crucial. The database holds the source of truth for the schema state. Your local files are just the instructions. If the metadata table says a migration ran, the tool assumes it ran. If you delete the migration file but the database still has the record, the tool skips it. If you delete the record but the file exists, the tool tries to run it again and crashes.
Treat the migration folder like a logbook. You can append entries. You never cross out the past.
Why Rust leaves this to the ecosystem
Rust is a systems language. It gives you control over memory and concurrency. It does not dictate your data layer. This is a feature, not a limitation. It means you can use the best tool for your database.
The two most common tools in the Rust ecosystem are Diesel and SQLx. Both provide migration support. Both use SQL files for migrations. Both create a metadata table to track state. They differ in how they integrate with the rest of your code.
Diesel is an ORM. It maps database rows to Rust structs. It generates a schema file that your code uses. Migrations are part of the Diesel workflow. SQLx is a query builder. It checks your SQL queries at compile time. It does not generate a schema file. Migrations are a separate feature that works alongside the query checker.
Diesel: The ORM bundle
Diesel bundles migrations with its ORM. You use the diesel CLI to generate, run, and revert migrations. The workflow is integrated with the schema generation. When you run a migration, Diesel can update the schema.rs file that your code depends on.
Start by setting up the migration directory. Run diesel setup in your project root. This creates the migrations/ folder and the diesel.toml configuration file. It also creates the diesel_schema_migrations table in your database.
Generate a new migration with diesel migration generate create_users. This creates a folder inside migrations/ with a timestamp prefix. The folder contains up.sql and down.sql. Edit up.sql to define the change.
-- migrations/20231001000000_create_users/up.sql
-- Create the users table with an auto-incrementing ID and a unique email.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL UNIQUE
);
Edit down.sql to revert the change. This is essential for rollbacks.
-- migrations/20231001000000_create_users/down.sql
-- Drop the users table to revert the migration.
DROP TABLE users;
Run the migration with diesel migration run. Diesel checks the diesel_schema_migrations table. It finds the pending migration. It executes up.sql inside a transaction. If the SQL fails, the transaction rolls back and the database stays unchanged. If it succeeds, Diesel inserts a row into the metadata table.
diesel migration run
Diesel also supports Rust-based migrations. You can write migration logic in Rust code instead of SQL. This is useful when you need to run complex transformations that SQL cannot express. The community convention is to stick to SQL for schema changes. SQL is portable and easier to review. Use Rust migrations only when you have no other choice.
Convention aside: diesel migration generate creates folders with timestamps. Never rename these folders manually. The timestamp determines the execution order. Renaming breaks the order and causes unpredictable behavior.
SQLx: The compile-time checker
SQLx is a lighter tool. It focuses on compile-time query verification. Migrations are a separate feature. You use the sqlx CLI to manage them. The workflow is simple and database-agnostic.
Add a migration with sqlx migrate add create_users. This creates a file in the migrations/ directory. The filename includes the timestamp and the name. SQLx uses a single file per migration. You write the up SQL in the file. You can add the down SQL using a special comment marker.
-- migrations/20231001000000000_create_users.sql
-- Create the users table.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE
);
-- This comment marks the start of the down migration.
-- sqlx migrate reverse
DROP TABLE users;
Run the migration with sqlx migrate run. SQLx checks the sqlx_migrations table. It executes pending migrations. It updates the metadata table.
sqlx migrate run
SQLx has a powerful feature for CI/CD pipelines. You can run migrations offline. This means the tool verifies the migration files without connecting to a database. This is useful when your build environment cannot access a live database. Use sqlx migrate run --offline in your CI configuration. The tool checks the checksums and ensures the migration history is consistent.
Convention aside: SQLx migrations live in migrations/ by default. You can change this path in your configuration. The community standard is to keep the default path. It reduces configuration overhead and makes the project structure predictable.
Under the hood: The metadata table
Both Diesel and SQLx rely on a metadata table to track migration state. Diesel creates diesel_schema_migrations. SQLx creates sqlx_migrations. The table stores the version, the checksum, and the execution time.
The checksum is the safety mechanism. When you run a migration, the tool calculates a hash of the SQL content. It stores the hash in the metadata table. If you edit a migration file that has already run, the hash changes. The tool detects the mismatch and refuses to run. This prevents silent corruption.
This behavior enforces a strict rule. You never edit a migration that has already been applied to a shared database. If you make a mistake in a migration, you create a new migration to fix it. You do not modify the old one.
Example scenario: You create a migration to add an email column. You run it. Later, you realize the column should be VARCHAR(255) instead of TEXT. You do not edit the original migration. You create a new migration to alter the column type.
-- migrations/20231002000000_fix_email_type/up.sql
-- Change the email column to VARCHAR(255).
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
This approach keeps the history accurate. The database state reflects the sequence of changes. You can replay the history on a fresh database and get the same result.
The metadata table also handles transactions. Migrations run inside a transaction. If any statement fails, the entire migration rolls back. This ensures atomicity. The database never ends up in a partial state.
Trust the metadata table. It is the source of truth. If the table and your files disagree, the table wins.
Pitfalls and error patterns
Migrations are simple in concept but easy to break in practice. The most common issue is the DATABASE_URL environment variable. Both Diesel and SQLx require this variable to connect to the database. If it is missing or incorrect, the tool fails immediately.
If you forget DATABASE_URL, Diesel rejects you with a ConnectionError telling you the environment variable is missing. SQLx returns a Configuration error. Set the variable before running the command.
export DATABASE_URL="postgres://user:password@localhost/mydb"
diesel migration run
Another pitfall is migration ordering. Tools rely on timestamps to determine order. If you generate multiple migrations quickly, they might have the same timestamp. This causes a conflict. The tool refuses to run. Delete the conflicting folders and regenerate them. Or manually adjust the timestamps to ensure unique values.
Be careful with irreversible changes. Some SQL operations cannot be undone. Dropping a column loses data. Renaming a table breaks references. If you write a down migration for an irreversible change, it might fail. Tools allow you to skip the down migration. Diesel lets you leave down.sql empty. SQLx lets you omit the reverse section.
When you skip the down migration, you accept the risk. You cannot roll back that change. This is acceptable for production databases where rollbacks are rare. It is dangerous for development databases where you iterate quickly.
Error handling matters. Migrations can fail due to syntax errors, missing dependencies, or lock conflicts. Diesel returns diesel::result::Error. SQLx returns sqlx::Error. Check the error message. It usually points to the specific SQL statement that failed. Fix the SQL and try again.
If a migration fails, the transaction rolls back. The database is unchanged. You can edit the migration and run it again. The tool will retry the same migration.
Convention aside: Use cargo make or just to wrap migration commands. This standardizes the workflow across your team. Define tasks like make migrate or just migrate. This reduces typos and ensures everyone runs the same command.
Decision matrix
Use Diesel when you are already using the Diesel ORM and want a unified workflow that integrates schema generation with migrations. Use Diesel when your team prefers Rust-based migrations for complex data transformations. Use SQLx when you want compile-time query verification and a lightweight migration tool that works with any database. Use SQLx with --offline when your CI environment cannot connect to a live database and you need to verify migration consistency during builds. Reach for raw SQL migrations when you need database-specific features that an ORM abstraction hides, such as custom indexes or partitioning.
The database is the source of truth for schema state. Your code is just the client. Keep them in sync.