Skip to content

transactions.mdx

Latest commit

 

History

History
292 lines (217 loc) · 8.98 KB

transactions.mdx

File metadata and controls

292 lines (217 loc) · 8.98 KB
title Transactions

import { Alert } from '/components/alert.tsx'

PostgreSQL transactions ensure that a series of database operations either all succeed or all fail together, maintaining data consistency. Node-postgres provides two approaches for handling transactions: manual transaction management and the very slightly higher-level pg-transaction module.

pg-transaction Module

The pg-transaction module provides a tiny level of abstraction for handling transactions, automatically managing BEGIN, COMMIT, and ROLLBACK operations while ensuring proper client lifecycle management.

The motivation for this module was I pretty much write the same exact thing in every project I start. Sounds like a good thing to just publish widely.

Installation

The pg-transaction module is included as part of the node-postgres monorepo:

npm install pg-transaction

Basic Usage

The transaction function accepts either a Client or Pool instance and a callback function:

import { Pool } from 'pg'
import { transaction } from 'pg-transaction'

const pool = new Pool()

// Using with a Pool (recommended)
const result = await transaction(pool, async (client) => {
  const userResult = await client.query(
    'INSERT INTO users(name) VALUES($1) RETURNING id',
    ['Alice']
  )
  
  await client.query(
    'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)',
    [userResult.rows[0].id, 's3.bucket.foo']
  )
  
  return userResult.rows[0]
})

console.log('User created:', result)

API Reference

transaction(clientOrPool, callback)

Parameters:

  • clientOrPool: A pg.Client or pg.Pool instance
  • callback: An async function that receives a client and returns a promise

Returns: A promise that resolves to the return value of the callback

Behavior:

  • Automatically executes BEGIN before the callback
  • Executes COMMIT if the callback completes successfully
  • Executes ROLLBACK if the callback throws an error, then re-throws the error for you to handle
  • When using a Pool, automatically acquires and releases a client
  • When using a Client, uses the provided client directly. The client must be connected already.

Usage Examples

With Pool (Recommended)

import { Pool } from 'pg'
import { transaction } from 'pg-transaction'

const pool = new Pool()

try {
  const userId = await transaction(pool, async (client) => {
    // All queries within this callback are part of the same transaction
    const userResult = await client.query(
      'INSERT INTO users(name, email) VALUES($1, $2) RETURNING id',
      ['John Doe', 'john@example.com']
    )
    
    const profileResult = await client.query(
      'INSERT INTO user_profiles(user_id, bio) VALUES($1, $2)',
      [userResult.rows[0].id, 'Software developer']
    )
    
    // Return the user ID
    return userResult.rows[0].id
  })
  
  console.log('Created user with ID:', userId)
} catch (error) {
  console.error('Transaction failed:', error)
  // All changes have been automatically rolled back
}

With Client

import { Client } from 'pg'
import { transaction } from 'pg-transaction'

const client = new Client()
await client.connect()

try {
  await transaction(client, async (client) => {
    await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [1])
    await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $1', [2])
  })
  console.log('Transfer completed successfully')
} catch (error) {
  console.error('Transfer failed:', error)
} finally {
  await client.end()
}

Binding for Reuse

You can bind the transaction function to a specific pool or client for convenient reuse. I usually do this as a module level singleton I export after I define my pool.

import { Pool } from 'pg'
import { transaction } from 'pg-transaction'

const pool = new Pool()
const txn = transaction.bind(null, pool)

// Now you can use txn directly
await txn(async (client) => {
  await client.query('INSERT INTO logs(message) VALUES($1)', ['Operation 1'])
})

await txn(async (client) => {
  await client.query('INSERT INTO logs(message) VALUES($1)', ['Operation 2'])
})

Error Handling and Rollback

The transaction function automatically handles rollbacks when errors occur:

import { transaction } from 'pg-transaction'

