migtool 11.2.0

Migrate is a SQLite-first toolkit: ordered *.sql files are the dev-time schema source of truth, and mig codegen turns them into typed F# query modules plus a migrations module that ships with the executable.

What it does

1. You write ordered *.sql migrations (applied by file name) during development.
2. You annotate tables/views with -- mig: comments for the ops you want.
3. mig codegen (or MigLib.Codegen.generate) applies migrations to a temp DB, introspects schema, and emits one .fs module per annotated relation plus Migrations.fs (scripts as ordinary F# string constants) into an output directory.
4. At runtime you call MigLib.migrateScripts dbPath Migrations.scripts and use dbTxn with generated helpers on Microsoft.Data.Sqlite.

Migrations travel inside the binary as compiled string data — not as loose *.sql files next to the app, not as EmbeddedResource / content files, and not via reflection. That keeps Native AOT and trimming friendly.

There is no F#-first schema DSL, no SqlProvider, no DbUp dependency, and no automatic normalization.

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).
  • 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/sql_first_rewrite.md for the full design.

Installation

dotnet tool install --global migtool

Library:

dotnet add package MigLib

Codegen

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

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

From build.fsx / F#:

open MigLib.Codegen

match generate "./Migrations" "./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 scripts compiled into the app (module MyApp.Db.Migrations)
do!
  migrateScripts dbPath Migrations.scripts
  |> Task.map (function Ok () -> () | Error e -> failwith e)

// use transactions + generated queries (module MyApp.Db.User)
let! user =
  dbTxn dbPath {
    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.