Migrations (db/migrate → migration/ crate) #

TL;DR

• sea-orm-cli migrate generate <verb_noun>; write the migration and the entity together; cargo run -- db migrate.
• Committed migrations are append-only (CI enforces it): change the schema with a new migration; a repair migration's down restores the predecessor's exact schema.
• Ids and foreign keys are 64-bit: big_pk_auto, big_integer(_null), i64 in the entity. Never integer/pk_auto for an id or *_id (offrails-check [ids]).
• Every migration has a working down (prove rollback, then migrate, on both backends); every FK has an index and an explicit on_delete; NOT NULL by default.
• Stick to SQLite-safe schema changes (rebuild tables for what SQLite's ALTER can't do); data backfills go in src/tasks/.

Read the rest when writing or repairing a migration, or changing a framework table.

Migrations are Rust, written with sea-orm-migration's schema DSL, which emits correct DDL for SQLite and Postgres. They live in the migration/ workspace crate (the standard SeaORM layout) and are embedded in the app binary, so deploying the binary deploys the migrations.

Commands #

Template #

Verified with sea-orm-migration 2.0.4 on SQLite and Postgres 18. The generated file names the migration with impl MigrationName for Migration { fn name(&self) -> &str { "m…" } }; #[derive(DeriveMigrationName)] is equivalent. Keep whichever the CLI wrote.

use sea_orm_migration::{prelude::*, schema::*};

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]          // `async_trait` comes from sea_orm_migration's prelude; don't add the crate
impl MigrationTrait for Migration {
    async fn up(&self, m: &SchemaManager) -> Result<(), DbErr> {
        m.create_table(
            Table::create()
                .table(Posts::Table)
                .if_not_exists()
                .col(big_pk_auto(Posts::Id))
                .col(big_integer(Posts::AuthorId))
                .col(string(Posts::Title))
                .col(text(Posts::Body))
                .col(string(Posts::Status).default("draft")
                    .check(Expr::col(Posts::Status).is_in(["draft", "published"])))
                .col(timestamp_with_time_zone_null(Posts::PublishedAt))
                .col(timestamp_with_time_zone(Posts::CreatedAt))
                .col(timestamp_with_time_zone(Posts::UpdatedAt))
                .foreign_key(
                    ForeignKey::create()
                        .from(Posts::Table, Posts::AuthorId)
                        .to(Users::Table, Users::Id)
                        .on_delete(ForeignKeyAction::Cascade),
                )
                .to_owned(),
        ).await?;
        m.create_index(
            Index::create().name("posts_author_id_idx").table(Posts::Table).col(Posts::AuthorId).to_owned(),
        ).await
    }

    async fn down(&self, m: &SchemaManager) -> Result<(), DbErr> {
        m.drop_table(Table::drop().table(Posts::Table).to_owned()).await
    }
}

#[derive(DeriveIden)]
enum Posts { Table, Id, AuthorId, Title, Body, Status, PublishedAt, CreatedAt, UpdatedAt }

#[derive(DeriveIden)]
enum Users { Table, Id }

Helper names come from sea_orm_migration::schema:

• big_pk_auto, string, text, big_integer, integer, small_integer, boolean, json, uuid, date, timestamp_with_time_zone, …;
• *_null for nullable and *_uniq for unique. Helpers are NOT NULL unless suffixed _null.

Chain modifiers onto them: string(X).default("draft").check(Expr::col(X).is_in([...])), json(X).default("{}"), string(X).primary_key() for natural keys.

Primary keys: big_pk_auto(T::Id). Verified DDL:

Entities use i64 for it.

Ids are 64-bit #

