Shipping a robust DB migration runner for Chaufr (OCaml + Caqti + Lwt)
Deterministic, auditable database evolution for an OCaml backend using Caqti + Lwt — with checksums, rollback, safe SQL parsing, and a focused CLI.
Today I shipped a production-minded database migration runner for Chaufr. The goal: a deterministic, auditable, and safe way to apply schema changes that integrates with the existing Caqti + Lwt connection infrastructure.
Summary
- Migration framework + runner integrated with the shared connection pool.
-
Lightweight CLI (
bin/migrate) + Makefile targets for local + CI workflows. - Migration tracking (table + timestamps), checksum validation, rollback support.
- Safe SQL executor that respects dollar-quoted blocks and string literals.
Components Implemented
1. migrations.ml
- Typed migration record, error variants, status helpers.
-
Ensures
schema_migrations(indexed) exists for audit + observability. -
Records execution time +
applied_at; deletes records on rollback. - Computes a checksum from up/down SQL to detect drift.
- Splits SQL intelligently (avoids breaking PL/pgSQL bodies / dollar quoting).
-
Reuses
Connection.with_connectionfor pooled Caqti execution + error mapping.
2. initial_migration.ml
-
Loads
sql/init.sql(+ optionalsql/init_down.sql). - Exposes a first-class migration representing the baseline schema.
3. migrate.ml (CLI)
-
Commands:
init,status,up,down,reset,version. -
Guarantees
Database.Connection.init()before operating. - Operator-focused messaging + readable error translation.
4. Tooling additions
-
duneexecutable stanza forbin/migrate. -
Make targets:
migrate-init,migrate-status,migrate-up,migrate-down,migrate-reset,migrate-version.
Design Rationale
| Goal | Implementation Choice |
|---|---|
| Auditability | Checksums + timestamps + persisted records |
| Safety | SQL splitter preserves literal + dollar-quoted regions |
| Operational parity | Uses same pool / masking / logging as runtime |
| Determinism | Explicit up/down SQL + versioned records |
Quick Usage
| Action | Command |
|---|---|
| Initialize system | make migrate-init |
| Show status | make migrate-status |
| Apply pending | make migrate-up |
| Roll back last | make migrate-down |
| Roll back all | make migrate-reset |
| Current version | make migrate-version |
Example
# Ensure schema_migrations table exists
dune exec -- ./bin/migrate.exe init
Migration system initialized successfully
Internal Behavior Highlights
- Checksums catch accidental edits to already-applied migrations.
- Execution time + ordering support forensic analysis.
- Drift detection encourages immutable migration history.
- Safe splitting prevents subtle corruption of function bodies.
Next Steps
- Tests: SQL splitter edge cases, record + remove behaviors, checksum mismatch flows.
- Rollback guidance: document destructive vs reversible paths.
- Telemetry: integrate OpenTelemetry spans + structured logs.
-
Ergonomics: auto-discover versioned filenames (e.g.
20250914_001_add_users.sql). - Dry-run / plan mode.
Why It Matters (OCaml Ecosystem)
- Demonstrates a pragmatic, typed migration pipeline integrated with Caqti + Lwt.
- Encourages explicit Result-based flow over exception-driven control.
- Provides a replicable template for early-stage OCaml services.
Possible Follow-ups
- Publish the SQL splitting module as a standalone gist/example.
- Document recommended naming/versioning conventions.
- Add Alcotest integration running migrations against a transient test DB.