Webority.Database.Migrator 3.0.1

webority-db-migrator

Shared schema runner for every Webority database project, SQL Server or PostgreSQL. It is the single, journaled apply mechanism for incremental schema releases to local / staging / production. Wraps DbUp. This is the "apply" half of the Option-C standard; the DACPAC (.sqlproj) is the declarative schema-of-record. See ~/.claude/conventions/sql-database.md.

Providers

Both journals carry the same information, and both stamp the applied time in UTC rather than the applying machine's clock, in a column that records the offset (datetimeoffset(7) on SQL Server, timestamptz on PostgreSQL) so a reader can tell what a stored time means.

Breaking for an existing SQL Server journal. Applied was a plain datetime, which carries no offset, and the next run widens it in place. Existing values are converted at +00:00. That is correct for every row written since 1.2.1, and it labels older rows from developer machines as UTC when they were really IST. Their true zone is not recoverable from anything stored, so saying so here is the best that can be done.

The two journals do not share identifier casing, deliberately: SQL Server uses ScriptName/Applied/Checksum, PostgreSQL uses unquoted lower case scriptname/applied/checksum, following each platform's own convention and DbUp's own journal for that provider. An earlier attempt to force PascalCase onto PostgreSQL so the two would look identical produced a real bug: an unquoted identifier is folded to lower case there, so a quoted column and the base class's unquoted query are two different names, and it failed only on a second run against a populated table. Matching the platform beats matching the other provider.

There is no declarative half on PostgreSQL. On SQL Server the .sqlproj DACPAC is the schema-of-record and this tool is the apply half (the Option C standard in sql-database.md). PostgreSQL has no DACPAC equivalent in the fleet, so on a PostgreSQL project the journaled Scripts/ are the only mechanism, and the project needs its own drift check. That gap is deliberate and recorded, not an oversight.

What it does

Two artifact classes under a DB project, applied in order per run:

The journal lives in the target database, so manual, local, and CI runs share one source of truth per environment: nothing re-applies what another already ran, and a missed script is structurally impossible. SchemaVersions.Applied is stamped in UTC (since 1.2.1; DbUp's stock journal wrote the applying machine's local clock, so older rows from developer machines in India carry IST). Forward-only: a Rollback/ or Diagnostics/ subfolder and *_Rollback.sql files are never auto-applied.

Use

# Run pending scripts + refresh procs against a DB (connection via flag or MIGRATOR_CONNECTION):
webority-db-migrator --connection "<conn>" --db-root path/to/Xxx.Database

# The same against PostgreSQL:
webority-db-migrator --connection "<conn>" --db-root path/to/Xxx.Database --provider postgres

# Preview what would run, change nothing:
webority-db-migrator --connection "<conn>" --db-root path/to/Xxx.Database --what-if

# Baseline an EXISTING database (record current Scripts/ as applied WITHOUT executing them):
webority-db-migrator --connection "<conn>" --db-root path/to/Xxx.Database --mark-applied

--db-root defaults to the current directory. Exit codes: 0 success, 1 migration failure, 2 usage/config error.

--what-if runs the applied-script guard too, so a clean preview means the apply will not stop on an edited script. It used to list the pending scripts and return before the guard, which meant a preview could report all clear on a database where the apply then hard-failed. Reaching the guard costs a journal read, which the preview already pays for its pending list, and it still writes nothing: that read creates no table, adds no column and takes no lock. On a journal with no checksum column yet there is nothing stored to compare, so the preview reports the pending list and passes.

--timeout <seconds> sets the per-script command timeout (default 600). DbUp's SqlClient default is 30s, which online index rebuilds and big-table ALTERs blow past. Raise it for a heavy migration: … --timeout 1800.

--tx-per-script wraps each script in its own transaction, so a script failing midway rolls back cleanly instead of leaving partial changes. Off by default because some DDL (CREATE FULLTEXT INDEX, ALTER DATABASE, …) cannot run inside a transaction; idempotent guards are the baseline safety either way. Use it for releases whose scripts are all transaction-safe.

Two runs at once

The apply takes an exclusive, session-scoped lock on the target database for its duration (sp_getapplock on SQL Server, pg_advisory_lock on PostgreSQL). A second run started in the same window waits, bounded by --timeout, then fails with a plain message rather than applying alongside the first. --what-if and --mark-applied do not take it: the preview writes nothing.

SchemaVersions.ScriptName is also unique. An existing journal gains the constraint on the next run. If it already holds duplicate rows (which the old build could produce), the run stops and names them; delete the surplus rows, keeping the earliest Applied for each, and run again.

Note that on SQL Server the journal's collation is usually case-insensitive, so Foo.sql and FOO.sql collide under that constraint even though the runner treats them as two different scripts. That is deliberate: it is the rename-with-different-case case failing loudly instead of silently landing a duplicate.

The applied-script guard

Every run-once script's content is hashed into SchemaVersions.Checksum when it is applied. On a later run, a script whose content no longer matches its stored hash fails the run (exit 1), naming the script and both hashes. That is deliberate: an applied script is immutable by convention, and editing one is otherwise a silent no-op on every database that already ran it.

The hash is taken over LF-normalised text with a trailing newline trimmed, so the same script checked out CRLF on Windows and LF on Linux agrees, and an editor adding or dropping a final newline does not trip it.

A row applied before the checksum column existed stores nothing, and the next run stamps it from the file on disk. There is no earlier hash to compare against, so that run accepts what is there; the next edit of that script is caught.

