A Guide to Database Testing with pgTAP

I want to start by thanking my colleagues at End Point Dev, where I learned much of what I know about database testing through study sessions and internal knowledge sharing. This article wouldn't be possible without their guidance and expertise.

Introduction

You know what's funny? I never thought I'd be writing about database testing. But here I am, and I've got a story to tell you about why it matters.

Think about tuning a piano. I remember watching a piano tuner work once - it was fascinating. They didn't just adjust each note in isolation. Instead, they tuned each note in relation to several previously tuned notes, testing different combinations to ensure harmony. It's like a dance, really - each note affects the others, and the tuner needs to find the perfect balance.

That's exactly how database testing works. You can't just test each component in isolation - you need to see how they work together, just like those piano notes. When I first started working with pgTAP, I was amazed at how it helped me catch issues that would have been nearly impossible to find through manual testing.

Setting Up pgTAP

Let me walk you through setting up pgTAP. I've done this enough times now that I can tell you exactly what you need:

Installation Methods

First, you'll need to install pgTAP. Here's how to do it on different systems:

sudo apt-get install postgresql-server-dev-<version>
sudo apt-get install postgresql-<version>-pgtap

For macOS (when I'm working on my Mac):

brew install postgresql
brew install pgtap

For RedHat/CentOS (when working with enterprise systems):

sudo yum install postgresql-server-devel
sudo yum install postgresql-<version>-pgtap

If you're feeling adventurous (or if the package manager versions aren't cutting it), you can build from source:

git clone https://github.com/pgtap/pgtap.git
cd pgtap
make && make install

Then, the magic happens - create the extension in your database:

CREATE EXTENSION pgtap;

Basic Testing Concepts

When I first started with pgTAP, I was overwhelmed by all the functions available. Let me break down the basics that I use most often:

Test Planning

Every test file needs a plan. It's like telling pgTAP "Hey, I'm going to run 3 tests, so don't freak out when you see them all":

SELECT plan(3);  -- We'll run 3 tests

Basic Assertions

The ok() function is your best friend. It's simple but powerful:

SELECT ok(1 + 1 = 2, 'Basic math works');

When you need to compare values, is() is your go-to:

SELECT is(
    (SELECT COUNT(*) FROM users),
    0,
    'Users table should be empty'
);

Testing Database Objects

Here's where it gets interesting. You can test if your database objects exist and are set up correctly:

-- Test if a table exists
SELECT has_table('public', 'users', 'Users table should exist');

-- Test if a column exists
SELECT has_column('public', 'users', 'email', 'Users table should have email column');

-- Test if a function exists
SELECT has_function(
    'public',
    'calculate_total',
    ARRAY['integer', 'numeric'],
    'Calculate total function should exist'
);

Writing Complex Tests

Now we're getting to the good stuff. I've learned some hard lessons about testing complex scenarios. Let me share what works for me:

Testing Functions with Side Effects

This one bit me hard early on. Always, always use transactions when testing functions that modify data:

BEGIN;

-- Create test data
INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');

-- Test the function
SELECT is(
    (SELECT COUNT(*) FROM users WHERE email = 'test@example.com'),
    1,
    'User should be created'
);

ROLLBACK;

Testing Complex User-Defined Functions

For complex functions, I've developed a systematic approach that's saved me countless hours:

BEGIN;

-- Create a temporary table to track test cases
CREATE TEMPORARY TABLE test_cases (
    id SERIAL PRIMARY KEY,
    test_name TEXT,
    input_data JSONB,
    expected_result JSONB
);

-- Insert test cases
INSERT INTO test_cases (test_name, input_data, expected_result) VALUES
    ('Basic case', '{"param1": "value1"}', '{"result": "expected1"}'),
    ('Edge case', '{"param1": ""}', '{"result": "expected2"}'),
    ('Error case', '{"param1": null}', '{"error": "expected error"}');

-- Test each case
DO $$
DECLARE
    test_case RECORD;
BEGIN
    FOR test_case IN SELECT * FROM test_cases LOOP
        -- Execute function and compare results
        SELECT is(
            complex_function(test_case.input_data),
            test_case.expected_result,
            test_case.test_name || ' should work as expected'
        );
    END LOOP;
END $$;

ROLLBACK;

Testing Triggers

Triggers can be tricky. Here's a pattern I've refined over time:

BEGIN;

-- Create a test table
CREATE TEMPORARY TABLE test_audit (
    id SERIAL PRIMARY KEY,
    action TEXT,
    table_name TEXT,
    changed_at TIMESTAMP
);

