SqlFx 1.0.1-preview

SqlFx

Generate reviewable SQL Server migrations from database schema changes and apply them from C#.

SqlFx is designed for database-first teams. Instead of manually recreating database changes in migration files, developers change the development database and let DacFx generate the corresponding SQL.

flowchart TD
    A["🗄️ Development database"]
    B["🔍 SqlFx detects schema changes"]
    C["⚙️ SqlFx generates and validates the migration"]
    D["👨‍💻 Developer reviews and commits the SQL"]
    E["✅ SqlFx applies and records the migration at production database"]

    A --> B --> C --> D --> E

This is specifically designed for enterprise/legacy scenarios where a code-first approach like EF Core is not possible. In other words, this is a db first tool.

SqlFx uses DacFx for schema extraction, comparison, dependency handling, and deployment script generation. It manages migration files, schema snapshots, project integration, execution, and migration history.

Installation

Install the CLI:

dotnet tool install SqlFx.Cli --version 1.0.1-preview --global

Create a dedicated migrations project:

sqlfx init --project src --assembly-name MyApp.Migrations
dotnet add src/MyApp.Migrations/MyApp.Migrations.csproj package SqlFx --version 1.0.1-preview

Add the runtime package and reference the migrations project from the application:

dotnet add src/MyApp/MyApp.csproj package SqlFx.SqlServer --version 1.0.1-preview
dotnet add src/MyApp/MyApp.csproj reference src/MyApp.Migrations/MyApp.Migrations.csproj

SqlFx migrations always live in this separate project. Add a marker type such as MigrationAssembly to it so the application can select the migration assembly without depending on the name of a generated migration.

namespace MyApp.Migrations;

public sealed class MigrationAssembly
{
}

Generate a migration

First, update the development database and configure its connection string:

export DEV_DB="Server=localhost;Database=MyApp_Development;User ID=sa;Password=...;TrustServerCertificate=True"

Then generate the migration:

sqlfx migration add create-payments \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$DEV_DB"

SqlFx compares the database with the previous snapshot and creates:

src/MyApp.Migrations/
├── Migrations/
│   ├── 202607211430_create-payments.cs
│   └── SqlFxSnapshot.dacpac
└── MyApp.Migrations.csproj

Review and commit the SQL migration and snapshot together.

Because the development database already contains the schema change, SqlFx records a generated migration with SQL as applied there by default. If no SQL is generated, the journal is not changed. Use --no-mark-applied only when the source database must remain unjournaled.

Apply migrations from C#

using MyApp.Migrations;
using SqlFx.SqlServer;

await SqlServerMigrator
    .Create(connectionString)
    .FromAssembly(typeof(MigrationAssembly).Assembly)
    .MigrateAsync();

SqlFx discovers compiled migrations, applies pending scripts in order, and records successful executions in the migration journal.

In cloud/multi-instance scenarios, prefer running migrations from a dedicated deployment process rather than from every application instance.

Commands

Add a migration

sqlfx migration add create-payments \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$DEV_DB"

The generated migration is marked as applied on $DEV_DB. This prevents an application connected to the same development database from trying to execute schema SQL that the database already contains. To opt out:

sqlfx migration add create-payments \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$DEV_DB" \
  --no-mark-applied

List migrations

sqlfx migration list \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj

Include a target connection to display applied and pending migrations:

sqlfx migration list \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$TARGET_DB"

Import an existing database

Use snapshot import when adopting SqlFx in a project with an existing schema:

sqlfx snapshot import \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$DEV_DB" \
  --source development

This creates the initial snapshot without generating a migration for the existing schema.

Preview pending migrations

sqlfx database update \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$TARGET_DB" \
  --dry-run

Apply pending migrations

sqlfx database update \
  --project src/MyApp.Migrations/MyApp.Migrations.csproj \
  --connection-string "$TARGET_DB"

Project discovery

--project always refers to the migrations project.

cd src/MyApp.Migrations
sqlfx migration list

Otherwise, specify the project explicitly.

Journal configuration

using MyApp.Migrations;
using SqlFx.SqlServer;

await SqlServerMigrator
    .Create(connectionString, options =>
    {
        options.Journal.Schema = "dbo";
        options.Journal.Table = "__SqlFxMigrations";
    })
    .FromAssembly(typeof(MigrationAssembly).Assembly)
    .MigrateAsync(cancellationToken);

Applied migrations are recorded and are not executed again.

Recommended workflow

1. Update the development database.
2. Generate a migration. SqlFx records it as applied on that development database.
3. Review the SQL.
4. Add explicit data movement when required.
5. Test the complete migration, including data movement, against a disposable database.
6. Commit the migration and snapshot together.
7. Apply pending migrations during deployment.

Generated migrations must still be reviewed. Schema comparison can detect structural changes, but it cannot always infer business intent.

Snapshots

Migrations/SqlFxSnapshot.dacpac represents the expected schema after the latest migration.

Previous snapshot + generated migration = current development schema

The migration and snapshot must remain synchronized and should always be committed together.