Diesel Migrations for Rust: up.sql, down.sql and schema.rs

Diesel keeps the database schema of a Rust project in two places that it keeps in sync for you: a migrations/ directory of plain SQL, one timestamped folder with up.sql and down.sql per change, and a generated src/schema.rs that describes the resulting tables to the compiler. You write the SQL, the CLI applies it and records it in __diesel_schema_migrations, and print-schema turns the live database into the table! macros your queries are type-checked against. Understanding that loop is most of what there is to know.

Setup: the CLI, DATABASE_URL and diesel setup#

The CLI is a separate binary, built with only the database backends you need so that it does not require every client library on your machine:

cargo install diesel_cli --no-default-features --features postgres
echo 'DATABASE_URL=postgres://petclinic:secret@localhost/petclinic' > .env
diesel setup

Diesel supports PostgreSQL, MySQL (and by extension MariaDB) and SQLite. diesel setup creates the database if it does not exist, creates the migrations/ directory and a diesel.toml, and runs any migrations already present. On PostgreSQL it also adds an initial migration that installs a small helper function used by Diesel's updated_at convention; you can keep or delete it.

diesel.toml is where you tell the CLI where the schema file lives and which tables to include:

[print_schema]
file = "src/schema.rs"

[migrations_directory]
dir = "migrations"

Generating a migration: one folder, two files#

diesel migration generate create_owners_and_pets

This creates a directory whose name is a timestamp followed by the name you gave, containing an empty up.sql and down.sql:

migrations/
  2026-09-21-120000_create_owners_and_pets/
    up.sql
    down.sql

Both files are ordinary SQL for your engine. Diesel does not parse them; it sends them to the database. A first migration for the PetClinic schema:

-- up.sql
CREATE TABLE owners
(
    id         INT          NOT NULL,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    address    VARCHAR(255),
    city       VARCHAR(80),
    telephone  VARCHAR(20),
    CONSTRAINT pk_owners PRIMARY KEY (id)
);

CREATE TABLE types
(
    id   INT         NOT NULL,
    name VARCHAR(80) NOT NULL,
    CONSTRAINT pk_types PRIMARY KEY (id)
);

CREATE TABLE pets
(
    id         INT         NOT NULL,
    name       VARCHAR(50) NOT NULL,
    birth_date DATE,
    type_id    INT         NOT NULL,
    owner_id   INT         NOT NULL,
    CONSTRAINT pk_pets PRIMARY KEY (id),
    CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
    CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);

-- down.sql
DROP TABLE pets;
DROP TABLE types;
DROP TABLE owners;

The timestamp is the migration's version and defines the order. Because migrations from two branches get different timestamps, merges rarely conflict, but they can still be applied out of order on a developer's database; diesel migration list shows which are applied, and diesel migration pending tells you whether a fresh pull left anything unapplied.

Newer Diesel versions can also generate the SQL for you from a change to schema.rs with --diff-schema. It is a convenience for simple additions; read what it writes before running it.

Running, reverting and redoing#

diesel migration run
diesel migration revert
diesel migration redo
diesel migration list

run applies every pending migration in version order. revert runs the down.sql of the most recently applied migration. redo reverts and re-runs the last one, which is the fastest way to iterate on a migration you are still writing: edit up.sql, redo, look at the result, repeat. It also happens to prove that your down.sql works, which is the only time most teams ever exercise it.

Two behaviours are worth knowing. First, on PostgreSQL and SQLite each migration runs inside a transaction by default, so a failing statement leaves nothing behind. A migration that needs to run outside a transaction, such as one that creates an index concurrently, can opt out with a metadata.toml next to its SQL files containing run_in_transaction = false. MySQL commits DDL implicitly regardless, so a failed up.sql there can leave partial results; keep MySQL migrations small. Second, after a successful run or revert the CLI regenerates schema.rs if diesel.toml points to it, so the compiler always sees the schema the database actually has.

The __diesel_schema_migrations table#

Applied versions are recorded in a table named __diesel_schema_migrations, one row per migration with the version string and the time it ran. There is no checksum: Diesel decides what is pending by comparing directory names against that table. Consequently, a migration whose up.sql you edit after it ran is not re-applied anywhere it has already run, and renaming a folder makes Diesel treat it as new. The discipline is the usual one: a migration that has run on any shared database is frozen, and mistakes are fixed with a new migration.

