Lone Badger Tech

Examples

Copy-ready scripts for common SQL Server changes, plus configuration and CI pipelines.

Examples

Create a table

The basic shape of every change: deploy, the exact opposite in revert, and a verify script that raises an error if the deploy did not take effect. Verify runs inside the same transaction, so a failing check rolls the change back.

changes/create-customer/deploy.sql
CREATE TABLE dbo.Customer (
    Id int IDENTITY NOT NULL CONSTRAINT PK_Customer PRIMARY KEY,
    Name nvarchar(200) NOT NULL,
    Email nvarchar(320) NULL,
    CreatedAt datetime2(3) NOT NULL CONSTRAINT DF_Customer_CreatedAt DEFAULT (sysutcdatetime())
);

CREATE UNIQUE INDEX UX_Customer_Email ON dbo.Customer (Email) WHERE Email IS NOT NULL;
changes/create-customer/revert.sql
DROP TABLE dbo.Customer;
changes/create-customer/verify.sql
IF OBJECT_ID(N'dbo.Customer', N'U') IS NULL
    THROW 50000, N'dbo.Customer was not created', 1;
IF INDEXPROPERTY(OBJECT_ID(N'dbo.Customer'), N'UX_Customer_Email', 'IsUnique') <> 1
    THROW 50000, N'UX_Customer_Email is missing or not unique', 1;
bsm add create-customer --note "customer table"

Add a NOT NULL column to a table with data

Add it nullable, backfill, then tighten. Batches separated by GO run in order inside the change's transaction. Name constraints explicitly: system-generated names differ between servers.

changes/add-customer-status/deploy.sql
ALTER TABLE dbo.Customer ADD Status tinyint NULL;
GO
UPDATE dbo.Customer SET Status = 1 WHERE Status IS NULL;
GO
ALTER TABLE dbo.Customer ALTER COLUMN Status tinyint NOT NULL;
ALTER TABLE dbo.Customer ADD CONSTRAINT DF_Customer_Status DEFAULT (1) FOR Status;
ALTER TABLE dbo.Customer ADD CONSTRAINT CK_Customer_Status CHECK (Status IN (1, 2, 3));
changes/add-customer-status/revert.sql
ALTER TABLE dbo.Customer DROP CONSTRAINT CK_Customer_Status;
ALTER TABLE dbo.Customer DROP CONSTRAINT DF_Customer_Status;
ALTER TABLE dbo.Customer DROP COLUMN Status;
bsm.plan (line added by bsm add -r create-customer)
add-customer-status [create-customer] @2026-11-04T09:02Z alice

Rename a column

Renames are reversible when you write both directions.

changes/rename-customer-email/deploy.sql
EXEC sp_rename N'dbo.Customer.Email', N'EmailAddress', N'COLUMN';
changes/rename-customer-email/revert.sql
EXEC sp_rename N'dbo.Customer.EmailAddress', N'Email', N'COLUMN';
changes/rename-customer-email/verify.sql
IF COL_LENGTH(N'dbo.Customer', N'EmailAddress') IS NULL
    THROW 50000, N'EmailAddress column missing', 1;

Data migration with a reversible split

Move data into a new shape and keep the revert honest. For very large tables, batch the update.

changes/split-customer-name/deploy.sql
ALTER TABLE dbo.Customer ADD FirstName nvarchar(100) NULL, LastName nvarchar(100) NULL;
GO
DECLARE @batch int = 5000;
WHILE 1 = 1
BEGIN
    UPDATE TOP (@batch) dbo.Customer
       SET FirstName = LEFT(Name, CHARINDEX(N' ', Name + N' ') - 1),
           LastName  = LTRIM(SUBSTRING(Name, CHARINDEX(N' ', Name + N' ') + 1, 200))
     WHERE FirstName IS NULL;
    IF @@ROWCOUNT < @batch BREAK;
END
changes/split-customer-name/revert.sql
UPDATE dbo.Customer SET Name = CONCAT_WS(N' ', FirstName, NULLIF(LastName, N''));
ALTER TABLE dbo.Customer DROP COLUMN FirstName, LastName;

An irreversible change

Some changes cannot be undone, such as dropping data you no longer keep. Mark them !irreversible: no revert.sql is needed and bsm revert stops with a clear message instead of guessing. Archiving first is good manners.

