`SELECT` in SQL template tag has parens around it, making it invalid in some contexts in SQLite

[AlexErrant]

Hi, I'm trying to convert this to kysley:

INSERT INTO "cardTag"
SELECT
  KEY AS cardId,
  value AS tag
FROM json_tree(blah)
EXCEPT
  SELECT *
  FROM "cardTag"
  WHERE "cardId" IN (?, ?, ?)

It uses the sqlite specific json_tree, a table-valued function, so I elect to use a SQL template string:

await db
  .insertInto('cardTag')
  .expression(
    (qb) => sql<CardTag>`
SELECT key   AS cardId,
       value AS tag
FROM json_tree(blah)
EXCEPT
${qb.selectFrom('cardTag').selectAll().where('cardId', 'in', cardIds)}`, // the problem is here
  )
  .execute()

Unfortunately, the injected select statement has parens around it, which makes it invalid in sqlite (as noted here):

INSERT INTO "cardTag"
SELECT
  KEY AS cardId,
  value AS tag
FROM json_tree(blah)
EXCEPT
( --this parens breaks sqlite
  SELECT *
  FROM "cardTag"
  WHERE "cardId" IN (?, ?, ?)
)  --this paren breaks sqlite

I tried to work around this by parameterizing just cardIds, the array, but it doesn't handle the array of ids properly:

await db
  .insertInto('cardTag')
  .expression(
    (qb) => sql<CardTag>`
SELECT key   AS cardId,
       value AS tag
FROM json_tree(blah)
EXCEPT
SELECT * FROM cardTag where cardId in ${cardIds}`,
  )
  .execute()

INSERT INTO "cardTag"
SELECT
  KEY AS cardId,
  value AS tag
FROM json_tree(blah)
EXCEPT
SELECT *
FROM cardTag
WHERE cardId IN ? -- not correct

How can I fix this? Here's the playground. Thanks!

[alenap93]

i think that we have to override visitSelectQuery in sqlite query compiler, removing wrapInParens ... https://github.com/kysely-org/kysely/blob/deea8e22aae3dc4e142f055b6ec4d9a50a402bda/src/query-compiler/default-query-compiler.ts#L143-L150

I'm available for a PR

[AlexErrant]

After rubbing some braincells together I realized I could fix my workaround using sql.join:

await db
  .insertInto('cardTag')
  .expression(
    (qb) => sql<CardTag>`
SELECT key   AS cardId,
       value AS tag
FROM json_tree(blah)
EXCEPT
SELECT * FROM cardTag where cardId in (${sql.join(cardIds)})`, // duh
  )
  .execute()

Gonna leave this issue open though because it's still a workaround.

[koskimas]

You can build the whole query without any raw SQL https://kyse.link/w9kuZ