DEV Community

Dynamically generating SQL queries using Node.js

Gajus Kuizinas on August 29, 2019

Ever since I have released Slonik (PostgreSQL client for Node.js) and written a controversial Stop using Knex.js article (tl;dr; query builders are...

Read full post

Ivan Kleshnin • Aug 30 '19 • Edited

Is it ok to use sql.raw to create a fully dynamic ORDER BY query?

let field = "createdAt"
let dir = "DESC" // generated like field.startsWith("+") ? "ASC" : "DESC"

let orderToken = sql`
  ORDER BY ${sql.identifier(field)} ${sql.raw(dir)}'
`

let q = sql`
  SELECT * FROM "post"
  ${orderToken}
  LIMIT 3
`

Gajus Kuizinas • Aug 30 '19 • Edited

You don't need to: sql tags can be nested.

let field = 'createdAt';
let dir = field.startsWith('+') ? sql`ASC` : sql`DESC`;

let orderToken = sql`
  ORDER BY ${sql.identifier([field])} ${dir}'
`;

let q = sql`
  SELECT * FROM "post"
  ${orderToken}
  LIMIT 3
`;

Ivan Kleshnin • Sep 3 '19 • Edited

Thanks. What about optional ORDER BY? For WHERE you started with TRUE so the absence of conditions results in WHERE (true) which is syntactically correct and has no performance implications. I can't find anything than can be used to order by default:

ORDER BY ??? default ???

Another attempt with ?: breaks the placeholder ordering in query:

SELECT ${makeProjection(pick)} 
FROM ${sql.identifier([table])}
WHERE ${makeWhere(filter)}
${sort ? sql`ORDER BY ${makeOrder(sort)}` : ``} -- doesn't work
LIMIT ${makeLimit(limit)}
OFFSET ${makeOffset(offset)}

syntax error at or near "$1"

Gajus Kuizinas • Sep 3 '19

${sort ? sql`ORDER BY ${makeOrder(sort)}` : sql``}

Ivan Kleshnin • Sep 3 '19

Causes

InvalidInputError: Unexpected SQL input. Query cannot be empty.

Gajus Kuizinas • Sep 3 '19

sql`--`

Gajus Kuizinas • Sep 3 '19

I cannot recall the original reason for this constraint. Will likely remove this constraint. Track github.com/gajus/slonik/issues/93.

Ivan Kleshnin • Sep 3 '19 • Edited

Another question. Why valueList and other similar functions support only primitive values?

Is TypeScript a reason? The thing I like about pg driver is that, unlike MySQL, I can SELECT or INSERT a date, a boolean, an object, etc. value an it will do "the right thing" without an extra hassle.

Maybe it's not entirely safe or somehow "not a good practice" – I dunno.
For now, the API feels limiting for no obvious reason.

Gajus Kuizinas • Sep 3 '19

Value token can be any primitive that is shared between pg and JavaScript (string, integer, float, boolean, null).

If you have a requirement for automatic coalescing of other object types (such as Date), I suggest raising an issue. I cannot think of other types that would be relatively safe to cast, though.

Ivan Kleshnin • Sep 3 '19

JS Object with JSON.stringify?

Gajus Kuizinas • Sep 3 '19

Too many edges cases where an object could be passed accidentally inserting potentially sensitive data to the database.

There is sql.json() for that, though.

cvh23 • Nov 6 '19

Slonik's approach is very interesting. Although it's maybe not directly related to Slonik, but because we don't use an ORM: What would be the best way for transforming a SELECT query result of rows into an array of nested objects? For instance if we are joining multiple tables and have a master-detail-relationship. The web client usually expects some kind of nested JSON structure.

Gajus Kuizinas • Nov 6 '19

You can return JSON from PostgreSQL query.

cvh23 • Nov 6 '19

So your recommendation is to do everything in PostgreSQL with its JSON functions? So we already have the ready-to-use object or array of objects as query result?

Gajus Kuizinas • Nov 6 '19

Not at all. I think it is entirely unnecessary. Keep the queries simple. If you need to nest objects, either use frameworks that automate nesting (e.g. GraphQL), or write simple iteration routines to amend the data structures.

cvh23 • Nov 6 '19

Sounds good, thank you! Do you know of any application which uses Slonik + GraphQL and is open source, so that I can study this approach?

Sven Kanoldt • Mar 6 '20

Seems that sql.valueList does not exist anymore. Also the link above provided Continue reading sql.array vs sql.valueList. does not lead to the right section of the README in the repo.

Was this feature removed lately?

Barrie Treloar • Jan 6 '20

sql.booleanExpression was removed in favour of sql.join but the article has not been updated.

See feat: remove multiple methods in favor of sql.join

The doc link is now github.com/gajus/slonik#slonik-que...

Paulo Vieira • Aug 31 '19

Hello Gajus,

I've been experimenting with Slonik for the first time today. Seems interesting, I'm very much aligned with the philosophy of "just write plain old sql". This article was useful, especially the final example. Thanks.

Ivan Kleshnin • Sep 3 '19 • Edited

What's the difference between tuple and valueList? The only one I see is that tuple adds ( and ) parens around its values... The implementation and types are almost identical to valueList, to the point I wonder why not to express one in terms of another or just ditch tuple as unnecessary. Most probably I miss something – that's just my first impression.

Gajus Kuizinas • Sep 3 '19

Eventually sql.valueList, sql.rawList, sql.tupleList, sql.identifierList and sql.booleanExpression will be removed in favour of a single method sql.expressionList.

github.com/gajus/slonik/issues/91

The original intention for separation was to force semantic resemblance and type strictness specific to the code fragment being generated. However, since then types have evolved for all of these helpers to allow a lot broader spectrum of allowed values.

Sajedul karim • Dec 12 '22

I am fetching issue where I have to generate dynamic ORDER BY clause for multiple columns.
Here is my sample query:

SELECT *
FROM user
WHERE gender = 'male'
  ORDER BY created_at ASC, updated_at DESC
LIMIT 10 OFFSET 0;

My Try:

const orderBy: string = 'ORDER BY created_at ASC, updated_at DESC';

SELECT *
FROM user
WHERE gender = 'male'
  ${orderBy}
LIMIT 10 OFFSET 0;

Here, if I pass ORDER BY clause as ${orderBy}, then it take it as a value. It shows error.

The generated sql is like below:

{
  sql: "\n  SELECT *\nFROM user\nWHERE gender = 'male'\n  $1\nLIMIT 10 OFFSET 0;\n",
  type: 'SLONIK_TOKEN_SQL',
  values: [ 'ORDER BY created_at ASC, updated_at DESC' ]
}

Please help me.

craig martin • Aug 29 '19

Hey Garjus - this is the first time I'm coming across your articles/project. Looks really interesting! I look foward to digging in :) I would love to hear your thoughts on github.com/craigmichaelmartin/pure... - a pure ORM (no query builder dimension) which allows you to write regular native SQL and receive back properly nested/structured nested pure (not db-aware stateful) business objects.

Vítězslav Ackermann Ferko • Apr 12 '22

Where have all the sql builder functions go and how to replace them?
comparisonPredicate, valueList, etc.

I am trying to construct multiple WHERE statements dynamically, but I get all sorts of errors. The thing is, some of them might be undefined and thus ommited, but then sql builder yells that it's not a valid value

Shijie Zhou • Aug 29 '19

I think you should look into graphic QL

Gajus Kuizinas • Aug 29 '19

What about GraphQL?