Why Automate Database Deployments?

Manual database deployments are error-prone, slow, and difficult to audit. By integrating SchemaSmith into your CI/CD pipeline, you gain:

Consistency - Every deployment follows the same process, eliminating human error
Speed - Database changes deploy alongside application code automatically
Auditability - Full history of what changed, when, and by whom in source control
Rollback capability - Revert to any previous state using your version control system
Environment parity - Dev, staging, and production stay synchronized

SchemaSmith CLI Basics

SchemaSmith deployments require two things in your CI/CD environment:

SchemaQuench binary - The CLI tool (from your artifact server or network share)
Schema package - A directory or zip file containing Product.json, templates, table definitions, and scripts. See the Schema Packages reference.

Every SchemaSmith setting can be injected through environment variables — the tools are pipeline-native from the start. The convention: prefix with SmithySettings_ and use double underscores (__) to represent nesting in the configuration hierarchy. SmithySettings_SchemaPackagePath points SchemaQuench at your schema package, either a directory or a zip file (no extraction needed).

REM Point at a schema package (directory or zip)
set SmithySettings_SchemaPackagePath=C:\artifacts\schema-package.zip

REM Run deployment
schemaquench

# Point at a schema package (directory or zip)
export SmithySettings_SchemaPackagePath=/artifacts/schema-package.zip

# Run deployment
schemaquench

Build/Deploy Model

Build: Package Your Schema

Check out your schema package — Product.json, templates, table definitions, and scripts — and zip it into a versioned artifact using standard CI tooling. The package is the artifact: no compilation, no transformation, no intermediate format. Store the resulting file (e.g., schema-package-1.4.2.zip) in your artifact repository for retrieval during deploy stages.

Deploy: Apply to Database

Download the versioned schema package and point SchemaQuench at it via the SmithySettings_SchemaPackagePath environment variable. SchemaQuench reads directly from zip archives with no extraction step. See the pipeline examples below for platform-specific syntax.

Build and deploy are separate pipelines: you build and version the artifact once, then deploy that same artifact to each environment (dev → staging → production). This ensures every environment applies identical schema definitions.

Pipeline Examples

Select your CI/CD platform below. These examples show the deploy stage, pointing SchemaQuench at your schema package via SmithySettings_SchemaPackagePath. In production workflows, the package is a versioned zip produced by a separate build pipeline.

Jenkinsfile (Declarative Pipeline) Groovy

pipeline {
    agent any

    environment {
        // Point to zip file or directory - both work
        SmithySettings_SchemaPackagePath = '/opt/artifacts/schema-package.zip'
    }

    stages {
        stage('Deploy Schema') {
            steps {
                sh 'schemaquench'
            }
        }
    }
}

Note: SchemaQuench reads directly from zip files - no extraction needed.

.github/workflows/deploy-schema.yml YAML

name: Deploy Database Schema

on:
  push:
    branches: [main]
  workflow_dispatch:

jobs:
  deploy:
    runs-on: self-hosted
    steps:
      - name: Checkout schema package
        uses: actions/checkout@v4

      - name: Deploy Schema
        env:
          SmithySettings_SchemaPackagePath: ${{ github.workspace }}
        run: schemaquench

Note: Uses a self-hosted runner with SchemaQuench pre-installed. The checkout provides the schema package.

.gitlab-ci.yml YAML

stages:
  - deploy

deploy-schema:
  stage: deploy
  tags:
    - schemasmith
  variables:
    SmithySettings_SchemaPackagePath: $CI_PROJECT_DIR
  script:
    - schemaquench
  only:
    - main
  environment:
    name: production

Note: Uses a runner with SchemaQuench pre-installed. $CI_PROJECT_DIR points to the checked-out schema package.

azure-pipelines.yml YAML

trigger:
  - main

pool:
  name: 'SchemaSmith'

steps:
  - checkout: self

  - script: schemaquench
    displayName: 'Deploy Schema'
    env:
      SmithySettings_SchemaPackagePath: $(Build.SourcesDirectory)

Note: Uses a self-hosted agent pool with SchemaQuench pre-installed. The checkout provides the schema package.

Pre-flight readiness checks

SchemaQuench validates your target environment — connectivity, version requirements, and the database and schema roster — without deploying anything. Catch environmental misconfigurations before your deployment window opens.

--Validate

Needs no database at all — no connection, no target, no credentials. --Validate loads your schema package from disk and lints it: a malformed package that would otherwise crash mid-deployment, accidental duplicates, dangling foreign keys, undefined {{Token}} references, JSON-schema violations, and table files whose names have drifted from their contents. Because it needs nothing but the files, it is cheap enough to run on every pull request. It exits 0 when there are no findings or warnings only, 2 on any error-severity finding.

--TestConnection

Opens a connection to every configured server — the primary plus any Target:SecondaryServers — runs a liveness query, and validates that each server meets the product's declared MinimumVersion floor. Nothing is read, generated, or deployed. It exits 0 when every server connects and clears the floor, 2 on any connection failure or version violation.

--PreviewTargets

Does everything --TestConnection does, then prints a read-only per-template report of the databases and schemas the deployment would target — the exact set of work units a full quench would process, without processing any of them. A template marked RequireAtLeastOneTarget that resolves nothing fails the preview, so a misconfigured environment is caught here rather than at run time. Same exit codes: 0 on pass, 2 on any connection failure, version violation, or required-template match miss.

