search

kysely-org / kysely

A type-safe typescript SQL query builder
https://kysely.dev
MIT License
10.22k stars 259 forks source link

Best practice for batch queries / mutations #901

Closed HendrixString closed 5 months ago

HendrixString commented 5 months ago

Hi 🤗 Kysely bros,

I love ❤️ the project, keep up the good work.

So, i was wondering, what is the current best practice for a transaction, that is made up in a single execution ?

Long story short, i am laying my eyes on remote databases such as Turso and Cloudflare D1. Interactive transaction is discouraged and most of the times I dont need it, I just need a batch of statements and send them as one execution where they will run on the server as a wrapped transaction.

What is the Kysely way to make it fly ?

koskimas commented 5 months ago

There's no built-in way to run batch queries. What you can do is use Kysely purely as a query builder an then concatenate the generated queries together

https://kysely.dev/docs/recipes/splitting-query-building-and-execution

HendrixString commented 5 months ago

@koskimas Thanks for looking at it. of course, this was my first goto, but, I feel it is more than needed to have an organic support for batch mutations in the driver level. It is a very common practice with cloud databases that incur latency to have such a feature for a batch of mutations.

Will you consider a feature like this ? an accumulating batch of only mutations in a transaction ? Turso / D1 / PlanetScale / Neon will benefit from this. I am not sure if this is easy to develop, But please consider, that if you support something like interactive transaction, then it should be possible to have a variant of it.

Again, I believe this feature is a critical feature and also a mature feature. This is not niche. It should fit perfectly in a minimalist perspective. A lot of value with minimal changes.

HendrixString commented 5 months ago

Actually, I think I can get away with this feature by using a transaction executeQuery ? Can i batch queries with compile ? including parameters ? and then feed it into trx.executeQuery(...) ?

koskimas commented 5 months ago

Picking certain queries (mutations) from a transaction and batching them into a single query definitely isn't something a query builder should do. That sounds like a terrible idea in general.

If you need something like that, you can build a custom dialect for it. It should be possible to do in the driver level.

HendrixString commented 5 months ago

@koskimas Thanks for pointing out my terrible ideas :), but you are not just a query builder, you also have a driver support. So, make up your mind about it.

Anyway, if a transaction has trx.executeQuery(..) method, how can I concatenate multiple queries into it ? suppose I compiled two queries and I want to use the trx.executeQuery(..)