How To Write Transactional Database Calls in TypeScript

If you write web services, chances are you interact with a database. Occasionally, you’ll need to make changes that must be applied atomically — either all succeed, or none do. This is where transactions come in. In this article, I’ll show you how to implement transactions in your code to avoid issues with leaky abstractions.

Example

A common example is processing payments:

• You need to get the user’s balance and check if it’s sufficient.
• Then, you update the balance and save it.

Structure

Typically, your application will have two modules to separate business logic from database-related code.

Repository Module

This module handles all database-related operations, such as SQL queries. Below, we define two functions:

• get_balance — Retrieves the user’s balance from the database.
• set_balance — Updates the user’s balance.

import { Injectable } from '@nestjs/common';
import postgres from 'postgres';

@Injectable()
export class BillingRepository {
  constructor(
    private readonly db_connection: postgres.Sql,
  ) {}

  async get_balance(customer_id: string): Promise<number | null> {
    const rows = await this.db_connection`
      SELECT amount FROM balances 
      WHERE customer_id=${customer_id}
    `;
    return (rows[0]?.amount) ?? null;
  }

  async set_balance(customer_id: string, amount: number): Promise<void> {
    await this.db_connection`
      UPDATE balances 
      SET amount=${amount} 
      WHERE customer_id=${customer_id}
    `;
  }
}

Service Module

The service module contains business logic, such as fetching the balance, validating it, and saving the updated balance.

import { Injectable } from '@nestjs/common';
import { BillingRepository } from 'src/billing/billing.repository';

@Injectable()
export class BillingService {
  constructor(
    private readonly billing_repository: BillingRepository,
  ) {}

  async bill_customer(customer_id: string, amount: number) {
    const balance = await this.billing_repository.get_balance(customer_id);

    // The balance may change between the time of this check and the update.
    if (balance === null || balance < amount) {
      return new Error('Insufficient funds');
    }

    await this.billing_repository.set_balance(customer_id, balance - amount);
  }
}

In the bill_customer function, we first retrieve the user’s balance using get_balance. Then, we check if the balance is sufficient and update it with set_balance.

Transactions

The problem with the above code is that the balance could change between the time it is fetched and updated. To avoid this, we need to use transactions. You could handle this in two ways:

• Embed business logic in the repository module: This approach couples business rules with database operations, making testing harder.
• Use a transaction in the service module: This could lead to leaky abstractions, as the service module would need to manage database sessions explicitly.

Instead, I recommend a cleaner approach.

Transactional Code

A good way to handle transactions is to create a function that wraps a callback within a transaction. This function provides a session object that doesn’t expose unnecessary internal details, preventing leaky abstractions. The session object is passed to all database-related functions within the transaction.

Here’s how you can implement it:

import { Injectable } from '@nestjs/common';
import postgres, { TransactionSql } from 'postgres';

export type SessionObject = TransactionSql<Record<string, unknown>>;

@Injectable()
export class BillingRepository {
  constructor(
    private readonly db_connection: postgres.Sql,
  ) {}

  async run_in_session<T>(cb: (sql: SessionObject) => T | Promise<T>) {
    return await this.db_connection.begin((session) => cb(session));
  }

  async get_balance(
    customer_id: string, 
    session: postgres.TransactionSql | postgres.Sql = this.db_connection
  ): Promise<number | null> {
    const rows = await session`
      SELECT amount FROM balances 
      WHERE customer_id=${customer_id}
    `;
    return (rows[0]?.amount) ?? null;
  }

  async set_balance(
    customer_id: string, 
    amount: number, 
    session: postgres.TransactionSql | postgres.Sql = this.db_connection
  ): Promise<void> {
    await session`
      UPDATE balances 
      SET amount=${amount} 
      WHERE customer_id=${customer_id}
    `;
  }
}

In this example, the run_in_session function starts a transaction and executes a callback within it. The SessionObject type abstracts the database session to prevent leaking internal details. All database-related functions now accept a session object, ensuring they can participate in the same transaction.

Updated Service Module

The service module is updated to leverage transactions. Here’s what it looks like:

import { Injectable } from '@nestjs/common';
import { BillingRepository } from 'src/billing/billing-transactional.repository';

@Injectable()
export class BillingService {
  constructor(
    private readonly billing_repository: BillingRepository,
  ) {}

  async bill_customer(customer_id: string, amount: number) {
    const balance = await this.billing_repository.get_balance(customer_id);

    if (balance === null || balance < amount) {
      return new Error('Insufficient funds');
    }

    await this.billing_repository.set_balance(customer_id, balance - amount);
  }

  async bill_customer_transactional(customer_id: string, amount: number) {
    return await this.billing_repository.run_in_session(async (session) => {
      const balance = await this.billing_repository.get_balance(customer_id, session);

      if (balance === null || balance < amount) {
        return new Error('Insufficient funds');
      }

      await this.billing_repository.set_balance(customer_id, balance - amount, session);
    });
  }
}

In the bill_customer_transactional function, we call run_in_session and pass a callback with the session object as a parameter, then we pass this parameter to every function of the repository that we call. This ensures that both get_balance and set_balance run within the same transaction. If the balance changes between the two calls, the transaction will fail, maintaining data integrity.

Conclusion

Using transactions effectively ensures your database operations remain consistent, especially when multiple steps are involved. The approach I’ve outlined helps you manage transactions without leaking abstractions, making your code more maintainable. Try implementing this pattern in your next project to keep your logic clean and your data safe!

---

Thank you for reading!

If you want to reduce churn and increate conversion of your business check out my latest product Widgetform 💬. It is a highly configurable widget that allows you to collect insights about your users.

👏Don’t forget to like if you liked the article👏

Contacts
If you like this article don’t hesitate to connect on LinkedIn and follow me on Twitter.

Subscribe to my mailing list: https://sergedevs.com

Be sure to like and follow 👏