MigLib.Codegen 12.1.0

Migrate is a SQLite-first toolkit: a schema directory is the desired catalog, _migration.sql is the hop from the previous catalog, and mig codegen emits typed F# query modules plus Migration.migrate.

What it does

1. You author snapshot *.sql files under schema/ (nested dirs allowed) and optional schema/_migration.sql.
2. You annotate tables/views with -- mig: comments for the ops you want.
3. mig codegen applies the snapshot to a temp DB, introspects, and emits one .fs module per annotated relation plus Migration.fs (let migrate dbPath).
4. At runtime you call Migration.migrate dbPath and use dbTxn with generated helpers.

SQL travels inside the binary as compiled string data. Empty databases get the snapshot; existing databases get the hop and must then match the snapshot catalog.

See specs/schema_dir_and_migration.md.

Annotation example

-- mig:rel User
-- mig:ops insert, select_by(email), select_by_id, count
-- mig:ops upsert
-- mig:bool active
-- mig:datetime created_at
CREATE TABLE app_user (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  email TEXT NOT NULL,
  active INTEGER NOT NULL,
  created_at TEXT NOT NULL
) STRICT;

-- mig:ops select_all, select_one_by(email), count
CREATE VIEW active_user AS
  SELECT id, email, created_at FROM app_user WHERE active = 1;

-- Optional AND filters (filter catalog):
-- mig:ops filter_search(created_at desc, id), filter_count
-- mig:filter status eq status
-- mig:filter label_prefix like_prefix label
-- mig:filter text_any eq_any label, notes
CREATE TABLE item (
  id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  status TEXT NOT NULL,
  label TEXT NOT NULL,
  notes TEXT NOT NULL,
  created_at TEXT NOT NULL
) STRICT;

Rules:

• Unannotated relations generate no F#.
• Ops are explicit; no ops means no type.
• Multiple -- mig:ops lines on one relation are merged in order (handy for long lists).
• Views allow read ops (select_*, count, filter_search, filter_count) plus optional delete_matching(table, key); other write ops fail at codegen.
• count emits unfiltered SELECT COUNT(*) FROM [relation] as let count : TxnStep<int64> (tables and views).
• Filter catalog (-- mig:filter name kind column[, column…] plus filter_search / filter_count):
  • Emits {Rel}Filter (each field T option), emptyFilter, public applyFilter (WHERE + params for hand SQL), search filter skip take, and/or countByFilter filter.
  • Kinds: eq, neq, gt, gte, lt, lte, like_prefix (binds v + "%"), eq_any (OR across 2+ columns, one bind), in (T list; empty list is 1=0).
  • Present Some values become AND clauses; all-None uses WHERE 1=1.
  • Requires at least one -- mig:filter with filter_search and/or filter_count (and vice versa). At most one of each filter op per relation.
• -- mig:rel Name is optional; otherwise the F# name is derived from the SQL identifier.

See specs/schema_dir_and_migration.md and specs/sql_first_rewrite.md.

Installation

dotnet tool install --global migtool

Library:

dotnet add package MigLib

Codegen

mig codegen \
  --migrations ./schema \
  --output ./Stores \
  --namespace MyApp.Db

This writes Stores/User.fs, Stores/ActiveUser.fs, etc., plus Stores/Migration.fs (module MyApp.Db.Migration with let migrate). Hand-written companions can live alongside generated files; only files marked // <auto-generated /> are deleted when a relation disappears (Migration.fs is always kept).

From build.fsx / F#:

open MigLib.Codegen

match generate "./schema" "./Stores" "MyApp.Db" with
| Ok r ->
    Console.WriteLine(
      "generated "
      + string r.relationCount
      + " relations in "
      + r.outputDir)
    r.generatedFiles |> List.iter (fun p -> Console.WriteLine("  " + p))
| Error e -> failwith e

Runtime

open System
open MigLib
open MyApp.Db

// apply compiled snapshot / hop (module MyApp.Db.Migration)
let! db = Migration.migrate dbPath

// use transactions + generated queries (module MyApp.Db.User)
let! user =
  db {
    match! User.selectOneByEmail email with
    | Some u -> return u
    | None ->
      let! id =
        User.insert
          { Email = email
            Active = true
            CreatedAt = DateTimeOffset.UtcNow }
      return! User.selectById id |> TxnStep.map Option.get
  }

Packages

Local build

dotnet fsi build.fsx -- --target build
dotnet fsi build.fsx -- --target install
cd src && dotnet test

Version

Major version 10 is a greenfield rewrite (SQL-first). Older MigSchema / attribute-based workflows are not supported.

Further reading

See documentation/interesting-links.md for SQLite concurrency, migrations, and related theory links.