How to use the mysql2 js library ? syntax to interpolate in a subquery INSERT statement?

Question

I have been banging my head on this issue and the 'usual semantic lookup tool suspects' (google and others) have been of no help

Specifically, I am using mysql2/promise library and attempting to complete a successful INSERT statement that looks like this:

const query = `INSERT INTO sso_user (user_id,username,user_status,organization_id) VALUES ?, (select organization_id from organization where organization_name= ?)`;
const [rows] = await conn.query(query, [valueArray]);

the valueArray looks something like:

[["uuid_t349199e","[email protected]","active","my sample org"] ]

And of course, I am attempting to insert a user record whose organization_id value looks up to an existing organization value.

I have been able to successfully complete this transaction written out as a normal query, however I keep getting various binding and straight up obscure "error at position 279" errors

Is this interpolation for an INSERT query that also includes a subquery lookup even possible in the mysql2/promise library?

Edit I should add I have also attempted variations of specifying the number of ? that match each value, and I have had no luck there as well

Edit2 this is an example error message that is somewhat constructive when I attempt to run the insert query with enumerated ? characters

Error: target: core.-.primary: vttablet: rpc error: code = InvalidArgument desc = missing bind var v1 (CallerID: auhldnxzu7h15g8j56ms)

Answer 1

You can't use a sub SELECT query for a single column since it could return multiple rows.

Instead, construct the sub query to return a combination of your static and dynamic values

const query =
  'INSERT INTO sso_user (organization_id, user_id, username, user_status) ' +
  'SELECT organization_id, ? FROM organization WHERE organization_name = ?';
const results = await Promise.all(
  valueArray.map(([uid, un, s, on]) => conn.query(query, [[uid, un, s], on])),
);

According to the docs, the [uid, un, s] array should be treated as a list, creating a sub-query like

SELECT organization_id, 'uuid_t349199e', '[email protected]', 'active'
FROM organization WHERE organization_name = 'my sample org'