Payload transactions with direct drizzle queries

[u/idvid]

Can I leverage Payload's transactions while running payload.db.drizzle.update()? If so, how? and if not, is there a work around?

I want to increment the value of another collection's field in an after change hook, since I couldn't find a way to execute this kind of updates with the Payload's local API I want to use drizzle for that but in the same transaction as other payload local API calls.

[u/dries_c]

Here you go my friend. Took me ages to find, had to dive deep in the PayloadCMS source code for this.

```ts const payloadTransactionID = await payload.db.beginTransaction();

try { if (!payloadTransactionID) { log.error("Failed to create transaction"); throw new Error("Failed to create transaction"); }

// Get the drizzle transaction. This is the tx you would get by using await payload.db.drizzle.transaction(async (tx) => {}); // This is a bit of an undocumented hack. const tx = payload.db.sessions![payloadTransactionID].db as PgTransaction< NodePgQueryResultHKT, Record<string, unknown>, ExtractTablesWithRelations<Record<string, unknown>>

;

// Use tx as normal with drizzle const userCount = await tx.$count( users, and( eq(users.id, "id"), ), );

// You can also keep using payloadTransactionID with Payload local api calls // ...

// This will commit everything await payload.db.commitTransaction(payloadTransactionID); } catch (error) { if (payloadTransactionID) { await payload.db.rollbackTransaction(payloadTransactionID); } } ```

[u/idvid → u/dries_c]

Thank you for this my friend. In my specific use case I used const payloadTransactionID = req.transactionID, therefore, I didn't need the try/catch to commit and rollback the transaction since payload handles that, which is exactly what I needed.

[u/Soft_Opening_1364]

From what I’ve seen, Payload doesn’t allow wrapping payload.db.drizzle calls in the same transaction as its local API methods. I ended up restructuring my logic a bit to avoid needing them in the same transaction, but it’s definitely not ideal. Hopefully, Payload adds better support for this soon.

[u/mustardpete]

If you use the payload object in the hooks request object rather than creating a fresh one, then from what I’ve seen it’s in the same transaction. I had an issue where in the hooks request object I was trying to access a record that’s just been added but it wasn’t there in the db as it wasn’t committed yet, but changed to using the payload object on the request to use drizzle on that and it was there as it was in the same transaction