KSDbMigrator 1.0.36

KSDbMigrator

Smart, safe, fully-automatic database migration package for .NET 8+
Zero manual SQL • Automatic backup • Export/restore on rollback • One-click rollback to any version
Works with PostgreSQL • SQL Server • MySQL • SQLite

Features

• 100% automatic migration on application startup (production safe)
• Automatic full database backup before every change
• Export data before rollback → restore without duplicates
• Rollback to any previous version with a single API call
• Optional built-in migration management API (/api/migrations)
• Works perfectly with Clean Architecture / multi-project solutions
• No manual SQL files required – generated automatically from EF Core migrations

Installation

dotnet add package KSDbMigrator

Step-by-step usage (Clean Architecture example)

1. Add the tracking entity (only once)

In your Infrastructure project, inside your DbContext.OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // your existing configuration ...

    modelBuilder.AddAppliedScript(); // ← only this line!
}

2. Register the migrator in Program.cs (WebApi project)

var builder = WebApplication.CreateBuilder(args);

// your existing services (DbContext, MediatR, etc.)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

var sqlScriptsDirectoryPath = Project.Infrastructure.AssemblyReference.SqlScriptsDirectory;
if (Directory.Exists(sqlScriptsDirectoryPath))
{
    builder.Services.AddKSDbMigrator<ProjectDbContext>(opt =>
    {
        opt.ApplyScriptsFolder    = Path.Combine(sqlScriptsDirectoryPath, "Apply");
        opt.RollbackScriptsFolder = Path.Combine(sqlScriptsDirectoryPath, "Rollback");
        opt.BackupsFolder         = Path.Combine(sqlScriptsDirectoryPath, "Backups");
        opt.ExportsFolder         = Path.Combine(sqlScriptsDirectoryPath, "Exports");
    
        opt.DatabaseType = DatabaseType.PostgreSQL;
        opt.PgDumpPath   = "pg_dump";

        opt.AutoApplyOnStartup = true;

        opt.EnableMigrationEndpoints = true;
        opt.MigrationRoute           = "api/db/migrations";
        opt.RequiredRole             = "";
    });
}

var app = builder.Build();

// This line activates auto-apply + optional API endpoints
if (Directory.Exists(sqlScriptsDirectoryPath))
{
    app.MapKSDbMigratorEndpoints<ProjectDbContext>();
}

app.Run();

3. Create the folder structure

Inside src/Project.Infrastructure create:

SQLScripts/
├── Apply/
├── Rollback/
├── Backups/      ← automatically filled
└── Exports/      ← automatically filled

Optional: Create the Initial migration manually

It could be helpful to create the first migration manually and then generate the script by using the following scripts

4. Automatically generate Apply & Rollback scripts (one-liner)

Create a tiny helper script (you only run this once per migration):

Linux / macOS (generate-migration.sh)

#!/bin/bash
set -e

if [ -z "$1" ]; then
  echo "استفاده: ./generate-migration.sh AddBaseEntities"
  exit 1
fi

NAME=$1
INFRA="src/Project.Infrastructure"
WEB="src/Project.WebApi"

echo "ساخت مایگریشن $NAME..."

dotnet ef migrations add $NAME --project $INFRA --startup-project $WEB

# شمارش فایل‌های موجود برای شماره‌گذاری
APPLY_COUNT=$(find "$INFRA/SQLScripts/Apply" -name "*.sql" 2>/dev/null | wc -l || echo 0)
NUM=$(printf "%03d" $((APPLY_COUNT + 1)))

APPLY_FILE="$INFRA/SQLScripts/Apply/${NUM}_${NAME}.sql"

echo "تولید اسکریپت Apply..."
dotnet ef migrations script --idempotent --output "$APPLY_FILE" --project $INFRA --startup-project $WEB

# حذف خطوط START TRANSACTION; و COMMIT; از اسکریپت Apply
sed -i '/^START TRANSACTION;$/d' "$APPLY_FILE"
sed -i '/^COMMIT;$/d' "$APPLY_FILE"

# اگر این اولین مایگریشن است (NUM = 001)، جدول applied_scripts رو به اول فایل اضافه کن
if [ "$NUM" = "001" ]; then
  echo "این اولین مایگریشن است — جدول applied_scripts به اول فایل اضافه می‌شود"

  CREATE_TABLE_SQL=$'
