Testing T-SQL: tSQLt, CI and safe deployments

"""A model of what tSQLt.Run 'withdraw_tests' does with the three tests of withdraw_tests.sql.

tSQLt runs each test inside a transaction and rolls it back when the test ends, so every change a test makes, the
fake tables included, is undone. tSQLt.FakeTable swaps a table for an empty copy without its constraints. This model
keeps the database as Python dictionaries, copies it before each test (the transaction) and puts the copy back after
it (the rollback). The version of dbo.withdraw under test does not refuse an amount of 0, so the third test fails;
the real table's CHECK constraint would have hidden that, but a fake table has none. Press Edit and set
CHECK_AMOUNT = True, the fix, to see all three tests succeed.
"""

import copy
from decimal import Decimal

CHECK_AMOUNT = False  # the planted bug: dbo.withdraw does not refuse an amount of 0 or less


class SqlError(Exception):
    def __init__(self, number, message):
        super().__init__(message)
        self.number = number


DATABASE = {  # account 1 of the practice schema and its opening deposit, with the two CHECK constraints
    "dbo.accounts": {"rows": [{"account_id": 1, "balance": Decimal("2500.00")}], "checks": ["balance >= 0"]},
    "dbo.ledger": {"rows": [{"account_id": 1, "amount": Decimal("2500.00")}], "checks": ["amount <> 0"]},
}


def check(table, row):
    if "balance >= 0" in table["checks"] and row["balance"] < 0:
        raise SqlError(547, "The UPDATE statement conflicted with the CHECK constraint accounts_balance_ck.")
    if "amount <> 0" in table["checks"] and row["amount"] == 0:
        raise SqlError(547, "The INSERT statement conflicted with the CHECK constraint ledger_amt_nonzero.")


def withdraw(db, account_id, amount):
    """dbo.withdraw: refuse a bad amount (only with the fix), refuse a short balance, then update and log."""
    if CHECK_AMOUNT and amount <= 0:
        raise SqlError(50020, "The amount must be greater than 0.")
    accounts = db["dbo.accounts"]
    account = next((r for r in accounts["rows"] if r["account_id"] == account_id), None)
    if account is None or account["balance"] < amount:
        raise SqlError(50021, "Insufficient funds or no such account.")
    changed = dict(account, balance=account["balance"] - amount)
    check(accounts, changed)
    account.update(changed)
    row = {"account_id": account_id, "amount": -amount}
    check(db["dbo.ledger"], row)
    db["dbo.ledger"]["rows"].append(row)


def fake_table(db, name):
    db[name] = {"rows": [], "checks": []}  # empty, and without its constraints


def test_lowers_balance(db):
    fake_table(db, "dbo.accounts")
    fake_table(db, "dbo.ledger")
    db["dbo.accounts"]["rows"].append({"account_id": 7, "balance": Decimal("1000.00")})
    withdraw(db, 7, Decimal("250.00"))
    actual = [(r["account_id"], r["balance"]) for r in db["dbo.accounts"]["rows"]]
    assert actual == [(7, Decimal("750.00"))], f"Expected: <[(7, 750.00)]> but was: <{actual}>"
    rows = len([r for r in db["dbo.ledger"]["rows"] if r["amount"] == Decimal("-250.00")])
    assert rows == 1, f"Expected: <1> but was: <{rows}>"


def expect_exception(number, act):
    try:
        act()
    except SqlError as e:
        if e.number != number:
            raise AssertionError(
                f"Exception did not match expectation! Expected Error Number: {number}, Actual: {e.number}"
            ) from None
        return
    raise AssertionError("Expected an error to be raised.")


def test_refuses_more_than_balance(db):
    fake_table(db, "dbo.accounts")
    fake_table(db, "dbo.ledger")
    db["dbo.accounts"]["rows"].append({"account_id": 7, "balance": Decimal("100.00")})
    expect_exception(50021, lambda: withdraw(db, 7, Decimal("250.00")))


def test_refuses_zero(db):
    fake_table(db, "dbo.accounts")
    fake_table(db, "dbo.ledger")
    db["dbo.accounts"]["rows"].append({"account_id": 7, "balance": Decimal("100.00")})
    expect_exception(50020, lambda: withdraw(db, 7, Decimal("0")))


TESTS = [
    ("test withdraw lowers the balance and writes one ledger row", test_lowers_balance),
    ("test withdraw refuses more than the balance", test_refuses_more_than_balance),
    ("test withdraw refuses an amount of zero", test_refuses_zero),
]

