Issues with dynamic ORDER BY and prepared statement variables

[dumaron]

Hello everyone,
I'm facing an issue while implementing a dynamic ORDER BY without screwing up the query. I'm almost sure it's just a lack of knowledge of the proper method, and yet I cannot figure it out or find it.

The background is that I'm working on an API that allows to specify both sorting parameters and direction, so my first implementation of the SQL fragment for the sorting was (similar) to this:

const f = sql.fragment`
... the rest of the query
${
  sortKeys.length > 0
    ? sql.fragment`
      ORDER BY ${sql.join(
        sortKeys.map(
          ({ key, direction }) =>
            sql.fragment`"${key}" ${direction}`,
        ),
        sql.fragment`, `,
      )}`
    : sql.fragment``
}
`

An important note is that the key parameter needs to be wrapped with "" quotes since the columns in the query are (and need to be) case sensitive.

This creates a prepared statement that is more or less like ORDER BY $1 $2, and it turns out that prepared statements cannot have variables in the ORDER BY section, and so the SQL is not valid.

After that I tried with sql.literalValue, even if its usage is discouraged. However, the casting of the string makes the final query look like `"'something'", which wasn't correct.

I ended up using a quirky detail of the backtick string implementation, which is that they are seen as array from the function they've been called on, and the first string will undergo no interpolation:

const f = sql.fragment`
... the rest of the query
${
  sortKeys.length > 0
    ? sql.fragment([  // <-- notice the array passed to `sql.fragment`. This string will be interpolated by Javascript
      `ORDER BY ${
      sortKeys
          .map(({ key, direction }) =>`"${key}" ${direction}`)
          .join(', '),
      }`])
    : sql.fragment``
}
`

But this doesn't feel right to me.

Is there any suggestion you can give me to achieve my goal in a more orthodox way? Can I add my ORDER BY parameters without producing an invalid SQL or avoiding the cast that comes with sql.literalValue?

[firxworx]

Check out the code in this package: https://github.com/ardsh/slonik-trpc
Stumbling upon it a little while back illuminated a lot for me about building queries with slonik.

See examples including data grid with dynamic sorts and how he builds the queries -- https://codesandbox.io/examples/package/slonik-trpc