---
title: Dynamic PostgreSQL WHERE IN Query
description: 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 for PostgreSQL. Some of the syntax might be helpful for other databases and libraries as well.
date: 2020-03-24
tags: Databases, Postgres, NodeJS, NPM, PG
source: https://brianchildress.co/blog/dynamic-postgres-where-in-query
---

# Dynamic PostgreSQL WHERE IN Query

If you're working with arrays of data in [PostgreSQL](https://www.postgresql.org/) this post will walk through a quick and simple solution to create dynamic WHERE IN database queries using [arrays](https://www.postgresql.org/docs/9.1/functions-array.html) 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](https://jwt.io/) that will manage the user access/authorization going forward. To perform this query in PostgreSQL it would look something like this:

```sql
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](https://en.wikipedia.org/wiki/Prepared_statement) 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](https://www.npmjs.com/package/pg) / [node-postgres](https://node-postgres.com/) 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.

```js
  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: 

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

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