Handling Transactions in PostgreSQL: A Comprehensive Guide with Node.js and Python Examples

PostgreSQL transactions is the backbone of database management. In this tutorial, we are working with transactions using hands-on examples in Node.js and Python, including covering isolation levels and best practices for avoiding common pitfalls. Here you can find how to prevent SQL injections and write a secure code.

Introduction for the PostgreSQL:

PostgreSQL, or simply Postgres, is a reliable, open-source relational database management system (RDBMS) that is widely recognized for its reliability, scalability, and extensibility. It is SQL standard compliant but also supports JSON and NoSQL-like features, making it a hybrid database that is perfectly suitable for contemporary applications. Originally developed at Berkeley University, California, PostgreSQL has become one of the leading and most widely used databases with a strong community following and enterprise-class features.

Introduction to PostgreSQL Transactions:

A PostgreSQL transaction packages several SQL operations as one atomic operation, following ACID principles (Atomicity, Consistency, Isolation, Durability). Transactions commit entirely (COMMIT) or roll back entirely (ROLLBACK), not leaving databases in half-updated states.

This operation is essential in applications such as financial systems where money is transferred from one account to another and must atomically update sender and receiver accounts.

Why Are Transactions Important?

Ensure data integrity and consistency
Prevent race conditions in concurrent transactions
Avoid half-completed database updates
Essential for applications like financial transactions where atomicity is crucial

Transaction Handling in Node.js:

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

async function transferFunds(senderId, receiverId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Deduct from sender
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, senderId]
    );

    // Add to receiver
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, receiverId]
    );

    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Key Considerations

Connection Isolation: Using pool.query() instead of using a separate client creates separate connections, breaking transaction atomicity
Error Handling: Always ROLLBACK in catch blocks to prevent hanging "IDLE in transaction" states
Nesting: While PostgreSQL lacks nested transactions, savepoints can create nested rollback points using SAVEPOINT and ROLLBACK TO statements

Python Transactions with psycopg2

The psycopg2 adapter has explicit commit control and context manager-based transactions. Disabling autocommit (conn.autocommit = False) enables manual transaction control

import psycopg2

def create_order(customer_id, items):
    conn = psycopg2.connect(DATABASE_URL)
    try:
        with conn.cursor() as cur:
            cur.execute("BEGIN")

            # Create order
            cur.execute(
                "INSERT INTO orders (customer_id) VALUES (%s) RETURNING id",
                (customer_id,)
            )
            order_id = cur.fetchone()[0]

            # Add items
            for item in items:
                cur.execute(
                    "INSERT INTO order_items (order_id, product_id, quantity) "
                    "VALUES (%s, %s, %s)",
                    (order_id, item['product_id'], item['quantity'])
                )

            conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        conn.close()

Context managers simplify this flow since psycopg2 2.5:

with conn:
    with conn.cursor() as cur:
        cur.execute("INSERT INTO logs (message) VALUES ('Transaction start')")
        # Additional queries
# Auto-commits if no exceptions

Best Practices and Common Pitfalls

Connection Management

Always release connections back to the pool (Node.js) or close them (Python)
Avoid long-running transactions blocking other operations

Error Handling

Implement retry mechanism for serialization failures
Use savepoints to perform partial rollbacks in compound transactions

Performance Considerations

Keep transactions short to reduce lock contention
Use RETURNING clause to fetch updated rows within the transaction

Testing Strategies

Force rollbacks in test environments to avoid test data persistence
Test under concurrency using parallel transaction executions

// Node.js example with RETURNING
await client.query(
  'UPDATE inventory SET stock = stock - $1 WHERE product_id = $2 RETURNING stock',
  [quantity, productId]
);
const updatedStock = res.rows[0].stock;

Preventing SQL Injection in Transactions

SQL injections are a major security threat in database operations. Follow these best practices:

✅ Use parameterized queries (instead of string concatenation)
✅ Sanitize user inputs before executing queries
✅ Use ORM libraries like Sequelize (Node.js) or SQLAlchemy (Python)

Example of safe query execution: Please check this blog how to prevent sql injections

Advanced Transaction Patterns

Two-Phase Commits (2PC)

For distributed systems coordinating across multiple databases:

BEGIN;
-- Perform operations
PREPARE TRANSACTION 'order_123';
-- Later
COMMIT PREPARED 'order_123';

SAVEPOINT Usage

cur.execute("SAVEPOINT sp1")
try:
    cur.execute("DELETE FROM temp_data")
except:
    cur.execute("ROLLBACK TO sp1")

Language-Specific Considerations

Node.js

Use more top-level libraries such as knex or sequelize for ORM-like transactions

Python

SQLAlchemy's session management provides abstraction over raw transactions
Django ORM handles transactions through decorators like @transaction.atomic

Monitoring and Debugging

Enable logging to analyze transaction behavior:

-- postgresql.conf
log_statement = 'all'
log_line_prefix = '%t [%p]: [%c] '

SELECT pid, now() - xact_start AS duration, query 
FROM pg_stat_activity 
WHERE state IN ('idle in transaction', 'active');

Conclusion

Proper transaction handling is the foundation of consistent PostgreSQL applications. Because of Node.js and Python's control systems, application developers can create robust data operations that are consistent under concurrent use.

Proper use of isolation levels and monitoring also assist in making the system robust. With increasing applications, database transactions with application-level patterns like compensating transactions or saga patterns are used to ensure consistency of the distributed system.

How to Handling Transactions in PostgreSQL: BEGIN, COMMIT, ROLLBACK