Every id is big_pk_auto, and every *_id is big_integer / big_integer_null, with i64 (or Option<i64>) in the entity. Never integer, small_integer, pk_auto or .integer() for an id. SQLite's INTEGER is 64-bit whatever you write, so a SQLite-only suite passes; on Postgres the column is INT4, and every insert or query that returns an id fails to decode into i64: a 500 on every write. A peer's Kanban shipped exactly that, "green", because its tests never ran on Postgres. offrails-check fails the build on it ([ids]: a 32-bit id/*_id column in migration/src, or an i32 id field in src/), and on a tests/app/support.rs that never reads TEST_DATABASE_URL ([postgres]).

Repairing an applied migration (rule 1 below: never edit it): a new migration widen_ids alters each column to BIGINT on Postgres (SERIAL's sequence too) and does nothing on SQLite, whose INTEGER already holds an i64. Its down restores INTEGER. Then add // offrails-check: ids widened in m<timestamp>_widen_ids to each repaired migration file (a comment, so the file's behavior doesn't change); the check then skips that file and fails if the named migration doesn't exist.

// migration/src/m<timestamp>_widen_ids.rs: one (table, column) per 32-bit id; foreign keys first, then ids.
const COLUMNS: &[(&str, &str)] = &[("cards", "column_id"), ("cards", "board_id"), ("cards", "id"), ("boards", "id")];

async fn up(&self, m: &SchemaManager) -> Result<(), DbErr> {
    if m.get_database_backend() != DbBackend::Postgres {
        return Ok(()); // SQLite: INTEGER is already 64-bit
    }
    for (table, column) in COLUMNS {
        m.alter_table(Table::alter().table(Alias::new(*table))
            .modify_column(ColumnDef::new(Alias::new(*column)).big_integer()).to_owned()).await?;
    }
    // pk_auto made SERIAL columns: their sequences are INT4 too (portable SQL, Postgres branch only).
    for table in ["cards", "boards"] {
        m.get_connection().execute_unprepared(&format!("ALTER SEQUENCE IF EXISTS {table}_id_seq AS bigint")).await?;
    }
    Ok(())
}
// down: the same loops with .integer() and `AS integer`, also Postgres only.

modify_column changes only the type (sea-query 1.0.2 emits ALTER COLUMN … TYPE bigint and nothing else); NOT NULL, defaults and foreign keys stay. Verified on Postgres 18 (the SQL, on serial tables with a foreign key and rows): types and constraints kept, and the next id after setval(…, 3000000000) inserts. The down fails once any id passes 2,147,483,647, which is correct. Prove it on Postgres: db rollback --steps 1 && db migrate, then the whole suite with TEST_DATABASE_URL. Entities change to i64 in the same slice, and the compiler lists every caller.

Verified portable:

• json columns are json_text on SQLite and json on Postgres.
• timestamp_with_time_zone is text on SQLite and timestamptz on Postgres. Both round-trip DateTimeUtc.
• A unique index on a nullable column allows many NULLs on both backends.

Rules #

1. Committed migrations are append-only. Never edit a migration that is on the main branch, has been deployed, or was run against any database you don't throw away (including other agents' and the human's dev databases): those databases never re-run it, so the edit silently never applies and the code then queries columns that don't exist ("no such column"). Change the schema with a new migration (alter_table, per the table below). Only a migration that exists solely in your own uncommitted work may be edited. CI enforces it: git diff --name-only --diff-filter=M origin/main... -- 'migration/src/m*.rs' must print nothing.
2. Every migration has a working down. Run rollback then migrate before you're done.
   • Repairing an earlier migration (rule 1 forbids editing it): write a new migration whose up fixes the schema, and whose down restores the predecessor's exact schema, not "drop everything". Example: an early create_mailbox_messages was a stub, and a later fix_mailbox_schema drops and recreates the table with the right columns. Its down must recreate the stub table (same columns, same indexes), so the next down (the stub's own) still finds a table to drop. If both downs drop the same table, a full rollback aborts midway ("table does not exist"), which breaks the DoD's rollback gate.
   • Test it: db rollback --steps all, then db migrate, on both backends (CI does).
3. Write the migration and the entity in the same step. They must match.
4. Every FK gets an index and an explicit on_delete (Cascade for owned children, Restrict for references, SetNull for optional).
5. NOT NULL by default. A column is nullable only if the spec says the value can be absent.
6. Name indexes <table>_<cols>_idx, and unique ones <table>_<cols>_key.
7. Case-insensitive uniqueness (emails): store lowercased in Rust and put a plain unique index on the column. It's portable, unlike lower() expression indexes.
8. Data backfills go in src/tasks/, not in migrations, unless trivial.

SQLite-safe schema changes (portability) #

SQLite's ALTER TABLE is limited. To keep every migration portable:

Framework tables #

Generators create these. Never hand-roll alternatives: users, sessions, password_resets (auth), jobs, job_queues, job_nodes, recurring_executions (jobs), mailbox_messages (dev mailbox), cache_entries (cache), broadcasts (realtime), blobs (storage).