Writing your first migration

Step 6 of 15. Step 5 left the service connected to Postgres through a pool, with /health doing a real round trip — and every other handler still answering hardcoded data, because the links table it needs does not exist. This step creates it: sqlx-cli, one plain SQL file under migrations/, and a startup hook that applies pending migrations before the server binds. From here the schema is code, versioned alongside the app.

Start from step 5’s end state: git checkout api-ch05 in your clone of the examples repository, inside axum-sqlx-api/. You also need the Postgres container from that step still running.

The dependencies

[dependencies]
axum = { version = "0.8", features = ["macros"] }
serde = { version = "1", features = ["derive"] }
sqlx = { version = "0.9", features = ["runtime-tokio", "tls-rustls", "postgres", "macros"] }
tokio = { version = "1", features = ["macros", "rt-multi-thread"] }

No new crates this step. The list is unchanged since step 5 — the demo program below uses SQLx and tokio from it.

Installing sqlx-cli

SQLx the crate and sqlx-cli are separate things: the crate is a library your service links against, the CLI is a binary that creates and applies migration files. Install it once per machine:

cargo install sqlx-cli --locked --no-default-features --features rustls,postgres

  Installing /Users/rango/.cargo/bin/cargo-sqlx
  Installing /Users/rango/.cargo/bin/sqlx
   Installed package `sqlx-cli v0.9.0` (executables `cargo-sqlx`, `sqlx`)

--no-default-features with rustls,postgres trims the build to the one database this track uses, and --locked builds the CLI from its published lockfile. This compiles for real — about nine minutes here — because there is no prebuilt binary for the pair you asked for. The result:

sqlx --version

sqlx-cli 0.9.0

This track’s CLI and crate are both on 0.9.

The migration file

sqlx migrate add create_links

Creating migrations/20261018060956_create_links.sql

Your timestamp will differ. The name follows {version}_{description}.sql: everything before the first underscore is the version, the rest becomes the description. A timestamp is sqlx’s default numbering; for a service this size, small integers read better in review, and ordering comes from the integer, not the clock:

mv migrations/20261018060956_create_links.sql migrations/0001_create_links.sql

The new file starts as a single comment — replace it with the table:

CREATE TABLE links (
    id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code       TEXT NOT NULL,
    url        TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Four columns, one per job the contract gives the table. id is an identity column — Postgres’s current replacement for SERIAL, managed by the database. code is the short string a visitor types after the domain, and it cannot be null, because a link without its short code is not a link. url is the destination. created_at fills itself. Step 9 adds a unique index on code together with the code generation that needs it; the table is done here.

Why a plain .sql file rather than something fancier: what you see is what runs. The file reviews in a pull request as SQL, executes through psql by hand if it ever needs to, and can use anything this Postgres understands. There is no DSL to learn and no builder API to outgrow. The trade-off sits on the reversal side: a plain migration only goes forward. sqlx migrate revert exists, but it wants reversible migrations — pairs of .up.sql and .down.sql files — and a revert discards data. The usual practice is to fix forward with the next migration instead.

Everything sqlx knows about a migration it parses out of the filename, and the rules are strict. Migrator::new is the type that reads a migrations directory, and the demo below builds a scratch one, feeds it three well-named files and one badly named one, then asks the migrator what it found:

use sqlx::migrate::Migrator;
use std::fs;
use std::path::Path;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let dir = Path::new("migrations-demo");
    fs::create_dir(dir)?;
    fs::write(dir.join("0001_create_links.sql"), "CREATE TABLE links (id BIGINT);")?;
    fs::write(dir.join("0002_add_hits_column.sql"), "ALTER TABLE links ADD COLUMN hits INT;")?;
    fs::write(dir.join("0100_backfill_codes.sql"), "SELECT 1;")?;

    for m in Migrator::new(dir).await?.iter() {
        println!("{}: {}", m.version, m.description);
    }

    fs::write(dir.join("create_links.sql"), "CREATE TABLE oops ();")?;
    match Migrator::new(dir).await {
        Ok(_) => println!("accepted"),
        Err(e) => println!("error: {e}"),
    }

    fs::remove_dir_all(dir)?;
    Ok(())
}

1: create links
2: add hits column
100: backfill codes
error: while resolving migrations: error parsing migration filename "create_links.sql"; expected integer version prefix (e.g. `01_foo.sql`)

Versions parse out of the prefix (leading zeros dropped), underscores become spaces, and iteration is in version order — that 0100 lands after 0002 is the point of numbering. One file without an integer prefix and the whole directory is rejected. These are the same rules the CLI applied above: sqlx migrate run reported the real file as version 1, description “create links”.

