Wrap each query in a transaction (e.g. for audit trail)

[rogiervandenberg]

I am exploring how I could move away from Prisma in favor of Kysely. Reason is to have more control over our queries + performance. However, we have audit logging in place and I cannot get my head around to make this work with Kysely.

Right now, each query is wrapped in a transaction that sets a current_user_id runtime parameter in Postgres. This is then used by the triggers that are set up to create an audit log entry using that user ID.

➔ How could I do a similar thing with Kysely, having a global way to wrap each query in a transaction in order to 'inject' the current userID in Postgres, so my trigger logs what the user did? For context, this is the Prisma way. It is described further on their blog.

Do I need to create a Plugin, Dialect, that replaces queries in some situations? Or, create a custom function to update a model, that does this? Anything else I'm missing here? Any ideas are highly appreciated :)

[igalklebanov]

Hey 👋

Feels like..

Wrapping every query in a transaction part - a dialect. Passing a user id per query execution part - a plugin that's used on-the-fly (receives the user id) + dialect that uses the extra metadata when executing the query.

Alternatively, use Kysely purely for query building, and do execution externally. https://kysely.dev/docs/recipes/splitting-build-compile-and-execute-code

[rogiervandenberg]

Hey 👋 😊 Thanks for your ideas. I was thinking that query building every time would create more work in the end, as you need to 'think' of what to add and execute all the time.

Therefore, I wanted to go for the Plugin route. Using a plugin, that accepts the user ID, would then:

• Create a transaction
• Execute SELECT set_config('app.current_user_id', ${user}, TRUE)
• Execute the actual query that is built.

Great in concept. However, reading all docs and doing some trials was not successful today. Extending Kysely to make Dialects or a plugin (e.g how to just add something to a query?) is quite complex. Therefore I have decided on making my own Kysely repository files, having functions like findUsers, updateThingie, findWhatever, etc. that all take in a userID.

That userID is then used for 2 things:

• Row level security, making sure I can only select things I'm allowed to select for a specific user, by using a couple of db.with queries.
• Running the audit log queries manually on where I need them in a transaction on my updateThingie functions.

This seems to be working quite OK for now.

So thanks again for your insights, maybe when having more experience with Kysely I might try and build a plugin after all.

[igalklebanov]

Be sure to check https://github.com/kysely-org/awesome-kysely?tab=readme-ov-file#plugins, maybe those access control plugins there suite your use case.