Transactions with PostgreSQL and MikroORM
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…
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 BEGIN and COMMIT. 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.BEGIN; --Deleting posts that belong to a given category DELETE FROM post_entity WHERE id IN ( SELECT post_entity_id FROM post_entity_categories WHERE category_id = 1 ); --Disconnecting posts from categories DELETE FROM post_entity_categories WHERE category_id=1; --Deleting the category DELETE FROM category WHERE id=1; COMMIT;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.BEGIN; DROP TABLE "post_entity_categories"; ROLLBACK;Thanks to using ROLLBACK, the post_entity_categories 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 import { Module } from '@nestjs/common'; import { ConfigModule, ConfigService } from '@nestjs/config'; import { MikroOrmModule } from '@mikro-orm/nestjs'; import { FlushMode } from '@mikro-orm/core/enums'; @Module({ imports: [ MikroOrmModule.forRootAsync({ imports: [ConfigModule], inject: [ConfigService], useFactory: (configService: ConfigService) => ({ flushMode: FlushMode.ALWAYS, // ... }), }), ], }) export class DatabaseModule {}With FlushMode.ALWAYS, MikroORM flushes before every query. Therefore, using it would prevent us from implementing transactions by delaying the flush. With FlushMode.AUTO, MikroORM sometimes flushes implicitly, which might be a little surprising. posts.service.ts async createPost(post: CreatePostDto, user: User) { const postData = { ...post, author: user, }; const newPost = await this.postRepository.create(postData); // creating a new post, but not flushing it yet this.postRepository.persist(newPost); // querying all of the current posts const allCurrentPosts = await this.postRepository.findAll(); const isNewPostPersisted = allCurrentPosts.some(post => { return post.id === newPost.id; }) console.log(isNewPostPersisted); // true return newPost; }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 FlushMode.COMMIT option that aims to delay the flush until the current transaction is committed. database.module.ts import { Module } from '@nestjs/common'; import { ConfigModule, ConfigService } from '@nestjs/config'; import { MikroOrmModule } from '@mikro-orm/nestjs'; import { FlushMode } from '@mikro-orm/core/enums'; @Module({ imports: [ MikroOrmModule.forRootAsync({ imports: [ConfigModule], inject: [ConfigService], useFactory: (configService: ConfigService) => ({ flushMode: FlushMode.COMMIT, debug: configService.get('SHOULD_DEBUG_SQL'), // ... }), }), ], }) export class DatabaseModule {} We also use debug to investigate what queries MikroORM is performing. Delaying flushing to implement transactions Let’s start by making some adjustments to our PostsService: posts.service.ts import { Injectable } from '@nestjs/common'; import { InjectRepository } from '@mikro-orm/nestjs'; import { EntityRepository } from '@mikro-orm/core'; import PostEntity from './post.entity'; import PostNotFoundException from './exceptions/postNotFound.exception'; @Injectable() export class PostsService { constructor( @InjectRepository(PostEntity) private readonly postRepository: EntityRepository