Just iterate through the results and recover the hierarchical structure. It’s not exactly rocket science:

    rows = sql("SELECT * FROM parent LEFT JOIN child ON parent.id = child.parent_id")
    results = []
    for row in rows {
        if row["parent.id"] != results.last.id {
            results.add({ id: row["parent.id"], children: [], ... })
        }
        if row["child.id"] != null {
            results.last.children.add({ id: row["child.id"], ... })
        }
    }

You have to handle arbitrarily nested hierarchies.

You also need to support referential equality — so that objects with the same type and ID map to the same in-memory JS instance — which requires building an identity map that can live across multiple paginated result set pages.

You have to support untangling the object trees for UPDATE statements, which will require tracking dirtiness to avoid unnecessary requests. Then something similar to deconstruct new objects into multiple INSERTs. Both require a knowledge of foreign-key relationships unless you run all constraints in DEFERRED mode.

Now you're building an ORM.

(Your query also makes assumptions about order and structure that you cannot make in the general case. The devil is in the details.)

but now it's no longer a declarative query. What if you want to include grand children? or if you want to fetch just one child but include it's parent and it's siblings of another type like a product, it's category and the categories tags:

   {id: 23, name: 'Trackpad', category: {id: 42, name:'Equipment', tags: [{id:...}, {id:...}]}}

Now instead of just adding two joins you have to build a custom loop with hand crafted conditions. Sure you can find a way to generalize that loop and put it into a library but still the query itself is separated from some kind of post processing that has be kept in sync.

Nice bit of code.

Just to clarify, wouldn't you need to order by parent_id for the row["parent.id"] != results.last.id to work? Otherwise, say you're ordering by child.date_modified, you could end up with two identical parent_ids in your array?

Yeah, I forgot the ORDER BY clause, but you get the idea.