SQL 128 🛢️ Database Unit Testing Frameworks (pgTAP, tSQLt)

Database unit testing frameworks exist because the logic inside stored procedures, functions, and triggers is code, and code that is not tested accumulates defects silently. The challenge is that database code does not return values or throw exceptions in the way that application code does. A stored procedure communicates through result sets and output parameters. A function returns a table or a scalar. A trigger fires as a side effect of a statement. The testing framework must capture these outputs, compare them to expectations, and report pass or fail in a format that a developer or a CI pipeline can read. Two frameworks dominate their respective ecosystems: pgTAP for PostgreSQL and tSQLt for SQL Server. Both wrap each test in a transaction that is rolled back at the end, so tests leave no residue and can run repeatedly against the same database.

Key point: pgTAP emits output in the Test Anything Protocol (TAP) format, which means its results can be consumed by standard test harnesses. tSQLt groups tests into schemas called test classes, runs each test inside a transaction that is rolled back, and stores results in a table that can be queried or reported.

---

Why database unit testing frameworks exist

The transaction isolation problem. A database test that inserts a row and asserts the result must clean up after itself. If it does not, the next test sees the row and fails for the wrong reason. pgTAP and tSQLt both solve this by wrapping each test in a transaction and rolling it back at the end . The test’s changes are visible to the test and invisible to everything else. This is the foundation that makes database tests repeatable.

The dependency isolation problem. A test for a stored procedure that reads from a table should not depend on the actual contents of that table. tSQLt provides FakeTable to replace a real table with an empty one, so the test controls every row the procedure sees . pgTAP does not have a direct equivalent, but its transaction rollback and its ability to create temporary schemas allow similar isolation. Without isolation, tests break when the data changes, and a test that breaks for the wrong reason is worse than no test.

The assertion problem. A test must compare an actual result to an expected result. tSQLt provides AssertEqualsTable, which compares two tables row by row and reports the differences . pgTAP provides a large collection of assertion functions: has_table, has_column, col_is_pk, results_eq, throws_ok, and dozens more . The assertions are the vocabulary of the test; the framework is the mechanism that runs them and reports the result.

---

a. pgTAP: PostgreSQL Unit Testing

pgTAP is a PostgreSQL extension written in PL/pgSQL that provides TAP-emitting assertion functions. It is installed with CREATE EXTENSION pgtap after the package is installed on the server . A test is a SQL script that calls plan() to declare how many assertions it will make, runs the assertions, and calls finish() to report the result. The entire script runs inside a transaction that is rolled back at the end.

-- A pgTAP test for a table structure
BEGIN;
SELECT plan(3);

SELECT has_table('profiles');
SELECT has_column('profiles', 'id');
SELECT col_is_pk('profiles', 'id');

SELECT * FROM finish();
ROLLBACK;

The plan() function takes the number of assertions expected. If the test makes more or fewer assertions than planned, the TAP output reports the mismatch. This catches tests that exit early or that accidentally add assertions without updating the count. The finish() function emits the final TAP summary.

pgTAP’s assertion library covers more than table structure. results_eq() compares the output of a query to an expected result set . throws_ok() asserts that a statement raises a specific error code or message . lives_ok() asserts that a statement does not raise an error. policies_are() verifies the set of RLS policies on a table. function_returns() checks the return type of a function.

-- Test that a function returns the expected rows
BEGIN;
SELECT plan(1);

SELECT results_eq(
    'SELECT * FROM get_active_users()',
    $$VALUES (1, 'Alice'), (2, 'Bob')$$,
    'get_active_users should return active users'
);

SELECT * FROM finish();
ROLLBACK;

The TAP output is consumed by pg_prove, a command-line tool that runs multiple test scripts and summarizes the results . pg_prove makes pgTAP suitable for CI pipelines: a failing test produces a non-zero exit code, which fails the build.

---

b. tSQLt: SQL Server Unit Testing

tSQLt is a T-SQL framework installed into a SQL Server database. It creates a tSQLt schema that contains the framework’s procedures. Tests are organized into test classes, which are schemas created with tSQLt.NewTestClass . A test is a stored procedure whose name begins with test. When tSQLt.RunTestClass or tSQLt.RunAll executes, it finds every procedure matching that pattern, runs it inside a transaction, and rolls the transaction back .

-- Create a test class (schema)
EXEC tSQLt.NewTestClass 'testFinancialApp';

