Schema Migrations

Weasel's migration system detects differences between your configured schema objects and the actual state of a live database, then generates and optionally applies the DDL needed to bring the database in line. The process is fully automated and works across all supported providers.

Migration Flow

The IDatabase Interface

IDatabase (in Weasel.Core.Migrations) is the central interface for managing a database's schema lifecycle:

public interface IDatabase_Sample
{
    AutoCreate AutoCreate { get; }
    Migrator Migrator { get; }
    string Identifier { get; }
    List<string> TenantIds { get; }

    IFeatureSchema[] BuildFeatureSchemas();
    string[] AllSchemaNames();
    IEnumerable<ISchemaObject> AllObjects();

    Task<SchemaMigration> CreateMigrationAsync(CancellationToken ct = default);
    Task<SchemaMigration> CreateMigrationAsync(IFeatureSchema group, CancellationToken ct = default);

    Task<SchemaPatchDifference> ApplyAllConfiguredChangesToDatabaseAsync(
        AutoCreate? @override = null,
        ReconnectionOptions? reconnectionOptions = null,
        CancellationToken ct = default);

    Task AssertDatabaseMatchesConfigurationAsync(CancellationToken ct = default);
    string ToDatabaseScript();
}

^{snippet source | anchor}

Key methods:

Method	Purpose
BuildFeatureSchemas()	Returns all feature schemas in dependency order.
CreateMigrationAsync()	Compares configured objects against the live database and returns a SchemaMigration.
ApplyAllConfiguredChangesToDatabaseAsync()	Detects changes and applies them, respecting the AutoCreate policy.
AssertDatabaseMatchesConfigurationAsync()	Throws if the database does not match configuration. Useful for production startup checks.
ToDatabaseScript()	Returns the full DDL creation script as a string.

IFeatureSchema

An IFeatureSchema groups related schema objects together (for example, all the tables and indexes for a document storage feature):

public interface IFeatureSchema_Sample
{
    ISchemaObject[] Objects { get; }
    string Identifier { get; }
    Migrator Migrator { get; }
    Type StorageType { get; }
}

^{snippet source | anchor}

Weasel processes features in the order returned by BuildFeatureSchemas(), so dependency relationships between features should be reflected by their position in the array.

SchemaMigration

The SchemaMigration class aggregates deltas from multiple schema objects into a single migration result:

var migration = await database.CreateMigrationAsync();

// Check the overall result
if (migration.Difference == SchemaPatchDifference.None)
{
    // Database is up to date
}

^{snippet source | anchor}

SchemaMigration exposes the collection of ISchemaObjectDelta instances and computes the aggregate Difference as the minimum (most severe) difference across all deltas.

AutoCreate Policy

The AutoCreate enum (from the JasperFx namespace) controls what schema changes Weasel is allowed to make at runtime:

Value	Behavior	Recommended Use
All	Creates, updates, and recreates objects as needed. May drop and rebuild tables that cannot be incrementally updated.	Development and testing.
CreateOrUpdate	Creates missing objects and applies incremental updates. Never drops existing objects.	Staging or early production deployments.
CreateOnly	Creates missing objects only. Will not modify existing objects.	Controlled deployments.
None	No runtime schema changes. Throws if the database does not match.	Production with CI/CD-managed migrations.

Set the policy on your database instance:

// In development -- let Weasel manage everything
database.AutoCreate = AutoCreate.All;

// In production -- fail fast if the schema is wrong
database.AutoCreate = AutoCreate.None;

^{snippet source | anchor}

You can also override the policy for a single call:

await database.ApplyAllConfiguredChangesToDatabaseAsync(
    @override: AutoCreate.CreateOrUpdate
);

^{snippet source | anchor}

The Migrator

Each database provider has a Migrator subclass that knows how to format SQL for that engine:

• PostgresqlMigrator -- wraps DDL in transactions, handles CREATE SCHEMA IF NOT EXISTS
• SqlServerMigrator -- uses GO batch separators, handles dbo schema conventions
• OracleMigrator -- Oracle-specific DDL formatting
• SqliteMigrator -- simplified DDL without schema creation SQL (SQLite schemas are fixed)

Privileges needed to apply a migration

A migration only needs the privilege to create what is actually missing. Both the PostgreSQL and SQL Server migrators check whether a schema exists before attempting to create it, so applying a delta into a schema that is already there does not require a database-level create privilege -- only the privileges the objects in the delta need.