Running it

export DATABASE_URL='postgres://postgres:dev@localhost:5432/shortlink'
sqlx migrate run

Applied 1/migrate create links (3.182584ms)

Line by line: applied migration 1; migrate is the type label for a plain one-way migration; “create links” is the description parsed from the filename; the duration will differ on your machine. Run it a second time and nothing prints — the applied state lives in the database now, and a no-op run is silent.

What landed:

docker exec shortlink-pg psql -U postgres -d shortlink -c '\d links'

                                    Table "public.links"
   Column   |           Type           | Collation | Nullable |           Default
------------+--------------------------+-----------+----------+------------------------------
 id         | bigint                   |           | not null | generated always as identity
 code       | text                     |           | not null |
 url        | text                     |           | not null |
 created_at | timestamp with time zone |           | not null | now()
Indexes:
    "links_pkey" PRIMARY KEY, btree (id)

And the bookkeeping:

docker exec shortlink-pg psql -U postgres -d shortlink -c \
  'SELECT version, description, installed_on, success FROM _sqlx_migrations;'

 version | description  |        installed_on         | success
---------+--------------+-----------------------------+---------
       1 | create links | 2026-10-18 06:10:32.6496+00 | t
(1 row)

_sqlx_migrations is sqlx’s own table, created on first run: one row per applied migration, with the version, the time, and whether it succeeded. There is also a checksum column — a SHA-384 of the file’s bytes at the moment it was applied. That checksum is about to matter.

The service applies its own migrations

The CLI is for running migrations by hand. A deploy should not depend on someone remembering to run it, so the service applies pending migrations itself, from a copy embedded at compile time. One line in main, immediately after the pool connects:

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

migrate! reads migrations/ at compile time and embeds every file it finds into the binary, which is why this line only compiles inside the project where that directory exists — it is the one line this step adds to main.rs, and the full file is one git checkout api-ch06 away. At startup, .run(&pool) walks the embedded list and applies whatever the database has not seen yet.

Proof on an empty database. Create one, start the service against it, and in a second terminal ask Postgres what exists:

docker exec shortlink-pg psql -U postgres -c 'CREATE DATABASE shortlink_staging;'
DATABASE_URL='postgres://postgres:dev@localhost:5432/shortlink_staging' cargo run

docker exec shortlink-pg psql -U postgres -d shortlink_staging -c '\dt'

              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | _sqlx_migrations | table | postgres
 public | links            | table | postgres
(2 rows)

An empty database, one cargo run, and the schema exists. Against the database you have been using, the same startup prints nothing — every embedded migration is already applied. (When you are done, drop shortlink_staging; it has served its purpose.)

Never edit an applied migration

The tempting next move: 0001 says something you want to reword, so you edit the file. Add a comment to migrations/0001_create_links.sql and start the service again:

thread 'main' (13014065) panicked at src/main.rs:118:39:
failed to run migrations: VersionMismatch(1)

The process exits with code 101. The thread id is machine-specific; line 118 is the migrate! line in the tagged main.rs. What happened: the binary now embeds the edited file, whose checksum differs from the one recorded in _sqlx_migrations, and sqlx refuses to guess what “modified” means. The CLI phrases the same refusal — exit code 1 from sqlx migrate run:

error: migration 1 was previously applied but has been modified

The rule follows from the mechanism. Migrations are append-only history. While 0001 exists only on your machine — written, never applied anywhere shared — editing it is free. Once it is applied, the fix is a new file, 0002_whatever_changed.sql, and the old one stays byte-for-byte as it was.

One practical detail keeps this working: since migrate! embeds the files, migration changes have to recompile the binary — and they do. Adding, editing, and deleting a file under migrations/ all triggered a rebuild here. Older tutorials recommend a build.rs with a rerun-if-changed line, and sqlx migrate build-script generates one, but nothing extra was needed on this setup.

Where this leaves the service

The links table exists — id, code, url, created_at — and _sqlx_migrations tracks version

The handlers still answer hardcoded data: the two SQL paths that fill and read the table are step 8, and the short-code generation with its unique index is step 9.

From a clean checkout, the whole flow is short: install sqlx-cli, export DATABASE_URL, cargo run — the service creates its own table on the way up. The end state is tagged api-ch06 in the examples repository — git checkout api-ch06 inside axum-sqlx-api/ puts your tree where this article left it.

Next: queries the compiler checks. sqlx::query! reads the schema through the same DATABASE_URL at compile time and rejects a misspelled column before the program exists — against the table this step just created. The track overview keeps the full list current as articles land.