@dise-international/pgp-query v1.6.2

PGPQuery

PGPQuery is used to build modular database queries for pg-promise. It's based on three basic concepts:

• of(statement, params) constructs query tuples
• empty() creates an empty query tuple
• concat(...queries) combines a set of queries into one

These three parts are then combined into more (or less) complex and dynamic queries. Each function will always return a query, a tuple containing a statement (a text query with placeholders) and params (an object used holding the actual data). The empty() query can be composed with any other query without affecting the result.

For more information on placeholders and interpolation, see https://github.com/vitaly-t/pg-promise.

Base operators

of(statement, params)

Takes a statement and params and returns a query.

const query = of('SELECT $0 FROM $1', ['*', 'my_table']);

assert.deepEqual(query, ['SELECT $0 FROM $1', ['*', 'my_table']]);

empty()

Takes no arguments, and returns an empty query, consisting of an empty string and an empty object.

const query = empty();

assert.deepEqual(query, ['', {}]);

concat(...queries)

Takes one or more queries and combines them into a single query.

const query1 = of('SELECT $0, $1', ['field_1', 'field_2']);
const query2 = of('FROM $<table>', { table: 'my_table' });
const query3 = empty();

const [statement, params] = concat(query1, query2, query3);

assert.equal     (statement, 'SELECT $0, $1\nFROM $<table>');
assert.deepEqual (params,    { 0: 'field_1', 1: 'field_2', table: 'my_table' });

Why?

By composing these somple functions, we can build complex queries in a declarative fashion, that are easy to split apart and recombine, especially by leveraging empty().

function get_issues_by(user_account_id, options) {
  const selectReportsByUser = of(`
    SELECT id, severity, date
    FROM issue_reports
    WHERE user_account_id=$<user_account_id>
  `, { user_account_id });

  const thatAreNewerThan = options.from_date
    ? of('AND date > $<from_date>', { from_date: options.from_date })
    : empty();

  const thatAreOlderThan = options.to_date
    ? of('AND date < $<to_date>', { to_date: options.to_date })
    : empty());

  return concat(
    selectReportsByUser,
    thatAreNewerThan, 
    thatAreOlderThan);
}

Writing safe extensions using lazy concatenation

This part is only relevant if you're looking to write your own extension. If not, it's enough to read the short section on lazy concatenation below. There is one particular barrier to truly leveraging the composability and to writing your own extensions, and that is naming collisions. Say that we want to create an operator for equality, that takes a column name and a value and returns a query:

const eq = (col, val) => of('$<col:name> = $<val>', { col: col, val: val });

This is a perfectly capable little helper if you use it by itself:

eq('points', 25) // ['$<col:name> = $<val>', {col: 'points', val: 25}]

You can even concatenate it:

concat(
  of('SELECT * FROM contestants WHERE'),
  eq('points', 25)) 
  // ['SELECT * FROM contestants WHERE $<col:name> = $<val>', { col: 'points', val: 25}]

The problems arises when we use it multiple times:

concat(
  of('SELECT * FROM contestants WHERE'),
  eq('points', 25), 
  of('AND'), 
  eq('wins', 10)) 
  // ['SELECT * FROM contestants WHERE $<col:name> = $<val>', { col: 'wins', val: 10}]

Our placeholder names have collided! The params are overwritten! Chaos!

Previously, the easiest way to deal with this has been to use template literals, but they are highly insecure and opens you up to SQL injections. By using lazy concatenation instead, we can write operators that are namespaced. There are two ways to go about it: Entirely manually, or entirely automatically.

Lazy concatenation

The lazy versions of of and concat are imported from 'lazy/of' and 'lazy/concat' respectively. They work similarly to their counterparts, except they return functions that in turn produce a result.

For of, this doesn't mean much, it's just wrapped to work out-of-the-box for simple use cases, much like its eager counterpart.

For concat, however, it's crucial, since it allows us to construct the entire tree of queries before composing them. When the lazy concat is finally executed, it will create a suffix that is passed down to every query generator. The suffix starts as "0" and is increased for every sibling (0, 1, 2), then a new suffix is added for every child (0.1, 0.2, 0.3...).

This means that, with a lazy-fied version of our eq, the final example would yield:

 [
   `SELECT * FROM contestants WHERE
   $<col_1:name> = $<val_1>
   AND
   $<col_3:name> = $<val_3>`, 
   { 
     col_1: 'points', 
     val_1: 25, 
     col_3: 'wins', 
     val_3: 10 
   }
 ]

No conflicts!

The manual way

Doing things the manual way requires some juggling with strings, but it's fairly simple.

We'll start off by writing our basic, lazy function shell. As mentioned, lazy/concat will supply a suffix representing the current place in the tree of queries:

function lazy_eq(_col, _val) {
  return function (_suffix) {
    return empty();
  }
}

This won't do what we want, but it works. It will produce an empty query when used by lazy/concat.

Let's add our naïve implementation of eq from above, and work from that.

import { of } from '../of';

function lazy_eq(col, val) {
  return function (_suffix) {
    /** 
     * Since we're in the _inner scope_ of the query, we need to use the regular `of`. 
     * The _outer scope_ should return a function, the inner should return a value. 
     */
    return of('$<col:name> = $<val>', { col: col, val: val });
  }
}

Now, all we need to do is to use our suffix in the statement, and in the params. It can get tricky due to there being some inversions and an overloaded use of $. Blame pg-promise :'D

import { of } from '../of';

function lazy_eq(col, val) {
  return function (suffix) {
    const col_placeholder = `eq_col_${suffix}`;
    const val_placeholder = `eq_val_${suffix}`;

    /**
     * using '$' both in pg-promise and in template literals maybe wasn't the brightest idea....
     */
    const statement = `$<${col_placeholder}:name> = $<${val_placeholder}>`;
    const params = {
      [col_placeholder]: col,
      [val_placeholder]: val
    };

    return of(statement, params);
  }
}

With this method, we use template literals to dynamically create the placeholders. No SQL injections afforded. We then set our param keys so that they match the placeholder names, and assign the original values to those keys.

This method is a bit clunky, and I want to stress that this only occurs when writing extensions, not when using them to build queries.

However, there is an automatic way to do it, using autosuffix. Autosuffix takes a query and creates a set of instructions that will update both the statement and the params. Then it returns a function that receives a suffix, and finally it re-creates the query using that suffix, preserving all pg-promise filters and whatnot. It currently works with all pg-promise delimiters.

Using the autosuffixer is very straightforward:

import { autosuffix } from '../util/autosuffix';
import { of } from '../of'; // Again using the non-lazy of. 

function lazy_eq(col, val) {
  const query = of(
    '$<col:name> = $<val>', 
    { col, val });

  return autosuffix(query);
}

Ta-da!