API with NestJS #64. Transactions with PostgreSQL and MikroORM

NestJS SQL

This entry is part 64 of 66 in the API with NestJS

One of the most important things to care about as a web developer is the integrity of the data. In this article, we learn what a transaction is and how it can help us ensure that our data is correct.

The idea behind transactions

A transaction is a set of instructions that either happens entirely or doesn’t happen at all. To understand why we might need transactions, let’s use the most common example.

When transferring money from one bank account to another, two steps happen:

  • we withdraw a certain amount of money from the first account,
  • we add the same amount to the second account.

If the whole operation fails completely, that’s something relatively harmless. The worst scenario would be to perform just a part of the above steps. For example, if we withdraw the money from the first account but fail to add it to the second one, we break the integrity of our data. To prevent that, we can bundle multiple steps into a single unit of work, referred to as a transaction.

ACID properties

A valid transaction can be described using a few properties:

Atomicity

All of the operations in a transaction are a single unit. Therefore, it either succeeds entirely or fully fails.

Consistency

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

Isolation

Multiple transactions could occur concurrently without the risk of having an invalid state of the database. In our case, another transaction should see the funds in one bank account or the other, but not in both.

Durability

As soon as we commit the changes from the transaction, they should survive permanently.

Transactions in PostgreSQL

Fortunately, PostgreSQL gives us the tools to ensure all ACID properties. To create a transaction, we need to group a set of statements with and .

In the previous part of this series, we’ve defined a many-to-many relationship between categories and posts. First, let’s create a transaction that deletes a category and all of the posts within it.

Thanks to using a transaction, if something goes wrong when deleting a category, PostgreSQL performs a rollback, and thanks to that, the posts are still intact.

We can also perform a rollback manually and abort the current transaction.

Thanks to using , the will never be dropped in the above transaction.

Transactions with MikroORM

MikroORM implements the unit of work pattern. Thanks to that, it batches queries out of the box.

In API with NestJS #62. Introduction to MikroORM with PostgreSQL, we’ve learned that we need to flush all of the changes we’ve made to our entities if we want the changes to be reflected in the database.

Flush Modes

A crucial thing to notice is that MikroORM supports a few flushing strategies.

database.module.ts

With , MikroORM flushes before every query. Therefore, using it would prevent us from implementing transactions by delaying the flush.

With , MikroORM sometimes flushes implicitly, which might be a little surprising.

posts.service.ts

Since we’ve queried all of the posts before flushing the newly created entity, MikroORM automatically flushed our changes for us.

The above behavior can sometimes get in the way of implementing transactions. Because of that, in this article, we use the option that aims to delay the flush until the current transaction is committed.

database.module.ts

We also use to investigate what queries MikroORM is performing.

Delaying flushing to implement transactions

Let’s start by making some adjustments to our :

posts.service.ts

Now, our accepts an additional argument. Thanks to doing that, we can avoid flushing if we need to.

Let’s also make changes to our to use the above functionality.

categories.service.ts

The crucial part above is the function. It calls the method on every post from the category, marking it for deleting without flushing.

Then, we also mark the category for deleting with the method.

A the end of the function, we used . Thanks to doing that, we removed all the posts and categories that we marked for deleting. If an error occurs at any point of the transaction, MikroORM automatically rolls back all of the changes.

Using would have the same effect as and would delete both posts and categories. We can use to put an emphasis on the fact that we make changes not only to the categories.

Thanks to the fact that we’ve used the debug mode in MikroORM, we can take a look at the logs:

[query] select “p0”.* from “post_entity” as “p0” left join “post_entity_categories” as “p1” on “p0″.”id” = “p1″.”post_entity_id” where “p1″.”category_id” = 6 [took 2 ms]
[query] select “c0”.* from “category” as “c0” where “c0″.”id” = 6 limit 1 [took 1 ms]
[query] begin
[query] delete from “post_entity” where “id” in (36, 37, 38, 39, 40) [took 1 ms]
[query] delete from “category” where “id” in (6) [took 0 ms]
[query] commit

Above, we can see that removing posts and the category was performed in a single transaction.

Creating transactions explicitly

So far, we’ve been defining transactions implicitly. If we want to be more verbose, we can do that explicitly.

categories.service.ts

When we use , MikroORM runs our callback inside a database transaction and flushes the changes at the end.

If we want to be even more explicit, we can manually begin, commit, and roll back a transaction.

categories.service.ts

The above is equivalent to the function. It forks the entity manager above to get a fresh entity manager with a new identity map.

Summary

In this article, we’ve gone through the idea of transactions and implemented them both through SQL and MikroORM. We’ve deleted a category and its post within a transaction. The above allowed us to prevent the posts from being deleted without removing the category. Thanks to doing that, we’ve dealt with the danger of losing the integrity of our database.

Series Navigation<< API with NestJS #63. Relationships with PostgreSQL and MikroORMAPI with NestJS #65. Implementing soft deletes using MikroORM and filters >>
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments