SqlExec.Providers.SqlServer 0.1.1

sql-exec

Terraform-style database migrations as a .NET global tool. You write versioned SQL scripts, sql-exec works out what each environment is missing, shows you the plan, and applies it under a deployment lock.

Ships with SQL Server, MySQL/MariaDB, PostgreSQL and Oracle, and any other engine can be added from outside the repository by implementing one interface.

• Install
• Quick start
• How it works
• Command reference
• Project file
• Connection strings
• Writing migrations
• Scoping a migration to some environments
• Controlled deployments
• Engine notes
• Bookkeeping tables
• Using it in CI/CD
• Adding a database engine
• Troubleshooting
• Contributing

Install

dotnet tool install -g SqlExec.Tool

The package is SqlExec.Tool; the command it installs is sqlexec. It targets .NET 8 and rolls forward, so any machine with a .NET 8 or newer runtime can run it.

dotnet tool update -g SqlExec.Tool      # upgrade
dotnet tool uninstall -g SqlExec.Tool   # remove
sqlexec --version                       # check what you have

To pin the version per repository instead of installing globally:

dotnet new tool-manifest
dotnet tool install SqlExec.Tool
dotnet tool run sqlexec plan --env dev

Quick start

sqlexec init --provider postgresql

That writes sqlexec.json and a migrations/ folder with a dev and a protected prod environment. Point them at your databases (see Connection strings), then:

export SQLEXEC_DEV_CONNECTION="Host=localhost;Database=app;Username=app;Password=..."
sqlexec new create_customers_table      # writes an empty, correctly named script
# ...write your SQL in the file it created...
sqlexec plan --env dev                  # see what would run
sqlexec apply --env dev                 # run it
sqlexec status --env dev                # what ran, when, by whom

How it works

sql-exec keeps two tables in every target database: a history table recording each migration that ran, and a lock table that serialises deployments. Both are created on first use.

A run always follows the same shape:

1. Discover — read migrations/, parse each file name into a version, a name, and optional engine and tag qualifiers.
2. Select — drop the scripts that do not apply to this environment (wrong tag) or this engine, and, for each version, pick the engine-specific variant when there is one.
3. Plan — compare the selected scripts against the history: what is pending, what changed since it was applied, what ran out of order, what failed last time.
4. Apply — take the lock, re-plan inside it, then run each pending migration in version order and record the result.

Because the plan is a pure comparison of files against history, plan is safe to run any time and from anywhere: it opens a read-only connection and changes nothing.

Command reference

Every command accepts -p|--project <PATH> (defaults to the nearest sqlexec.json, searching upwards from the working directory) and -h|--help. Commands that talk to a database also accept -e|--env, --allow-out-of-order, --ignore-checksums and --to.

sqlexec init [DIRECTORY]

Creates sqlexec.json and the migrations directory.

sqlexec new <NAME>

Creates an empty migration whose name follows the convention, stamped with a UTC timestamp version.

sqlexec env list|add|remove

Reads and edits the environments block of sqlexec.json without opening the file by hand.

env list shows every environment: engine, where its connection string comes from and whether that resolves in this shell, whether it is protected, its tags, and how many migrations currently reach it.

env add <NAME> appends one. Everything that can be checked offline is checked before the file is written, and nothing is written when a check fails:

env remove <NAME> drops one, after a confirmation (-y|--yes skips it; it is required when the session is not interactive). The database is untouched — its history table still records everything that was applied there, so re-adding the environment picks that history back up. Removing the last environment is refused.

sqlexec env add staging --provider postgresql   --connection-env SQLEXEC_STAGING_CONNECTION --protected --tag seed

Two things to know about editing the file this way. The project file is rewritten from its parsed form, so JSON comments do not survive — the command warns when it drops some. And env add refuses to set both connectionString and connectionStringEnv, although the loader accepts both and lets the variable win.

sqlexec validate

Parses the project, resolves every environment's provider and connection string, and parses every migration file name. Touches no database, so it is the right check to run on a pull request.

sqlexec plan --env <name>

Shows what apply would do: applied count, pending migrations in execution order, and any drift.

sqlexec apply --env <name>

Applies pending migrations under the deployment lock.

sqlexec status --env <name>

The deployment history of an environment — version, name, when, by whom, how long, and whether it succeeded — followed by anything still pending.

sqlexec providers

Lists the engines this installation can deploy to, with their aliases, whether their DDL is transactional, and where each came from (built-in or a plugin path), plus the plugin directories that were scanned.

Shared options

Exit codes

Project file

{
  "version": 1,
  "migrationsPath": "migrations",
  "history": { "schema": null, "table": "sqlexec_history", "lockTable": "sqlexec_lock" },
  "pluginPaths": [],
  "environments": {
    "dev": {
      "provider": "postgresql",
      "connectionStringEnv": "SQLEXEC_DEV_CONNECTION",
      "description": "Local development database",
      "tags": ["seed"]
    },
    "prod": {
      "provider": "postgresql",
      "connectionString": "Host=${DB_HOST};Database=app;Username=deploy;Password=${DB_PASSWORD}",
      "protected": true
    }
  }
}

An environment must set either connectionString or connectionStringEnv. Nothing secret belongs in this file: keep credentials in environment variables and reference them. sqlexec env add writes and validates this block for you, which is safer than editing it by hand.

Two projects can target the same database safely as long as they use different history.table and history.lockTable names — that is how you keep an independent timeline (a seeding project, a reporting schema) beside the main one.

Connection strings

sql-exec passes the string straight to the engine's ADO.NET driver, so anything the driver accepts works.

The account needs enough rights to create the two bookkeeping tables (and the configured schema, where the engine supports creating one) plus whatever your migrations do.

sql-exec never prints a connection string. What it shows is a host/database summary built from the non-secret keys — localhost/app — so plan output and CI logs stay safe to share.

Writing migrations

migrations/
  20260831120000__create_customers.sql
  20260831121500__add_orders_index.sql
  20260901090000__partition_orders.oracle.sql
  20260901093000__seed_demo_data.+seed.sql

The file name is <version>__<name>[.<engine>][.+<tag>...].sql:

• Version — everything before the first __. sqlexec new stamps a UTC timestamp (20260901093000), which is collision-resistant across a team; hand-written schemes like V001 work too. Versions sort naturally, so V2 runs before V10. Pick one scheme per project and stay with it: digits sort before letters, so a timestamp version runs before every V001-style one, which is rarely what you meant.
• Name — free text, for humans and for the plan output.
• .<engine> — makes the script engine-specific. It overrides the portable script of the same version when deploying to that engine, which is how one timeline covers engines that need different DDL. A trailing segment counts as an engine only if it is a known provider id, so V1__add.column.sql keeps its name intact.
• .+<tag> — scopes the script to environments carrying that tag. See the next section.

Engine and tag qualifiers compose in any order: V004__seed.oracle.+dev.sql and V004__seed.+dev.oracle.sql are the same thing.

Rules worth knowing:

• Each version runs once per database and is recorded with a SHA-256 checksum of its content.
• Editing a migration that has already run is an error — the database no longer matches the repository. Ship the change as a new migration, or override deliberately with --ignore-checksums.
• Checksums normalise CRLF to LF and trim trailing whitespace, so a Windows/Linux checkout difference never looks like drift.
• Files and folders starting with _ or . are ignored, so a script can be parked without deleting it.
• Subdirectories are scanned recursively; the directory layout is for your benefit, ordering comes from the version alone.
• SQL Server scripts may use GO as a batch separator, and Oracle scripts a lone / — both are handled before the SQL reaches the driver.

Scoping a migration to some environments

Untagged migrations run everywhere. To restrict one, tag it — the environment declares which tags it accepts, and the script declares which tags it needs:

