A transaction rollback should go through Prisma middleware

[okomarov]

Problem

We use Prisma middleware to audit log database operations. If we're running those operations in a transaction, and the transaction rolls back, the middleware has no way to catch the rollback. This results is operations which were rolled back to be audit logged.

For example:

prisma.$use(async (params, next) => {
  // log params.args.data to another table
})

where the params are something like:

{
  "args": {
    "data": {
      "id": "2c8b0c37-782c-4713-8dd7-75bdedb1aea2",
      "num_assets": 3,
      "date": "2022-06-03T00:00:00.000Z",
    }
  },
  "dataPath": [],
  "runInTransaction": true,
  "action": "create",
  "model": "Transfer"
}

Notice "runInTransaction": true which indicates this operation is running in a transaction, namely to check permissions, and rolls back if permissions are denied (this is another CASL + Prisma limitation on why we need the model to exist to check permissions).

Suggested solution

Generate a prisma transaction uuid and trigger the middleware on rollback too. So we can audit log the operation with the transaction id and on rollback remove those from the audit table.

Context

We use the interactive transaction API, e.g. prisma.$transaction(async () => { ... })

[matthewmueller]

We use Prisma middleware to audit log database operations. If we're running those operations in a transaction, and the transaction rolls back, the middleware has no way to catch the rollback. This results is operations which were rolled back to be audit logged.

Thanks for the clear problem statement!

Are you using the interactive transaction API, e.g. prisma.$transaction(async () => { ... })

Or the regular batch API, e.g. prisma.$transaction([fn1, fn2]) ?

[okomarov]

We use the interactive transaction API (also edited original post).

[matthewmueller]

Thanks! We're actively working on making interactive transactions generally available, so we'll be looking into seeing if this is possible soon.

[millsp]

Hey @okomarov, have you tried to do something like this with await next(params):

import { PrismaClient, Prisma } from "@prisma/client"

async function main() {
    const prisma = new PrismaClient()

    prisma.$use(async (params, next) => {
        try {
            return await next(params)
        } catch (e) {
            if (params.runInTransaction) {
                console.log('tx failed with', params)
            }
        }
      })

    const email = `user.${Date.now()}@prisma.io`

    await prisma.$transaction(async (prisma) => {
        await prisma.user.create({
            data: {
                email: email
            }
        })

        await prisma.user.create({
            data: {
                email: email
            }
        })
    })
}

void main().catch((e) => {
    console.log(e.message)
    process.exit(1)
})

[janpio]

Hey @okomarov, did you have a look at the possible approach that Pierre posted? Would that work for you? If not, why?

[okomarov]

Haven't tested as we moved audit logging to the database with triggers. Way fewer headaches.

As far as I'm aware what Pierre suggests, ie log only if transaction is successful needs to happen right after next(params) or otherwise it can still get logged, fail and not rolled back.

Again, I think it's easier to rely on database triggers for this level of consistency rather than relying on order of execution of middleware and within the middleware

[janpio]

I think Pierre only suggested that you can try-catch to react to a rollback that happens in a transaction - how you would use that, is up to you.

But I don't disagree, audit logging might be more adequate to do via database functionality if that is an option for you.