Possible to add dynamic WHERE clause with a QueryFile?

Question

I have a complex query stored in an SQL file and I would like to reuse it for various routes but change up the WHERE clause depending on the route. This would be instead of having a large complex query in multiple files with the only difference being the WHERE statement.

Is it possible to dynamically add a WHERE when using QueryFile? Simplified example below:

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          WHERE "users"."id" = "todos"."userId"
      ) sqUser
  ) as "user"
FROM "todos"

const queryIndex = new pgp.QueryFile('sql/todos/index.pgsql', queryOptions);
// 1. Use as is to get a list of all todos
// 2. OR Append WHERE "locationId" = $1 to get list filtered by location
// 3. OR Append WHERE "id" = $1 to get a specific item
// without having three separate SQL files?

It seems like (maybe?) you could get away with adding the below in the query file but that still feels limiting (would still need two files for = and LIKE and it still limits to only one WHERE condition). It also also feels weird to do something like WHERE 1 = 1 to get all records to return.

WHERE $1 = $2

I would be interested in hearing peoples' thoughts on this or if there is a better approach.

Answer 1

You can inject dynamic condition into a query-file as Raw Text:

SELECT "id", "userId", "locationId", "title", "body",
  (
    SELECT row_to_json(sqUser)
    FROM (
      SELECT "id", "firstname", "lastname"
      FROM "users"
          ${condition:raw}
      ) sqUser
  ) as "user"
FROM "todos"

Pre-formatted parameters, based on the condition:

// Generate a condition, based on the business logic:
const condition = pgp.as.format('WHERE col_1 = $1 AND col_2 = $2', [111, 222]);

Executing your query-file:

await db.any(myQueryFile, {condition});

Advanced

Above is for the scenario when your have a simple dynamic condition that you want to generate in the code. But sometimes you may have complex static conditions that you want to alternate. In this case, you can have your master query file refer to the condition from a slave query file (nested query files are supported right out of the box). And in this case you do not even need to use :raw filter, because query files are injected as raw text by default:

Master query:

SELECT * FROM table ${condition}

Load your slave query files with complex conditions (when the app starts):

const conditionQueryFile1 = new QueryFile(...);
const conditionQueryFile2 = new QueryFile(...);

Selecting the right slave query, based on the business logic:

const condition = conditionQueryFile1; // some business logic here;

Executing master query with a slave as parameter:

await db.any(myQueryFile, {condition});