Introduction

A couple of months ago in my country Nigeria, the central bank government implemented a certain cash policy that caused the use of electronic cash transfers to gain massive traction. As a result of the sudden spike in traffic of commercial banks app and Fintech apps, some issues surfaced, such as:

• duplicate transactions

• missing transactions

• inconsistent data

• incessant app downtime etc.

Now imagine you're the lead developer in one of these banks amid this chaos. One of the most important things to implement in your application code to savage this situation is a database transaction (DT). With the power of a well-defined and properly executed database transaction (DT), you can ensure that all the necessary database operations are treated as a single, indivisible unit.

In this article, we will explore the art of writing transaction logic. By the end of this guide, you'll be equipped with the basic knowledge of creating solid applications that can gracefully handle complex database operations while preserving data integrity and ensuring consistency.

Understanding Database Transactions

A DT is a logical unit that groups multiple database operations as a single, indivisible entity. It ensures that either all the operations within the transaction are completed successfully, or none of them are applied to the database. Transactions can include a combination of read and write operations such as inserting, updating, deleting, or querying data. By bundling these operations into a transaction, developers can ensure data integrity, prevent data inconsistencies, and handle complex business processes effectively.

Approaches to Transaction Management

There are majorly two approaches to transaction management.

1. Manual Approach

This involves the use of database-specific APIs or libraries to create and perform the database transaction. For example, in Python, use the psycopg2 package to interact with a Postgresql database, and in Nodejs, use the mysql2 package to interact with a Mysql database.

You will be less likely to use this approach for a large-scale or production-grade system. However, below is an implementation code of this approach.

import psycopg2

# Establish a database connection
connection = psycopg2.connect(database="mydb", user="myuser", password="mypassword", host="localhost")

# Create a cursor to execute SQL queries
cursor = connection.cursor()

try:
    # Begin the transaction
    connection.begin()

    # Perform database operations within the transaction
    cursor.execute("INSERT INTO orders (product_id, quantity) VALUES (1, 10)")
    cursor.execute("UPDATE inventory SET quantity = quantity - 10 WHERE product_id = 1")

    # Commit the transaction if all operations succeed
    connection.commit()
except Exception as e:
    # Rollback the transaction if an error occurs
    connection.rollback()
    print("Transaction rolled back due to an error:", str(e))
finally:
    # Close the cursor and database connection
    cursor.close()
    connection.close()

1. Object-Relational Mapping (ORM) Approach

This involves the use of ORM frameworks like SQLAlchemy in Python, Hibernate in Java, Doctrine in PHP, Sequelize and TypeORM in Node.js. ORMs provide a high level of abstraction and rich APIs for managing transactions. In this guide, we shall be writing our DT with Node.js(Typescript) and Sequelize.

Writing Database Transaction Logic

Writing effective transaction logic involves identifying the sequence of database operations that need to be performed as a single unit. A single logical unit of a DT typically includes the following steps:

1. Begin Transaction: The transaction begins by initiating a connection to the database and signaling the start of the transaction.

2. Perform Database Operations: Within the transaction, you perform the necessary database CRUD (Create, Read, Update, Delete) operations.

3. Commit: If all the operations are successful and the transaction meets the desired criteria, the changes made within the transaction are committed to the database.

4. Rollback: If any operation fails or an error occurs, the transaction is rolled back, and all the changes made within the transaction are undone.

5. End Transaction: Once the commit or rollback operation is completed, the transaction is officially ended. This step typically involves closing the database connection or releasing any resources associated with the transaction.

Enough of the talks! And now to the interesting part of it all where codes meet transactional magic. The code snippet below is a multi-step process of a task edit/update API in a task management app built with Nestjs. The processes involved in this single API service include - updating the task, fetching the updated task and creating a notification. If you're not familiar with Nestjs, that's not a problem. Read through the code and understand the flow, the process is typically the same for any language, backend framework and ORM.

import { Sequelize } from 'sequelize';
import { InjectModel } from '@nestjs/sequelize';
import { Task } from './../task/task.model';
import { Notification } from './../notification/notification.model';

@Injectable()
export class TaskService {
  constructor(
    @InjectModel(Task) private taskModel: typeof Task,
    @InjectModel(Notification) private notificationModel: typeof Notification,
    @Inject(Sequelize) private readonly sequelize: Sequelize,
  ) {}

  //   **********************EDIT/UPDATE A TASK ITEM**********************
  async editTask(
    taskId: string,
    userId: string,
    dto: UpdateTaskDto,
  ): Promise<Task> {

    // 1: Start Transaction
    const transaction = await this.sequelize.transaction();

    try {
      // update task DB operaton
      const [numOfAffectedRows, updateTask] = await this.taskModel.update(dto, {
        where: {
          id: taskId,
          ownerId: userId,
        },
        returning: true,
        transaction: transaction, // 2:set option: mark as a transaction
      });

      if (numOfAffectedRows === 0)
        throw new NotFoundException('Task Not Found');

      // fetch task DB operation
      const updatedTask = await this.taskModel.findByPk(taskId, {
        transaction:transaction, // 2:set option: mark as a transaction
      });

      // create notification instance
      const notification = new this.notification();
      notification.title = 'Task update'
      notification.date = new Date();

      // create notification DB operation
      await notification.save({transaction:transaction}) // 2:set option: mark as a transaction

      // 3:Commit the transactions if all DB operations was successful
      await transaction.commit();

      return updatedTask;
    } catch (error) {
      if (error.status === 404) throw new NotFoundException('Task Not Found');

      // 4:Rollback transactions if an error occured with one or more of the DB operations
      await transaction.rollback();

      throw new HttpException(
        `${error.message}`,
        HttpStatus.INTERNAL_SERVER_ERROR,
      );
    }
  }

Conclusion

In conclusion, database transactions are a fundamental concept in the world of databases and application development. Whether it's financial transactions, inventory management, or multi-step processes, the proper use of database transactions is a key aspect of building resilient and dependable systems. With this tutorial, I hope you've gained confidence in implementing DT. You can as well explore other concepts in DT such as transaction isolation levels and locking mechanisms, which I might cover in some upcoming articles.

That's it for now guys, thanks for reading!