LowCodeHub.Migration.PostgreSql 0.0.12

LowCodeHub.Migration.PostgreSql

A startup migration runner for PostgreSQL using embedded .sql scripts and deterministic execution order. Scripts are ordered by Unix timestamp prefix, executed in individual transactions, and journaled by DbUp.

Why This Library?

Installation

dotnet add package LowCodeHub.Migration.PostgreSql

Quick Start

using LowCodeHub.Migration.PostgreSql.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddPostgreSqlMigrations(migrations =>
{
    migrations.AddTarget<UsersScriptScanner>(options =>
    {
        options.ConnectionString = builder.Configuration.GetConnectionString("UsersDb")!;
        options.Directories = ["MyApp.Users.Migrations"];
    });
});

var app = builder.Build();

await app.RunPostgreSqlMigrationAsync(app.Lifetime.ApplicationStopping);

app.Run();

The scanner type is still required when adding a target because it identifies the assembly that contains embedded SQL resources. You do not pass scanners when running migrations; registered targets are enough.

Multiple PostgreSQL Targets

Register one target per scanner/database pair:

builder.Services.AddPostgreSqlMigrations(migrations =>
{
    migrations.AddTarget<UsersScriptScanner>(options =>
    {
        options.ConnectionString = builder.Configuration.GetConnectionString("UsersDb")!;
        options.Directories = ["MyApp.Users.Migrations"];
        options.AlwaysExecuteDirectories = ["MyApp.Users.Always"];
    });

    migrations.AddTarget<BillingScriptScanner>(options =>
    {
        options.ConnectionString = builder.Configuration.GetConnectionString("BillingDb")!;
        options.Directories = ["MyApp.Billing.Migrations"];
    });
});

await app.RunPostgreSqlMigrationAsync();

Targets run in registration order. Scripts are globally ordered only inside a single target. If two targets point at the same database, each target still uses its own scanner assembly and options, and the runner executes them sequentially.

Target-Based API

This package uses the target-based API only:

builder.Services.AddPostgreSqlMigrations(migrations =>
{
    migrations.AddTarget<MyScriptScanner>(options =>
    {
        options.ConnectionString = "Host=localhost;Database=MyDb;Username=postgres;Password=yourpassword";
        options.Directories = ["MyApp.Migrations.Scripts"];
    });
});

await app.RunPostgreSqlMigrationAsync();

Script Naming Convention

Scripts must start with a numeric Unix timestamp prefix:

Ordering rules inside each target:

• Primary sort: numeric prefix ascending
• Secondary sort: full script resource name
• Directory name: used only for filtering

Target Options

Each target has its own MigrationOptions.

Journaled vs Always-Execute Scripts

Always-execute scripts must be idempotent.

Execution Behavior

Migrations execute synchronously on the calling thread because DbUp has no async API. The Async method returns a completed task and exists for startup-code ergonomics.

For each target:

1. Ensure the target database exists, unless disabled.
2. Scan the target scanner assembly for embedded .sql resources.
3. Split scripts into journaled and always-execute groups.
4. Order each group by Unix timestamp prefix.
5. Acquire a PostgreSQL advisory lock.
6. Execute each script in its own transaction.

If one target fails, migration stops and later targets are not executed.

Cancellation

Pass a CancellationToken to cancel between script executions:

await app.RunPostgreSqlMigrationAsync(app.Lifetime.ApplicationStopping);

A running script completes or rolls back before cancellation takes effect.

Best Practices

1. Keep script names immutable once merged; renaming breaks the journal.
2. Use Unix seconds prefixes for predictable ordering.
3. Keep one concern per script.
4. Use AlwaysExecuteDirectories only for idempotent scripts.
5. Keep FailOnNoScriptsFound = true in production.
6. Embed SQL files as resources:

<EmbeddedResource Include="Scripts\**\*.sql" />

Requirements

• .NET 10 or later
• dbup-postgresql 7.2+ (included as a dependency)
• Npgsql 7.0+ (included as a dependency)

License

MIT (c) Ahmed Abuelnour