custom prisma transaction rollbacks for handled errors

[drakedeatonuk]

@Papooch, I was hoping for some advice on implementing a custom rollback method when errors are handled rather than thrown. I've added the relevant code below.

I'm using a Result type that wraps all prisma methods:

export type TOk<T = void> = T extends void ? { ok: true; } : { ok: true; val: T; };
export type TErr<E extends Error = Error> = { ok: false; err: E; };
export type Result<T = void, E = Error> = { ok: true; val: T; } | { ok: false; err: E | Error; };

Example method call:

@Transactional()
async create<T extends Prisma.UserCreateArgs>(
  args: T
): Promise<Result<Prisma.UserGetPayload<T>>> {
  return this.txHost.tx.user
        .create(args)
          // where 'ok' returns TOk
          .then(ok)
          // where 'err' returns TErr
          .catch(e => err("e::user-create-failed"))
}

I'm thinking of extending the TransactionalAdapterPrisma class like so:

optionsFactory = (prisma: TClient) => ({
    wrapWithTransaction: async (
        options: PrismaTransactionOptions,
        fn: (...args: any[]) => Promise<any>,
        setClient: (client?: PrismaTransactionalClient<TClient>) => void,
    ) => {
        return await prisma.$transaction(async (p) => {
            setClient(p);
            
            const result: unknown | Result<unknown> = await fn();
            if (isResult(result)) 
              return result.ok
                ? result
                : p.$executeRaw`ROLLBACK;`
                  .then(() => result)
                  .catch((e) => {
                    console.error("e::rollback-failed", e);
                    return result;
                  });
                 
          return result
        }, options);
    },
    getFallbackInstance: () => prisma,
});

My understanding is that this will roll back the transaction both if:

1. fn throws an error; and if,
2. fn returns TErr

Couple questions on the above:

1. does this approach look sound & is my understanding correct?
2. will p.$executeRawROLLBACK;` work for manually rolling back transactions? I've seen that prisma doesnt yet have an ORM method for rolling back transactions, but I saw my proposed solution was recommended here.

Apologies if these are basic questions, I'm new to managing prisma transactions directly. I previously had a more manual rollback system which was flawed as you rightfully point out to me last year! This solution is based on this issue I saw although hopefully this discussion encapsulates the solution a bit more completely.

Thanks in advance 🙏

[Papooch]

Hi, your reasoning seems correct. The result unwrapping must be done in the adapter in order for the rollback to work.

With the addition of support for nested transactions, the adapter is a bit more complicated now, but it also means you can use the built-in rollbackTransaction directly, since I'm no longer using managed transactions there.

EDIT: I mixed it up with the Drizzle ORM adapter, there's no rollbackTransaction method on Prisma.

Let me know if you have any trouble with the implementation.

[drakedeatonuk]

Thanks for the info @Papooch . Where exactly is the rollbackTransaction method? I can't find any references to it in the nestjs-cls repo.

[Papooch]

I'm sorry, I got it confised with the Drizzle ORM adapter.

If you look at the implementation of DrizzleAdapter, you can see that the transaction is handled explicitly and the correct transaction statements are selected based on the provided SQL flavor instead of using the $transaction callback.

That is currently the only way to implement transaction savepoints with Prisma.

If you don't require any of that, then I think your suggested implementation should work, too.

However, to avoid any potential errors related to manually rolling back a managed transaction and then resolving the promise instead of throwing, I would suggest the following implementation:

// remember whether a result was returned from fn
let resolvedWithResult = false
try  {
  return await prisma.$transaction(async (p) => {
    setClient(p);
    const result: unknown | Result<unknown> = await fn();
    if (!isResult(result))
      // return normally when we don't have a result 
      return result
    }
    // otherwise remember that we had a result
    resolvedWithResult = true
    if (result.ok) {
       return result
    } else {
       // if it is an error, throw the underlying err
       // so that it triggers the rollback
       throw result.err
    }
  }, options)
} catch (e) {
  if (resolvedWithResult) {
    // if we had a result, wrap the thrown error back into one
    // and return it normally
    return err(e)
  } else {
    // otherwise rethrow the original error
    throw e
  }
}

[drakedeatonuk]

Right okay. I will likely need savepoints. Most backend workflows I have will likely use the Propagation.Required, but I'm sure there's some that will call for Propagation.Nested too, so I'll go with the more robust solution you've proposed 🙏

Slight tangent. It's tricky with the Result monad. There are handled errors where you want to rollback everything (e.g. a db failure), and there's other handled errors where that you may want to trigger additional actions (e.g. db returns null unexpectedly, card payment declines...). It's possible I'm actually overusing the Result monad perhaps. Anyways - do you ever find with the transactional decorator that there are some edgier cases where it's hard to get the rollback behaviour just right?