Ever since I have releasedSlonik (PostgreSQL client for Node.js) and written a controversialStop using Knex.js article (tl;dr; query builders are designed to be building blocks for ORMs; they do not add value when majority of the query is static.), I have been asked a lot – then how do I generate dynamic queries? I will answer this by sharing a couple of real-life examples.

All of the queries in this article are actual queries used in real-life business,Applaudience whichheavily relies on PostgreSQL.

Disclaimer: (1) All examples discuss only SQL injection threats. Authorization logic (e.g. whitelisting columns user is authorized to access) is not in the scope of this article. (2) All statements assume there are no bugs in Slonik implementation.

Static query with dynamic value bindings

If your query logic does not change depending on user's input, then simply construct SQL query usingsql tagged template literal, e.g.

sql`  SELECT c1.country_id  FROM cinema_movie_name cmn1  INNER JOIN cinema c1 ON c1.id = cmn1.cinema_id  WHERE cmn1.id =${cinemaMovieNameId}`;

If you are using Slonik, it issafe to pass values as template literal placeholders.sql will interpret all placeholder tokens and construct final SQL query. In this case, the only dynamic part of the query is the value bindings themselves, therefore the final query is:

SELECTc1.country_idFROMcinema_movie_namecmn1INNERJOINcinemac1ONc1.id=cmn1.cinema_idWHEREcmn1.id=$1

Query and bound values will be sent to PostgreSQL separately: no risk of SQL injection.

Binding a list of values

When your query input is a list of values (e.g. such as when retrieving rows matching multiple identifiers), then youmay usesql.valueList, e.g.

sql`  SELECT m1.*  FROM movie m1  WHERE m1.id IN (${sql.valueList(movieIds)})`;

This will generate a query with dynamic set of value bindings, i.e. ifmovieIds is[1, 2, 3] the query that is sent to PostgreSQL will be:

SELECTm1.*FROMmoviem1WHEREm1.idIN($1,$2,$3)

However, despite this being a common pattern, I do not advise to use this pattern. Instead, usesql.array, e.g.

sql`  SELECT m1.*  FROM movie m1  WHERE m1.id = ANY(${sql.array(movieIds,'int4')})`;

This will generate a fixed-length query that does not change based on its inputs, i.e.

SELECTm1.*FROMmoviem1WHEREm1.id=ANY($1::"int4"[])

Continue readingsql.array vssql.valueList.

Query with dynamic columns

If your query result refers to columns that depend on user's input, then usesql.identifier to generate SQL that identifies those columns, e.g.

(Note: Not an actual query used in business. See next paragraph.)

sql`  SELECT m1.id,${sql.identifier(['m1',movieTableColumnName])}  FROM movie m1  WHERE    m1.id =${moveId}`;

This query will produce a query that selects exactly 1 dynamically identified column. There is no risk of SQL injection, i.e. even if logic leading to generation ofmovieTableColumnName was somehow compromised, the worst that can happen is that query attacker will be able to return any column underm1 alias or execute query with invalid column identifier values (both carry risk; business logic is not in scope of this article).

Just because you can do this, you probably shouldn't. When your application requires to return different columns depending on user's query, it is better to select all columns that are in scope of the business logic and pick value of the needed column, i.e. If the intent of the latter query was to return a different movie identifier based onmovieTableColumnName, then it is better to write a static query:

sql`  SELECT    m1.id,    m1.foreign_comscore_id,    m1.foreign_imdb_id,    m1.foreign_metacritic_id    m1.foreign_rottentomatoes_id,    m1.foreign_tmdb_id,    m1.foreign_webedia_id  FROM movie m1  WHERE    m1.id =${moveId}`;

The latter has does return some superfluous data on every query, but it has several advantages:

1. It reduces risk of SQL injection (regardless of how much you trust code generation logic, static code is always safer than dynamic code).
2. It produces only one entrypg_stat_statements. You will learn to appreciate as few as possible queries inpg_stat_statements as your application scales.

Query with multiple dynamic columns

Same as the above, butsql.identifierList.

