Express Transactions

Introduction

Database transactions are essential in web applications when you need to perform multiple related operations as a single unit. Imagine transferring money between bank accounts—you need to ensure that if the money is deducted from one account, it must be added to another. If any part fails, all operations should be rolled back to maintain data consistency.

In this guide, we'll explore how to implement transactions in Express.js applications with different databases. Transactions provide four key properties (ACID):

Atomicity: All operations succeed or all fail
Consistency: The database moves from one valid state to another
Isolation: Concurrent transactions don't interfere with each other
Durability: Completed transactions persist even in system failures

Understanding Transactions

Before diving into code examples, let's understand what transactions look like in a basic flow:

Begin a transaction
Perform database operations
If all operations succeed, commit the transaction
If any operation fails, rollback the transaction

Implementing Transactions with Different Databases

Let's explore how to implement transactions with popular database systems used in Express applications.

Transactions with Sequelize (SQL databases)

Sequelize is an ORM for SQL databases like PostgreSQL, MySQL, and SQLite.

First, install Sequelize and your database driver:

bash
npm install sequelize
npm install pg # for PostgreSQL
# or npm install mysql2 # for MySQL

Now let's implement a simple money transfer between accounts:

javascript
const express = require('express');
const { Sequelize, DataTypes } = require('sequelize');
const app = express();

// Set up Sequelize with your database
const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres' // or 'mysql', 'sqlite', 'mssql'
});

// Define Account model
const Account = sequelize.define('Account', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  owner: {
    type: DataTypes.STRING,
    allowNull: false
  },
  balance: {
    type: DataTypes.DECIMAL(10, 2),
    allowNull: false
  }
});

app.use(express.json());

// Transfer money endpoint with transaction
app.post('/transfer', async (req, res) => {
  const { fromAccountId, toAccountId, amount } = req.body;
  
  // Validate input
  if (!fromAccountId || !toAccountId || !amount || amount <= 0) {
    return res.status(400).json({ error: 'Invalid transfer details' });
  }
  
  // Start transaction
  const t = await sequelize.transaction();
  
  try {
    // Get accounts with row locking for update
    const fromAccount = await Account.findByPk(fromAccountId, { 
      lock: t.LOCK.UPDATE,
      transaction: t 
    });
    const toAccount = await Account.findByPk(toAccountId, { 
      lock: t.LOCK.UPDATE, 
      transaction: t 
    });
    
    // Validate accounts exist
    if (!fromAccount || !toAccount) {
      await t.rollback();
      return res.status(404).json({ error: 'One or both accounts not found' });
    }
    
    // Check sufficient balance
    if (fromAccount.balance < amount) {
      await t.rollback();
      return res.status(400).json({ error: 'Insufficient funds' });
    }
    
    // Perform transfer
    await fromAccount.decrement('balance', { by: amount, transaction: t });
    await toAccount.increment('balance', { by: amount, transaction: t });
    
    // Commit transaction if all operations succeeded
    await t.commit();
    
    res.json({ 
      message: 'Transfer successful',
      fromAccount: fromAccount.id,
      toAccount: toAccount.id,
      amount
    });
  } catch (error) {
    // Rollback transaction if any operation failed
    await t.rollback();
    console.error('Transaction error:', error);
    res.status(500).json({ error: 'Transfer failed', details: error.message });
  }
});

// Start the server
app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

In this example:

We define an Account model with Sequelize
Create a /transfer endpoint that handles transfers between accounts
Use sequelize.transaction() to start a transaction
Use row-level locking to prevent race conditions
Either commit changes if successful or rollback if any issues occur

Transactions with MongoDB (using Mongoose)

MongoDB also supports transactions starting from version 4.0 for replica sets. Let's implement a similar example using Mongoose:

bash
npm install mongoose express

javascript
const express = require('express');
const mongoose = require('mongoose');
const app = express();

// Connect to MongoDB
mongoose.connect('mongodb://localhost:27017/bankapp', {
  useNewUrlParser: true,
  useUnifiedTopology: true
});

// Define Account schema
const accountSchema = new mongoose.Schema({
  owner: { type: String, required: true },
  balance: { type: Number, required: true }
});

const Account = mongoose.model('Account', accountSchema);

app.use(express.json());

// Transfer money endpoint with transaction
app.post('/transfer', async (req, res) => {
  const { fromAccountId, toAccountId, amount } = req.body;
  
  // Validate input
  if (!fromAccountId || !toAccountId || !amount || amount <= 0) {
    return res.status(400).json({ error: 'Invalid transfer details' });
  }

  // Start session for transaction
  const session = await mongoose.startSession();
  session.startTransaction();
  
  try {
    // Find accounts
    const fromAccount = await Account.findById(fromAccountId).session(session);
    const toAccount = await Account.findById(toAccountId).session(session);
    
    // Validate accounts exist
    if (!fromAccount || !toAccount) {
      await session.abortTransaction();
      session.endSession();
      return res.status(404).json({ error: 'One or both accounts not found' });
    }
    
    // Check sufficient balance
    if (fromAccount.balance < amount) {
      await session.abortTransaction();
      session.endSession();
      return res.status(400).json({ error: 'Insufficient funds' });
    }
    
    // Perform transfer
    await Account.findByIdAndUpdate(
      fromAccountId, 
      { $inc: { balance: -amount } },
      { session }
    );
    
    await Account.findByIdAndUpdate(
      toAccountId, 
      { $inc: { balance: amount } },
      { session }
    );
    
    // Commit the transaction
    await session.commitTransaction();
    session.endSession();
    
    res.json({ 
      message: 'Transfer successful',
      fromAccount: fromAccountId,
      toAccount: toAccountId,
      amount
    });
  } catch (error) {
    // Abort transaction on error
    await session.abortTransaction();
    session.endSession();
    console.error('Transaction error:', error);
    res.status(500).json({ error: 'Transfer failed', details: error.message });
  }
});