"environments": {
  "dev":     { "provider": "postgresql", "connectionStringEnv": "SQLEXEC_DEV_CONNECTION",  "tags": ["seed"] },
  "staging": { "provider": "postgresql", "connectionStringEnv": "SQLEXEC_STG_CONNECTION",  "tags": ["seed"] },
  "prod":    { "provider": "postgresql", "connectionStringEnv": "SQLEXEC_PROD_CONNECTION", "protected": true }
}

sqlexec new "seed demo data" --tag seed

That writes 20260901093000__seed_demo_data.+seed.sql, which deploys to dev and staging and is invisible to prod — it never shows up in prod's plan, never runs there, and is not reported as drift.

• A migration runs where the environment carries any of its tags, so .+dev.+staging.sql targets both.
• An environment with no tags takes only untagged migrations.
• sqlexec new --tag tells you which environments the new migration will reach, and warns when no environment carries the tag — the migration would never run anywhere.
• Tags scope where, not when: within an environment, tagged and untagged migrations share one version order and one history table.
• Removing a tag from an environment that already applied a tagged migration leaves a history row with no matching script. That is reported as a warning, not an error.

Tags may contain letters, digits, dashes and underscores.

Controlled deployments

• Plan before apply. plan connects read-only and prints exactly what would run.
• Confirmation in front of production. "protected": true makes apply prompt, and refuse outright in a non-interactive session unless --yes is passed. CI has to say so explicitly.
• One deployment at a time. apply takes a row-based lock in the target database, so two pipelines cannot migrate the same database concurrently. --lock-timeout bounds the wait.
• The plan is rebuilt inside the lock, so nothing can slip in between the preview and the rollout.
• Stop at a version with --to <version> to roll out in stages.
• Ordering is enforced. A migration whose version sorts before one that already ran is blocked until you re-number it or pass --allow-out-of-order — that way a long-lived branch cannot quietly apply its changes underneath newer ones.
• Transactional where the engine allows it. On SQL Server and PostgreSQL a failed migration is rolled back completely and leaves no history row. MySQL and Oracle commit DDL implicitly: there the failure is recorded with success = 0, reported on the next run, and retried once you have fixed the script or the database state. The CLI says which of the two happened instead of pretending everything rolled back.
• Migrations are never undone. There is no down script: a mistake is corrected by a new migration, so the history is a straight line and every environment reaches the same state by the same path.

Engine notes

On MySQL and Oracle, prefer one DDL statement per migration. A migration that creates three tables and fails on the third leaves the first two behind, and only you can decide whether the retry should drop them or skip ahead.

Bookkeeping tables

sqlexec_history — one row per migration that has run against this database:

sqlexec_lock holds at most one row (lock_id, acquired_utc, acquired_by) for the duration of an apply. If a deployment is killed hard enough to leave the row behind, later runs will wait for the lock timeout and then tell you which table to clear.

Both tables are ordinary tables you can query, and both are safe to read while a deployment runs.

Using it in CI/CD

A pull request build can check everything that does not need a database:

- run: dotnet tool install -g SqlExec.Tool
- run: sqlexec validate

A deployment job plans first, then applies. --yes is what makes a protected environment deployable without a TTY:

- run: dotnet tool install -g SqlExec.Tool
- run: sqlexec plan --env prod
  env:
    SQLEXEC_PROD_CONNECTION: ${{ secrets.SQLEXEC_PROD_CONNECTION }}
- run: sqlexec apply --env prod --yes
  env:
    SQLEXEC_PROD_CONNECTION: ${{ secrets.SQLEXEC_PROD_CONNECTION }}

To run a job only when there is something to deploy, use the detailed exit code (2 means "valid plan with changes"):

sqlexec plan --env prod --detailed-exit-code

Pass the connection string through the CI system's secret store, never through the project file. plan and apply print only the host/database summary, so the logs stay shareable.

