database migrations#

embedded migration system supporting sqlite and postgresql.

overview#

migrations are applied automatically on server startup. the system:

• tracks applied migrations in _migrations table
• embeds SQL files in the binary via @embedFile
• supports dialect-specific SQL (sqlite vs postgres)
• is idempotent (safe to run repeatedly)

structure#

src/db/
├── migrations/
│   ├── 001_initial/
│   │   ├── sqlite.sql       # sqlite DDL
│   │   └── postgres.sql     # postgres DDL
│   └── 002_add_feature/     # future migrations
│       ├── sqlite.sql
│       └── postgres.sql
├── migrations_data.zig      # migration registry
└── migrate.zig              # migration runner

adding a new migration#

1. create directory: src/db/migrations/NNN_description/
2. add sqlite.sql and postgres.sql with your DDL
3. register in migrations_data.zig:

pub const all = [_]Migration{
    .{
        .id = "001_initial",
        .sqlite_sql = @embedFile("migrations/001_initial/sqlite.sql"),
        .postgres_sql = @embedFile("migrations/001_initial/postgres.sql"),
    },
    .{
        .id = "002_add_feature",  // <-- add new entry
        .sqlite_sql = @embedFile("migrations/002_add_feature/sqlite.sql"),
        .postgres_sql = @embedFile("migrations/002_add_feature/postgres.sql"),
    },
};

4. rebuild: zig build

migration sql guidelines#

• use CREATE TABLE IF NOT EXISTS for new tables
• use CREATE INDEX IF NOT EXISTS for indexes
• for schema changes, write dialect-specific SQL:
  • sqlite: may need table recreation for some ALTER operations
  • postgres: supports most ALTER operations directly

example migration adding a column:

sqlite.sql:

-- sqlite doesn't support ADD COLUMN IF NOT EXISTS, so we use a pragma check
ALTER TABLE flow_run ADD COLUMN new_field TEXT DEFAULT '';

postgres.sql:

ALTER TABLE flow_run ADD COLUMN IF NOT EXISTS new_field TEXT DEFAULT '';

tracking table#

CREATE TABLE IF NOT EXISTS _migrations (
    id TEXT PRIMARY KEY,        -- "001_initial"
    applied_at TEXT NOT NULL    -- ISO 8601 timestamp
);

runtime behavior#

on startup:

1. creates _migrations table if not exists
2. queries applied migrations
3. applies pending migrations in order
4. records each applied migration

05:39:28.243 | INFO | migrations - applying: 001_initial
05:39:28.262 | INFO | migrations - applied 001_initial (44 statements)

on subsequent runs:

05:39:42.218 | DEBUG | migrations - skipping already applied: 001_initial

design decisions#

• manual registration: migrations must be explicitly added to migrations_data.zig
• no rollbacks: forward-only migrations (simplicity over flexibility)
• no transactions for DDL: sqlite auto-commits DDL; statements run sequentially
• statement splitting: SQL files can contain multiple statements separated by ;

future work#

• --migrate-only CLI flag for CI/CD
• migration generation tooling
• rollback support (if needed)

reference#

• python-reference/ - how prefect python handles migrations
• old schema files kept in src/db/schema/ for reference