results = []
for name, test in TESTS:
    work = copy.deepcopy(DATABASE)  # BEGIN TRANSACTION
    try:
        test(work)
        results.append((name, "Success"))
    except AssertionError as failure:
        results.append((name, "Failure"))
        print(f"[withdraw_tests].[{name}] failed: (Failure) {failure}")
    except SqlError as error:
        results.append((name, "Error"))
        print(f"[withdraw_tests].[{name}] failed: (Error) {error} [Error {error.number}]")
    # ROLLBACK TRANSACTION: work is thrown away, DATABASE is untouched

width = max(len(n) for n, _ in results) + 18
print("+" + "-" * 22 + "+")
print("|Test Execution Summary|")
print("+" + "-" * 22 + "+")
for i, (name, result) in enumerate(results, 1):
    print(f"|{i}|{('[withdraw_tests].[' + name + ']').ljust(width)}|{result}")
ok = sum(r == "Success" for _, r in results)
failed = sum(r == "Failure" for _, r in results)
errored = sum(r == "Error" for _, r in results)
print(f"Test Case Summary: {len(results)} test case(s) executed, {ok} succeeded, {failed} failed, {errored} errored.")
real = DATABASE["dbo.accounts"]["rows"]
print(f"After the run the real tables are unchanged: account 1 at {real[0]['balance']}, "
      f"{len(DATABASE['dbo.ledger']['rows'])} ledger row for it, no account 7")

