Mastering Database Transactions in PostgreSQL With Node.js and Knex.js

When your application performs multiple database operations that must succeed or fail together, transactions are essential. In this tutorial, we'll explore how to implement robust transaction logic using PostgreSQL and the Knex.js query builder.

Why Use Transactions?

Transactions allow you to execute a group of queries atomically—either all succeed or none do. This ensures data integrity when performing related inserts, updates, or deletes.

Step 1: Set Up Your Project

npm init -y
npm install knex pg

Create a basic Knex configuration:

// knexfile.js
module.exports = {
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'youruser',
    password: 'yourpassword',
    database: 'yourdb'
  }
};

Step 2: Initialize Knex

// db.js
const knex = require('knex')(require('./knexfile'));
module.exports = knex;

Step 3: Use Transactions in Your Code

// transaction-example.js
const db = require('./db');

async function createOrderWithItems(orderData, items) {
  const trx = await db.transaction();

  try {
    const [orderId] = await trx('orders').insert(orderData).returning('id');

    for (const item of items) {
      await trx('order_items').insert({
        order_id: orderId,
        product_id: item.product_id,
        quantity: item.quantity
      });
    }

    await trx.commit();
    console.log('Transaction committed!');
  } catch (err) {
    await trx.rollback();
    console.error('Transaction rolled back!', err);
  }
}

createOrderWithItems(
  { customer_id: 1, total: 59.99 },
  [
    { product_id: 10, quantity: 2 },
    { product_id: 22, quantity: 1 }
  ]
);

Best Practices

• Always handle both commit() and rollback().
• Use transactions for operations that span multiple tables.
• Log or track failed transactions for auditing and debugging.

Conclusion

Knex.js makes managing PostgreSQL transactions straightforward and safe. Use this pattern anytime you're dealing with interdependent operations to ensure your data remains consistent and reliable.

If this post helped you, consider supporting me: buymeacoffee.com/hexshift