Migrations

Q: sqlx prepare relationship?

Regenerate .sqlx offline data after schema changes affecting query!.

Q: SeaORM migrations?

sea-orm-migration generates Rust migration modules - pick one tool per repo.

Version database schema with repeatable, ordered migration files - sqlx migrate and refinery are the common Rust choices.

Recipe

Quick-reference recipe card - copy-paste ready.

sqlx migrate add create_users
# edit migrations/<timestamp>_create_users.sql
sqlx migrate run

sqlx::migrate!("./migrations").run(&pool).await?;

When to reach for this: Any service with a persistent database needs auditable schema change history.

Working Example

-- migrations/20240101000001_create_items.sql
CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrations/20240102000001_add_items_sku.sql
ALTER TABLE items ADD COLUMN sku TEXT;
CREATE UNIQUE INDEX items_sku_key ON items (sku) WHERE sku IS NOT NULL;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = sqlx::PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
    sqlx::migrate!("./migrations").run(&pool).await?;
    Ok(())
}

What this demonstrates:

Timestamp-prefixed SQL files applied in order.
Additive migration with nullable column before backfill.
Partial unique index for optional sku.
Runtime migrate! on deploy or startup.

Deep Dive

How It Works

Migration runner records applied versions in _sqlx_migrations table.
Each file runs once; checksum detects edited historical files.
Reversible migrations optional - forward-only is common in prod.

Migration Rules

Rule	Why
One concern per file	Easier rollback narrative
Avoid destructive first deploy	Add column nullable, backfill, then NOT NULL
Test on copy of prod data	Catch lock/time issues
Run in CI before merge	Catch SQL errors early

Rust Notes

// Diesel migrations (alternative)
// diesel migration run

Gotchas

Editing applied migrations - Checksum mismatch breaks deploys. Fix: New migration file to correct schema.
Long locks - ALTER on big tables blocks writes. Fix: Online migration strategies, batch backfills.
Missing migrate in CI - Schema drift between dev and prod. Fix: sqlx migrate run in pipeline against ephemeral DB.
Data + schema in one file - Heavy data fix runs in transaction lock. Fix: Separate data backfill jobs.
No down migrations - Rollback plan unclear. Fix: Document manual rollback SQL in PR.

Alternatives

Alternative	Use When	Don't Use When
Diesel migrations	Diesel ORM stack	sqlx-only services
refinery	Library-agnostic SQL files	Already standardized on sqlx CLI
Flyway/Liquibase (JVM)	Polyglot org standard	Pure Rust pipeline preferred

FAQs

Startup vs job migrate?

Run in deploy job before traffic; optional sanity check on startup.

Squash migrations?

Only before first production deploy; never rewrite applied history.

Seed data?

Separate seed scripts, not mixed into schema migrations.

Multiple services one DB?

Coordinate migration ownership - one service runs migrations or shared migration repo.

Zero downtime column add?

Add nullable -> backfill job -> set NOT NULL in later migration.

sqlx prepare relationship?

Regenerate .sqlx offline data after schema changes affecting query!.

SQLite dev PG prod?

Keep migrations portable or maintain dialect-specific files consciously.

Rollback production?

Forward-fix migration preferred over reversing applied files.

Review checklist?

Indexes, lock time estimate, backwards compatibility for rolling deploys.

SeaORM migrations?

sea-orm-migration generates Rust migration modules - pick one tool per repo.

Databases Basics - Pool setup
sqlx - migrate! macro
Transactions & Isolation - Migration transactions
Databases Best Practices - Migration hygiene
Deployment Basics - Deploy order

Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.

Recipe

Quick-reference recipe card - copy-paste ready.

sqlx migrate add create_users
# edit migrations/<timestamp>_create_users.sql
sqlx migrate run

sqlx::migrate!("./migrations").run(&pool).await?;

When to reach for this: Any service with a persistent database needs auditable schema change history.

Working Example

-- migrations/20240101000001_create_items.sql
CREATE TABLE items (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- migrations/20240102000001_add_items_sku.sql
ALTER TABLE items ADD COLUMN sku TEXT;
CREATE UNIQUE INDEX items_sku_key ON items (sku) WHERE sku IS NOT NULL;

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let pool = sqlx::PgPool::connect(&std::env::var("DATABASE_URL")?).await?;
    sqlx::migrate!("./migrations").run(&pool).await?;
    Ok(())
}

What this demonstrates:

Timestamp-prefixed SQL files applied in order.
Additive migration with nullable column before backfill.
Partial unique index for optional sku.
Runtime migrate! on deploy or startup.

Deep Dive

How It Works

Migration runner records applied versions in _sqlx_migrations table.
Each file runs once; checksum detects edited historical files.
Reversible migrations optional - forward-only is common in prod.

Migration Rules

Rule	Why
One concern per file	Easier rollback narrative
Avoid destructive first deploy	Add column nullable, backfill, then NOT NULL
Test on copy of prod data	Catch lock/time issues
Run in CI before merge	Catch SQL errors early

Rust Notes

// Diesel migrations (alternative)
// diesel migration run

Gotchas

Editing applied migrations - Checksum mismatch breaks deploys. Fix: New migration file to correct schema.
Long locks - ALTER on big tables blocks writes. Fix: Online migration strategies, batch backfills.
Missing migrate in CI - Schema drift between dev and prod. Fix: sqlx migrate run in pipeline against ephemeral DB.
Data + schema in one file - Heavy data fix runs in transaction lock. Fix: Separate data backfill jobs.
No down migrations - Rollback plan unclear. Fix: Document manual rollback SQL in PR.

Alternatives

Alternative	Use When	Don't Use When
Diesel migrations	Diesel ORM stack	sqlx-only services
refinery	Library-agnostic SQL files	Already standardized on sqlx CLI
Flyway/Liquibase (JVM)	Polyglot org standard	Pure Rust pipeline preferred

FAQs

Startup vs job migrate?

Run in deploy job before traffic; optional sanity check on startup.

Squash migrations?

Only before first production deploy; never rewrite applied history.

Seed data?

Separate seed scripts, not mixed into schema migrations.

Multiple services one DB?

Coordinate migration ownership - one service runs migrations or shared migration repo.

Zero downtime column add?

Add nullable -> backfill job -> set NOT NULL in later migration.

sqlx prepare relationship?

Regenerate .sqlx offline data after schema changes affecting query!.

SQLite dev PG prod?

Keep migrations portable or maintain dialect-specific files consciously.

Rollback production?

Forward-fix migration preferred over reversing applied files.

Review checklist?

Indexes, lock time estimate, backwards compatibility for rolling deploys.

SeaORM migrations?

sea-orm-migration generates Rust migration modules - pick one tool per repo.

Databases Basics - Pool setup
sqlx - migrate! macro
Transactions & Isolation - Migration transactions
Databases Best Practices - Migration hygiene
Deployment Basics - Deploy order

Stack versions: This page was written for Rust 1.97.0 (edition 2024), Tokio 1.x, Axum 0.8, serde 1.0, sqlx 0.8, clap 4, and Polars 0.46+.

Migrations

Buscar en la documentación

Migrations