[withdraw_tests].[test withdraw refuses an amount of zero] failed: (Failure) Expected an error to be raised.
+----------------------+
|Test Execution Summary|
+----------------------+
|1|[withdraw_tests].[test withdraw lowers the balance and writes one ledger row]|Success
|2|[withdraw_tests].[test withdraw refuses more than the balance]              |Success
|3|[withdraw_tests].[test withdraw refuses an amount of zero]                  |Failure
Test Case Summary: 3 test case(s) executed, 2 succeeded, 1 failed, 0 errored.
After the run the real tables are unchanged: account 1 at 2500.00, 1 ledger row for it, no account 7

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab_tsqlt: the practice schema and tSQLt, in a database of their
-- own. Client: sqlcmd. Needs tSQLt 1.0.8083.3529 in that database: PrepareServer.sql once per server (it enables CLR
-- and installs the certificate for tSQLt's assembly), then tSQLt.class.sql in the database. Never install tSQLt on a
-- production server.
-- dbo.withdraw takes money from an account and writes a ledger row. The test class withdraw_tests has three tests: one
-- that succeeds, one that expects error 50021, and one that fails because this first version of dbo.withdraw does
-- not refuse an amount of 0. The script then adds the check and runs the class again. tsqlt_model.py models both runs.
SET NOCOUNT ON;
GO
CREATE OR ALTER PROCEDURE dbo.withdraw
  @account_id int,
  @amount     decimal(14,2)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  IF NOT EXISTS (SELECT 1 FROM dbo.accounts WHERE account_id = @account_id AND balance >= @amount)
    THROW 50021, N'Insufficient funds or no such account.', 1;
  BEGIN TRANSACTION;
  UPDATE dbo.accounts SET balance = balance - @amount
  WHERE account_id = @account_id AND balance >= @amount;
  IF @@ROWCOUNT = 0
    THROW 50021, N'Insufficient funds or no such account.', 1;   -- another session got there first
  INSERT INTO dbo.ledger (account_id, amount, posted_at, memo)
  VALUES (@account_id, -@amount, SYSDATETIME(), N'withdrawal');
  COMMIT TRANSACTION;
END;
GO
EXEC tSQLt.NewTestClass 'withdraw_tests';
GO
CREATE PROCEDURE withdraw_tests.[test withdraw lowers the balance and writes one ledger row]
AS
BEGIN
  EXEC tSQLt.FakeTable 'dbo.accounts';          -- empty, no constraints, no identity: account_id can be given
  EXEC tSQLt.FakeTable 'dbo.ledger';
  INSERT INTO dbo.accounts (account_id, customer_id, kind, balance) VALUES (7, 1, N'savings', 1000.00);

  EXEC dbo.withdraw @account_id = 7, @amount = 250.00;

  SELECT account_id, balance INTO #actual FROM dbo.accounts;
  SELECT TOP (0) account_id, balance INTO #expected FROM dbo.accounts;
  INSERT INTO #expected (account_id, balance) VALUES (7, 750.00);
  EXEC tSQLt.AssertEqualsTable '#expected', '#actual';

  DECLARE @rows int = (SELECT COUNT(*) FROM dbo.ledger WHERE account_id = 7 AND amount = -250.00);
  EXEC tSQLt.AssertEquals 1, @rows;
END;
GO
CREATE PROCEDURE withdraw_tests.[test withdraw refuses more than the balance]
AS
BEGIN
  EXEC tSQLt.FakeTable 'dbo.accounts';
  EXEC tSQLt.FakeTable 'dbo.ledger';
  INSERT INTO dbo.accounts (account_id, customer_id, kind, balance) VALUES (7, 1, N'savings', 100.00);

  EXEC tSQLt.ExpectException @ExpectedErrorNumber = 50021;
  EXEC dbo.withdraw @account_id = 7, @amount = 250.00;
END;
GO
CREATE PROCEDURE withdraw_tests.[test withdraw refuses an amount of zero]
AS
BEGIN
  EXEC tSQLt.FakeTable 'dbo.accounts';          -- the fake ledger has no CHECK (amount <> 0) to hide the bug
  EXEC tSQLt.FakeTable 'dbo.ledger';
  INSERT INTO dbo.accounts (account_id, customer_id, kind, balance) VALUES (7, 1, N'savings', 100.00);

  EXEC tSQLt.ExpectException @ExpectedErrorNumber = 50020;
  EXEC dbo.withdraw @account_id = 7, @amount = 0;
END;
GO
-- Run 1: 3 test cases, 2 succeeded, 1 failed. tSQLt.Run then raises error 50000 with its summary line; sqlcmd
-- without -b goes on to the next batch, as a CI job with -b would not.
EXEC tSQLt.Run 'withdraw_tests';
GO
-- The fix: refuse an amount of 0 or less before anything else.
CREATE OR ALTER PROCEDURE dbo.withdraw
  @account_id int,
  @amount     decimal(14,2)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  IF @amount IS NULL OR @amount <= 0
    THROW 50020, N'The amount must be greater than 0.', 1;
  IF NOT EXISTS (SELECT 1 FROM dbo.accounts WHERE account_id = @account_id AND balance >= @amount)
    THROW 50021, N'Insufficient funds or no such account.', 1;
  BEGIN TRANSACTION;
  UPDATE dbo.accounts SET balance = balance - @amount
  WHERE account_id = @account_id AND balance >= @amount;
  IF @@ROWCOUNT = 0
    THROW 50021, N'Insufficient funds or no such account.', 1;
  INSERT INTO dbo.ledger (account_id, amount, posted_at, memo)
  VALUES (@account_id, -@amount, SYSDATETIME(), N'withdrawal');
  COMMIT TRANSACTION;
END;
GO
-- Run 2: 3 test cases, 3 succeeded. Then the class and the procedure are removed.
EXEC tSQLt.Run 'withdraw_tests';
GO
EXEC tSQLt.DropClass 'withdraw_tests';
DROP PROCEDURE IF EXISTS dbo.withdraw;
-- The real tables end as they began: each test ran in a transaction that tSQLt rolled back, fake tables included.
SELECT account_id, balance FROM dbo.accounts WHERE account_id = 1;   -- 1, 2500.00
GO

"""withdraw_tests.sql in the lab: sqlcmd runs it as sa in the database lab_tsqlt, which holds the practice schema and
tSQLt 1.0.8083.3529. Each test's duration changes from run to run and shows as <ms>."""

from mssql_session import Lab

with Lab(tsqlt=True) as lab:
    lab.vary(r"\|( *\d+)\|(?:Success|Failure|Error|Skipped)\|", "<ms>")   # each test's duration
    lab.sqlcmd("tests/withdraw_tests.sql", errors=1)

$ sqlcmd -S localhost -U sa -C -d lab_tsqlt -i tests/withdraw_tests.sql
.[test withdraw refuses an amount of zero] failed: (Failure) Expected an error to be raised.

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                               |Dur(ms)|Result |
+--+-----------------------------------------------------------------------------+-------+-------+
|1 |[withdraw_tests].[test withdraw lowers the balance and writes one ledger row]|   <ms>|Success|
|2 |[withdraw_tests].[test withdraw refuses more than the balance]               |   <ms>|Success|
|3 |[withdraw_tests].[test withdraw refuses an amount of zero]                   |   <ms>|Failure|
----------------------------------------------------------------------------------------
Msg 50000, Level 16, State 10, Server lab-mssql, Line 1
Test Case Summary: 3 test case(s) executed, 2 succeeded, 0 skipped, 1 failed, 0 errored.
----------------------------------------------------------------------------------------

+----------------------+
|Test Execution Summary|
+----------------------+

|No|Test Case Name                                                               |Dur(ms)|Result |
+--+-----------------------------------------------------------------------------+-------+-------+
|1 |[withdraw_tests].[test withdraw lowers the balance and writes one ledger row]|   <ms>|Success|
|2 |[withdraw_tests].[test withdraw refuses an amount of zero]                   |   <ms>|Success|
|3 |[withdraw_tests].[test withdraw refuses more than the balance]               |   <ms>|Success|
----------------------------------------------------------------------------------------
Test Case Summary: 3 test case(s) executed, 3 succeeded, 0 skipped, 0 failed, 0 errored.
----------------------------------------------------------------------------------------
account_id balance
---------- -------
         1 2500.00