Stored procedures are never hashed or checked. They are re-applied every run, so an edit to one is how it is meant to change. A journal baselined by 1.0.x or 1.1.x --mark-applied may still hold rows named after files in StoredProcedures/ (those versions journaled procedures; 1.2.0 stopped). Those rows are ignored: never stamped, never verified. They can be left in place. 3.0.0 did stamp and verify them, so on 3.0.0 the first edit to such a procedure failed the run.

If the change is intended, review it and accept it by journal name:

webority-db-migrator --connection "<conn>" --db-root ./Xxx.Database \
  --accept-checksum 20260101_0900_AddColumn.sql

Repeat the flag per script. It rewrites the stored hash for exactly those scripts from the files on disk, printing the old and new values, then continues the run. The name is the journal name, so a script in a subfolder is Sub.Nested.sql, not Sub/Nested.sql. A name that is not on disk, or not in the journal, stops the run (exit 2) rather than doing nothing.

There is deliberately no switch that turns the guard off everywhere: the point is to record one reviewed decision, not to disable the control.

--mark-applied is guarded: it prints exactly which scripts it will record, and refuses to run against a database whose journal already has entries (that's not a first baseline, and pending scripts would be marked applied without ever running). Override with --force only when that is genuinely intended.

The command line refuses four things rather than quietly accepting them, each of which used to run and do the wrong thing:

All four exit 2, the documented usage code.

Local (today)

dotnet run --project ../libraries/webority-db-migrator/webority-db-migrator -- \
  --connection "Server=...;Database=...;..." --db-root ./Xxx.Database

Release state

3.0.0 is the current release. It is on nuget.org and is the version to install.

Do not use 1.3.0 or 1.4.0. Both are still listed on nuget.org and both are broken:

Unlisting those two on nuget.org is a release action, not a code change.

How it is tested

Two suites, run locally before pushing (CLAUDE.md has the commands). publish.yml is the fleet's thin shared caller and runs no build or test step itself; nothing in CI checks the code before it is packed and pushed.

webority-db-migrator.Tests needs no database. The journal tests assert the SQL each journal sends through a recording stand-in, and the exit-code tests run the built tool as a real process. Between them they cover checksum stability across line endings, the applied-script guard's branches, the journals bringing the table up to date before reading it, the uniqueness rule, the run lock's arguments, ordinal apply order and the documented exit codes.

webority-db-migrator.LiveTests runs against a real SQL Server, for the five behaviours that emitted SQL cannot prove: that sp_getapplock really makes a second runner wait and really lets go afterwards, including after a failed run; that the Applied column widens from datetime to datetimeoffset(7) on a journal that already holds rows, without moving a single instant; that a journal holding two rows for one script stops the run and names the script; that an edited applied script hard-fails against a real journal and --accept-checksum records the new hash without re-running the script; and that --what-if reports the pending list and the mismatch while changing nothing at all, on a journal that predates this tool.

It finds a database in this order, and never invents one:

A connection string that is set but unreachable fails rather than skipping, because someone asked for that server. An Azure SQL host is refused outright: every test creates and drops a database of its own, so this must never be pointed at a shared or production instance. The skip is a real Skipped result with its reason in the run summary, never a silent pass.

Manual verification against real databases, from before the live lane existed:

PostgreSQL, 18-Sep-2026, against a throwaway PostgreSQL 17 cluster: seven checks plus two extras, all clean, including the journal's identifiers being unquoted lower case. That casing was itself a bug found here: an earlier version quoted them as PascalCase so both providers' journals would "read as one table", which passed every first-run test and failed on the second run against a populated table, because PostgreSQL folds an unquoted identifier to lower case.

SQL Server, 19-Sep-2026, against a real SQL Server 2025 instance. Six checks:

The test database was created for the purpose and dropped afterwards. That matrix is what missed the 1.4.0 defect: every row starts from a journal 1.4.0 itself created, so none of them exercised an existing 1.2.x journal. The live lane above covers that case now, and both rows this matrix left open, so it is kept as a record rather than as a checklist anyone has to run by hand.

As a dotnet tool

dotnet tool install --global Webority.Database.Migrator   # public, from nuget.org
webority-db-migrator --connection "<conn>" --db-root ./Xxx.Database

Script conventions

• One change per file, sortable timestamp prefix: YYYYMMDD_HHMM_Description.sql (UTC). Lexicographic order = apply order.
• Subfolders under Scripts/ are fine: DbUp journals a script by its relative path with separators normalized to . on every OS (Migrations/X.sql → Migrations.X.sql). Note apply order is lexicographic on that full journal name, so ordering across sibling subfolders is folder-first, not timestamp-first. Keep chronologically-coupled scripts in ONE folder.
• Every script idempotent + guarded (IF NOT EXISTS / OBJECT_ID / COL_LENGTH) so a re-run is safe.
• An applied script is immutable, and so is its location. The journal matches by name only: editing an already-applied script is a silent no-op on every DB that ran it, and MOVING one to another folder changes its journal name so it re-applies on the next run (survivable only because scripts are idempotent). To change something, write a NEW script; don't reorganize applied ones.
• Destructive steps ship a matching *_Rollback.sql (never auto-run).
• Procs are CREATE OR ALTER and excluded from the DACPAC model (this runner owns them). Subfolders under StoredProcedures/ are fine (run-always, order-independent).

Adopting in a new repo

1. Add Scripts/ (and keep StoredProcedures/) under the Xxx.Database project.
2. Build Remove both from the .sqlproj model (procs) / already-excluded (Scripts/).
3. Baseline the existing prod/staging DB once with --mark-applied so history isn't re-run.
4. From then on, every schema change = a Scripts/ migration, applied by this tool.