-- Create a test procedure
CREATE PROCEDURE testFinancialApp.[test that ConvertCurrency converts using given rate]
AS
BEGIN
    -- Assemble: set up the data the test needs
    DECLARE @actual MONEY;
    DECLARE @expected MONEY = 100.00;

    -- Act: execute the code under test
    EXEC FinancialApp.ConvertCurrency @amount = 100, @rate = 1.0, @result = @actual OUTPUT;

    -- Assert: compare actual to expected
    EXEC tSQLt.AssertEquals @expected, @actual;
END;

The FakeTable procedure is tSQLt’s mechanism for isolation. It renames the real table, creates an empty table with the same structure, and redirects the test to the empty table . The test inserts its own rows, runs the procedure, and asserts the result. When the transaction rolls back, the original table is restored.

-- Isolate the table dependency
EXEC tSQLt.FakeTable 'dbo.Orders';

-- Insert test data
INSERT INTO dbo.Orders (id, total) VALUES (1, 100.00);

-- Run the procedure and capture the result
-- Assert the result with AssertEqualsTable

AssertEqualsTable compares two tables and reports the rows that differ . This is the primary assertion for procedures that return result sets. For scalar values, AssertEquals compares two values. For expected errors, ExpectException declares the error the test expects.

---

c. Running Tests in CI and Reporting Results

Both frameworks are designed to be run from the command line. pgTAP uses pg_prove, which runs SQL scripts and parses the TAP output. tSQLt can be run from any T-SQL client, including sqlcmd, and its results are stored in the tSQLt.TestResult table . A CI step can query that table and fail the build if any test has a status other than “Success”.

# Run pgTAP tests from a directory
pg_prove --dbname mydb --username postgres tests/

-- Run tSQLt tests and query the results
EXEC tSQLt.RunAll;

SELECT * FROM tSQLt.TestResult WHERE Result <> 'Success';

The results table approach is more flexible for CI than parsing console output. A script can query the table, count failures, and exit with a non-zero code if any exist. The table also contains the test name, the class, the message, and the duration, which makes it suitable for reporting dashboards.

---

Complete Example Session

-- ============================================
-- PART 1: INSTALL pgTAP
-- ============================================
CREATE EXTENSION pgtap;


-- ============================================
-- PART 2: WRITE A TABLE STRUCTURE TEST
-- ============================================
BEGIN;
SELECT plan(3);
SELECT has_table('orders');
SELECT has_column('orders', 'id');
SELECT col_is_pk('orders', 'id');
SELECT * FROM finish();
ROLLBACK;


-- ============================================
-- PART 3: WRITE A FUNCTION TEST
-- ============================================
BEGIN;
SELECT plan(1);
SELECT results_eq(
    'SELECT * FROM get_order_total(1)',
    $$VALUES (250.00)$$,
    'get_order_total should return the correct total'
);
SELECT * FROM finish();
ROLLBACK;


-- ============================================
-- PART 4: TEST AN EXPECTED ERROR
-- ============================================
BEGIN;
SELECT plan(1);
SELECT throws_ok(
    'INSERT INTO orders (id) VALUES (1)',
    '23505',
    NULL,
    'Duplicate primary key should raise unique violation'
);
SELECT * FROM finish();
ROLLBACK;


-- ============================================
-- PART 5: RUN pgTAP TESTS FROM THE COMMAND LINE
-- ============================================
-- pg_prove --dbname mydb tests/


-- ============================================
-- PART 6: INSTALL tSQLt
-- ============================================
-- Download tSQLt from tsqlt.org
-- Execute PrepareServer.sql, then tSQLt.class.sql in the database


-- ============================================
-- PART 7: CREATE A tSQLt TEST CLASS
-- ============================================
EXEC tSQLt.NewTestClass 'testOrders';


-- ============================================
-- PART 8: WRITE A tSQLt TEST WITH FAKETABLE
-- ============================================
CREATE PROCEDURE testOrders.[test that GetOrderTotal sums line items]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.OrderLines';
    INSERT INTO dbo.OrderLines (order_id, price, quantity)
    VALUES (1, 10.00, 2), (1, 5.00, 1);

    DECLARE @actual MONEY;
    EXEC dbo.GetOrderTotal @order_id = 1, @total = @actual OUTPUT;

    EXEC tSQLt.AssertEquals 25.00, @actual;
END;


-- ============================================
-- PART 9: RUN tSQLt TESTS
-- ============================================
EXEC tSQLt.RunAll;

SELECT * FROM tSQLt.TestResult;


-- ============================================
-- PART 10: CI FAILURE CHECK
-- ============================================
-- If any test has Result <> 'Success', fail the build
SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result <> 'Success';

The ten parts trace the pgTAP installation, test writing, and command-line execution, then the tSQLt equivalent. Part 4 shows the error assertion that neither framework can do without: verifying that the code fails in the way it is supposed to fail.

---

Quick Reference

Framework Comparison

Aspect	pgTAP	tSQLt
Database	PostgreSQL	SQL Server
Installation	CREATE EXTENSION pgtap	Execute SQL scripts
Test grouping	SQL script files	Schemas (test classes)
Isolation	Transaction rollback	Transaction rollback
Table isolation	Temporary schemas	FakeTable
Output format	TAP	Results table
CI runner	pg_prove	sqlcmd + query results

pgTAP Assertions

Assertion	Purpose
has_table(name)	Table exists
has_column(table, column)	Column exists
col_is_pk(table, column)	Column is primary key
results_eq(query, expected)	Query returns expected rows
throws_ok(query, code, msg)	Query raises expected error
lives_ok(query)	Query does not raise
policies_are(schema, table, policies)	RLS policies match

tSQLt Procedures

Procedure	Purpose
NewTestClass(name)	Create a test class schema
FakeTable(name)	Isolate table dependency
AssertEquals(expected, actual)	Compare scalar values
AssertEqualsTable(expected, actual)	Compare result sets
ExpectException(proc, error)	Assert expected error
RunTestClass(name)	Run all tests in a class
RunAll	Run all tests in all classes

---

Best Practices

✅ Do This:

-- Declare the expected assertion count in pgTAP
SELECT plan(3);                                                       -- ✅

-- Use FakeTable to isolate dependencies in tSQLt
EXEC tSQLt.FakeTable 'dbo.Orders';                                    -- ✅

-- Test the error path, not just the success path
SELECT throws_ok('INSERT ...', '23505', NULL, 'Duplicate key');       -- ✅

-- Run tests in CI and fail the build on failure
SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result <> 'Success';      -- ✅

❌ Don’t Do This:

-- Test against production data without isolation
SELECT * FROM orders; -- unpredictable results                       -- ❌

-- Skip the plan() declaration in pgTAP
-- The framework cannot detect missing assertions                     -- ❌

-- Assume a procedure that returns no error is correct
-- A silent wrong result is worse than an error                       -- ❌

---

Common Pitfalls

Pitfall	Why It Happens	Fix
pgTAP test reports mismatch	plan() count wrong	Update plan or fix missing assertions
tSQLt test sees real data	FakeTable not called	Isolate dependencies before test
Tests pass locally, fail in CI	Data differences	Use FakeTable or transaction rollback
pgTAP not found	Extension not installed	CREATE EXTENSION pgtap
tSQLt cannot create class	Insufficient permissions	Grant db_ddladmin or install as admin

---

Real-World Examples

1. pgTAP Table Test

BEGIN; SELECT plan(1); SELECT has_table('orders'); SELECT * FROM finish(); ROLLBACK;

2. pgTAP Function Result Test

SELECT results_eq('SELECT * FROM get_total(1)', 'VALUES (100.00)');

3. pgTAP Error Test

SELECT throws_ok('INSERT INTO t (id) VALUES (1)', '23505', NULL);

4. tSQLt Create Class

EXEC tSQLt.NewTestClass 'testOrders';

5. tSQLt FakeTable

EXEC tSQLt.FakeTable 'dbo.Orders';

6. tSQLt AssertEquals

EXEC tSQLt.AssertEquals 100.00, @actual;

7. tSQLt AssertEqualsTable

EXEC tSQLt.AssertEqualsTable 'expected', 'actual';

8. Run pgTAP Tests

pg_prove --dbname mydb tests/

9. Run tSQLt Tests

EXEC tSQLt.RunAll;

10. CI Failure Check

SELECT COUNT(*) FROM tSQLt.TestResult WHERE Result <> 'Success';

---

Visual

pgTAP Test Lifecycle

┌──────────────────────────────────────────────────────────────┐
│  BEGIN;                                                      │
│         │                                                    │
│         ▼                                                    │
│  ┌─────────────────┐                                        │
│  │ SELECT plan(N)  │  Declare assertion count               │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ Assertions      │  has_table, results_eq, throws_ok      │
│  │                 │  Each emits TAP output                 │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ SELECT * FROM   │  Emit final TAP summary                │
│  │ finish()        │                                        │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ ROLLBACK;       │  Clean up all test changes             │
│  └─────────────────┘                                        │
│                                                              │
│  The transaction wraps the entire test.                      │
│  No residue remains after rollback.                          │
│                                                              │
└──────────────────────────────────────────────────────────────┘