app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

Key points in MongoDB transactions:

We use mongoose.startSession() to create a session
Start a transaction with session.startTransaction()
Pass the session to all operations within the transaction
Either commit with commitTransaction() or abort with abortTransaction()

Native PostgreSQL Transactions with node-postgres

For more direct control, you might use a native PostgreSQL client like node-postgres:

bash
npm install express pg

javascript
const express = require('express');
const { Pool } = require('pg');
const app = express();

// Create PostgreSQL connection pool
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'bankapp',
  password: 'password',
  port: 5432,
});

app.use(express.json());

// Transfer money endpoint with transaction
app.post('/transfer', async (req, res) => {
  const { fromAccountId, toAccountId, amount } = req.body;
  
  // Validate input
  if (!fromAccountId || !toAccountId || !amount || amount <= 0) {
    return res.status(400).json({ error: 'Invalid transfer details' });
  }
  
  // Get client from connection pool
  const client = await pool.connect();
  
  try {
    // Start transaction
    await client.query('BEGIN');
    
    // Check if accounts exist and have sufficient funds
    const fromAccountResult = await client.query(
      'SELECT balance FROM accounts WHERE id = $1 FOR UPDATE',
      [fromAccountId]
    );
    
    if (fromAccountResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Source account not found' });
    }
    
    const toAccountResult = await client.query(
      'SELECT id FROM accounts WHERE id = $1 FOR UPDATE',
      [toAccountId]
    );
    
    if (toAccountResult.rows.length === 0) {
      await client.query('ROLLBACK');
      return res.status(404).json({ error: 'Destination account not found' });
    }
    
    const currentBalance = parseFloat(fromAccountResult.rows[0].balance);
    if (currentBalance < amount) {
      await client.query('ROLLBACK');
      return res.status(400).json({ error: 'Insufficient funds' });
    }
    
    // Perform the transfer
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromAccountId]
    );
    
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toAccountId]
    );
    
    // Commit transaction
    await client.query('COMMIT');
    
    res.json({ 
      message: 'Transfer successful',
      fromAccount: fromAccountId,
      toAccount: toAccountId,
      amount
    });
  } catch (error) {
    // Rollback transaction on error
    await client.query('ROLLBACK');
    console.error('Transaction error:', error);
    res.status(500).json({ error: 'Transfer failed', details: error.message });
  } finally {
    // Return client to pool
    client.release();
  }
});

app.listen(3000, () => {
  console.log('Server is running on port 3000');
});

In this example:

We use SQL commands directly: BEGIN, COMMIT, and ROLLBACK
We use FOR UPDATE to lock rows while the transaction is in progress
We handle database operations without an ORM, giving more direct control

Real-World Transaction Scenarios

Let's explore some common real-world scenarios where transactions are crucial:

E-commerce Order Processing

When a user places an order, you might need to:

Create the order record
Update inventory for multiple products
Process payment
Create shipping information

javascript
app.post('/place-order', async (req, res) => {
  const { userId, products, paymentDetails, shippingAddress } = req.body;
  const t = await sequelize.transaction();
  
  try {
    // 1. Create order
    const order = await Order.create({
      userId,
      status: 'processing',
      shippingAddress
    }, { transaction: t });
    
    // 2. Create order items and update inventory
    for (const product of products) {
      // Check inventory
      const inventoryItem = await Inventory.findByPk(product.id, { 
        lock: t.LOCK.UPDATE,
        transaction: t 
      });
      
      if (!inventoryItem || inventoryItem.quantity < product.quantity) {
        throw new Error(`Insufficient inventory for product ${product.id}`);
      }
      
      // Add to order items
      await OrderItem.create({
        orderId: order.id,
        productId: product.id,
        quantity: product.quantity,
        price: product.price
      }, { transaction: t });
      
      // Update inventory
      await inventoryItem.decrement('quantity', { 
        by: product.quantity, 
        transaction: t 
      });
    }
    
    // 3. Process payment (simplified)
    const paymentResult = await processPayment(paymentDetails, totalAmount);
    if (!paymentResult.success) {
      throw new Error('Payment processing failed');
    }
    
    // 4. Update order status
    await order.update({ 
      status: 'confirmed',
      paymentId: paymentResult.id 
    }, { transaction: t });
    
    // Commit transaction
    await t.commit();
    
    res.status(201).json({ 
      success: true, 
      orderId: order.id 
    });
  } catch (error) {
    await t.rollback();
    console.error('Order processing failed:', error);
    res.status(500).json({ 
      success: false, 
      error: error.message 
    });
  }
});

