[gmac]

I'm also awed by this!

:)

1. Whether `Selectable[]` can be used to query for a subset of fields and how.

Right — this is not (currently) supported. I guess if you had wide tables of large values, this could be an important optimisation, but it hasn't been a need for me as yet.

2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?

Multiple authors with the same id isn't going to happen, since id is intended as a primary key, so I'd argue that the example as given isn't brittle. On the other hand, there's a fair question about what happens for many-to-many joins, and since my use case hasn't yet required this I haven't given it much thought.

[gmac]

OK, I gave the one-to-many queries a bit more thought, and the converse join query (getting each author with all their books, rather than all books each with their author) works nicely with a GROUP BY:

    type authorBookSQL = s.authors.SQL | s.books.SQL;
    type authorBookSelectable = s.authors.Selectable & { books: s.books.Selectable };

    const
      query = db.sql<authorBookSQL>`
        SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"}
        FROM ${"books"} JOIN ${"authors"} 
          ON ${"authors"}.${"id"} = ${"books"}.${"authorId"}
        GROUP BY ${"authors"}.${"id"}`,

      authorBooks: authorBookSelectable[] = await query.run(db.pool);

This exploits the fact that selecting all fields is, logically enough, permitted when grouping by primary key (https://www.postgresql.org/docs/current/sql-select.html#SQL-... and https://dba.stackexchange.com/questions/158015/why-can-i-sel...)

I'll update demo.ts and README shortly.