Hacker News Reader

Comment by atombender | Hacker News Reader

If your original data is flat and your app can consume it flat, no worries. But SQL is a worse fit for structured data, so you end up with an ORM on top, which is what GraphQL sort of is. Typically web apps are written with web technologies that work with structured objects, and you want something like {title: "Stapler", price: 1230, category: {id: "123", name: "Office supplies"}, mainPhoto: {url: "https://...", width: 600, height: 300}} back to feed directly in the UI.

Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal.

View on HN

"Some databases (Postgres comes to mind) are better at letting you express this as a query which builds the structure using JSON functions in the SELECT part of the query. But it's not even close to ideal."

Depending on which version of postgres you have(Want to say pre 9.5.) it can be a pain. There are various functions that allow a JSON output and another to make it human readable. With regards to performance I couldn't say but I imagine it would not be as fast as GraphQL.

In this context, what do you think of Datomic? Wish they make it the usual open source with a paid version with more features.

I don't know Datomic very well, but everything I've read makes it sound a bit like a modern take on an object database. Certainly interesting, but as long as it's commercial software I'm not going to touch it. Its close coupling (AFAIK) to Clojure is also not interesting to me.

You don’t need an ORM, just a big JOIN.

JOINs also return flat data structures, no?

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"], ... })
        }
    }

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.

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

Reply on news.ycombinator.com