Suggestion: Allow Execution driver to execute multiple queries

[HendrixString]

DatabaseConnection has a executeQuery method. I believe it will be beneficial to add the plural form as well executeQueries, which receive an array of queries. This should be exposed from kysely class as well.

What is the gain ? Several dialects ( D1 / Turso ) drivers can use the plural form as intent to run their native batch command, which is a best practice for them. The base class or default implementation in executor shall have a uniform implementation of:

async executeQueries<R>(
    compiledQueries: CompiledQuery[],
    queriesIds: QueryId[],
  ): Promise<QueryResult<R>[]> {
  const results = []
  for(let ix = 0; ix < compiledQueries.length; ix++) {
    results.push(
       await this.executeQuery(compiledQueries[ix], queriesIds[ix])
    );
  }
  return results;
}

Then, other dialects can override this behavior in the driver level, is this too much to ask for ? It seems like an easy win with no cost to me :)

BTW, Drizzle has recognized this issue and supports it for al the mentioned databases above.

[koskimas]

BTW, Drizzle has recognized this issue and supports it for al the mentioned databases above.

Kysely is something that's written for shits and giggles by two people on their random spare time.

There's no competition going on with Drizzle, at least on our part. We don't care if something's implemented in Drizzle. That's not a reason for us to implement something.

We don't make money from this project. In fact, we lose money (and our mental health) doing this.

[koskimas]

I'm not saying we'll do this, but if we did, we could add an optional method to the Driver interface. That way all existing drivers would still work and this wouldn't be a breaking change.

But there are a lot of quesions to answer first:

Can all batched queries return a result? If so, we need to keep that type-safe.

We could have an API like this:

const [persons, pets] = await db.executeBatch([
  db.selectFrom('person').selectAll().where('id', '=', 1),
  db.insertInto('pet').values(pet).returningAll(),
])

We'd have a bunch of typed overloads up to certain amount of queries, and then a less type-safe version for an arbitrary array of queries.

But first we really need to figure out if this is generally useful. Now there has been one request for this.

[muniter]

Executing multiple statements at once is something I'm very interested on.

Use cases:

• Running contents of a .sql file
• Prepare a complex multi statement migration/procedure in any sql "IDE" like DataGrip, then use it for a migration or a procedure on the app.

Both use cases are for raw sql probably authored outside of normal code workflow that needs to be integrated later on into your application.