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.

[darktohka]

Seconded, this feature would be very useful.

[eikowagenknecht]

That would be very nice to have for running .sql files.

For me, it's to create a new database from import an exact .sql export of a SQLite database that I have to use as an initial state.

Luckily, there's a workaround for this simple case where I don't need look at any results of the execution:

const statements: string[] = contentOfSomeSqlFile
  .split(";")
  .map((s) => s.trim())
  .filter((s) => s.length > 0);

for (const statement of statements) {
  await db.executeQuery(CompiledQuery.raw(statement));
}

[igalklebanov]

Hey 👋

PostgreSQL allows to send multiple statements in the same query string - given there are no parameters. This is a major limitation.
It also has "pipeline mode" which breaks request-response, allowing to send multiple requests before receiving a single response on the same connection. This enables driver libraries to provide "batch-like" methods that multi-query, not multi-statement, under the hood.

pg allows to send multiple statements by default. It doesn't support "pipeline mode".
postgres supposedly uses "pipeline mode" and provides a const results = await sql.begin(sql => [sql, ...]) method to pipeline multiple queries.
@neondatabase/serverless's fetch wrapper allows sending multiple queries in a non-interactive transaction request.
@electric-sql/pglite supports sending multiple statements with exec without returning results. It's sql and query methods support single statement only.

--

MySQL allows to send multiple statements in the same query string - disabled by default.

mysql2 allows to send multiple statements when multipleStatements: true.
mariadb supports pipelining, but it's unclear if this works in MySQL too.

--

MSSQL allows to send multiple statements in the same query string.
It also has MARS which is similar to PostgreSQL's pipeline mode.

tedious supports sending multiple statements. They'll never support MARS.

--

SQLite? not really, and pretty weird to even ask.

better-sqlite3 has exec for multiple statements, but it is not recommended.
@libsql/client has executeMultiple for multiple statements, without returning results. It also has batch to run multiple queries in a non-interactive transaction.
@tursodatabase/serverless has a similar batch method.

[WilcoKruijer]

For reference node-postgres has an open PR to support pipeline mode: brianc/node-postgres#3357

The benefit of pipelining as described in the Postgres manual:

Pipeline mode is most useful when the server is distant, i.e., network latency (“ping time”) is high, and also when many small operations are being performed in rapid succession. There is usually less benefit in using pipelined commands when each query takes many multiples of the client/server round-trip time to execute. A 100-statement operation run on a server 300 ms round-trip-time away would take 30 seconds in network latency alone without pipelining; with pipelining it may spend as little as 0.3 s waiting for results from the server.

The reactions (thumbs up / hearts) in this thread show that there is at least some interest in this functionality.

@igalklebanov I'd argue that batching does not make much sense for pglite, same as sqlite since they're both meant to run in-process.

On @koskimas 's concern of type-safety: a builder-pattern could be used for fully type-safe results with no limit on the amount of queries:

const [person,,] = await db
  .batch()
  .add(db.selectFrom('person').selectAll().where('id', '=', 1))
  .add(db.updateTable('person').set({ active: true }).where('id', '=', 1))
  .add(db.deleteFrom('pet').where('id', '=', 123))
  .execute()

What do you think?

I've built a PoC for this BatchBuilder here.