Comment by phiresky

I think relational databases are great, but this is my biggest problem with SQL, even before the ridiculous syntax.

A query like

    select users.*, orders.* from users left join orders on orders.user_id = users.id

Should always have returned a structure like:

    type SingleReturnRow = { users: {id: ..., }[], orders: {user_id: ..., id: ..., }[]}
    type Return = SingleReturnRow[]

Mangling the columns together and _removing_ groupings that naturally appear is just so unnecessary.

I don't think a larger change in the query language would even be needed.

Even better of course would be a return value like

    type SingleReturnRow = { user: User, orders: Order[] }

But I see how that would require a fundamental change in the language.

Of course in PG now you can use

    select users.*, json_agg(orders.*) as orders from users left join orders on orders.user_id = users.id group by users.id

but using JSON as intermediate steps just feels unnatural.