feat: add database transactions

[jacobtipp]

I think this is a wonderful and unique example for using clean(ish) architecture in nextjs. Especially with the uses-cases. Although multiple writes in a single use-case is dangerous without using transactions. For example, in your registerUserUseCase there are multiple reads/writes going on in different tables.

Here are some comments pointing out the points of failure:

export async function registerUserUseCase(email: string, password: string) {
  const existingUser = await getUserByEmail(email); // read
  if (existingUser) {
    throw new Error("An user with that email already exists.");
  }
  const user = await createUser(email); // write
  await createAccount(user.id, password);

  const displayName = uniqueNamesGenerator({
    dictionaries: [colors, animals],
    separator: " ",
    style: "capital",
  });
  await createProfile(user.id, displayName); // write

  const token = await createVerifyEmailToken(user.id); // write
  await sendEmail(
    email,
    `Verify your email for ${applicationName}`,
    <VerifyEmail token={token} />
  ); // possible failure without any rollback to the previous writes

  return { id: user.id };
}

If any of those writes fail for whatever reason, none of the previous writes would be rolled back without a transaction. This can also happen with the sendEmail side-effect, if it fails, those previous writes will stay persisted without a rollback.

Perhaps a TransactionManager should be created that can be used in your use cases, where a scoped transaction context can be provided to your db-access functions. Although this would be a big change, as all your db-access functions would have an optional parameter that accepts the scope.

Something like this.

// transaction-manager.ts

import { database } from "@/db";
import { PgTransaction, PgQueryResultHKT } from 'drizzle-orm/pg-core';
import * as schema from '@/db/schema';
import { ExtractTablesWithRelations } from 'drizzle-orm';

export type TransactionScope = PgTransaction<
  PgQueryResultHKT,
  typeof schema,
  ExtractTablesWithRelations<typeof schema>
>;

export type TransactionCallback<T> = (trx: TransactionScope) => Promise<T>;

export class TransactionManager {
  async runInTransaction<T>(operation: TransactionCallback<T>): Promise<T> {
    return await db.transaction(async (trx) => {
      return await operation(trx);
    });
  }
}

// export the singleton since this class is stateless
export const transactionManager = new TransactionManager();

And your revised registerUserUseCase:

export async function registerUserUseCase(email: string, password: string) {
  return await transactionManager.runInTransaction(async (trx) => {
    const existingUser = await getUserByEmail(email, trx); // Pass the transaction context
    if (existingUser) {
      throw new Error("A user with that email already exists.");
    }

    const user = await createUser(email, trx); // Pass the transaction context
    await createAccount(user.id, password, trx); // Pass the transaction context

    const displayName = uniqueNamesGenerator({
      dictionaries: [colors, animals],
      separator: " ",
      style: "capital",
    });

    await createProfile(user.id, displayName, trx); // Pass the transaction context

    const token = await createVerifyEmailToken(user.id, trx); // Pass the transaction context

    await sendEmail(
      email,
      `Verify your email for ${applicationName}`,
      <VerifyEmail token={token} />
    );

    return { id: user.id };
  });
}

An example db-access function with the new api change

import { TransactionScope } from '../transaction-manager';

export async function createUser(
  email: string,
  trx?: TransactionScope
) {
  // Use the provided transaction context if available, otherwise fallback to the regular db client
  const dbClient = trx || database;

  const [user] = await dbClient
    .insert(users)
    .values({
      email,
    })
    .returning();

  return user;
}

Now, any db-access function can optionally be used in a transaction or with the default global database client.

This is something I'm building in my personal projects but if you're interested I'd be willing to make a pull request, or maybe you have a better implementation in mind.

[jacobtipp]

Actually, I do see that you have a createTransaction function that already does this for updating the password, so no need for this.