API with NestJS #153. SQL transactions with the Drizzle ORM

NestJS SQL

This entry is part 153 of 158 in the API with NestJS

Ensuring the integrity of the data is a fundamental responsibility of each developer. Fortunately, SQL databases give us the tools to ensure our database stays consistent and accurate.

A crucial scenario to consider is when two SQL queries depend on each other. A typical example is transferring money between two bank accounts. Suppose we have two bank accounts, each holding $1000. Transferring $500 from one account to the other involves two steps:

  1. reducing the first account’s balance by $500,
  2. adding $500 to the second account’s balance.

If the first operation fails, data integrity is intact, and the total sum in both accounts remains $2000. The worst-case scenario occurs when only part of the process succeeds:

  1. we withdraw $500 from the first account,
  2. we fail to deposit the money into the second account because it was recently closed.

The total in both accounts is now just $1500, with the missing $500 unaccounted for in either account.

The ACID properties of transactions

Thankfully, transactions offer a solution to the above issue. A transaction can contain multiple SQL queries and ensures the following:

Atomicity

A transaction either fully succeeds or completely fails.

Consistency

The transaction transitions the database from one valid state to another.

Isolation

Multiple transactions can be executed simultaneously without losing data consistency. In our example, the second transaction should detect the transferred money in one account but not both.

Durability

Once a transaction is committed, the changes to the database are permanent.

Transactions with PostgreSQL

To initiate a transaction block, we start with the statement. Next, we write the queries we want to include in the transaction and finish with the keyword to save our changes.

By wrapping our queries in a transaction, we can revert the entire operation if the money transfer to the second account fails for any reason. To achieve this, we use the keyword.

Transactions with the Drizzle ORM

To run multiple queries in a transaction using Drizzle, we use the function. Let’s write a function that deletes a user together with all of their articles.

users.service.ts

What’s crucial is that we need to use the function instead of . When using PostgreSQL, we handle a pool of multiple clients connected to the database. Using the  object ensures that the same client instance is used for all queries within the transaction.

If our code throws an error at any point in the transaction, the Drizzle ORM rolls back our transaction. For example, if deleting the user fails, the articles are not permanently removed from the database.

We can also call the function manually.

Sharing the transaction across various methods

As our application grows more complex, transactions may involve multiple methods within our service. To manage this, we can pass the transaction instance as an argument.

To achieve this, we must first define a TypeScript type that describes a Drizzle ORM transaction related to our database schema.

postgres-transaction.ts

When working on the method, we had some duplicated code because we already have a method to delete a user. Let’s modify it to accept the object as an additional argument.

users.service.ts

Thanks to our approach, the method can work either within a transaction or as a standalone SQL query. Let’s use it in our method.

users.service.ts

We could take it further and move the logic of removing the articles to the . However, we need to watch out for circular dependencies.

If you want to know more, check out API with NestJS #61. Dealing with circular dependencies

Summary

In this article, we explored the concept of transactions and their importance. We also learned how to use them with the Drizzle ORM, implementing both a straightforward example and a more complex one that spans multiple methods. Thanks to this knowledge, we are now equipped to ensure the integrity of our database.

Series Navigation<< API with NestJS #152. SQL constraints with the Drizzle ORMAPI with NestJS #154. Many-to-many relationships with Drizzle ORM and PostgreSQL >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments