Testing Stored Procedures and Triggers: pgTAP and SQL Unit Testing

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 install

Enable 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.sql

Output looks like standard TAP:

test/sql/test_order_total.sql .. ok
All tests successful.
Files=1, Tests=6,  0 wallclock secs
Result: PASS

pgTAP 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/*.sql

Conclusion

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.

Read more

Start now free