LowCodeHub.Migration.PostgreSql 0.0.3

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
• Configuration
• Journaled vs Always-Execute Scripts
• Execution Behavior
• Cancellation
• How It Works
• Best Practices
• Requirements
• License

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

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<TScriptScanner>()      │
│  1. Scan assembly for embedded .sql resources           │
│  2. Filter by Directories / AlwaysExecuteDirectories    │
│  3. Validate script naming (fail-fast on invalid)       │
│  4. Order by Unix timestamp prefix                      │
└─────────────────────┬───────────────────────────────────┘
											│
											▼
┌─────────────────────────────────────────────────────────┐
│  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 which assembly to scan for embedded resources.
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