Scrolls
3 min read
On this page

Migrations

Database migrations are version-controlled changes to your schema. They record every CREATE TABLE, ALTER TABLE, and index addition in order, so any developer can reproduce the exact database state from scratch. SQLx provides a migration tool that integrates tightly with the Rust build process.

Setting Up sqlx-cli

Install the CLI tool:

cargo install sqlx-cli --features postgres

Set your database URL:

export DATABASE_URL="postgres://user:pass@localhost/mydb"

Or create a .env file in your project root:

DATABASE_URL=postgres://user:pass@localhost/mydb

Create the database if it does not exist:

sqlx database create

Creating Migrations

Generate a new migration:

sqlx migrate add create_users_table

This creates a file like migrations/20240115120000_create_users_table.sql. Write your SQL in it:

-- migrations/20240115120000_create_users_table.sql
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_username ON users (username);

Run the migration:

sqlx migrate run

Applied 20240115120000/migrate create_users_table (25.4ms)

Reversible Migrations

By default, sqlx migrate add creates a single .sql file that only goes up. For reversible migrations, use the --reversible flag:

sqlx migrate add --reversible add_tasks_table

This creates two files:

migrations/20240115130000_add_tasks_table.up.sql
migrations/20240115130000_add_tasks_table.down.sql

Write the up migration:

-- migrations/20240115130000_add_tasks_table.up.sql
CREATE TABLE tasks (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    completed BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_tasks_user_id ON tasks (user_id);

Write the down migration:

-- migrations/20240115130000_add_tasks_table.down.sql
DROP TABLE IF EXISTS tasks;

Revert the last migration:

sqlx migrate revert

Running Migrations in Code

For production, run migrations on application startup:

use sqlx::postgres::PgPoolOptions;

#[tokio::main]
async fn main() {
    let pool = PgPoolOptions::new()
        .max_connections(20)
        .connect("postgres://user:pass@localhost/mydb")
        .await
        .expect("Failed to connect to database");

    sqlx::migrate!()
        .run(&pool)
        .await
        .expect("Failed to run migrations");

    // Application continues...
}

sqlx::migrate!() embeds the migrations directory into the binary at compile time. The application carries its own migrations and applies them on startup. This ensures the schema is always up to date when the application starts.

Migration Workflow in Development

A typical development workflow:

# 1. Create a migration
sqlx migrate add add_tags_to_tasks

# 2. Write the SQL
# Edit migrations/20240115140000_add_tags_to_tasks.sql

# 3. Run it
sqlx migrate run

# 4. If something is wrong, fix and re-run
# For reversible migrations:
sqlx migrate revert
# Fix the SQL
sqlx migrate run

# 5. Check migration status
sqlx migrate info

Applied 20240115120000 create_users_table (applied)
Applied 20240115130000 add_tasks_table (applied)
Pending  20240115140000 add_tags_to_tasks (pending)

Migration Workflow in CI

In CI, you need a database to run compile-time checks and tests. A typical approach:

# .github/workflows/ci.yml (relevant steps)
services:
  postgres:
    image: postgres:16
    env:
      POSTGRES_DB: test_db
      POSTGRES_USER: test_user
      POSTGRES_PASSWORD: test_pass

steps:
  - name: Run migrations
    run: sqlx migrate run
    env:
      DATABASE_URL: postgres://test_user:test_pass@localhost/test_db

  - name: Build and test
    run: cargo test
    env:
      DATABASE_URL: postgres://test_user:test_pass@localhost/test_db

For projects that want CI without a live database, SQLx supports offline mode:

# Generate query metadata locally (requires a running database)
cargo sqlx prepare

# This creates a .sqlx/ directory with cached query data
# Commit .sqlx/ to version control

# In CI, build without a database
SQLX_OFFLINE=true cargo build

Schema Migrations vs Data Migrations

Schema migrations change the structure: tables, columns, indexes, constraints. Data migrations change the contents: backfilling values, transforming existing rows.

Keep them separate when possible:

-- 20240115150000_add_status_column.sql
ALTER TABLE tasks ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

-- 20240115150100_backfill_task_status.sql
UPDATE tasks SET status = 'completed' WHERE completed = TRUE;
UPDATE tasks SET status = 'pending' WHERE completed = FALSE;

-- 20240115150200_remove_completed_column.sql
ALTER TABLE tasks DROP COLUMN completed;

Three migrations, applied in order: add the new column, backfill the data, remove the old column. This approach lets you deploy incrementally — the application can work with both the old and new columns during the transition.

Schema Versioning

SQLx tracks applied migrations in a _sqlx_migrations table:

-- This table is created automatically
SELECT version, description, installed_on, success
FROM _sqlx_migrations
ORDER BY version;

 version              | description           | installed_on                | success
 20240115120000       | create_users_table    | 2024-01-15 12:00:05.123+00 | true
 20240115130000       | add_tasks_table       | 2024-01-15 13:00:02.456+00 | true

Never edit this table manually. Never rename or reorder migration files after they have been applied. Once a migration is in production, it is immutable — create a new migration to make changes.

Naming Conventions

Good migration names describe the change:

20240115120000_create_users_table.sql
20240115130000_add_tasks_table.sql
20240115140000_add_email_index_to_users.sql
20240115150000_add_status_to_tasks.sql
20240115160000_create_tags_table.sql
20240115170000_add_due_date_to_tasks.sql

Bad migration names:

20240115120000_update.sql
20240115130000_fix.sql
20240115140000_changes.sql

The name should tell you what changed without opening the file.

Common Pitfalls

  • Editing applied migrations. Once a migration has been applied, it is locked. SQLx checks checksums. If you change a migration that has already run, future migrate run calls will fail with a checksum mismatch.
  • Not making migrations idempotent. Use IF NOT EXISTS for CREATE TABLE and IF EXISTS for DROP TABLE. This prevents failures if a migration is accidentally applied twice.
  • Large data migrations in a single transaction. PostgreSQL locks the table during ALTER TABLE and large UPDATE statements. For big tables, batch the data migration or use concurrent index creation.
  • Forgetting down migrations. If you always use irreversible migrations, you cannot roll back in development. Use --reversible for migrations you might need to undo.
  • Storing the database URL in code. Always use environment variables or .env files. Never commit credentials to version control.

Key Takeaways

  • Use sqlx-cli to create, run, and revert migrations from the command line.
  • sqlx::migrate!() embeds and runs migrations at application startup, keeping the schema in sync with the code.
  • Use --reversible for migrations you might need to undo during development.
  • Separate schema migrations from data migrations for safer deployments.
  • Use cargo sqlx prepare for CI environments that lack a live database.
  • Never edit applied migrations. Create new ones to make changes.
  • Name migrations descriptively so the history tells the story of your schema.