Ensuring Data Integrity in Financial Transactions: The PostgreSQL Transaction Solution

When it comes to financial systems, data integrity is one of the most crucial concerns. In a financial transaction environment, failures can result in loss of money, inconsistency in account balances, or even security breaches. To ensure that data remains consistent and secure, the use of transactions in the database is essential. In this article, we will explore how transactions in PostgreSQL help solve these problems, with practical examples using NestJS and Prisma to implement robust solutions.

What Are Banking Transactions?

In a banking context, a transaction usually involves moving money between accounts or updating balances. The atomicity of a transaction is essential to ensure that all necessary steps to complete the operation are successfully carried out, and that if an error occurs at any stage, the entire process is rolled back to prevent inconsistencies.

For example, when transferring money from one account to another, the transaction involves:

1. Subtracting the amount from the source account.
2. Adding the amount to the destination account.

If any of these steps fail, such as a network failure or system error, the balance of one account may be updated without the other, leading to inconsistent data. Transactions are used to ensure that both actions occur, or neither, maintaining data integrity.

The Solution: Transactions in PostgreSQL

PostgreSQL is a relational database that natively supports transactions, ensuring that database operations are atomic, consistent, isolated, and durable — characteristics known by the acronym ACID (Atomicity, Consistency, Isolation, and Durability). Using transactions, we can guarantee that all steps of a banking operation are successfully completed or, in case of an error, rolled back.

How Does a Transaction Work in PostgreSQL?

In simple terms, a transaction in PostgreSQL starts with the BEGIN statement and ends with COMMIT. If any error occurs during the transaction, the ROLLBACK command is called to undo all changes made up to that point. Here is a basic example of a transaction in PostgreSQL:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Subtract 100 from the source account
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- Add 100 to the destination account

COMMIT; -- If everything goes well, confirm the changes

If something goes wrong, like the source account not having sufficient funds or a database failure, a ROLLBACK can be triggered to ensure that none of the changes are applied.

Practical Example Using NestJS and Prisma

Now that we understand the concept of transactions, let’s integrate this with NestJS and Prisma.

Configuring Prisma with PostgreSQL

First, let's configure Prisma to work with PostgreSQL in your NestJS project. Make sure that Prisma is set up correctly in your project.

1. Install Prisma and dependencies:

npm install @prisma/client
npm install prisma --save-dev

1. Configure Prisma (in the prisma/schema.prisma file):

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Account {
  id     Int       @id @default(autoincrement())
  balance Decimal   @default(0) @db.Decimal(10, 2)
}

1. Run the migration to apply the changes to the database:

npx prisma migrate dev --name init

Implementing Transactions in NestJS with Prisma

In NestJS, we can use Prisma to manage transactions within services. Let’s create a service that simulates transferring funds between accounts. We will use the prisma.$transaction method to ensure the transfer is atomic.

Service Code

import { Injectable } from '@nestjs/common';
import { PrismaService } from './prisma.service'; // Prisma service
import { Prisma } from '@prisma/client';

@Injectable()
export class TransactionsService {
  constructor(private prisma: PrismaService) {}

  async transferFunds(sourceAccountId: number, destinationAccountId: number, amount: number) {
    const sourceAccount = await this.prisma.account.findUnique({ where: { id: sourceAccountId } });
    const destinationAccount = await this.prisma.account.findUnique({ where: { id: destinationAccountId } });

    if (!sourceAccount || !destinationAccount) {
      throw new Error('Account not found');
    }

    if (sourceAccount.balance < amount) {
      throw new Error('Insufficient balance');
    }

    // Starting the transaction
    return this.prisma.$transaction(async (prisma) => {
      // Subtract from the source account
      await prisma.account.update({
        where: { id: sourceAccountId },
        data: { balance: sourceAccount.balance - amount },
      });

      // Add to the destination account
      await prisma.account.update({
        where: { id: destinationAccountId },
        data: { balance: destinationAccount.balance + amount },
      });
    });
  }
}

Code Explanation:

1. prisma.$transaction: This method is used to wrap database operations within a transaction. If any part of the transaction fails, all changes made within the transaction are automatically rolled back.
2. Validation: Before starting the transaction, we validate that the accounts exist and that there is sufficient balance in the source account.
3. Atomic operations: We use prisma.account.update to update the balances of the accounts. Both the subtraction and addition operations happen within the same transaction, ensuring that both occur successfully or that neither occurs if an error happens.

Benefits of Transactions for Banking Systems

By using transactions in PostgreSQL, we ensure:

• Atomicity: If something goes wrong, such as insufficient balance or a database error, the entire transaction is rolled back, and no changes are persisted.
• Consistency: After the transaction, the database will be in a consistent state, without incorrect balances or duplicated data.
• Isolation: Transactions are isolated from each other, meaning concurrent transactions do not interfere with each other.
• Durability: Once the transaction is confirmed with COMMIT, the data is guaranteed to be persistent, even in the event of a system failure.

Final Considerations

Using transactions in PostgreSQL is essential to ensure data integrity in financial systems. Transactions ensure that critical operations, such as fund transfers, are performed atomically and securely.

By adopting appropriate transactions and following good data handling practices, we can minimize risks, such as data loss and inconsistencies, providing a secure and reliable experience for users of the banking system.