Merging multiple queries

[HendrixString]

Hi,

I wonder about the practice of the following dilemma :

I have 2 compiled mutation queries and I want to use db.executeQuery() or the transaction variant tx.executeQuery(..) on the two compiled queries at once. How do I do that with one invocation ?

[HendrixString]

@koskimas Any lead or tip would be very appreciated

[thelinuxlich]

The mysql driver has an option for that but it's bad practice.

[HendrixString]

bad practice to send multiple mutations in one go ? I Never heard of such thing. This will be the only way to create mutation transactions in many databases

[thelinuxlich]

https://github.com/mysqljs/mysql#multiple-statement-queries

[HendrixString]

@thelinuxlich That's not what I meant. I meant multiple mutation statements sent to the db at once, think like a batch write.

[thelinuxlich]

This is exactly about sending multiple statements at once, if you want a batch insert, Kysely supports it

[HendrixString]

@thelinuxlich How is it supported ?

[thelinuxlich]

You can insert an array of values

[HendrixString]

@thelinuxlich This is not what I need, thank you anyway. @igalklebanov @koskimas Do you have suggestions about how to combine two or more mutations into one raw sql with parameters and all ?

[thelinuxlich]

If it's not what you need, please provide an example.

[igalklebanov]

Hey 👋

Sending multiple SQL statements in the same request is considered a bad practice. Mostly because it opens you up for SQL injection. We don't support this pattern in Kysely core.

However, the community can definitely come up with an implementation.

I have shared a TS playground on discord in the past with a rough implementation for MySQL. Will look into generalizing it and publishing it as a separate library.

[HendrixString]

@thelinuxlich @igalklebanov kysely is a query builder, so I should be able to create:

begin transaction
1. insert statement
2. delete statement
commit

I have compiled both statements already, and I will need to use it in a batch write. This batch writes and deletes don't depend on interactive input, they are pure functions. Why should I send a statement -> wait for something that i dont use and then send another statemment, instead I can send everything in batch

[thelinuxlich]

Isn't that what happens inside a Kysely transaction?

[HendrixString]

@thelinuxlich Almost, in Kysely transaction, it executes each command. each execution might be a roundtrip in the cloud. Which is problematic, because interactive transactions are discouraged because they are usually time bounded. On the other hand, pure batch mutations are excellent.

[igalklebanov]

Kysely being a query builder doesn't mean it has to support all possible queries with API methods. We provide escape hatches so you can still execute stuff that's not supported.

Have you tried Promise.all?

Have you tried sql.join([query0, query1], ';').execute(trx) ?

[HendrixString]

@igalklebanov I just asked what a good practice will be, I didn't ask features to be added because of my needs. God bless, I can program things, I just wanted an advise from the Kysely experts here. I am leaning towards sql.join([query0, query1], ';').execute(trx), I hope it will rewrite the parameters, will it ?

[igalklebanov]

I'm in a phone booth at the office. 😅

You can also try:

sql`${query0};${query1}`.execute(trx)

We have a playground at https://kyse.link

[HendrixString]

@igalklebanov Sababa, i will give it a go, thanks

[igalklebanov]

See last suggestion - https://kyse.link/1sFlF

[HendrixString]

@thelinuxlich @igalklebanov Thanks, I appreciate the help