tSQLt Test Execution

┌──────────────────────────────────────────────────────────────┐
│  EXEC tSQLt.RunTestClass 'testOrders';                       │
│         │                                                    │
│         ▼                                                    │
│  ┌─────────────────┐                                        │
│  │ Find procedures │  Names starting with "test"            │
│  │ in schema       │                                        │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ For each test:  │                                        │
│  │ 1. BEGIN        │                                        │
│  │ 2. SetUp        │  If exists                             │
│  │ 3. Test proc    │                                        │
│  │ 4. ROLLBACK     │                                        │
│  │ 5. Record result│  tSQLt.TestResult                      │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ Results table   │  Query for CI failure check            │
│  └─────────────────┘                                        │
│                                                              │
│  Each test is a transaction.                                 │
│  Rollback restores the original state.                       │
│                                                              │
└──────────────────────────────────────────────────────────────┘

FakeTable Isolation

┌──────────────────────────────────────────────────────────────┐
│  BEFORE FakeTable                                            │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ dbo.Orders: real data (unpredictable)                   ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ EXEC tSQLt.FakeTable 'dbo.Orders';                      ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  AFTER FakeTable                                             │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ dbo.Orders: empty table with same structure             ││
│  │ Test inserts its own rows                               ││
│  └─────────────────────────────────────────────────────────┘│
│                                                              │
│  Test runs against known data.                               │
│  Rollback restores the original table.                       │
│                                                              │
└──────────────────────────────────────────────────────────────┘

CI Integration

┌──────────────────────────────────────────────────────────────┐
│  CI PIPELINE                                                 │
│                                                              │
│  ┌─────────────────┐                                        │
│  │ Deploy schema   │                                        │
│  │ to test DB      │                                        │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ Run tests       │  pg_prove or tSQLt.RunAll              │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ Check results   │  Exit code or results table            │
│  └────────┬────────┘                                        │
│           ▼                                                  │
│  ┌─────────────────┐                                        │
│  │ Fail build if   │                                        │
│  │ any test failed │                                        │
│  └─────────────────┘                                        │
│                                                              │
│  Database code is tested like application code.              │
│  Defects are caught before deployment.                       │
│                                                              │
└──────────────────────────────────────────────────────────────┘

---

Summary

Item	Value
pgTAP	PostgreSQL unit testing extension
pgTAP output	TAP (Test Anything Protocol)
pgTAP plan	SELECT plan(N) declares assertion count
pgTAP rollback	ROLLBACK at end of test
tSQLt	SQL Server unit testing framework
tSQLt test class	Schema created with NewTestClass
tSQLt isolation	FakeTable replaces real table
tSQLt assertion	AssertEquals, AssertEqualsTable
CI integration	pg_prove or query tSQLt.TestResult

Key takeaways:

• Both frameworks wrap each test in a transaction that is rolled back. pgTAP tests end with ROLLBACK, and tSQLt tests are executed inside a transaction that is rolled back after the test procedure completes . This is the mechanism that makes database tests repeatable and isolated.
• pgTAP declares the expected assertion count. The plan(N) call tells the framework how many assertions the test will make. If the test makes a different number, the TAP output reports the mismatch. This catches tests that exit early or that add assertions without updating the count .
• tSQLt groups tests into schemas. A test class is a schema, and a test is a procedure whose name starts with test. RunTestClass finds the procedures, runs them, and records the results in tSQLt.TestResult .
• FakeTable is the isolation mechanism in tSQLt. It replaces a real table with an empty table of the same structure, so the test controls every row the procedure sees. pgTAP relies on transaction rollback and temporary schemas for similar isolation .
• Both frameworks are designed for CI. pgTAP emits TAP, which pg_prove parses into a summary and exit code. tSQLt stores results in a table that a CI step can query to determine whether to fail the build.

Remember: Database unit testing is not about testing the database engine; it is about testing the code that runs inside it. A stored procedure that calculates a total, a function that validates an input, a trigger that writes an audit row—these are code, and they can be wrong. The frameworks provide the assertions, the isolation, and the reporting that make testing them practical. pgTAP is the PostgreSQL choice, emitting TAP output that integrates with standard test tooling. tSQLt is the SQL Server choice, using schemas as test classes and tables as test results. Both wrap each test in a transaction that rolls back, so the test database is never left in a modified state. The choice between them is determined by the database engine, not by a preference for one framework’s features.

---

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!