Nesting dynamic SQL queries

sql tagged template literals can be nested, e.g.

(Note: Simplified version of an actual query used in business.)

constfutureEventEventChangeSqlToken=sql`  SELECT    ec1.event_id,    ec1.seat_count,    ec1.seat_sold_count  FROM event_change_future_event_view ec1`;sql`  SELECT    event_id,    seat_count,    seat_sold_count  FROM (${futureEventEventChangeSqlToken}  ) AS haystack  WHERE${paginatedWhereSqlToken}  ORDER BY${orderSqlToken}  LIMIT${limitSqlToken}`

This allows to pass pre-bound SQL queries as first-class citizens across your program. This is handy when the intent is to isolate SQL generation logic for testing or when large SQL fragments are shared between queries or when the intent is to simply reduce concentration of code complexity in one place.

Injecting dynamic SQL fragments

sql.raw is used to inject dynamic SQL fragments, i.e.

sql`  SELECT${sql.raw('foo bar baz')}`

translates to (invalid) query:

SELECTfoobarbaz

Unlike the previous example usingsql tagged template,sql.raw is not safe – it allows to create dynamic SQL using user input.

There are no known use cases for generating queries usingsql.raw that aren't covered by nesting boundsql expressions (described in "Nesting dynamic SQL queries") or by one of the other existingquery building methods.sql.raw exists as a mechanism to execute externally storedstatic (e.g. queries stored in files).

Query with a dynamic comparison predicate members or operator

If an operator of a comparison predicate present in your query is dynamic, then usesql.comparisonPredicate, e.g.

(Note: Not an actual query used in business.)

sql`  SELECT    c1.id,    c1.nid,    c1.name  FROM cinema c1  WHERE${sql.comparisonPredicate(sql`c1.name`,nameComparisonOperator,nameComparisonValue)}`;

nameComparisonOperator can be values such as=,>,<, etc. AssumingnameComparisonOperator is "=", then the resulting query is going to be:

SELECTc1.id,c1.nid,c1.nameFROMcinemac1WHEREc1.name=$1

The latter is an extremely rare use case, reserved almost entirely to building higher level SQL abstraction tools (such as ORMs). It may be useful for "advance search" scenarios, however continue reading to familiarise with alternative patterns (seesql.booleanExpression).

Query with dynamic WHERE clause members

If presence ofWHERE clause members is dynamic, then usesql.booleanExpression.

constfindCinemas=(root,parameters,context)=>{constbooleanExpressions=[sql`TRUE`,];if(parameters.input.query){constquery=parameters.input.query;if(query.countryId!==undefined){booleanExpressions.push(sql`c2.id =${query.countryId}`);}if(query.nid!==undefined){booleanExpressions.push(sql`c1.nid %${query.nid}`);}if(query.name!==undefined){booleanExpressions.push(sql`c1.name %${query.name}`);}}constwhereSqlToken=sql.booleanExpression(booleanExpressions,'AND');returncontext.pool.any(sql`    SELECT      c1.id,      c1.nid,      c1.name,      c2.code_alpha_2 country_code,      c2.name country_name    FROM cinema c1    INNER JOIN country c2 ON c2.id = c1.country_id    WHERE${whereSqlToken}  `);},

findCinemas is an implementation of a GraphQL resolver. WHERE clause of the query is constructed using a combination of 3 possible boolean expressions. As is the case with all the other query building methods in Slonik, all expressions can be nested: you can have other boolean expressions as members of a boolean expression or even SQL expression constructed usingsql tagged template literal.

Summary

These examples cover every common dynamic SQL building scenario and provide enough knowledge of how Slonik works to enable reader to continue journey of familiarising withother query building methods provided by Slonik. The primary intent of this article was to demonstrate that Slonik provides a safe abstraction for constructing SQL queries keeping the static parts of the query intact.

If you value my work and want to see Slonik andmany other of my Open-Source projects to be continuously improved, then please consider becoming a patron:

Finally, I missed a use case scenario that you would like me to cover, mention it in the comments and I will happily include it.