Transaction Examples

These examples demonstrate transaction patterns with proper setup and teardown.

Referenced by: best-practices.md, patterns.md

Example 1: Simple Transaction

import { withClient } from '@/lib/db';
import { createResource } from '@db/graph';

const result = await withClient(
    {
        setup: async (client) => {
            await client.query('BEGIN');
        },
        teardown: async (client, error) => {
            if (error) {
                await client.query('ROLLBACK');
            } else {
                await client.query('COMMIT');
            }
        }
    },
    async (client) => {
        const id1 = await createResource(client, {
            p_type_namespace: 'test.type',
            p_external_id: 'item-1'
        });
        const id2 = await createResource(client, {
            p_type_namespace: 'test.type',
            p_external_id: 'item-2'
        });
        return { id1, id2 };
    }
);

Example 2: Transaction with Savepoints

import { withClient } from '@/lib/db';
import { createResource } from '@db/graph';
import { logAction } from '@db/audit';

const result = await withClient(async (client) => {
    await client.query('BEGIN');
    
    try {
        // Main operation
        const id = await createResource(client, {
            p_type_namespace: 'doc.document',
            p_external_id: 'doc-1'
        });
        
        // Savepoint for optional audit
        await client.query('SAVEPOINT sp1');
        try {
            await logAction(client, {
                p_action: 'created',
                p_resource_id: id
            });
        } catch (auditError) {
            await client.query('ROLLBACK TO SAVEPOINT sp1');
            console.warn('Audit failed, continuing');
        }
        
        await client.query('COMMIT');
        return id;
    } catch (error) {
        await client.query('ROLLBACK');
        throw error;
    }
});

Example 3: Retry on Serialization Failure

async function withRetry<T>(
    operation: () => Promise<T>,
    maxRetries: number = 3
): Promise<T> {
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            return await operation();
        } catch (error) {
            if (error.code === '40001' && attempt < maxRetries) {
                // Serialization failure - retry with backoff
                await new Promise(resolve => 
                    setTimeout(resolve, 100 * attempt)
                );
                continue;
            }
            throw error;
        }
    }
    throw new Error('Max retries exceeded');
}

// Usage
const result = await withRetry(() =>
    withClient(async (client) => {
        await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
        const result = await createResource(client, data);
        await client.query('COMMIT');
        return result;
    })
);

Example 4: Transaction Test

import { test } from '@/test-utils-integration/config/database.context';
import { createResource } from '@db/graph';

test('transaction rollback', async ({ newDbClient }) => {
    await newDbClient.query('BEGIN');
    
    try {
        await createResource(newDbClient, {
            p_type_namespace: 'test.type',
            p_external_id: 'test-id'
        });
        
        // Force rollback
        throw new Error('Simulated error');
    } catch (error) {
        await newDbClient.query('ROLLBACK');
    }
    
    // Verify rollback worked
    const count = await newDbClient.query(
        'SELECT count(*) FROM graph.resource'
    );
    expect(parseInt(count.rows[0].count)).toBe(0);
});