Testing Stored Procedures and Triggers: pgTAP and SQL Unit Testing
Stored procedures, triggers, and views are first-class code. They contain business logic, enforce invariants, and can fail in subtle ways that corrupt your data silently. Yet most teams have no automated tests for their database-level code — the stored procedures get tested manually once when they're written, then survive through inertia until someone changes a table and the procedure quietly starts producing wrong results.
This post covers practical approaches to testing database-level code in PostgreSQL: pgTAP for native SQL unit testing, Jest with raw SQL for procedure testing from the application layer, and patterns for testing triggers across all write operations.
Why Database Logic Deserves Tests
The case for testing application code is obvious, but database logic gets treated differently. Consider what happens without tests:
A stored procedure that calculates order totals includes a discount logic bug. The bug produces totals that are slightly wrong — wrong enough to matter for reconciliation, but not so wrong that orders obviously fail. The bug was introduced six months ago when a developer added a new discount tier. Without tests, there's no automated check that the procedure produces correct output. The bug lives until an accountant's monthly reconciliation fails.
Triggers are even more vulnerable. A trigger that maintains a last_modified column, tracks changes to an audit log, or enforces a complex constraint runs on every INSERT, UPDATE, and DELETE. If it fires on the wrong events, doesn't handle NULL values correctly, or conflicts with another trigger, the results are often non-obvious. Tests that explicitly exercise each trigger scenario catch these issues immediately.
pgTAP: Native PostgreSQL Unit Testing
pgTAP is a TAP (Test Anything Protocol) testing framework for PostgreSQL. Tests are written in pure SQL and run inside the database, which means they have access to everything — internal functions, temporary tables, transaction state, everything.
Installing pgTAP
# On Ubuntu/Debian
sudo apt-get install postgresql-16-pgtap
# On macOS with Homebrew
brew install pgtap
# Or install from source
git clone https://github.com/theory/pgtap.git
cd pgtap
make && sudo make installEnable it in your test database:
CREATE EXTENSION IF NOT EXISTS pgtap;Your First pgTAP Test
-- test/sql/test_order_total.sql
BEGIN;
SELECT plan(6); -- Tell pgTAP how many tests to expect
-- Test 1: Basic total calculation
SELECT is(
calculate_order_total(
ARRAY[ROW(1, 'Widget', 2, 9.99::numeric)::order_item_type]
),
19.98::numeric,
'total is quantity × price'
);
-- Test 2: Multiple items
SELECT is(
calculate_order_total(
ARRAY[
ROW(1, 'Widget', 2, 9.99::numeric)::order_item_type,
ROW(2, 'Gadget', 1, 24.99::numeric)::order_item_type
]
),
44.97::numeric,
'total sums multiple items correctly'
);
-- Test 3: Single item
SELECT is(
calculate_order_total(
ARRAY[ROW(1, 'Solo', 1, 99.99::numeric)::order_item_type]
),
99.99::numeric,
'single item total equals item price'
);
-- Test 4: Zero quantity (edge case)
SELECT throws_ok(
$$ SELECT calculate_order_total(
ARRAY[ROW(1, 'Zero', 0, 9.99::numeric)::order_item_type]
) $$,
'P0001',
'quantity must be at least 1',
'rejects zero quantity'
);
-- Test 5: Negative price rejected
SELECT throws_ok(
$$ SELECT calculate_order_total(
ARRAY[ROW(1, 'Neg', 1, -5.00::numeric)::order_item_type]
) $$,
'P0001',
'price must be positive',
'rejects negative price'
);
-- Test 6: Empty array returns zero
SELECT is(
calculate_order_total(ARRAY[]::order_item_type[]),
0::numeric,
'empty order total is zero'
);
SELECT * FROM finish();
ROLLBACK;Run it:
pg_prove -U postgres -d testdb test/sql/test_order_total.sqlOutput looks like standard TAP:
test/sql/test_order_total.sql .. ok
All tests successful.
Files=1, Tests=6, 0 wallclock secs
Result: PASSpgTAP Assertion Reference
pgTAP provides a rich set of assertions. The most useful ones:
-- Equality
SELECT is(actual_value, expected_value, 'test description');
SELECT isnt(actual_value, unexpected_value, 'should not be equal');
-- Throws / doesn't throw
SELECT throws_ok($$ SQL $$, 'SQLSTATE', 'message', 'description');
SELECT lives_ok($$ SQL $$, 'should not throw');
-- NULL checks
SELECT is_empty($$ SELECT FROM table WHERE false $$, 'result is empty');
SELECT results_eq(
$$ SELECT col FROM table ORDER BY id $$,
ARRAY[1, 2, 3],
'result matches expected array'
);
-- Schema assertions
SELECT has_table('users', 'users table exists');
SELECT has_column('users', 'email', 'email column exists');
SELECT col_is_pk('users', 'id', 'id is primary key');
SELECT col_not_null('users', 'email', 'email is not null');
SELECT col_is_unique('users', ARRAY['email'], 'email is unique');
SELECT has_index('users', 'users_email_key', 'email index exists');
SELECT has_function('calculate_order_total', 'function exists');The schema assertions are excellent for the schema testing use case — they read like documentation.
Testing Stored Procedures with Jest and Raw SQL
For teams who prefer keeping all tests in JavaScript, you can test stored procedures from Jest using the pg driver:
// test/procedures/calculateDiscount.test.js
const { Client } = require('pg');
let client;
beforeAll(async () => {
client = new Client({ connectionString: process.env.TEST_DATABASE_URL });
await client.connect();
});
afterAll(async () => {
await client.end();
});
beforeEach(async () => {
await client.query('BEGIN');
});
afterEach(async () => {
await client.query('ROLLBACK');
});
async function callCalculateDiscount(subtotal, tier) {
const result = await client.query(
'SELECT calculate_discount($1, $2) AS discount',
[subtotal, tier]
);
return parseFloat(result.rows[0].discount);
}
describe('calculate_discount procedure', () => {
test('no discount for silver tier under $100', async () => {
const discount = await callCalculateDiscount(99.99, 'silver');
expect(discount).toBe(0);
});
test('10% discount for silver tier over $100', async () => {
const discount = await callCalculateDiscount(150.00, 'silver');
expect(discount).toBeCloseTo(15.00);
});
test('15% discount for gold tier', async () => {
const discount = await callCalculateDiscount(100.00, 'gold');
expect(discount).toBeCloseTo(15.00);
});
test('25% discount for platinum tier', async () => {
const discount = await callCalculateDiscount(200.00, 'platinum');
expect(discount).toBeCloseTo(50.00);
});
test('discount cannot exceed subtotal', async () => {
const discount = await callCalculateDiscount(10.00, 'platinum');
expect(discount).toBeLessThanOrEqual(10.00);
});
test('throws on invalid tier', async () => {
await expect(
callCalculateDiscount(100.00, 'diamond')
).rejects.toThrow(/invalid tier/);
});
});The BEGIN/ROLLBACK wrapping each test means every test starts with a clean slate and any changes (including procedure side effects) are automatically rolled back.
Trigger Testing: INSERT, UPDATE, DELETE Scenarios
Triggers are the hardest database code to test because they fire implicitly. The goal is to test each trigger event and verify the expected side effect:
-- The trigger we're testing
CREATE OR REPLACE FUNCTION update_order_modified()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER orders_update_modified
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_order_modified();// test/triggers/orderModified.test.js
test('trigger updates updated_at on order status change', async () => {
// Insert an order with a known updated_at
const insertResult = await client.query(`
INSERT INTO orders (user_id, total, status, created_at, updated_at)
VALUES (1, 99.99, 'pending', NOW(), '2020-01-01 00:00:00')
RETURNING id, updated_at
`);
const orderId = insertResult.rows[0].id;
const originalUpdatedAt = insertResult.rows[0].updated_at;
// Small delay to ensure timestamp difference
await new Promise(resolve => setTimeout(resolve, 10));
// Update the order
await client.query(`UPDATE orders SET status = 'confirmed' WHERE id = $1`, [orderId]);
// Verify updated_at changed
const selectResult = await client.query(`SELECT updated_at FROM orders WHERE id = $1`, [orderId]);
const newUpdatedAt = selectResult.rows[0].updated_at;
expect(new Date(newUpdatedAt).getTime())
.toBeGreaterThan(new Date(originalUpdatedAt).getTime());
});
test('trigger does NOT fire on INSERT (BEFORE UPDATE only)', async () => {
// This verifies the trigger is scoped correctly to UPDATE events only
const result = await client.query(`
INSERT INTO orders (user_id, total, status, created_at, updated_at)
VALUES (1, 99.99, 'pending', NOW(), '2020-01-01 00:00:00')
RETURNING updated_at
`);
// updated_at should remain as inserted, not overwritten by trigger
expect(result.rows[0].updated_at.toISOString()).toContain('2020-01-01');
});Testing Audit Log Triggers
Audit triggers are particularly important to test thoroughly because they're the paper trail:
// test/triggers/auditLog.test.js
describe('users audit trigger', () => {
test('logs INSERT to audit_log', async () => {
const result = await client.query(`
INSERT INTO users (email, name, password_hash)
VALUES ('audit@example.com', 'Audit User', 'hash')
RETURNING id
`);
const userId = result.rows[0].id;
const auditResult = await client.query(`
SELECT action, table_name, record_id, changed_by
FROM audit_log
WHERE table_name = 'users' AND record_id = $1
ORDER BY created_at DESC
LIMIT 1
`, [userId.toString()]);
expect(auditResult.rows).toHaveLength(1);
expect(auditResult.rows[0].action).toBe('INSERT');
});
test('logs UPDATE with before/after values', async () => {
const insertResult = await client.query(`
INSERT INTO users (email, name, password_hash)
VALUES ('before@example.com', 'Before Name', 'hash')
RETURNING id
`);
const userId = insertResult.rows[0].id;
await client.query(`
UPDATE users SET name = 'After Name' WHERE id = $1
`, [userId]);
const auditResult = await client.query(`
SELECT action, old_data, new_data
FROM audit_log
WHERE table_name = 'users' AND record_id = $1 AND action = 'UPDATE'
`, [userId.toString()]);
expect(auditResult.rows).toHaveLength(1);
expect(auditResult.rows[0].old_data.name).toBe('Before Name');
expect(auditResult.rows[0].new_data.name).toBe('After Name');
});
test('logs DELETE with final state', async () => {
const insertResult = await client.query(`
INSERT INTO users (email, name, password_hash)
VALUES ('delete@example.com', 'To Delete', 'hash')
RETURNING id
`);
const userId = insertResult.rows[0].id;
await client.query(`DELETE FROM users WHERE id = $1`, [userId]);
const auditResult = await client.query(`
SELECT action, old_data
FROM audit_log
WHERE table_name = 'users' AND record_id = $1 AND action = 'DELETE'
`, [userId.toString()]);
expect(auditResult.rows).toHaveLength(1);
expect(auditResult.rows[0].old_data.email).toBe('delete@example.com');
});
});Testing Views and Materialized Views
Views are often untested, but they can contain complex logic — joins, aggregations, filtering — that breaks when underlying tables change:
// test/views/activeCustomers.test.js
describe('active_customers view', () => {
beforeEach(async () => {
await client.query('BEGIN');
// Create test users and orders
await client.query(`
INSERT INTO users (id, email, name, password_hash, status) VALUES
(1001, 'active1@test.com', 'Active One', 'h', 'active'),
(1002, 'active2@test.com', 'Active Two', 'h', 'active'),
(1003, 'inactive@test.com', 'Inactive', 'h', 'inactive'),
(1004, 'noorders@test.com', 'No Orders', 'h', 'active')
`);
// Create orders (1001 and 1002 have orders in last 90 days, 1004 has no orders)
await client.query(`
INSERT INTO orders (user_id, total, status, created_at) VALUES
(1001, 50.00, 'delivered', NOW() - INTERVAL '30 days'),
(1002, 75.00, 'delivered', NOW() - INTERVAL '60 days'),
(1001, 25.00, 'delivered', NOW() - INTERVAL '10 days')
`);
});
afterEach(async () => {
await client.query('ROLLBACK');
});
test('includes users with recent orders', async () => {
const result = await client.query(`
SELECT user_id FROM active_customers
WHERE user_id IN (1001, 1002, 1003, 1004)
ORDER BY user_id
`);
const ids = result.rows.map(r => r.user_id);
expect(ids).toContain(1001);
expect(ids).toContain(1002);
});
test('excludes inactive users', async () => {
const result = await client.query(`SELECT user_id FROM active_customers WHERE user_id = 1003`);
expect(result.rows).toHaveLength(0);
});
test('excludes users with no orders', async () => {
const result = await client.query(`SELECT user_id FROM active_customers WHERE user_id = 1004`);
expect(result.rows).toHaveLength(0);
});
test('shows correct order count per customer', async () => {
const result = await client.query(`
SELECT order_count FROM active_customers WHERE user_id = 1001
`);
expect(result.rows[0].order_count).toBe(2);
});
});For materialized views, you additionally need to test the refresh:
test('materialized view refreshes with new data', async () => {
// Insert new order
await client.query(`
INSERT INTO orders (user_id, total, status, created_at)
VALUES (1005, 100.00, 'delivered', NOW())
`);
// Before refresh, new order shouldn't appear
const beforeRefresh = await client.query(`
SELECT * FROM customer_summary_mv WHERE user_id = 1005
`);
expect(beforeRefresh.rows).toHaveLength(0);
// Refresh the materialized view
await client.query(`REFRESH MATERIALIZED VIEW customer_summary_mv`);
// After refresh, data appears
const afterRefresh = await client.query(`
SELECT * FROM customer_summary_mv WHERE user_id = 1005
`);
expect(afterRefresh.rows).toHaveLength(1);
expect(parseFloat(afterRefresh.rows[0].total_revenue)).toBe(100.00);
});Running pgTAP Tests in CI
Integrate pgTAP into your CI pipeline with pg_prove:
# .github/workflows/database-tests.yml
name: Database Tests
on: [push, pull_request]
jobs:
pgtap:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_PASSWORD: testpass
POSTGRES_DB: testdb
ports:
- 5432:5432
options: >-
--health-cmd pg_isready
--health-interval 10s
--health-timeout 5s
--health-retries 5
steps:
- uses: actions/checkout@v4
- name: Install pgTAP
run: |
sudo apt-get install -y postgresql-16-pgtap
- name: Setup schema
run: |
PGPASSWORD=testpass psql -h localhost -U postgres -d testdb -f schema/schema.sql
- name: Enable pgTAP
run: |
PGPASSWORD=testpass psql -h localhost -U postgres -d testdb -c "CREATE EXTENSION pgtap"
- name: Run pgTAP tests
run: |
PGPASSWORD=testpass pg_prove \
--host localhost \
--username postgres \
--dbname testdb \
test/sql/*.sqlConclusion
Database-level code — stored procedures, triggers, views — deserves the same testing discipline as application code. pgTAP provides a native SQL testing framework with an excellent schema assertion library that doubles as documentation. For teams who prefer JavaScript test suites, Jest with raw SQL queries and transaction rollback cleanup works well for procedure and trigger tests.
The key habits to develop: test every trigger event explicitly (INSERT, UPDATE, DELETE), test procedures at their boundaries (nulls, empty inputs, overflow values), and keep tests isolated with transaction rollback so they never interfere with each other. Once the pattern is established, writing database tests becomes as natural as writing unit tests for application code.