LowCodeHub.Migration.SqlServer 0.0.1

LowCodeHub.Migration.SqlServer

LowCodeHub.Migration.SqlServer provides a simple startup migration runner for SQL Server using embedded .sql scripts and deterministic execution order.

What this package does

• Runs SQL scripts from embedded resources.
• Orders scripts by Unix timestamp prefix in file name.
• Supports normal (journaled) scripts and always-execute scripts.
• Fails fast on invalid script naming or missing scripts (by default).

Install

dotnet add package LowCodeHub.Migration.SqlServer

Quick Start

using LowCodeHub.Migration.SqlServer.Extensions;

var builder = WebApplication.CreateBuilder(args);

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

var app = builder.Build();

await app.RunDatabaseMigrationAsync<Program>(); // optional token overload exists

app.Run();

Configuration

{
  "Migration": {
    "ConnectionString": "Server=.;Database=MyDb;Trusted_Connection=True;Encrypt=False",
    "Directories": [ "MyApp.Migrations.Scripts" ],
    "AlwaysExecuteDirectories": [ "MyApp.Migrations.Always" ],
    "FailOnNoScriptsFound": true
  }
}

Script Naming Rules (Strict)

Scripts must start with a Unix-time numeric prefix:

• Valid: 1766733156_create_users.sql
• Valid: 1766733160_add_index.sql
• Invalid: create_users.sql (no prefix)
• Invalid: v1_create_users.sql (prefix not numeric)

If naming is invalid, migration throws with a clear format error.

Ordering

• Ordering is based on the numeric prefix in the script file name.
• Folder name is used only for filtering (which scripts to include), not for ordering.
• If two scripts share the same numeric prefix, secondary ordering is by script name.

Journal vs Always Execute

• Scripts in Directories are journaled (run once; tracked by DbUp journal).
• Scripts in AlwaysExecuteDirectories run every startup (not journaled).

This is useful for:

• permissions grants
• view/procedure refresh scripts
• maintenance scripts that should always re-apply

Failure and Transactions

• Scripts execute one by one.
• Each script runs in its own transaction.
• If a script fails:
  • that script is rolled back
  • migration stops immediately
  • next scripts are not executed

Cancellation

You can pass an optional cancellation token:

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

Cancellation is checked between script executions.

Best Practices

1. Keep script names immutable once merged.
2. Use Unix seconds prefix for predictable order.
3. Keep one concern per script (small and reversible where possible).
4. Use AlwaysExecuteDirectories only for idempotent scripts.
5. Keep FailOnNoScriptsFound = true in production.