This matters because a schema-level grant is the usual way to let an application manage its own tables while a separate migration role owns everything else. On PostgreSQL, GRANT USAGE, CREATE ON SCHEMA my_schema TO my_app is enough for that application to apply its own migrations, and it needs no CREATE on the database. Creating the schema in the first place does, so a role without it has to be given the schema up front.

The Migrator is used internally by WriteCreateStatement(), WriteDropStatement(), and WriteUpdate() on every schema object and delta.

Putting It Together

A typical migration workflow in application startup:

// 1. Configure your database with schema objects
var database = new MyPostgresqlDatabase(dataSource);

// 2. Apply all changes (respects AutoCreate policy)
var result = await database.ApplyAllConfiguredChangesToDatabaseAsync();

// result is SchemaPatchDifference.None if no changes were needed

^{snippet source | anchor}

For CI/CD pipelines, you can generate migration scripts without applying them:

// Generate a migration script file
await database.WriteMigrationFileAsync("migrations/next.sql");

// Or get the full creation script
var script = database.ToDatabaseScript();

^{snippet source | anchor}

Migration Logging

Implement IMigrationLogger to capture the SQL that Weasel generates:

public interface IMigrationLogger_Sample
{
    void SchemaChange(string sql);
    void OnFailure(DbCommand command, Exception ex);
}

^{snippet source | anchor}

The default logger writes SQL to the console and rethrows exceptions. It also accepts a TextWriter, which is the simplest way to capture one database's DDL without implementing the interface:

var buffer = new StringWriter();
database.MigrationLogger = new DefaultMigrationLogger(buffer);

Prefer this over a hand-rolled IMigrationLogger when all you want is redirection. Every provider checks logger is DefaultMigrationLogger to decide whether a failed migration statement is rethrown with its original stack trace or handed to OnFailure, so a custom type changes the stack trace you get on a failure, while the TextWriter overload does not.

Databases implementing IDatabaseWithMigrationLogger — which includes everything deriving from DatabaseBase<T> — expose MigrationLogger as a settable property, so tooling that applies many databases can give each one its own destination rather than having them all write to a shared console.

Schema Fingerprinting

For deployments with many databases and/or many replicas, repeated no-op applies (one per database, per process start, per rolling update) are measurably expensive: ApplyAllConfiguredChangesToDatabaseAsync introspects the catalog for every configured schema object even when nothing changed. Opt-in schema fingerprinting turns the no-op apply into a single SELECT:

migrator.UseSchemaFingerprinting = true;

With the flag enabled, a successful full apply stamps a SHA-256 fingerprint of the configured schema's expected DDL into {DefaultSchemaName}.weasel_schema_fingerprints. The next full apply recomputes the fingerprint in memory and, when that exact fingerprint is present, returns immediately — no global lock, no catalog introspection. Any configuration change (a new table, column, index, or managed partition) changes the fingerprint and re-enables the real apply, which then adds a new stamp.

Semantics to be aware of:

• A matching stamp is trusted. Schema drift applied outside Weasel (manual DDL, another tool) is not detected while the stamp matches — exactly like an application that skips migrations altogether. Use AssertDatabaseMatchesConfigurationAsync when you need verification; it is unaffected by the stamp. Deleting the stamp row (or table) forces the next apply to run in full.
• Only the full apply reads or writes the stamp. Feature-level applies (EnsureStorageExistsAsync) behave exactly as before.
• Concurrent appliers re-check the stamp after attaining the global migration lock, so replicas racing through a rolling update do the introspection work at most once per configuration.
• Rows are keyed by the fingerprint itself, not by any per-database identity, so several logical databases sharing one physical database each keep their own stamp instead of overwriting each other's. A configuration change therefore leaves the previous row in place rather than replacing it; the table is capped at the 25 most recent stamps. Eviction is harmless — a database whose stamp was pruned runs one full apply and stamps again.

WARNING

Before weasel#439 the stamp was a single row in weasel_schema_fingerprint (singular), which meant two logical databases on the same physical database silently overwrote each other's fingerprint and neither ever short-circuited — measurably slower than leaving the feature off. If you evaluated fingerprinting on a multi-store deployment and saw no benefit, that was why. The obsolete table is dropped automatically on the first stamp after upgrading.