Readiness gate

Because all three switches return 0 for go and 2 for stop, they drop straight into a pipeline as a readiness gate ahead of the deploy step:

REM Readiness gate: abort the deploy if pre-flight fails
schemaquench --Validate
if errorlevel 1 (echo Package validation failed & exit /b 1)

schemaquench --TestConnection
if errorlevel 1 (echo Pre-flight failed & exit /b 1)

schemaquench --PreviewTargets
if errorlevel 1 (echo Target preview failed & exit /b 1)

REM Only reached when every gate passes:
schemaquench

# Readiness gate: abort the deploy if pre-flight fails
schemaquench --Validate       || { echo "Package validation failed"; exit 1; }
schemaquench --TestConnection || { echo "Pre-flight failed"; exit 1; }
schemaquench --PreviewTargets || { echo "Target preview failed"; exit 1; }

# Only reached when every gate passes:
schemaquench

For the full behavior of --TestConnection and --PreviewTargets — secondary-server handling, the version-floor rules, and the target-report format — see the SchemaQuench reference.

Operational profiles

Your CI/CD pipeline has different jobs — full releases, patch-only runs, PR validations, idempotency checks — each with a different operational posture. SchemaQuench's small config surface covers all of them, identically across SQL Server, PostgreSQL, and MySQL.

Full release pipeline

The standard deployment profile. Structural changes land, helper procedures stay current, and tables removed from the product are caught early.

{
  "KindleTheForge": true,
  "UpdateTables": true,
  "WhatIfONLY": false,
  "DropTablesRemovedFromProduct": true
}

DropTablesRemovedFromProduct is environment-dependent: set it true in CI and staging to catch removals early, and many teams set it false in production for rollback safety — the next release can always clean up once the window passes. See Drop control for the full cascade.

Datafix patch pipeline

Migration scripts only — no DDL, no table quenching, no run-once tracking inserts. SchemaSmith itself performs no structural changes under this profile; reach for it when shipping a data patch, hotfix, or bulk load between structural releases.

{
  "KindleTheForge": false,
  "UpdateTables": false,
  "DropTablesRemovedFromProduct": false,
  "TrackRunOnceMigrations": false
}

See Data fixes for the full per-flag reasoning and the least-privilege deploy-account grants these patches need.

Idempotency check

Runs every object script and [ALWAYS] script twice in sequence and requires both passes to succeed — a strong guarantee that your scripts are truly stateless and can be re-applied after a partial failure.

{
  "WhatIfONLY": false,
  "RunScriptsTwice": true,
  "DropTablesRemovedFromProduct": true
}

Run this against a disposable database in CI — it is not meant for production targets. RunScriptsTwice is an idempotency check, not a dependency-resolution mechanism; the retry loop already handles inter-object dependencies. Run-once migration scripts are excluded from the double-run.

WhatIf: a review tool, not a standing gate

Setting WhatIfONLY: true runs the full deployment logic — validation, token replacement, DDL generation, dependency resolution — without touching a real database, so you can preview exactly what a change would do. Reach for it during a complex migration, an unfamiliar package, or an unfamiliar target: run it against a disposable database and read the preview. Once you trust the package and the pipeline, direct deploys are the normal mode — WhatIf earns its keep on the tricky changes, not as a standing gate on every run.

Best Practices

Test in Dev First

Deploy to a dev environment that mirrors production's schema with scrubbed or synthetic data. Catches edge cases at realistic object counts without exposing production data. Review the log to confirm expected changes:

# Deploy and review logs
schemaquench

Use WhatIfONLY: true for manual review sessions when you want to preview changes without applying them.

Pre-Install on Runners

Drop the SchemaQuench binary onto each CI runner or agent once and add it to PATH — MSI on Windows, zip into /usr/local/bin on Linux. Pipelines stay clean, you skip the download on every run, and install matches your existing runner provisioning.

Self-contained executable — no .NET SDK, no runtime, no package manager plugins.

Per-Environment Config

Deploy the same schema package to every environment. Per-environment values — server, credentials, script tokens — come from your CI secret store as env vars. SmithySettings_Target__Server changes from db-staging-01 to db-prod-01 when you promote; every other setting stays constant.

Same artifact deploys to every environment — only env vars change.

Never Commit Secrets

Store connection strings and credentials in your CI/CD platform's secret management:

Jenkins: Credentials Plugin
GitHub: Repository Secrets
GitLab: CI/CD Variables (masked)
Azure DevOps: Variable Groups / Key Vault

Use environment variables or file injection at runtime.

Pro Tip: Validate Before Deploying

Wire up JSON Schema validation when you zip your schema package — it catches structural problems before the first database call. The SchemaSmith demos include JSON Schema files for Product.json, Template.json, and table JSON files that you can use with tools like ajv, along with a sample GitHub Action for CI validation.

Pro Tip: Resume After a Failed Run

SchemaQuench records a checkpoint after each completed step. If a pipeline deployment trips partway through — a network blip, a lock timeout, a bad data row at step 14 of 20 — re-run with --ResumeQuench and it skips everything already applied, resuming at the first incomplete step. Checkpoint files are cleared automatically on clean runs. Leave the flag off for pipelines that rebuild databases from scratch every run.

Hands-on lab

Run SchemaQuench in your CI platform to validate schema changes, then deploy the same versioned package through dev, staging, and production.

Start the pipeline lab

CI/CD Integration Guide