Adding a database engine

sql-exec loads providers from ~/.sql-exec/plugins/, <project>/.sqlexec/plugins/ and any directory listed in pluginPaths. A provider is a normal class library referencing one package:

dotnet add package SqlExec.Abstractions

[assembly: SqlExecProvider(typeof(MyEngineProvider))]

public sealed class MyEngineProvider : IDatabaseProvider
{
    public string Id => "myengine";
    public string DisplayName => "My Engine";
    public ISqlDialect Dialect { get; } = new MyEngineDialect();
    public DbConnection CreateConnection(string connectionString) => new MyConnection(connectionString);
    public string DescribeTarget(string connectionString) =>
        ConnectionStringSummary.Describe(connectionString, ["server"], ["database"]);
}

ISqlDialect is the whole engine-specific surface — derive from SqlDialectBase and override what differs:

Everything else — discovery, planning, checksums, history, locking, the CLI — is engine-agnostic and already done. Then publish the plugin so its driver ships alongside it:

dotnet publish -o ~/.sql-exec/plugins/myengine
sqlexec providers

Publish rather than copy: a plugin directory must contain the database driver too, and a single assembly copied on its own will fail the moment it tries to open a connection. Each plugin loads into its own AssemblyLoadContext, so it can carry its own dependency versions. Plugins cannot claim an id already taken by a built-in engine, and a plugin that fails to load is reported as a warning instead of breaking the run.

A complete, working example lives in samples/SqlExec.Providers.Sqlite — about 60 lines, and it is what the end-to-end tests deploy against.

Troubleshooting

Repository layout

Contributing

dotnet build sql-exec.slnx
dotnet test sql-exec.slnx                             # no database server required
dotnet format sql-exec.slnx                           # apply .editorconfig
dotnet run --project src/SqlExec.Cli -- plan --env dev
dotnet pack sql-exec.slnx -c Release -o ./artifacts   # the tool plus the six libraries

The test suite runs entirely against SQLite through the sample provider, so it needs nothing installed. The default branch is develop.

A few repository-wide conventions:

• Package versions live in Directory.Packages.props. Central package management is on, so a PackageReference in a .csproj carries no Version — dotnet add package updates the central file.
• NuGet.config pins the feed. Inherited sources are cleared and every package is mapped to nuget.org, so restores resolve the same way everywhere. NuGet audit runs over direct and transitive dependencies.
• .editorconfig is enforced at build time. Style rules marked warning fail the build; formatting is a suggestion, so run dotnet format (CI uses dotnet format --verify-no-changes).
• Versions come from git tags, not from a file. MinVer reads the nearest v tag, so nothing in the repository needs bumping — see Releasing.

Releasing

There is no version number to edit. MinVer derives the version of all seven packages from the nearest reachable tag prefixed with v, so an untagged working copy builds as 0.0.0-alpha.0.<n> and the tag v1.2.3 builds as 1.2.3.

Publishing is one command, once the change is merged into develop:

git checkout develop && git pull
git tag v1.2.3 && git push origin v1.2.3

The release workflow picks the tag up and, in order: builds, runs the test suite against the tagged commit, packs, checks the packed version really is the tag's, pushes all seven packages to nuget.org, and opens a GitHub release with notes generated from the pull requests merged since the previous tag.

It authenticates with trusted publishing: GitHub mints an OIDC token, nuget.org exchanges it for an API key that expires in an hour, and no long-lived key is stored in the repository. That trust is registered on nuget.org against the repository and the workflow's file name, so renaming release.yml breaks publishing until the policy is updated to match. The only secret involved is NUGET_USER, the nuget.org profile name to publish as.

A published version cannot be replaced, only unlisted, so the tag is the point of no return. To rehearse the whole path first, tag a prerelease — v1.2.3-preview.1 matches the workflow's trigger and nuget.org hides prereleases from searches unless they are asked for.

License

MIT