Inserting multiple rows at once

[atjeff]

Hey guys, not sure if I'm missing something, but does this client not support inserting more than one row at a time? Having trouble finding any mention in planetscale docs, readme, and the code in this library

connection.execute(
  `
	  INSERT INTO test (id)
	  VALUES (?)
  `,
  [[1], [2]]
)

I've also tried:

VALUES (??)
VALUES ??
VALUES ?

etc

[orware]

Hello @atjeff,

This may be able to be improved within database-js directly, and I'll let others on our team chime in on that point, but I am wondering if this may simply be SQL syntax related more than anything else.

For example, the SQL syntax for inserting multiple records usually looks something like this:

INSERT INTO test (id)
VALUES (?), (?), (?), ...;

Depending on how many rows you are planning to add at one time, which looks a bit different than the examples you shared above.

I'm not sure if it would work, but if you know how many rows you are going to pass in, you could make the placeholder section of your query dynamic, and include as as many placeholders as rows you are planning to insert, and then pass in your values and that may work for this simplified table example you had shared:

// Example knowing you have two values you'll be passing in:
connection.execute(
  `
	  INSERT INTO test (id)
	  VALUES (?), (?)
  `,
  [[1], [2]] // This may have to change as well to [1, 2] instead
)

Another option you may be interested in exploring would be the kysely-planetscale dialect for kysely, which provides additional functionality on top of the basic features database-js provides, and includes built-in methods such as one for performing a multiple row insert that you can take advantage of.

[atjeff]

Thank you

[hyperscientist]

A little feedback if someone is looking for an answer to this. I am creating queries with 35M characters which are inserting 5k rows at a time and it works perfectly fine. Such a query takes ~20s to execute in my case.

[dmarcucci]

In case anybody else is looking at this later, you do indeed need to flatten the values to a single array (so using the example above, [1, 2] and not [[1], [2]]).