-- ساخت جدول applied_scripts (اگر وجود نداشته باشه)
CREATE TABLE IF NOT EXISTS applied_scripts (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "ScriptName" character varying(255) NOT NULL,
    "MigrationName" character varying(255) NOT NULL,
    "AppliedOn" timestamp with time zone NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT "PK_applied_scripts" PRIMARY KEY ("Id")
);

'

  # اضافه کردن به اول فایل
  { echo "$CREATE_TABLE_SQL"; cat "$APPLY_FILE"; } > temp.sql && mv temp.sql "$APPLY_FILE"
fi

echo "تولید اسکریپت Rollback..."
dotnet ef migrations script $NAME --idempotent --output "$INFRA/SQLScripts/Rollback/${NUM}_${NAME}_Rollback.sql" --project $INFRA --startup-project $WEB

echo "مایگریشن $NAME با موفقیت ساخته شد!"
echo "حالا برنامه رو اجرا کن → همه چیز خودکار اعمال میشه"

Windows PowerShell (generate-migration.ps1)

param(
    [Parameter(Mandatory=$true)]
    [string]$Name
)

$infra = "src/Project.Infrastructure"
$web = "src/Project.WebApi"

Write-Host "ساخت مایگریشن $Name..." -ForegroundColor Green

dotnet ef migrations add $Name --project $infra --startup-project $web

$applyPath = Join-Path $infra "SQLScripts/Apply"
$applyFiles = Get-ChildItem -Path $applyPath -Filter "*.sql" -File -ErrorAction SilentlyContinue
$applyCount = if ($applyFiles) { $applyFiles.Count } else { 0 }
$num = "{0:D3}" -f ($applyCount + 1)

$applyFile = "$infra/SQLScripts/Apply/${num}_${Name}.sql"

Write-Host "تولید اسکریپت Apply..." -ForegroundColor Green
dotnet ef migrations script --idempotent --output $applyFile --project $infra --startup-project $web

# حذف START TRANSACTION; و COMMIT; از اسکریپت Apply
$content = Get-Content $applyFile
$content = $content | Where-Object { $_ -notmatch '^START TRANSACTION;$|^COMMIT;$'}
Set-Content $applyFile -Value $content

# اگر این اولین مایگریشن است، جدول applied_scripts رو به اول فایل اضافه کن
if ($num -eq "001") {
    Write-Host "این اولین مایگریشن است — جدول applied_scripts به اول فایل اضافه می‌شود" -ForegroundColor Yellow

    $createTableSql = @"
-- ساخت جدول applied_scripts (اگر وجود نداشته باشه)
CREATE TABLE IF NOT EXISTS applied_scripts (
    "Id" integer GENERATED BY DEFAULT AS IDENTITY,
    "ScriptName" character varying(255) NOT NULL,
    "MigrationName" character varying(255) NOT NULL,
    "AppliedOn" timestamp with time zone NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    CONSTRAINT "PK_applied_scripts" PRIMARY KEY ("Id")
);

"@

    $originalContent = Get-Content $applyFile -Raw
    $newContent = $createTableSql + $originalContent
    Set-Content $applyFile -Value $newContent
}

Write-Host "تولید اسکریپت Rollback..." -ForegroundColor Green
dotnet ef migrations script $Name --idempotent --output "$infra/SQLScripts/Rollback/${num}_${Name}_Rollback.sql" --project $infra --startup-project $web

Write-Host "مایگریشن $Name با موفقیت ساخته شد!" -ForegroundColor Cyan
Write-Host "حالا برنامه رو اجرا کن → همه چیز خودکار اعمال میشه" -ForegroundColor Cyan

نحوه استفاده در ویندوز:

فایل رو با نام generate-migration.ps1 در ریشه پروژه‌ات ذخیره کن. در PowerShell یا Terminal (در VS Code) اجرا کن:

PowerShell.\generate-migration.ps1 AddBaseEntities

اگر خطای Execution Policy دادی:

PowerShellSet-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Run it:

./generate-migration.sh AddUsersTable

5. That's it!

Run your application:

dotnet run --project src/Project.WebApi

• In Production → all pending migrations are applied automatically + backup taken
• In Development → you still get normal EF Core behavior

Optional: Built-in migration API (when EnableMigrationEndpoints = true)

All endpoints are protected by the role you specified (Administrator by default).

License

MIT © 2025