try {
  await transaction(pool, async (client) => {
    await client.query('INSERT INTO orders(user_id, total) VALUES($1, $2)', [userId, 100])
    
    // This will cause the transaction to rollback
    if (Math.random() > 0.5) {
      throw new Error('Payment processing failed')
    }
    
    await client.query('UPDATE inventory SET quantity = quantity - 1 WHERE product_id = $1', [productId])
  })
} catch (error) {
  // The transaction has been automatically rolled back
  console.error('Order creation failed:', error.message)
}

Best Practices

  1. Use with Pools: When possible, use the transaction function with a Pool rather than a Client for better resource management.

  2. Keep Transactions Short: Minimize the time spent in transactions to reduce lock contention.

  3. Handle Errors Appropriately: Let the transaction function handle rollbacks, but ensure your application logic handles the errors appropriately.

  4. Avoid Nested Transactions: PostgreSQL doesn't support true nested transactions. Use savepoints if you need nested behavior.

  5. Return Values: Use the callback's return value to pass data out of the transaction.

Migration from Manual Transactions

If you're currently using manual transaction handling, migrating to pg-transaction is straightforward:

Before (Manual):

const client = await pool.connect()
try {
  await client.query('BEGIN')
  const result = await client.query('INSERT INTO users(name) VALUES($1) RETURNING id', ['Alice'])
  await client.query('INSERT INTO profiles(user_id) VALUES($1)', [result.rows[0].id])
  await client.query('COMMIT')
  return result.rows[0]
} catch (error) {
  await client.query('ROLLBACK')
  throw error
} finally {
  client.release()
}

After (pg-transaction):

return await transaction(pool, async (client) => {
  const result = await client.query('INSERT INTO users(name) VALUES($1) RETURNING id', ['Alice'])
  await client.query('INSERT INTO profiles(user_id) VALUES($1)', [result.rows[0].id])
  return result.rows[0]
})

Manual Transaction Handling

For cases where you need more control or prefer to handle transactions manually, you can execute BEGIN, COMMIT, and ROLLBACK queries directly.

You must use the same client instance for all statements within a transaction. PostgreSQL isolates a transaction to individual clients. This means if you initialize or use transactions with the{' '} pool.query method you will have problems. Do not use transactions with the pool.query method.

Manual Transaction Example

import { Pool } from 'pg'
const pool = new Pool()

const client = await pool.connect()

try {
  await client.query('BEGIN')
  const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
  const res = await client.query(queryText, ['brianc'])

  const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
  const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
  await client.query(insertPhotoText, insertPhotoValues)
  await client.query('COMMIT')
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

When to Use Manual Transactions

Consider manual transaction handling when you need:

  • Savepoints: Creating intermediate rollback points within a transaction
  • Custom Transaction Isolation Levels: Setting specific isolation levels
  • Complex Transaction Logic: Conditional commits or rollbacks based on business logic
  • Performance Optimization: Fine-grained control over transaction boundaries

Savepoints Example

const client = await pool.connect()

try {
  await client.query('BEGIN')
  
  // First operation
  await client.query('INSERT INTO orders(user_id, total) VALUES($1, $2)', [userId, total])
  
  // Create a savepoint
  await client.query('SAVEPOINT order_items')
  
  try {
    // Attempt to insert order items
    for (const item of items) {
      await client.query('INSERT INTO order_items(order_id, product_id, quantity) VALUES($1, $2, $3)', 
        [orderId, item.productId, item.quantity])
    }
  } catch (error) {
    // Rollback to savepoint, keeping the order
    await client.query('ROLLBACK TO SAVEPOINT order_items')
    console.log('Order items failed, but order preserved')
  }
  
  await client.query('COMMIT')
} catch (error) {
  await client.query('ROLLBACK')
  throw error
} finally {
  client.release()
}

Recommendation: Use pg-transaction for most use cases, and fall back to manual transaction handling only when you need advanced features like savepoints or custom isolation levels. Note: the number of times I've done this in production apps is nearly zero.