changes/drop-legacy-orders/deploy.sql
SELECT * INTO archive.LegacyOrder_20261103 FROM dbo.LegacyOrder;
DROP TABLE dbo.LegacyOrder;
bsm add drop-legacy-orders --irreversible

Statements that cannot run in a transaction

SQL Server refuses some statements inside a transaction (ALTER DATABASE, full-text DDL, RECONFIGURE, resumable index operations). The linter catches them; mark the change !notx and make the script safe to re-run, because a failure halfway leaves partial work.

changes/product-fulltext/deploy.sql
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'ftCatalog')
    CREATE FULLTEXT CATALOG ftCatalog;
GO
IF NOT EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.Product'))
    CREATE FULLTEXT INDEX ON dbo.Product (Name, Description) KEY INDEX PK_Product ON ftCatalog;
changes/product-fulltext/revert.sql
IF EXISTS (SELECT 1 FROM sys.fulltext_indexes WHERE object_id = OBJECT_ID(N'dbo.Product'))
    DROP FULLTEXT INDEX ON dbo.Product;
GO
IF EXISTS (SELECT 1 FROM sys.fulltext_catalogs WHERE name = N'ftCatalog')
    DROP FULLTEXT CATALOG ftCatalog;
bsm.plan
product-fulltext [create-product] !notx

A system-versioned (temporal) table

Name the history table so it is the same on every server and the revert can drop it. To change the table later, switch versioning off, change both tables and switch it on again in one change (lint rule BSM004 catches a missing ON).

changes/create-price/deploy.sql
CREATE TABLE dbo.Price (
    Id int NOT NULL CONSTRAINT PK_Price PRIMARY KEY,
    ProductId int NOT NULL,
    Amount money NOT NULL,
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceHistory, HISTORY_RETENTION_PERIOD = 2 YEARS));
changes/create-price/revert.sql
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Price, dbo.PriceHistory;
changes/create-price/verify.sql
IF OBJECTPROPERTY(OBJECT_ID(N'dbo.Price'), 'TableTemporalType') <> 2
    THROW 50000, N'dbo.Price is not system-versioned', 1;

A partitioned table

The function, scheme and table go in one change. Filegroups come from a variable because they differ per environment. If a job adds partitions in production, set schemaFingerprint.partitionBoundaries: ignore for that environment so its splits are not reported as drift.

changes/create-order/deploy.sql
CREATE PARTITION FUNCTION pfOrderDate (date) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-04-01', '2026-07-01', '2026-10-01');
CREATE PARTITION SCHEME psOrderDate AS PARTITION pfOrderDate ALL TO ({OrderFilegroup});
CREATE TABLE dbo.[Order] (
    OrderDate date NOT NULL,
    Id bigint NOT NULL,
    CustomerId int NOT NULL,
    CONSTRAINT PK_Order PRIMARY KEY (OrderDate, Id)) ON psOrderDate (OrderDate);
CREATE INDEX IX_Order_Customer ON dbo.[Order] (CustomerId) ON psOrderDate (OrderDate);
changes/create-order/revert.sql
DROP TABLE dbo.[Order];
DROP PARTITION SCHEME psOrderDate;
DROP PARTITION FUNCTION pfOrderDate;
bsm.yaml
variables:
  OrderFilegroup: "[PRIMARY]"          # Azure SQL Database has only PRIMARY
environments:
  onprem-prod:
    variables:
      OrderFilegroup: "[FG_Orders]"
    schemaFingerprint:
      partitionBoundaries: ignore      # a job splits new partitions every month

Views, procedures and functions (repeatables)

Programmable objects live in repeatable/ and use CREATE OR ALTER. Badger SQL Migrate re-applies one only when its hash changes, after all changes, in dependency order. Declare dependencies with a header.

repeatable/views/vActiveCustomer.sql
CREATE OR ALTER VIEW dbo.vActiveCustomer
AS
SELECT Id, Name, EmailAddress
FROM dbo.Customer
WHERE Status = 1;
repeatable/procedures/usp_FindCustomer.sql
-- bsm:requires views/vActiveCustomer.sql
CREATE OR ALTER PROCEDURE dbo.usp_FindCustomer
    @search nvarchar(200)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT TOP (50) Id, Name, EmailAddress
    FROM dbo.vActiveCustomer
    WHERE Name LIKE @search + N'%'
    ORDER BY Name;
END

Seed data for one environment

