sql.join() for building dynamic queries

[DanielFGray]

postgres.js gives us parameterized tagged templates and the ability to nest sql partials, and this is fantastic, but I think it is missing a piece of the puzzle:

I want to be able concat partials together (that may contain query parameters), usually with some delimiter (eg whitespace, or a comma, or maybe an sql operator)

something like:

sql.join = (a: PendingQuery[] | Helper[], p: PendingQuery): PendingQuery {...}

would be amazing if this were a first-class feature in postgres.js

this should allow things like

let dynamicFilters = [
  sql`somecol = ${somevalue}`,
  sql`col2 = ${v2}`
];
const dynamicColumns = [sql('somecol'), sql('col2')]
await sql`
  select ${sql.join(dynamicColumns, sql`, `)} from t
  where ${sql.join(dynamicFilters, sql` and `)};
`;

[porsager]

Yeah, I think it makes sense to add, but I've just been doing this when needed

xs.flatMap((x, i) => i ? [sql`, `, x] : x)

You can fairly quickly make a function to use instead if you feel like it - eg 😋

sql.join = (xs, joiner) => xs.flatMap((x, i) => i ? [joiner, x] : x)

I'll keep the issue open for now.

[DanielFGray]

sql.join = (xs, joiner) => xs.flatMap((x, i) => i ? [joiner, x] : x)

oh interesting! i would never have come up with that on my own

do you have any reservations about adding this as a core feature?

[DanielFGray]

and if no reservations, would you accept a PR adding this as a core feature?

[davepar]

It would be great to at least add the work-around to the docs. It wasn't obvious to me how to join sql fragments with a comma. Glad I found this.

[DanielFGray]

i'm working on a PR

anyone want to bike-shed over join vs concat to avoid confusion with sql joins?
or concat_ws to use actual postgres api 😁

[Destreyf]

Yeah, I think it makes sense to add, but I've just been doing this when needed

xs.flatMap((x, i) => i ? [sql`, `, x] : x)

You can fairly quickly make a function to use instead if you feel like it - eg 😋

sql.join = (xs, joiner) => xs.flatMap((x, i) => i ? [joiner, x] : x)

I'll keep the issue open for now.

This was insanely helpful, I have a really nested structure so my version is slightly different, and I am monkey patching the sql/database variable.

// Dumb placeholder to shorten code
export type PendingQuery<T extends postgres.Row = postgres.Row> =
  postgres.PendingQuery<T[]>;

// Hack to add the "join" method.
export type sql = ReturnType<typeof postgres> & {
  join: (a: Array<PendingQuery | null>, b: PendingQuery) => PendingQuery;
};

const db = postgres(config) as sql;

db["join"] = (fragments: (PendingQuery | null)[], joiner: PendingQuery) =>
  fragments.flatMap((x, i) => {
    if (!i) return x;

    if (Array.isArray(x)) {
      // already an array, means we're nested, so lets spread it
      return [joiner, ...x];
    }

    return [joiner, x];
  }) as unknown as PendingQuery;

It's typescript, but you can strip the types and it should work, usage is:

db.join([
  db`name = 'test1'`,
  db`name = 'test2'`,
  db`name = 'test3'`,
  db.join([
    db`updated_at >= 2024-01-01`,
    db`updated_at <= '2024-12-31`
  ],db`AND`)
], db`OR`)

Hopefully this can make it into somewhere in the codebase as it's super useful for report building.

@DanielFGray hopefully this code can help if you haven't figured out this part.

[awildeep]

Any word on making this happen without hacks?

[FrancescoDiMuro]

Any word on making this happen without hacks?

It's crazy annoying to join where clauses.
Any suggestions?

[DanielFGray]

this will likely have a better chance of landing if we can fix tests

if you're interested in helping, you can send a PR to my PR on the appropriate branch

my favorite api for this, which is mentioned in my PR but not implemented:

-sql.join(sql` and `, fragments)
+sql.join` and `(fragments)

i do have a branch where this is implemented but it also needs types and tests

[davepar]

It's literally a one line function, which doesn't annoy me at all. Personally, I think it just needs to be documented. Here's my typescript version in case it's useful to anybody (I decided not to mess with the sql variable):

import type { PendingQuery, Row } from 'postgres';
type SqlClause = PendingQuery<Row[]>;
const sqlJoin = (arr: SqlClause[], joiner = sql`, `) => arr.flatMap((x, i) => (i ? [joiner, x] : x));

For a where clause, use it like this:

const whereClauseArray = [sql`id = 'foo'`, sql`name = 'bar'`];
const result = sql`SELECT ... FROM ... WHERE ${sqlJoin(whereClauseArray, sql` AND `)}`;