#Executing raw sql with payload request's transaction.

8 messages · Page 1 of 1 (latest)

lapis field
#

Hi everyone,

I have a question related to the payload request transactions.

I'm having an N+1 query problem for some specific use cases. To optimize them the only solution I now see is writing raw sql queries.

In payload hooks, when I execute raw sql queries how can make them execute within the same request transaction.

I've tried this multiple times but failed to achieve the atomic execution.

req.payload.db.drizzle.execute(sql`-- Query`)

I really appreciate your help. Thank you 🙂

versed hatchBOT
lapis field
#

Found a way

versed hatchBOT
fleet acorn
#

If you found a way you should share it so others who search for this in the future save some time 🙂

lapis field
# fleet acorn If you found a way you should share it so others who search for this in the futu...

Def, I wrote this little helper

import type { sql } from '@payloadcms/db-postgres';

export type MaybePayloadRequest = Partial<PayloadRequest> & Pick<PayloadRequest, 'payload'>;
// i use this pattern to write functions which could be used with both payload request and just payload instance

export const execute = async <T>(req: MaybePayloadRequest, query: ReturnType<typeof sql<T>>) => {
  type Result = Awaited<ReturnType<PayloadRequest['payload']['db']['drizzle']['execute']>>;

  const db = req.payload.db;
  const txID = await req.transactionID;
  const txn = txID ? db.sessions?.[txID]?.db : null;

  if (txn) {
    return db.execute({
      // @ts-expect-error
      db: txn,
      sql: query,
    }) as Promise<Result>;
  }

  return db.drizzle.execute(query);
};

Usage:

export const Users: CollectionConfig = {
  slug: 'users',

  fields: [
    {
      name: 'firstName',
      type: 'text',
      required: true,
    },
  ],
  
  hooks: {
    afterOperation: [
      async ({ req, operation, result }) => {
         const data = await execute(req, sql`--query`); // query result
        return result;
      },
    ],
  },
};
lucid fable
#

Also, one way to avoid N+1 issues while still using the default payload functions is to provide a value in the context and use it to decide whether to run hooks.

If you want to run operations in the same transaction, you can do something like this:

const transactionID = await payload.db.beginTransaction()
try {
  await payload.create({
    collection: 'orders',
    data: orderData,
    req: { transactionID },
  })
  await payload.update({
    collection: 'inventory',
    id: itemId,
    data: { stock: newStock },
    req: { transactionID },
  })
  await payload.db.commitTransaction(transactionID)

If it's a nested operation inside of a hook, you can just pass the req (it includes a transactionId already) and the operation will run in the same transaction:

const resaveChildren: CollectionAfterChangeHook = async ({ collection, doc, req }) => {
  // Find children - pass req
  const children = await req.payload.find({
    collection: 'children',
    where: { parent: { equals: doc.id } },
    req, // Maintains transaction context
  })

  // Update each child - pass req
  for (const child of children.docs) {
    await req.payload.update({
      id: child.id,
      collection: 'children',
      data: { updatedField: 'value' },
      req, // Same transaction as parent operation
    })
  }
}
```
#

Message was too long, but I uploaded the snippets to show what I mentioend above about using a context value to control the hook execution.

📎 message.txt