Testing Exceptions and Aborted Transactions
Handle PostgreSQL's transaction abort behavior when testing operations that should fail. This is essential for security testing where you verify that unauthorized operations are rejected.
When to Apply
Use this skill when:
-
Testing RLS policy violations (user can't access other users' data)
-
Testing constraint violations (unique, foreign key, check)
-
Testing permission denied errors
-
Testing any operation that should throw an error
-
Verifying database state after a failed operation
The Problem
When PostgreSQL encounters an error inside a transaction, it aborts the entire transaction. The connection rejects all further commands until you explicitly end the transaction:
current transaction is aborted, commands ignored until end of transaction block
This breaks naive exception testing:
// THIS WILL FAIL it('users cannot insert pets for other users', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': bob });
await expect(
db.query(INSERT INTO pets (name, owner_id) VALUES ('Fake', $1), [alice])
).rejects.toThrow(/violates row-level security/);
// ERROR: transaction is aborted, this query fails!
const count = await db.query(SELECT COUNT(*) FROM pets WHERE owner_id = $1, [alice]);
});
The Solution: Savepoints
Create a savepoint before the failing operation, then roll back to it:
it('users cannot insert pets for other users', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': bob });
// 1. Create savepoint before expected failure await db.savepoint('insert_attempt');
// 2. Test the operation that should fail
await expect(
db.query(INSERT INTO pets (name, owner_id) VALUES ('Fake', $1), [alice])
).rejects.toThrow(/violates row-level security/);
// 3. Roll back to clear the error state await db.rollback('insert_attempt');
// 4. Continue with verification queries
const count = await db.query(SELECT COUNT(*) FROM pets WHERE owner_id = $1, [alice]);
expect(parseInt(count.rows[0].count)).toBe(2);
});
Pattern Template
// 1. Create savepoint await db.savepoint('my_savepoint_name');
// 2. Execute operation that should fail
await expect(
db.query(...)
).rejects.toThrow(/expected error pattern/);
// 3. Roll back to savepoint await db.rollback('my_savepoint_name');
// 4. Continue with additional queries
const result = await db.query(...);
Common Scenarios
RLS Policy Violations
it('users cannot modify other users data', async () => { db.setContext({ role: 'authenticated', 'jwt.claims.user_id': bob });
await db.savepoint('update_attempt');
await expect(
db.query(UPDATE items SET name = 'stolen' WHERE owner_id = $1, [alice])
).rejects.toThrow(/violates row-level security/);
await db.rollback('update_attempt');
// Verify data unchanged
db.setContext({ role: 'authenticated', 'jwt.claims.user_id': alice });
const item = await db.query(SELECT name FROM items WHERE owner_id = $1, [alice]);
expect(item.rows[0].name).toBe('original');
});
Permission Denied
it('anonymous users cannot modify data', async () => { db.setContext({ role: 'anonymous' });
await db.savepoint('anon_insert');
await expect(
db.query(INSERT INTO users (name) VALUES ('hacker'))
).rejects.toThrow(/permission denied/);
await db.rollback('anon_insert');
await db.savepoint('anon_update');
await expect(
db.query(UPDATE users SET name = 'hacked')
).rejects.toThrow(/permission denied/);
await db.rollback('anon_update');
await db.savepoint('anon_delete');
await expect(
db.query(DELETE FROM users)
).rejects.toThrow(/permission denied/);
await db.rollback('anon_delete');
});
Constraint Violations
it('rejects duplicate emails', async () => {
await db.query(INSERT INTO users (email) VALUES ('test@example.com'));
await db.savepoint('duplicate_email');
await expect(
db.query(INSERT INTO users (email) VALUES ('test@example.com'))
).rejects.toThrow(/duplicate key value violates unique constraint/);
await db.rollback('duplicate_email');
// Verify only one user exists
const count = await db.query(SELECT COUNT(*) FROM users);
expect(parseInt(count.rows[0].count)).toBe(1);
});
PLPGSQL Function Validation
describe('plpgsql_expr', () => {
it('rejects NULL query', async () => {
await db.savepoint('null_query');
await expect(
db.any(SELECT my_function(NULL))
).rejects.toThrow('query cannot be NULL');
await db.rollback('null_query');
});
it('rejects invalid input', async () => {
await db.savepoint('invalid_input');
await expect(
db.any(SELECT my_function('{"invalid": true}'::jsonb))
).rejects.toThrow('invalid input format');
await db.rollback('invalid_input');
});
});
Multiple Failure Tests in Sequence
it('validates all input constraints', async () => {
// Test 1: null name
await db.savepoint('null_name');
await expect(
db.query(INSERT INTO products (name, price) VALUES (NULL, 10))
).rejects.toThrow(/null value in column "name"/);
await db.rollback('null_name');
// Test 2: negative price
await db.savepoint('negative_price');
await expect(
db.query(INSERT INTO products (name, price) VALUES ('item', -5))
).rejects.toThrow(/violates check constraint/);
await db.rollback('negative_price');
// Test 3: valid insert works
await db.query(INSERT INTO products (name, price) VALUES ('item', 10));
const result = await db.query(SELECT * FROM products);
expect(result.rows).toHaveLength(1);
});
Key Rules
-
Always use unique savepoint names - Avoid conflicts between tests
-
Roll back immediately after the expected failure - Before any other queries
-
Use descriptive savepoint names - Makes debugging easier
-
Each failure needs its own savepoint - Can't reuse savepoints after rollback
Why This Matters
Without savepoints, you cannot verify database state after a failed operation. That verification is often the most important part of a security test - confirming that the malicious operation had no effect.
References
-
Related skill: pgpm-testing for general test setup
-
Related skill: pgpm-env for environment configuration