SQL transactions with the Drizzle ORM
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…
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: reducing the first account’s balance by $500, 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: we withdraw $500 from the first account, 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 BEGIN statement. Next, we write the queries we want to include in the transaction and finish with the COMMIT keyword to save our changes.BEGIN; UPDATE bank_accounts SET balance = 500 WHERE id = 1; UPDATE bank_accounts SET balance = 1500 WHERE id = 2; COMMIT; 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 ROLLBACK keyword. Transactions with the Drizzle ORM To run multiple queries in a transaction using Drizzle, we use the transaction() function. Let’s write a function that deletes a user together with all of their articles. users.service.ts import { Injectable, NotFoundException, } from '@nestjs/common'; import { DrizzleService } from '../database/drizzle.service'; import { databaseSchema } from '../database/database-schema'; import { eq } from 'drizzle-orm'; @Injectable() export class UsersService { constructor(private readonly drizzleService: DrizzleService) {} // ... deleteWithArticles(userId: number) { return this.drizzleService.db.transaction(async (transaction) => { await transaction .delete(databaseSchema.articles) .where(eq(databaseSchema.articles.authorId, userId)); const deletedUsers = await transaction .delete(databaseSchema.users) .where(eq(databaseSchema.users.id, userId)) .returning(); if (deletedUsers.length === 0) { throw new NotFoundException(); } }); } }What’s crucial is that we need to use the transaction. delete function instead of this.drizzleService.db.delete. When using PostgreSQL, we handle a pool of multiple clients connected to the database. Using the transaction 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 transaction.rollback() 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 import { PgTransaction } from 'drizzle-orm/pg-core'; import { NodePgQueryResultHKT } from 'drizzle-orm/node-postgres'; import { databaseSchema } from './database-schema'; import { ExtractTablesWithRelations } from 'drizzle-orm'; export type PostgresTransaction = PgTransaction< NodePgQueryResultHKT, typeof databaseSchema, ExtractTablesWithRelations