What ORMs have taught me: just learn SQL (2014)

const existingBooks = await pool.exe(select("books", { authorId }));

or

const existingBooks = await pool(select("books", { authorId }));

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

    const
      query = db.sql`
        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);

I'll update demo.ts and README shortly.

um... what? Are you meaning to say that Postgres does a pretty good job as a document store? (not synonymous with "nosql")

Despite that that wikipedia article says, most (if not all) of the "impedence mismatches" described apply to most document stores as well. I would be curious to hear which of the mismatches described in that article you think are avoided by using Postgres as a document store. In my mind, the reason for using a document store is to have flexibility in the structure of your data (which can be a positive or negative depending on your needs).

> Or you can use something that actually was built to do reporting properly. I do a lot of stuff in Elasticsearch...

Of course there are document stores with good reporting. I was talking specifically about the downside of using Postgres as a document store given your complaints about its native json support being fiddly.

> But for the kind of stuff people end up doing where they have an employee and customer class that are both persons that have addresses and a lot of stuff that is basically only ever going to be fetched by person id and never queried on, I'll take a document approach every time vs. doing joins between a dozen tables. I also like to denormalize things into documents.

I often de-normalize addresses in my tables, but that choice is based on how you will want to store and update that data. A separate address table is good if you want to be able to automatically propagate address edits between records. A de-normalized address is good if you want keep records of that address for the purpose for which it was used. De-normalization is always an option with a relational DB, but normalization is not always easy some document stores.

> Having a category table and then linking categories by id is a common pattern in relational databases. Or you can just decide that the category id is a string that contains some kind of urn or string representation of the category and put those directly in in a column or in the json. You lose the referential integrity check on the foreign key of course; but then you should not rely on your database to do input validation so that check would be kind of redundant.

I'm not quite sure what you are on about here. You can use constraints on columns that are strings and you can have tables that are composed entirely of an indexed string column to point that constraint towards. Integer Ids are primarily used just to save space. (I don't really see how this is relevant.)

I don't see anything here to justify your assertion:

> A good table structure often makes for a poor domain model and vice versa. The friction you get from the object relational impedance mismatch is best avoided by treating them as two things instead of one.

To be frank, it sounds to me like you ran across a bunch of poorly designed DB schemas (or schemas you didn't understand the design decisions for) and decided that it must be impossible to design good DB schemas and so you just use unstructured document stores instead.