User Registration with Profile and Settings

When a user registers, you might want to create multiple related records:

javascript
app.post('/register', async (req, res) => {
  const { username, email, password, profileData } = req.body;
  const t = await sequelize.transaction();
  
  try {
    // Create user account
    const user = await User.create({
      username,
      email,
      password: await bcrypt.hash(password, 10) // Remember to hash passwords!
    }, { transaction: t });
    
    // Create user profile
    await Profile.create({
      userId: user.id,
      firstName: profileData.firstName,
      lastName: profileData.lastName,
      bio: profileData.bio || ''
    }, { transaction: t });
    
    // Create default user settings
    await UserSettings.create({
      userId: user.id,
      theme: 'light',
      emailNotifications: true,
      language: 'en'
    }, { transaction: t });
    
    // Commit all changes
    await t.commit();
    
    res.status(201).json({ 
      success: true, 
      message: 'User registered successfully' 
    });
  } catch (error) {
    await t.rollback();
    
    // Check for duplicate email/username
    if (error.name === 'SequelizeUniqueConstraintError') {
      return res.status(409).json({ 
        success: false, 
        error: 'Username or email already exists' 
      });
    }
    
    console.error('Registration failed:', error);
    res.status(500).json({ 
      success: false, 
      error: 'Registration failed' 
    });
  }
});

Best Practices for Transactions

Keep transactions short: The longer a transaction runs, the longer it holds locks on database resources, potentially affecting performance.
Handle deadlocks: Deadlocks can occur when transactions wait for resources held by each other. Use appropriate timeout settings and retry logic.
Error handling: Always have proper error handling to ensure transactions are rolled back on failure.
Consider isolation levels: Different isolation levels offer different balances between consistency and performance:
- Read Uncommitted (lowest isolation, highest performance)
- Read Committed
- Repeatable Read
- Serializable (highest isolation, lowest performance)
Use optimistic locking for high-concurrency scenarios with low conflict probability.
Monitor transaction performance to identify bottlenecks.

Common Challenges and Solutions

1. Long-Running Transactions

Challenge: Transactions that run for a long time can lock resources and reduce application performance.

Solution: Break down large operations into smaller units of work, or use background processing for non-critical operations.

2. Deadlocks

Challenge: Two transactions waiting for resources held by each other.

Solution:

javascript
async function executeWithRetry(transactionFn, maxRetries = 3) {
  let retries = 0;
  
  while (retries < maxRetries) {
    try {
      return await transactionFn();
    } catch (error) {
      if (error.name === 'SequelizeDeadlockError' && retries < maxRetries - 1) {
        // Wait with exponential backoff before retrying
        await new Promise(resolve => 
          setTimeout(resolve, Math.pow(2, retries) * 100)
        );
        retries++;
      } else {
        throw error;
      }
    }
  }
}

// Usage
app.post('/transfer', async (req, res) => {
  try {
    const result = await executeWithRetry(async () => {
      // Your transaction logic here
      const t = await sequelize.transaction();
      try {
        // ...transaction operations
        await t.commit();
        return { success: true };
      } catch (error) {
        await t.rollback();
        throw error;
      }
    });
    
    res.json(result);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

3. Distributed Transactions

Challenge: Transactions across multiple services or databases.

Solution: Consider using the Saga pattern or two-phase commit protocols for distributed transactions. These are beyond Express itself but important for microservice architectures.

Summary

Database transactions are essential for maintaining data integrity in Express applications. We've learned:

How to implement transactions in Express with different databases (Sequelize, MongoDB, raw PostgreSQL)
The ACID properties that transactions provide
Real-world scenarios where transactions are crucial
Best practices and common challenges when working with transactions

Implementing proper transaction handling ensures your application data remains consistent even when operations fail, providing reliability and a better user experience.

Additional Resources

Practice Exercises

Blog Post System: Implement a transaction that creates a blog post and associated tags in a single operation.
Inventory Management: Create an API endpoint that uses transactions to transfer items between warehouses.
User Role System: Implement a transaction that assigns multiple permissions to a user role, ensuring either all permissions are assigned or none.
Challenge: Implement a retry mechanism for transactions that fail due to deadlocks or temporary connection issues.

By mastering transactions in Express.js, you'll build more robust and reliable web applications that correctly handle complex data operations.

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Understanding Transactions​

Implementing Transactions with Different Databases​

Transactions with Sequelize (SQL databases)​

Transactions with MongoDB (using Mongoose)​

Native PostgreSQL Transactions with node-postgres​

Real-World Transaction Scenarios​

E-commerce Order Processing​

User Registration with Profile and Settings​

Best Practices for Transactions​

Common Challenges and Solutions​

1. Long-Running Transactions​

2. Deadlocks​

3. Distributed Transactions​

Summary​

Additional Resources​

Practice Exercises​