Dynamic PostgreSQL WHERE IN Query

If you’re working with arrays of data in PostgreSQL this post will walk through a quick and simple solution to create dynamic WHERE IN database queries using arrays for PostgreSQL. Some of the syntax might be helpful for other databases and libraries as well.

The Problem

Here’s the real-world situation, in my PostgreSQL DB I store user authorization information as part of the user record in the DB. The authorization is represented as an array of integer values. Something like this:

public.users

| user_id | ...  | user_authz  |
--------------------------------
| abc123  | ...  | [1,3,11]    |
| def456  | ...  | [2,3,10,11] |

The user_authz column is an array of integers that map back to the primary keys in another table (users_scopes) that tracks the users authorization with the concept of a ‘scope’. A scope is used to isolate user authorization to certain functions or functionality of a system. For example, if a user has the ‘product:create’ user scope they are allowed to create products within the system. The table looks something like this:

public.users_scopes

| scope_id | ...  | scope_title    |
------------------------------------
| 1        | ...  | admin          |
| 2        | ...  | product:create |

When a user authenticates with the system one of the final things I have to do is to determine what accesses they should be granted within the system. I do this by looking up each of the user_scopes by it’s scope_id to return an array of the scope_title that will be used to generate a JSON Web Token that will manage the user access/authorization going forward. To perform this query in PostgreSQL it would look something like this:

SELECT * from users_scopes WHERE scope_id IN (1,3,11);

Hello SQL Injection!! To avoid issues this won’t work for us because we need to create a dynamic query that also performs query parameterization or prepared statements to greatly reduce the risk of malicious code being run against our database.

This simple function will handle the generation of a dynamic WHERE IN query that can be passed to your PostgreSQL client. I like to use the pg / node-postgres client to interact with my database from my NodeJS application.

The function simple takes in an array that we want to query and generates the IN ($1,$2,$3) portion of your query.

  const scopes = [1,3,11];

  let params = [];
  
  for (let i = 1; i <= scopes.length; i++) {
    params.push(`$${i}`);
  }

  db.query(reqId, `SELECT scope_title FROM users_scopes WHERE scope_id IN ( ${params.join(',')} )`, scopes)
...

While using template literals, our query looks like this: SELECT scope_title FROM users_scopes WHERE scope_id IN ( ${params.join(',')} ), and our parameters are defined in the scopes variable. What the Postgres client sees is this:

Query: SELECT scope_title FROM users_scopes WHERE scope_id IN ( $1,$2 ) 
Parameters: [ 1, 2 ]

The result should look something from the query should look like this:

[
  { scope_title: 'platform:create' },
  { scope_title: 'platform:read' }
]

I like this approach to storing, looking up, and managing user scopes within my PostgreSQL database.