Add utility for adding adhoc WHERE to queries

[gajus]

This idea is inspired by https://github.com/danielrearden/slonik-dataloaders

The way it would work is something like:

const foo = sql.type(
  z.object({
    id: z.number(),
    name: z.string(),
  })
)`
  SELECT id, name
  FROM person
`

sql.where(foo, ({name}) => {
  return sql`${name} = 'Foo'`;
});

The query it would generate is something along the lines of:

SELECT t1.*
FROM (
  SELECT id, name
  FROM person
) t1.*
WHERE t1.name LIKE $1

Not sure whether it should belong in Slonik or a utility library.

[gajus]

We could extend this beyond WHERE, e.g.

extendQuery(foo, ({name}) => {
  return sql`
  WHERE ${name} = 'Foo'`
  ORDER BY ${name} ASC
});

[gajus]

We could even go further by allowing to compose queries using sql tags, e.g.

const foo = sql.type(
  z.object({
    id: z.number(),
    name: z.string(),
  })
)`
  SELECT id, name
  FROM person
`;

foo`
  WHERE ${foo.name} = 'Foo'
  ORDER BY ${foo.name} ASC
`

A downside of this approach is that it would break syntax highlighting in IDEs (at least one that is based on sql tag presence).

[gajus]

At the moment this is just for food thought.

[gajus]

Example implementation:

import {
  sql,
  type IdentifierSqlToken,
  type QueryResultRow,
  type SqlSqlToken,
  type SqlToken,
} from 'slonik';
import {
  type z,
  type ZodTypeAny,
} from 'zod';

type ColumnIdentifiers<T> = Record<keyof T, IdentifierSqlToken>;

const TABLE_ALIAS = 't1';

export const getColumnIdentifiers = <T>(
  tableAlias: string,
) => {
  return new Proxy(
    {},
    {
      get: (_target, property: string) => {
        return sql.identifier([
          tableAlias,
          property,
        ]);
      },
    },
  ) as Record<keyof T, IdentifierSqlToken>;
};

export const extendQuery = <T extends ZodTypeAny>(
  query: SqlSqlToken<T>,
  where: (
    identifiers: ColumnIdentifiers<z.infer<T>>
  ) => SqlSqlToken<QueryResultRow>,
) => {
  const columnIdentifiers = getColumnIdentifiers<T>(
    TABLE_ALIAS,
  );

  const conditions: SqlToken[] = where ?
    [
      sql`(${where(columnIdentifiers)})`,
    ] :
    [];

  return sql`
    SELECT *
    FROM (
      ${query}
    ) ${sql.identifier([
    TABLE_ALIAS,
  ])}
    WHERE ${
  conditions.length ?
    sql`${sql.join(conditions, sql` AND `)}` :
    sql`true`
}
  ` as unknown as SqlSqlToken<T>;
};