Writing composable SQL using JavaScript

Gajus Kuizinas

Node.js

PostgreSQL

Every application will have to dynamically generate SQL, e.g.

generating UPDATE ... SET clause based on user's inputs

adjusting SELECT ... WHERE clause based on user's inputs

First of all, whatever you do, you should never concatenate strings when building queries. Even when you are sure about what you are doing, the risk of making a mistake is just too damn high. Find a query builder.

My query builder of choice is Slonik. It is better than the alternatives because it allows one to write plain SQL and use composition to create complex queries.

Let's start with a simple select query:

sql`
  SELECT name
  FROM person
`

This is a tagged template that contains static SQL. When executed, it runs exactly what you can see.

The rest of the article shows examples of building SQL queries, increasing in complexity with each example. This is an evergreen article that I will update with new examples as I encounter them in the wild.

SQL composition

SQL composition refers to the ability to compose static SQL tags, e.g.,

sql`
  ${sql`SELECT name`}
  ${sql`FROM person`}
`

There is no reason to use SQL composition in this basic example, but it a simple enough example to demonstrate the concept. All it does is concatenate SELECT name and FROM person SQL fragments into SELECT name FROM person query.

Notice that the members are sql tagged templates themselves. This is the only reason you are able to use them to compose SQL. As you will see in the later examples, if you passed anything else, those values would be interpreted as bound parameters.

In contrast to simply concatenating the result of two arbitrary variables, sql tagged templates can only be static values. Meaning it is impossible to inject arbitrary SQL whatever the user input is.

Adding a parameter

Let's say we want to find all users whose name is equal to whatever the user's input is. All you need to do is pass that value as an expression:

sql`
  SELECT name
  FROM person
  WHERE name = ${name}
`

In this example, name is a variable containing arbitrary user input. Behind the scenes, Slonik will execute whatever is the query as a prepared statement with bound parameters. Therefore, what the database sees is:

SELECT name
FROM person
WHERE name = $1

Since the query and bound values are sent separately, SQL injection attacks are impossible.

Adding a parameter conditionally

Let's say we want to search by name and/or email. The easiest way to do it is something like this:

sql`
  SELECT name
  FROM person
  WHERE
    ${name ? sql`name = ${name}` : true} AND
    ${email ? sql`email = ${email}` : true}`

Depending on the values of name and email, the WHERE clause of this query is going to render to one of the following:

name = $1 AND email = $2
name = $1 AND true
true AND email = $1
true AND true

I like this pattern because the query is mostly static, e.g., unexpected user input or changes leading to this query will not produce any variations beyond those described above.

Note: There is no performance penalty for having OR true. The query planner simply ignores that branch of the query execution.

Note: null, true and false are special values that are embedded directly into the query to allow the query planner to optimize queries better. If those values were anything else, they would have been passed as parameters.

This example also demonstrated how to use SQL composition to embed sql fragments into another sql query. Unlike concatenating strings, SQL composition ensures safe handling of parameter values and does not allow embedding arbitrary code.

This idea – that sql tags can be composed with other sql tags – extends to an infinite depth and be used to construct very complex queries.

Adding a variable number of parameters

In the preceding examples, we have assumed that the parameter number is well defined. However, let's assume that is not the case. We can then use sql.join function. It works like Array.prototype.join(), just with SQL components.

SELECT name
FROM person
WHERE ${sql.join(components, sql` AND `)}

Here components is an array of sql fragments with bound values, e.g.,

const components = [];

components.push(sql`name = ${name}`);
components.push(sql`email = ${email}`);

As you may have guessed, this would generate the following SQL code:

SELECT name
FROM person
WHERE name = $1 AND email = $2

Adding groups of parameters

As you may have also guessed, individual fragments are not restricted to a single expression. You can combine them to build pretty complex queries, e.g.,

const parson = (name: string, email: string) => {
  return sql`(name = ${name} AND email = ${email})`;
};

`sql
SELECT name
FROM person
WHERE
  ${sql.join([person('Foo', 'foo@foo', 'Bar', 'bar@bar')], sql` OR `)}
`

This would generate query:

SELECT name
FROM person
WHERE
  (name = $1 AND email = $2) OR
  (name = $3 AND email = $4)

Add a variable parameter

Everything that you've seen up to now was a composition of static SQL fragments or value bindings. However, if you are building a query builder (e.g., abstracting common patterns within your codebase), then you may run into the need to inject dynamic identifiers. In that case, you can use sql.identifier(names[]) utility.

SELECT ${sql.identifier(['foo', 'bar'])}

This is equivalent to:

SELECT "foo"."bar"

For what it is worth, the good thing about Slonik is that even if you do end up using this when you shouldn't have, at most, you will either break the query or allow to fetch unintended data, i.e. It still protects you against SQL injections. This is one of the biggest benefits of using Slonik – there are no known ways of writing code that is vulnerable to SQL injections.

However, before you rush to build your query abstractions, learn the existing abstraction that Slonik provides – there are many.

Building UPDATE queries

A common question is how to build dynamic UPDATE queries. There are a few ways of doing it, and I am still debating which is the best way.

The most common pattern that I've seen in the wild is this:

const fieldsToUpdate = [
  {
    name: 'name',
    value: input.name,
  },
  // ...
];

await pool.query(sql`
  UPDATE user_account
  SET
  ${sql.join(
    fieldsToUpdate.map(({ name, value }) => {
      return sql`${sql.identifier([name])} = ${value}`;
    }),
    sql`,`
  )}
  WHERE id = ${userAccountId}
`);

It leverages sql.join to join SET expressions with bound values. The code itself is safe, but the problem that I have with this pattern is that fieldsToUpdate is defined outside of the query. It could be completely safe... or someone could (without understanding the broader context) modify fieldsToUpdate to allow dynamic name value, and that is just asking for trouble.

Therefore, a better way is to statically type all expressions and allow them to fallback to the current value if not value is set, e.g.

await pool.query(sql`
  UPDATE user_account
  SET
    name = ${input.name ?? sql`user_account.name`}
  WHERE id = ${userAccountId}
`);

This way, there is no way for anyone to mess up. This is my current preferred way to write dynamic update queries (the dynamic part is the falling back to user_account.name if value is not set).

More complex examples

These were the most common examples that come up in GitHub issues. If you would like me to include more examples, raise a GitHub issue, and I will update this article.

Like this project

Posted Sep 23, 2022

A walkthrough of common patterns of writing SQL queries in JavaScript with the help of Slonik.

Likes

Views

9.9K

Clients