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 :grin:

[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.