-- Create and test the trigger
CREATE OR REPLACE FUNCTION audit_trigger() RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO test_audit (action, table_name, changed_at)
    VALUES (TG_OP, TG_TABLE_NAME, NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER test_audit_trigger
    AFTER INSERT ON users
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

-- Test the trigger
INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');

SELECT is(
    (SELECT COUNT(*) FROM test_audit),
    1,
    'Audit trigger should fire on insert'
);

ROLLBACK;

Testing Complex Business Logic

For business logic, I've found that testing multiple conditions is crucial:

BEGIN;

-- Test data setup
CREATE TEMPORARY TABLE test_orders (
    id SERIAL PRIMARY KEY,
    amount DECIMAL(10,2),
    status TEXT
);

-- Test various scenarios
INSERT INTO test_orders (amount, status) VALUES (100.00, 'pending');
INSERT INTO test_orders (amount, status) VALUES (1000.00, 'pending');

-- Test business rules
SELECT is(
    (SELECT COUNT(*) FROM test_orders WHERE amount > 500 AND status = 'pending'),
    1,
    'Should have one high-value pending order'
);

ROLLBACK;

Best Practices

I've learned these lessons the hard way. Here's what I always keep in mind:

1. Use Transactions: Always wrap your tests in transactions. Trust me, you'll thank me later:

BEGIN;
-- Your tests here
ROLLBACK;

2. Organize Test Files: I use this structure religiously now:

tests/
  ├── 00-setup.sql
  ├── 01-basic_tests.sql
  ├── 02-function_tests.sql
  └── 03-trigger_tests.sql

3. Test Data Management: Temporary tables are your friend:

CREATE TEMPORARY TABLE test_data (
    id SERIAL PRIMARY KEY,
    test_value TEXT
);

4. Meaningful Test Names: Write clear descriptions. Your future self will thank you:

SELECT ok(
    user_is_active('test@example.com'),
    'Active user should be recognized as active'
);

Running Tests

I use pg_prove all the time. Here are my favorite commands:

# Run a single test file
pg_prove -d your_database tests/01-basic_tests.sql

# Run all test files
pg_prove -d your_database tests/*.sql

# Run tests with verbose output (when I need to debug)
pg_prove -d your_database -v tests/*.sql

Common Pitfalls to Avoid

I've fallen into these traps more times than I'd like to admit:

1. Forgetting to Plan: Always specify the number of tests you'll run
2. Not Using Transactions: Always wrap tests in transactions
3. Testing Real Data: Never test against production data (learned this one the hard way!)
4. Incomplete Cleanup: Ensure all test data is properly cleaned up
5. Missing Error Cases: Test both success and failure scenarios
6. Insufficient Test Coverage: Test all possible cases and edge cases
7. Poor Test Organization: Keep tests focused and well-organized

Advanced Testing Patterns

Here are some patterns I've developed over time that have saved me countless hours:

Testing Database Constraints

BEGIN;

-- Test foreign key constraint
SELECT throws_ok(
    'INSERT INTO orders (user_id) VALUES (999)',
    '23503',
    'insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"'
);

ROLLBACK;

Testing Custom Types

BEGIN;

-- Test custom type constraints
SELECT throws_ok(
    'SELECT ''invalid@email''::email',
    '22P02',
    'invalid input syntax for type email'
);

ROLLBACK;

Testing Complex Data Relationships

BEGIN;

-- Create test tables with relationships
CREATE TEMPORARY TABLE test_customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TEMPORARY TABLE test_orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES test_customers(id),
    amount DECIMAL(10,2)
);

-- Test data integrity
INSERT INTO test_customers (name) VALUES ('Test Customer');
INSERT INTO test_orders (customer_id, amount) VALUES (1, 100.00);

-- Test relationship constraints
SELECT throws_ok(
    'INSERT INTO test_orders (customer_id, amount) VALUES (999, 100.00)',
    '23503',
    'insert or update on table "test_orders" violates foreign key constraint'
);

ROLLBACK;

Conclusion

pgTAP has become an essential part of my database development workflow. It's helped me catch countless bugs before they made it to production and has given me confidence in my database changes.

Remember, good database testing is about more than just checking if things work - it's about ensuring your database behaves correctly in all scenarios, including edge cases and error conditions. Just like a well-tuned piano, every component needs to work in harmony with the others.

Resources

• pgTAP Documentation
• Test Anything Protocol