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

June 17, 2024

This entry is part 153 of 180 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:

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<typeof databaseSchema>

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

users.service.ts

import {

BadRequestException,

Injectable,

NotFoundException,

} from '@nestjs/common';

import { DrizzleService } from '../database/drizzle.service';

import { databaseSchema } from '../database/database-schema';

import { eq } from 'drizzle-orm';

import { PostgresErrorCode } from '../database/postgres-error-code.enum';

import { isDatabaseError } from '../database/databse-error';

import { PostgresTransaction } from '../database/postgres-transaction';

@Injectable()

export class UsersService {

constructor(private readonly drizzleService: DrizzleService) {}

// ...

async delete(userId: number, transaction?: PostgresTransaction) {

const database = transaction ?? this.drizzleService.db;

try {

const deletedUsers = await database

.delete(databaseSchema.users)

.where(eq(databaseSchema.users.id, userId))

.returning();

if (deletedUsers.length === 0) {

throw new NotFoundException();

}

} catch (error) {

if (

isDatabaseError(error) &&

error.code === PostgresErrorCode.ForeignKeyViolation

) {

throw new BadRequestException(

'Can not remove a user that is an author of an article',

);

}

throw error;

}

// ...

}

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

users.service.ts

import { Injectable } 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));

await this.delete(userId, transaction);

});

}

We could take it further and move the logic of removing the articles to the ArticlesService. 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 >>