LowCodeHub.Migration.PostgreSql 0.0.10

LowCodeHub.Migration.PostgreSql

A simple 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 — no EF Core migrations, no code-first overhead.

Why This Library?

Installation

dotnet add package LowCodeHub.Migration.PostgreSql

Quick Start

using LowCodeHub.Migration.PostgreSql.Extensions;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddMigration(builder.Configuration, sectionName: "Migration");

var app = builder.Build();

await app.RunDatabaseMigrationAsync<Program>();

app.Run();

{
	"Migration": {
		"ConnectionString": "Host=localhost;Database=MyDb;Username=postgres;Password=yourpassword",
		"Directories": ["MyApp.Migrations.Scripts"],
		"FailOnNoScriptsFound": true
	}
}

That's it. On startup, the library scans the assembly for embedded .sql resources in the specified directories, orders them by Unix timestamp prefix, and executes each one in its own transaction — skipping already-applied scripts.

Table of Contents

• Script Naming Convention
• Multiple Script Assemblies
• Configuration
• Journaled vs Always-Execute Scripts
• Execution Behavior
• Cancellation
• How It Works
• Best Practices
• Requirements
• License

Multiple Script Assemblies

For one script assembly, keep using the generic scanner overload:

await app.RunDatabaseMigrationAsync<Program>();

For scripts split across several assemblies, pass marker types or assemblies explicitly:

await app.RunDatabaseMigrationAsync(
    new[] { typeof(CoreMigrations), typeof(BillingMigrations) });

await app.RunDatabaseMigrationAsync(
    new[] { typeof(CoreMigrations).Assembly, typeof(BillingMigrations).Assembly },
    app.Lifetime.ApplicationStopping);

Scripts from all assemblies are merged and ordered by numeric prefix before execution.

Script Naming Convention

Scripts must start with a numeric Unix timestamp prefix:

If naming is invalid, the migration throws with a clear format error at startup — fail-fast, not silent corruption.

Ordering Rules

• Primary sort — numeric prefix (ascending)
• Secondary sort — script file name (for duplicate prefixes)
• Folder name — used only for filtering (which scripts to include), not for ordering

Configuration

appsettings.json

{
	"Migration": {
		"ConnectionString": "Host=localhost;Database=MyDb;Username=postgres;Password=yourpassword",
		"Directories": ["MyApp.Migrations.Scripts"],
		"AlwaysExecuteDirectories": ["MyApp.Migrations.Always"],
		"FailOnNoScriptsFound": true
	}
}

All Options

Code-Based Configuration

builder.Services.AddMigration(options =>
{
		options.ConnectionString = "Host=localhost;Database=MyDb;Username=postgres;Password=yourpassword";
		options.Directories = ["MyApp.Migrations.Scripts"];
		options.AlwaysExecuteDirectories = ["MyApp.Migrations.Always"];
		options.FailOnNoScriptsFound = true;
});

Journaled vs Always-Execute Scripts

Always-execute scripts are useful for:

• Permission grants
• View / stored procedure refresh scripts
• Maintenance scripts that must re-apply every deployment

Always-execute scripts must be idempotent — they run on every startup.

Execution Behavior

Migrations execute synchronously on the calling thread — DbUp has no async API. The Async methods return an already-completed task and exist for API symmetry with startup code.

Per-Script Transactions

Each script runs in its own transaction:

• Success — transaction is committed, script is journaled
• Failure — transaction is rolled back, migration stops immediately, subsequent scripts are not executed

Failure Behavior

Script 1: ✓ committed + journaled
Script 2: ✓ committed + journaled
Script 3: ✗ rolled back → migration stops
Script 4: ⊘ not executed
Script 5: ⊘ not executed

On next startup, Script 3 runs again (it was rolled back and not journaled).

Cancellation

Pass a CancellationToken to cancel between script executions:

await app.RunDatabaseMigrationAsync<Program>(app.Lifetime.ApplicationStopping);

Cancellation is checked between scripts — a running script completes (or rolls back on error) before cancellation takes effect.

How It Works

┌─────────────────────────────────────────────────────────┐
│  RunDatabaseMigrationAsync<Program>()                   │
└─────────────────────┬───────────────────────────────────┘
											│
											▼
┌─────────────────────────────────────────────────────────┐
│  IMigrationService.MigrateAsync(assemblies, ct)         │
│  1. Scan assembly for embedded .sql resources           │
│  2. Filter by Directories / AlwaysExecuteDirectories    │
│  3. Order by Unix timestamp prefix                      │
│  4. Acquire DB-level migration lock (pg_advisory_lock)  │
└─────────────────────┬───────────────────────────────────┘
											│
											▼
┌─────────────────────────────────────────────────────────┐
│  DbUp Engine                                            │
│  For each script:                                       │
│  ├── Check journal (skip if already applied)            │
│  ├── BEGIN TRANSACTION                                  │
│  ├── Execute SQL                                        │
│  ├── COMMIT + journal entry                             │
│  └── On error: ROLLBACK + stop                          │
└─────────────────────────────────────────────────────────┘

1. Assembly scanning — The <TScriptScanner> type parameter identifies one assembly; pass scanner types or assemblies to scan several.
2. Filtering — Only resources matching the configured directories are included.
3. Naming validation — Every script must have a valid numeric prefix. Invalid names cause immediate failure.
4. Execution — DbUp runs each script in its own transaction, journaling successful scripts.

Best Practices

1. Keep script names immutable once merged — renaming breaks the journal.
2. Use Unix seconds prefix for predictable, conflict-free ordering.
3. Keep one concern per script (small and reversible where possible).
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 © Ahmed Abuelnour