Middleware/wrapper to wrap all queries in a transaction

[ksorensen76]

Environment

Knex version: Knex 3.0.1
Database + version: PostgreSQL 14.11
OS: Ubuntu

Feature discussion / request

I am working on automating audit logging for a Next.js application. I have a trigger function set up to run on insert/update/delete and the issue comes when trying to pass the application user's id (not the PostgreSQL user) to the trigger. I have got it working manually with the following code, but would like to be able to wrap all queries in the transaction to automate things a bit more.

    const result = await db().transaction(async (trx) => {
      await trx.raw('SET LOCAL my_app.app_session_user = ${session.user.id}')
      const insert = await trx.insert(data)
        .into('${schema}.${tableName}')
      return insert
    })

Is there currently a middleware or other method to wrap the insert query above, adding the transaction and setting the app_session_user to all queries automatically so it can be accessed in the trigger function?

Or maybe I am thinking about this all wrong, if so, any suggestions on a better method would be appreciated.

[Mahamed-Belkheir]

I'm not sure of your usecase, personally I would probably just not use triggers for this case and manually invoke things in the application myself.

But passing the transaction around is a problem I had to solve, and the way I went about it is using AsyncLocalStorage, I write all my DB queries in repository classes, which allows me to wrap up knex with my own function that basically checks ASL for a transaction, if it exists, uses it, and returns the query builder.

This method also works for other "context" things, like the user ID, we're building a multi tenant application and also made use of the repository layer to store tenant context in ASL and centralize the safeguards (e.g. if a query is running and we somehow we did not authenticate a user, it would always fail without manually setting ASL for edge cases)

It doesn't look like you're using a repository pattern, but you can still do a similar approach with a function

const trxStore = new AsyncLocalStorage<KnexTransaction>;

export getKnex() {
  const q = knex()
  const trx = trxStore.get();
  if (q) {
    trx.transacting(trx)
  }
  return q
}

You can set the transaction in ASL using either a helper function or a middleware in express/whatever framework you use, really up to you, though I don't suggest starting a transaction on every request by default