multiple row insert with db.sql / INSERT INTO

[maxweisel]

I'm aware of the db.insert() shortcut that can perform multiple-row insert, however, is it possible to accomplish the same thing with INSERT INTO without performing custom string concatenation?

I love that I can do this:

const [user] = await db.sql<s.users.SQL, s.users.Selectable[]>`
  INSERT INTO ${'users'} (${db.cols(user)})
  VALUES                 (${db.vals(user)})
  RETURNING ${'user_id'}
`.run(client)

I'd love to be able to do this:

const [user] = await db.sql<s.users.SQL, s.users.Selectable[]>`
  INSERT INTO ${'users'} (${db.cols(users[0])})
  VALUES                  ${db.rows(users)}
  RETURNING ${'user_id'}
`.run(client)

Where ${db.rows(users)} expands to something like:

(${db.vals(users[0])}),
(${db.vals(users[1])}),
(${db.vals(users[2])}),
...

I could write my own shortcut to do that, but I always fear writing something that generates an unsafe query.

Max

[jawj]

First, is there a reason you don't want to use the insert shortcut, which handles all this for you?

You'd just write

const userIds = db.insert('users', users, { returning: ['user_id'] }).run(pool);

If there is a reason, I guess we could either create something like your db.rows function suggestion, or possibly just export some of the utility functions the insert shortcut uses, where the key bits look like this:

const completedValues = completeKeysWithDefaultValue(values, Default);
const valuesSQL = mapWithSeparator(completedValues, sql`, `, v => sql`(${vals(v)})`);

Edit: ah, I now see you say you're aware of the insert shortcut. But it might be helpful if you could say why you prefer not to use it?

[maxweisel]

Mostly just for workflow reasons. Typically I'm prototyping these queries as SQL with psql. Some of them have become fairly complex and it's easier for me to take the final query and use the db.sql<>`` syntax than it is to try to back port the query to a series of db shortcuts + db.sql<>`` statements for the pieces that I can't represent using the shortcuts.

[jwhitmarsh]

I've just searched for the same thing, and I can't (please correct me if I'm wrong) use the insert shortcut because I need to set an ON CONFLICT... statement to the end of the query. Adding this to add weight to @maxweisel's request!

[jawj]

@maxweisel Fair enough, I'll look into adding your db.rows function (or similar), then.

@jwhitmarsh Thanks. Can I check that the upsert shortcut won't work for you?

[jwhitmarsh]

@jawj being a developer is endlessly humbling. Apologies for missing that, and thank you for the help!

[t7costa]

I'd also love this functionality to help when writing CTEs (with db.sql)