Scripts under environments/<name>/ run only in that environment, after the shared repeatables. Write them idempotently; they re-run when they change.

environments/dev/seed-customers.sql
-- bsm:requires views/vActiveCustomer.sql
MERGE dbo.Customer AS t
USING (VALUES
    (N'Ada Lovelace', N'ada@example.com', 1),
    (N'Grace Hopper', N'grace@example.com', 1)
) AS s (Name, EmailAddress, Status)
    ON t.EmailAddress = s.EmailAddress
WHEN NOT MATCHED THEN
    INSERT (Name, EmailAddress, Status) VALUES (s.Name, s.EmailAddress, s.Status);

Environment-specific names with variables

The same script targets calc_test in test and calc_prod in prod. Built-ins like {env} and {db} are always available; your own variables live in bsm.yaml.

bsm.yaml
variables:
  schemas:
    calc: calc_{env}
  AppRole: app_reader
environments:
  test:
    database: AppTest
  prod:
    database: AppProd
    variables:
      AppRole: app_reader_prod
      linkedServer: REPORTING01
changes/create-calc-schema/deploy.sql
CREATE SCHEMA [{schemas.calc}];
GO
CREATE TABLE [{schemas.calc}].[table1] (
    Id int NOT NULL PRIMARY KEY,
    Note nvarchar(100) NOT NULL DEFAULT (N'{{env}} stays literal')
);
GO
CREATE ROLE [{AppRole}];
GRANT SELECT ON SCHEMA::[{schemas.calc}] TO [{AppRole}];
PRINT 'deployed {change} to {env} ({db}) with bsm {bsm.version}';
bsm render create-calc-schema -e prod    # prints the exact SQL prod will receive
bsm vars -e prod                          # every variable, its value and where it came from
output of bsm render -e prod
CREATE SCHEMA [calc_prod];
GO
CREATE TABLE [calc_prod].[table1] (
    Id int NOT NULL PRIMARY KEY,
    Note nvarchar(100) NOT NULL DEFAULT (N'{env} stays literal')
);
GO
CREATE ROLE [app_reader_prod];
GRANT SELECT ON SCHEMA::[calc_prod] TO [app_reader_prod];
PRINT 'deployed create-calc-schema to prod (AppProd) with bsm 0.1.0';

Logins, users and grants that differ per environment

Principals usually differ per environment. Keep the shape in the change and the names in variables. Secrets come from the environment, never from the file.

bsm.yaml
environments:
  prod:
    variables:
      apiIdentity: myapp-api-prod          # Entra managed identity name
  test:
    variables:
      apiIdentity: myapp-api-test
changes/grant-api-identity/deploy.sql
CREATE USER [{apiIdentity}] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [{apiIdentity}];
GRANT EXECUTE ON SCHEMA::dbo TO [{apiIdentity}];
changes/grant-api-identity/revert.sql
REVOKE EXECUTE ON SCHEMA::dbo FROM [{apiIdentity}];
ALTER ROLE db_datareader DROP MEMBER [{apiIdentity}];
DROP USER [{apiIdentity}];

An Always Encrypted column

The change names a column encryption key; each environment decides which column master key protects it. Badger SQL Migrate creates the keys before the change runs.

bsm.yaml
environments:
  test:
    alwaysEncrypted:
      columnMasterKeys:
        CMK_Test: { keyStore: AZURE_KEY_VAULT, keyPath: "https://myapp-test-kv.vault.azure.net/keys/cmk/1a2b3c" }
      columnEncryptionKeys:
        CEK_Customer: { columnMasterKey: CMK_Test }
  prod:
    alwaysEncrypted:
      columnMasterKeys:
        CMK_Prod_2026: { keyStore: AZURE_KEY_VAULT, keyPath: "https://myapp-prod-kv.vault.azure.net/keys/cmk/9f8e7d" }
      columnEncryptionKeys:
        CEK_Customer: { columnMasterKey: CMK_Prod_2026 }
