Feature: There should be easier way to WHERE/OR/AND combinations

[jussikinnula]

Here's working code similar I use in an application. The application is closed source, I haven't tested this actual code - but I'll test it and fix if there are issues.

So I'm proposing that there should be sql.or() and sql.and() kind of mechanism so that complex WHERE/OR/AND combinations could be written. This example illustrates the "problem":

import postgres from 'postgres';

// How to make getItems simpler?
const getItems = (params: {
  foo: string[];
  bar: string[];
  baz: string[];
} = {}) => {
  // This should be enough (pseudo code, also OR case should be thought of):
  // return sql`
  //   SELECT * FROM items WHERE ${sql.and(params)}
  // `;
  // 
  // For inspiration, Perl DBIx has a pretty good and versatile syntax:
  // https://stackoverflow.com/questions/27962418/how-do-i-do-where-or-and-and-with-dbixclass

  const { foo = [], bar = [], baz = [] } = params;

  if (foos.length && bars.length && bazs.length) {
    return sql`
      SELECT * FROM items
        WHERE foo IN (${foo})
          AND bar IN (${bar})
          AND baz NOT IN (${baz})
    `;
  }

  if (foos.length && bars.length) {
    return sql`
      SELECT * FROM items
        WHERE foo IN (${foo})
          AND bar IN (${bar})
    `;
  }

  if (foos.length && bazs.length) {
    return sql`
      SELECT * FROM items
        WHERE foo IN (${foo})
          AND baz NOT IN (${baz})
    `;
  }

  if (bars.length && bazs.length) {
    return sql`
      SELECT * FROM items
        WHERE bar IN (${bar})
          AND baz NOT IN (${baz})
    `;
  }

  if (bars.length) {
    return sql`
      SELECT * FROM items
        WHERE bar IN (${bar})
    `;
  }

  if (bazs.length) {
    return sql`
      SELECT * FROM items
        WHERE NOT baz IN (${bazs})
    `;
  }}

(async function run() {
  const sql = postgres(process.env.DATABASE_URL || 'postgres://localhost:5432/test');

  await sql`
    DROP TABLE IF EXISTS test
  `;

  await sql`
    CREATE TABLE items (
      id int NOT NULL,
      foo char(1) NOT NULL,
      bar char(1) NOT NULL,
      baz char(1) NOT NULL,
      CONSTRAINT foos_pk PRIMARY KEY (id)
    );
  `;

  const items = [
    { id: 1, foo: 'a', bar: 'a', baz: 'a' },
    { id: 2, foo: 'a', bar: 'a', baz: 'b' },
    { id: 3, foo: 'a', bar: 'b', baz: 'a' },
    { id: 4, foo: 'b', bar: 'a', baz: 'a' },
    { id: 5, foo: 'b', bar: 'a', baz: 'b' },
    { id: 6, foo: 'a', bar: 'b', baz: 'b' },
    { id: 7, foo: 'b', bar: 'b', baz: 'b' },
    { id: 8, foo: 'b', bar: 'b', baz: 'c' },
    { id: 9, foo: 'b', bar: 'c', baz: 'b' },
    { id: 10, foo: 'c', bar: 'b', baz: 'b' },
    { id: 11, foo: 'c', bar: 'b', baz: 'c' },
    { id: 12, foo: 'c', bar: 'c', baz: 'c' },
  ];

  const rows = await sql`
    INSERT INTO items ${sql(items, Object.keys(items[0]))}
  `;

  console.log(await getItems({ foos: ['a', 'b'] }));
  console.log(await getItems({ bars: ['c'], bazs: ['a', 'c'] }));
  console.log(await getItems({ foos: ['c'], bars: ['b', 'c'], bazs: ['b'] }));

  process.exit();
})();

[yckao]

releated to #12 and #101
It will be possible when sql.partial is implemented.
and we can do something like

/* notice postgres-partial is experimental and should **NOT** use in production */
/* let's wait for https://github.com/porsager/postgres/pull/101 */
/* this code can actually run. */
import postgres from 'postgres';
import { wrap, SqlWithDynamic } from 'postgres-partial'
const getItems = (sql: SqlWithDynamic<never>, params: {
  foos?: string[];
  bars?: string[];
  bazs?: string[];
}) => {
  const { foos = [], bars = [], bazs = [] } = params;

  const and = (...items: any[]) => items.reduce((left, right) => sql.partial`${left}${right ? sql.partial` AND ${right}` : sql.skip}`, sql.partial`TRUE`)

  const where = sql.partial`WHERE ${
    and(
      foos.length && sql.partial`foo IN (${foos})`,
      bars.length && sql.partial`bar IN (${bars})`,
      bazs.length && sql.partial`baz NOT IN (${bazs})`
    )}`

  return sql`
    SELECT * FROM items ${where}
  `;
}

(async function run() {
  const sql = wrap(postgres(process.env.DATABASE_URL || 'postgres://postgres:postgres@localhost:5432/test', {
    debug: (connection: number, query: string, parameters: any[]) => {
      console.log(query, parameters)
    }
  }));

  await sql`
    DROP TABLE IF EXISTS items
  `;

  await sql`
    CREATE TABLE items (
      id int NOT NULL,
      foo char(1) NOT NULL,
      bar char(1) NOT NULL,
      baz char(1) NOT NULL,
      CONSTRAINT foos_pk PRIMARY KEY (id)
    );
  `;

  const items = [
    { id: 1, foo: 'a', bar: 'a', baz: 'a' },
    { id: 2, foo: 'a', bar: 'a', baz: 'b' },
    { id: 3, foo: 'a', bar: 'b', baz: 'a' },
    { id: 4, foo: 'b', bar: 'a', baz: 'a' },
    { id: 5, foo: 'b', bar: 'a', baz: 'b' },
    { id: 6, foo: 'a', bar: 'b', baz: 'b' },
    { id: 7, foo: 'b', bar: 'b', baz: 'b' },
    { id: 8, foo: 'b', bar: 'b', baz: 'c' },
    { id: 9, foo: 'b', bar: 'c', baz: 'b' },
    { id: 10, foo: 'c', bar: 'b', baz: 'b' },
    { id: 11, foo: 'c', bar: 'b', baz: 'c' },
    { id: 12, foo: 'c', bar: 'c', baz: 'c' },
  ];

  const rows = await sql`
    INSERT INTO items ${sql(items, 'id', 'foo', 'bar', 'baz')}
  `;

  console.log(await getItems(sql, { foos: ['a', 'b'] }));
  console.log(await getItems(sql, { bars: ['c'], bazs: ['a', 'c'] }));
  console.log(await getItems(sql, { foos: ['c'], bars: ['b', 'c'], bazs: ['b'] }));

  process.exit();
})();

And I will do some research how to let user extends the parsing system then create another PR.
Once we have a way to extend the parsing system, we may easily implement something like sql.condition() in user land.
Also we can maintain these utilities in different package.

[porsager]

The definitely should be @jussikinnula 🙂

It's tracked in the issues mentioned by @yckao , so I'll close this one.

[porsager]

Hi @hobberwickey Instead of trying to cheat the tagged template function, just use unsafe. The new feature to do things like this safely is also just around the corner, so I've hidden your comment to not confuse anyone ;)