08. Database Migrations

📋 Jump to Takeaways

🎁 A migration is a versioned SQL file. Run them in order and you get your schema. Every developer, every environment, every deployment — same schema, reproducibly.

SQLx Migrate CLI

Install the SQLx CLI:

cargo install sqlx-cli --no-default-features --features postgres

Creating Migrations

sqlx migrate add create_links_table

This creates migrations/20260101000000_create_links_table.sql — the timestamp prefix ensures ordering.

Write the SQL:

-- migrations/20260101000000_create_links_table.sql
CREATE TABLE links (
    id         BIGSERIAL PRIMARY KEY,
    code       VARCHAR(16)  NOT NULL UNIQUE,
    url        TEXT         NOT NULL,
    clicks     BIGINT       NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_links_code ON links (code);

Running Migrations

sqlx migrate run

SQLx creates a _sqlx_migrations table to track which migrations have run. Re-running is safe — already-applied migrations are skipped.

Running Migrations at Startup

Embed and run migrations automatically when the service starts:

use sqlx::PgPool;

pub async fn run_migrations(pool: &PgPool) {
    sqlx::migrate!("./migrations")
        .run(pool)
        .await
        .expect("failed to run migrations");
}

// src/main.rs
let pool = create_pool(&config.database_url).await;
run_migrations(&pool).await;

sqlx::migrate! embeds the migration files into the binary at compile time. The service is self-migrating — no manual step needed on deployment.

Adding Columns

Each change is a new migration file:

sqlx migrate add add_label_to_links

-- migrations/20260201000000_add_label_to_links.sql
ALTER TABLE links ADD COLUMN label VARCHAR(255);

Never edit an existing migration that has already been applied. Always add a new one.

Reverting

SQLx supports down migrations with a naming convention:

sqlx migrate add --reversible add_label_to_links

This creates two files: ...up.sql and ...down.sql. Write the rollback in down.sql:

-- ...down.sql
ALTER TABLE links DROP COLUMN label;

sqlx migrate revert  # runs the most recent down migration

Migration Best Practices

Keep each migration focused on one change
Never modify a migration that has been applied in any environment
Test migrations on a copy of production data before applying to prod
Use TIMESTAMPTZ for all timestamps — store in UTC, display in local time
Add indexes in the same migration as the table they belong to

Key Takeaways

sqlx migrate add <name> creates a timestamped SQL migration file
sqlx migrate run applies pending migrations; already-applied ones are skipped
sqlx::migrate!() embeds migrations in the binary and runs them at startup
Never edit an applied migration — always add a new one
--reversible creates paired up/down migration files for rollbacks
TIMESTAMPTZ stores timestamps with timezone — always prefer it over TIMESTAMP

🎁 Your handlers need the database pool, but it was created in main. How do you get it into every handler without threading it through every function call? Next up: Axum's state system.