Kysely•4mo ago

Is `sql.join` the best way to concatenate sql templates?

I have some business logic that incrementally builds up a sql WHERE query as a string. It looks like I can't do the dumb thing and just string concat the pieces together like so:

let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `

let q = sql.raw` noteFtsFv.rowid ` + sql.raw` in `

So instead, I build a list of sql.raw and use sql.join with an empty string like so:

 const things = [
  sql.raw(" noteFtsFv.rowid "),
  sql.raw(" not "),
  sql.raw("IN (SELECT rowid FROM noteFtsFv WHERE noteFtsFv.fieldValues MATCH "),
  "some user input",
  sql.raw(" ) "),
]

let q = sql.join(things, sql``) as RawBuilder<SqlBool>

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(q)
  .executeTakeFirst()

 const things = [
  sql.raw(" noteFtsFv.rowid "),
  sql.raw(" not "),
  sql.raw("IN (SELECT rowid FROM noteFtsFv WHERE noteFtsFv.fieldValues MATCH "),
  "some user input",
  sql.raw(" ) "),
]

let q = sql.join(things, sql``) as RawBuilder<SqlBool>

const person = await db
  .selectFrom('person')
  .select(['id', 'first_name'])
  .where(q)
  .executeTakeFirst()

Is this the best way or am I missing a better solution 😅

2 Replies

koskimas•4mo ago

Why are you building the where statement using raw SQL? You can easily do that using the query builder That's kind of the whole point of it And you should never ever ever ever ever concatenate user input to raw SQL. You'll get your employer bankrupted or at the very least, yourself fired. That's like the first lesson of running SQL queries.

AlexErrant•4mo ago

To be clear, the generated SQL is parameterized:

SELECT
  "id",
  "first_name"
FROM
  "person"
WHERE
  noteFtsFv.rowid NOT IN (
    SELECT
      rowid
    FROM
      noteFtsFv
    WHERE
      noteFtsFv.fieldValues MATCH $1
  )
  AND $2

SELECT
  "id",
  "first_name"
FROM
  "person"
WHERE
  noteFtsFv.rowid NOT IN (
    SELECT
      rowid
    FROM
      noteFtsFv
    WHERE
      noteFtsFv.fieldValues MATCH $1
  )
  AND $2

https://kyse.link/gpJdB The SQL that I write will go into sql.raw. User queries go into the array as-is and are parameterized. I'm building a feature similar to Github's search syntax. https://gist.github.com/bonniss/4f0de4f599708c5268134225dda003e0 I've built the grammar and now I'm walking the syntax tree and incrementally building the SQL. My grammar has support for grouping, e.g. (X and Y) or (Z and Y). While it is possible to have Kysely to group WHERE clauses like the above... it isn't as easy to do in an incremental fashion. With strings, if I see a "Group" syntax node, I stringconcat a (, recursively call myself on the children, then concat ). Easy. With Kysely, I'd have to walk the group's children to find out if the contents are "AND" or "OR" and I just don't wanna do that 😅 Relevant code if you care https://github.com/AlexErrant/Pentive/blob/b6aaaba608483c66988351fc033581e0abe482ec/shared-dom/src/language/query2sql.ts#L70-L98 Feel free to ignore this paragraph just justifying my laziness 😅

Gaming

Programming

Is `sql.join` the best way to concatenate sql templates?