changes/add-customer-taxid/deploy.sql
ALTER TABLE dbo.Customer ADD TaxId char(11) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH (
        COLUMN_ENCRYPTION_KEY = CEK_Customer,
        ENCRYPTION_TYPE = Deterministic,
        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL;
changes/add-customer-taxid/revert.sql
ALTER TABLE dbo.Customer DROP COLUMN TaxId;
bsm env status -e prod      # keys: in sync, to create, to rotate, conflicting
bsm deploy -e prod           # creates CMK_Prod_2026 and CEK_Customer, then runs the change

A complete bsm.yaml

A realistic configuration: a local container, Azure test and production, a template for shared settings, and production rules.

bsm.yaml
provider: sqlserver
defaultEnvironment: local

preprocessor:
  unknown: error                    # unknown {name} in a script is an error

variables:
  AppRole: app_reader
  schemas:
    reporting: reporting_{env}

environments:
  _azure:                           # template: shared by test and prod through extends
    database: App
    tls: { mode: strict }
    authentication: entraInteractive
    entra:
      tenantId: 7f3c1e2a-0000-0000-0000-000000000000
    connectionProperties:
      Connect Retry Count: 3
      Connect Retry Interval: 10

  local:
    host: localhost
    database: App
    authentication: password
    user: sa
    password: ${env:BSM_LOCAL_PASSWORD}
    tls: { mode: mandatory, trustServerCertificate: true }

  test:
    extends: _azure
    description: Shared test database
    host: myapp-test.database.windows.net

  prod:
    extends: _azure
    description: Production
    host: myapp-prod.database.windows.net
    protected: true                 # type "prod" (or pass --yes) to deploy
    allowRevert: false              # fix forward
    ordering: strict                # exact plan order only
    authentication: entraWorkloadIdentity
    variables:
      AppRole: app_reader_prod
    connectionProperties:
      Command Timeout: 600

A plan after two developers merged

Alice and Bob each appended a line on their branches. Git's union merge driver (set up by bsm init) kept both lines without a conflict.

bsm.plan
# Badger SQL Migrate plan: one change per line, in deploy order.

create-customer                                  @2026-11-03T14:10Z brock  # customer table
add-customer-status [create-customer]            @2026-11-03T16:42Z brock
@release-1.0
rename-customer-email [create-customer]          @2026-11-04T09:02Z alice
add-order-table [create-customer]                @2026-11-04T09:15Z bob
product-fulltext [create-product] !notx          @2026-11-05T11:30Z bob
drop-legacy-orders !irreversible                 @2026-11-06T08:00Z alice
bsm plan check     # unique names, dependencies, folders, lint, variables in every environment

GitHub Actions

Check on every pull request; deploy to test on merge; deploy to production with workload identity federation (no secrets).

.github/workflows/database.yml
name: database
on:
  pull_request:
  push:
    branches: [main]

permissions:
  id-token: write      # Entra workload identity federation
  contents: read

jobs:
  check:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with: { dotnet-version: 10.0.x }
      - run: dotnet tool install --global Badger.SqlMigrate
      - run: bsm plan check -C db

  deploy:
    if: github.ref == 'refs/heads/main'
    needs: check
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-dotnet@v4
        with: { dotnet-version: 10.0.x }
      - run: dotnet tool install --global Badger.SqlMigrate
      - uses: azure/login@v2
        with:
          client-id: ${{ vars.AZURE_CLIENT_ID }}
          tenant-id: ${{ vars.AZURE_TENANT_ID }}
          allow-no-subscriptions: true
      - run: bsm deploy -C db -e test --json
      - run: bsm deploy -C db -e prod --yes --json
      - run: bsm verify -C db -e prod
bsm.yaml (prod for this pipeline)
  prod:
    extends: _azure
    authentication: entraAzureCli      # uses the azure/login session above

Azure Pipelines

With a workload-identity service connection, use entraAzurePipelines, or run inside an AzureCLI task with entraAzureCli.

azure-pipelines.yml
steps:
  - task: UseDotNet@2
    inputs: { packageType: sdk, version: 10.0.x }
  - script: dotnet tool install --global Badger.SqlMigrate
  - script: bsm deploy -C db -e prod --yes
    env:
      SYSTEM_ACCESSTOKEN: $(System.AccessToken)
bsm.yaml
  prod:
    extends: _azure
    authentication: entraAzurePipelines
    entra:
      tenantId: 7f3c1e2a-0000-0000-0000-000000000000
      clientId: 0a1b2c3d-0000-0000-0000-000000000000
      serviceConnectionId: 5e6f7a8b-0000-0000-0000-000000000000
      systemAccessToken: ${env:SYSTEM_ACCESSTOKEN}

A runnable sample project is in the repository under samples/demo-db. For every option, see the documentation.