When you look at the database in a client or a diagram, this table is Diesel's bookkeeping, not part of your model. Tools that draw the schema generally hide it, and you should not add foreign keys to it or reference it from application code.

schema.rs and the table! macro#

The second half of the loop is diesel print-schema. It connects to the database, reads the tables and their columns and writes Rust code:

// @generated automatically by Diesel CLI.

diesel::table! {
    owners (id) {
        id -> Int4,
        #[max_length = 50]
        first_name -> Varchar,
        #[max_length = 50]
        last_name -> Varchar,
        #[max_length = 255]
        address -> Nullable<Varchar>,
        #[max_length = 80]
        city -> Nullable<Varchar>,
        #[max_length = 20]
        telephone -> Nullable<Varchar>,
    }
}

diesel::table! {
    pets (id) {
        id -> Int4,
        #[max_length = 50]
        name -> Varchar,
        birth_date -> Nullable<Date>,
        type_id -> Int4,
        owner_id -> Int4,
    }
}

diesel::joinable!(pets -> owners (owner_id));
diesel::joinable!(pets -> types (type_id));

diesel::allow_tables_to_appear_in_same_query!(owners, pets, types);

Each table! block names the table, its primary key in parentheses and every column with its SQL type as Diesel sees it (Int4, Varchar, Nullable<Date>). joinable! is generated from the foreign keys and is what lets pets::table.inner_join(owners::table) compile without you spelling out the join condition. A foreign key you forgot in up.sql therefore costs you twice: no constraint in the database and no joinable! in the code.

Because the file is generated from the live database, it belongs in version control but not in your editor. Treat a hand edit to schema.rs as a bug; change the migration and regenerate instead. The [print_schema] section of diesel.toml can restrict which tables are dumped and add derives to the generated types when the defaults do not fit.

Embedding migrations in the binary#

Many Rust services do not ship a separate CLI to production. The diesel_migrations crate compiles the migrations/ directory into the binary and runs it at startup:

use diesel::pg::PgConnection;
use diesel_migrations::{embed_migrations, EmbeddedMigrations, MigrationHarness};

pub const MIGRATIONS: EmbeddedMigrations = embed_migrations!("migrations");

pub fn run_migrations(conn: &mut PgConnection) {
    conn.run_pending_migrations(MIGRATIONS)
        .expect("migrations failed");
}

embed_migrations! reads the directory at compile time, so a new migration folder requires a rebuild, which is what you want: the binary and the schema it expects are one artifact. run_pending_migrations behaves like diesel migration run. Run it once, from a single process, before the service starts serving; several replicas racing to migrate the same database at boot is a classic source of confusing errors.

Practices that hold up#

• Write down.sql and test it with redo before opening the pull request.
• Commit schema.rs in the same change as the migration, generated by the CLI, so reviewers see both the SQL and its effect on the types.
• Use explicit constraint names (pk_owners, fk_pets_owners) rather than generated ones; they read better in errors and in diagrams, as argued in Database Naming Conventions.
• Develop against the same engine as production. SQLite is tempting for its speed, but its ALTER TABLE and type handling differ enough that a migration can pass locally and fail on PostgreSQL; the specifics are in Using SQLite in Development.
• Run the migrations from scratch in CI against a throw-away database container, then compile. A migration that runs but produces a schema the code does not expect fails at that point, where it is cheap.

Where SchemaDrawer fits#

schema.rs tells the compiler about the schema; a diagram tells people. SchemaDrawer accepts a Diesel migrations/ directory as it is, runs the up.sql files in order with the Diesel CLI in Docker against a throw-away PostgreSQL, MySQL, MariaDB or SQLite of the version you choose, reads the resulting schema over JDBC and returns an SVG with every table, column, primary key and foreign key. The bookkeeping table is left out. The Diesel page lists the accepted layout, and the GitHub Action can regenerate the diagram whenever a migration folder changes.

Key takeaways#

• A Diesel migration is a timestamped folder with up.sql and down.sql; the timestamp is the version and the order.
• run, revert and redo apply, undo and re-apply migrations; applied versions live in __diesel_schema_migrations with no checksum.
• schema.rs is generated from the live database by print-schema; never edit it by hand.
• embed_migrations! compiles the migrations into the binary so a service can migrate itself at startup, once.
• Transactions protect a failed migration on PostgreSQL and SQLite, not on MySQL.