API design question

[ivan-kleshnin]

Hi @gajus

The docs state that:

For conditional queries, use sql.booleanExpression. For comparison expressions, use sql.comparisonPredicate. If you are still unsure how to create a query, then raise an issue to discuss your requirement.

Below q2 is much easier to read and much more raw SQL-like than q1.

let q1 = sql`
  SELECT * FROM "account"
    WHERE ${sql.comparisonPredicate(sql.identifier(["username"]), "=", "ivan-kleshnin")}
      AND ${sql.comparisonPredicate(sql.identifier(["role"]), "=", "admin")}`

let q2 = sql`
  SELECT * FROM "account"
    WHERE "username" = ${sql.valueList(["ivan-kleshnin"])}
      AND "role" = ${sql.valueList(["admin"])}`

let {rows: rows1} = await pool.query(q1)
let {rows: rows2} = await pool.query(q2)

// The results are the same  

1) Does q1 approach have some benefits (besides dynamic column names)?

2) If q2 is better for static columns, then why not go further:

let q2 = sql`
  SELECT * FROM "account"
    WHERE "username" = ${sql.value("ivan-kleshnin")}
      AND "role" = ${sql.value("admin")}`

or even

let {value} = sql

let q2 = sql`
  SELECT * FROM "account"
    WHERE "username" = ${value("ivan-kleshnin")}
      AND "role" = ${value("admin")}`

Pls. correct me if I'm wrong, but I thought being as lean and SQL-like as possible is the selling point of this library. q1 looks like a generic query builder – not much of original SQL syntax is left there...

[gajus]

I have published an article that answers your question.

https://dev.to/gajus/dynamically-generating-sql-queries-using-node-js-2c1g

[ivan-kleshnin]

Great, thanks for sharing, man! For some reason I missed the line where you show the basic interpolation example 😞

[gajus]

:tada: This issue has been resolved in version 18.11.2 :tada:

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot :package::rocket: