Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 38 ยท Databases with SQLx

Migrations and Transactions in SQLx

Version your schema with sqlx migrate, run migrations at startup, and keep multi-step writes consistent with SQLx transactions, commit and rollback, with runnable stand-ins for both mechanisms.

Two things keep a database-backed service healthy over time. Migrations change the schema in small, ordered, repeatable steps so every environment (your laptop, CI, staging, production) ends up with the same tables. Transactions group several writes so they succeed or fail as one unit, so you never leave half a change behind. SQLx supports both without extra crates.

SQLx and a live database are not available on this site's runner, so the real code is read-only. Each concept has a runnable stand-in that models the mechanism.

Migrations with sqlx-cli

Install the command line tool once and create a database from DATABASE_URL:

Terminal
cargo install sqlx-cli --no-default-features --features rustls,postgres
sqlx database create
sqlx migrate add create_todos

The last command creates migrations/20260101120000_create_todos.sql (a timestamp prefix, so files sort in order). Write plain SQL inside:

SQL
-- migrations/20260101120000_create_todos.sql
CREATE TABLE todos (
    id         BIGSERIAL PRIMARY KEY,
    title      TEXT        NOT NULL,
    done       BOOLEAN     NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX todos_done_idx ON todos (done);

Run pending migrations with sqlx migrate run. SQLx records each applied file, with a checksum, in a _sqlx_migrations table. If someone edits an already-applied file, the checksum no longer matches and SQLx refuses to continue. That is deliberate: never edit a migration that has shipped. Write a new one instead. Use sqlx migrate add -r name for reversible migrations with up and down files.

Running migrations at startup

You can also embed the migrations in the binary and apply them when the server boots, which suits containers:

Rust
// Real code: read-only.
let pool = PgPoolOptions::new().connect(&url).await?;
sqlx::migrate!("./migrations").run(&pool).await?;

migrate! reads the folder at compile time, so the final binary carries its own schema history. If you run several app instances, SQLx takes an advisory lock so only one applies the migrations at a time. Many teams run migrations as a separate deploy step so a bad migration cannot half-start a fleet.

Make migrations safe for rolling deploys: add columns as nullable or with a default first, deploy the code that uses them, and only later add constraints or drop old columns. Old and new app versions run side by side for a while.

Model: a migration runner

This stand-in records applied migrations with a checksum, skips the ones already done, and detects tampering, the three behaviours that matter.

Rust Ordered, checksummed migrations (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

Transactions

Consider moving credit between two accounts. If the first UPDATE succeeds and the process dies before the second, money has vanished. A transaction makes both statements atomic: either both commit, or neither does.

Rust
// Real code: read-only.
async fn transfer(db: &PgPool, from: i64, to: i64, cents: i64) -> Result<(), AppError> {
    let mut tx = db.begin().await?;

    let debited = sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2 AND balance >= $1")
        .bind(cents)
        .bind(from)
        .execute(&mut *tx)
        .await?;
    if debited.rows_affected() == 0 {
        return Err(AppError::InsufficientFunds); // tx dropped: automatic rollback
    }

    sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2")
        .bind(cents)
        .bind(to)
        .execute(&mut *tx)
        .await?;

    tx.commit().await?;
    Ok(())
}

Key points:

  • db.begin() checks out a connection and starts the transaction. Queries inside use &mut *tx as their executor, so they all share that one connection.
  • If the Transaction is dropped without commit(), SQLx rolls it back. Early returns with ? are therefore safe.
  • The AND balance >= $1 guard inside the UPDATE makes the check and the change one atomic step, avoiding a read-then-write race.
  • A transaction holds a connection (and often row locks) until it ends: keep it short and never .await slow external calls inside.

Model: commit or roll back

The stand-in implements a transaction as "work on a copy, then swap it in on commit":

Rust Atomic transfer with rollback (stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with io::stdin().read_line()), it gets the next line from this box.

The failed transfers leave both balances untouched, the same observable result a database rollback gives you.

Isolation and retries

PostgreSQL's default isolation level (read committed) means each statement sees data committed before it started. For stricter guarantees use SELECT ... FOR UPDATE to lock rows, or run at SERIALIZABLE and retry when the database reports a serialization failure (SQLSTATE 40001). Keep operations inside a retry loop idempotent. The chapter project URL shortener uses a unique constraint instead of locks to stay simple.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why must you not edit a migration that was already applied in production?

    Applied migrations are recorded with a checksum. Changing history causes a mismatch error; add a new migration instead.
  2. 2. What happens when a SQLx Transaction is dropped without calling commit?

    Dropping an uncommitted transaction rolls it back, so early returns with ? are safe.
  3. 3. Which executor do you pass to queries inside a transaction tx?

    Queries must run on the transaction's own connection, which you borrow with &mut *tx.
  4. 4. Why keep transactions short?

    A long transaction starves the pool and blocks other writers waiting on the same rows.

Summary

  • Migrations are ordered, timestamped SQL files tracked in _sqlx_migrations with checksums.
  • Create with sqlx migrate add, apply with sqlx migrate run or sqlx::migrate!().run(&pool).
  • Never edit an applied migration; add a new one and make changes backward-compatible.
  • pool.begin() starts a transaction; commit() saves it, dropping rolls it back.
  • Keep transactions short and push checks into the SQL so they are atomic.

Next: Error Handling with IntoResponse