Hacker News Reader

Definitely appreciate your "sometimes ORMs/sometime raw SQL" pragmatic stance. I agree!

> then if you join on both you now are loading A * B * C rows

I'm realizing it's not actually written up in our proper docs, but the "cross join lateral" trick is pretty neat:

https://joist-orm.io/blog/nextjs-sample-app#join-based-prelo...

This will return A number of (author) rows, where each author row as a `_b` column that is a JSON array of that author's books. And if you asked for C comment rows, then one of the "columns"/indices in each of those book arrays will itself be a nested array of the book's comments.

So all the data gets returned, but with no repetition/Cartesian product.

Hat tip to PostGraphile where I first saw the "cross join later" + "json_agg" technique used. Disclaimer we're not actually running this Joist feature in production yet--just haven't had time to run it through the test suite.

I wasn't aware of Joist until I read a comment of yours somewhere else in this comment section, interesting stuff! I think Joist is the closest thing I've seen to an actual fix to this problem. The way you use the JS event loop as your 'scope' is pretty clever, and satisfies most use-cases I think.

It aligns pretty closely to what we do internally. We don't use classes or annotations to do entity definitions, instead we'll have something like this:

    const User = {
        fields: {
            id: defineIdField(),
            email: defineField('string', false), // false = NOT NULL
        },
        manyToOne: {
            role: { entity: 'Role', nullable: false },
        },
    };

Then the way to query and save is something like this:

    datasource.inTransaction(async (orm) => {
        const users = await orm.findMany('User', { where: sql`...`, orderBy: sql`email asc` });
    
        // Await in this loop might look bad, but only blocks for the first user
        for (const user of users) {
            console.log(user.email, (await user.role).name);
        }

        const adminRole = await orm.findOne('Role', 1); // by ID, throws if not found
        return orm.saveMany('User', users.map(u => ({ ...u, role: adminrole }));
    });

Which looks different from what Joist is doing, but the fetching mechanics under the hood are very similar I think. Our 'scope' is just somewhat more explicit, since we don't use the event loop mechanics of JS but rather use the inTransaction callback as the boundary.

(The reason we haven't open sourced this yet is that I'd like to iron out some of the ugly parts first, like the string entity identifiers, and the forced usage of an id column, and the lack of type safe where clauses right now)

The fundamental problem is with the wire protocol. It’s inherently tabular, but should actually be more like a binary JSON format, with hierarchical representation for joins to avoid repeating the data.

Better yet, the wire formats should have efficient representations for things like column store compression.

Well, looking at something like the PostgreSQL protocol, it's decently efficient already. You could probably run a layer of compression on top of it, like HTTP gzip or so, but I doubt that will give a significant performance gain.

If you look at the end-to-end problem of 'what is the minimum amount of data I need during this request' vs 'how much data do I fetch, and what is my total latency / number of roundtrips to the db doing so?' I think for most ORM patterns that use lazy loading your primary target is reducing roundtrips, and for most hand rolled queries or ORMs tweaked to do eager loading, the primary target is deduplicating the results.

My take on this is that a decent approximation is a query per relation you're fetching, so if you have 10 entities A in a transaction, and each has 20 entities B attached, ideally you want 2 queries: one for the 10 entities A, and one for the 200 entities B. Lazy loading will give you 1 query for A + 10 queries for B, and eager loading will duplicate the 10 A entities data 20 times each (and that problem gets worse as your graph gets bigger with more one-to-many relations).

Once you run into the raw data transfer between database and backend being the limit, trying to optimize that protocol comes into play, but at least in the use cases I tend to have this is not usually a bottleneck. Besides, I'll typically serialize the data fetched to send out over HTTP again, which essentially has the same challenges if you're not using protobuf or so.

> I'll typically serialize the data fetched to send out over HTTP again

That's a whole other can of worms, so let's just ignore it for a second.

I've found that in a sense, the converse is the case, where back-end protocols significantly pre-date HTTP and "haven't learned the hard lessons" around things like cookies, caching, load-balancing, etc...

Ask yourself this: How many SQL database platforms can provide a redirect to clients? Or handle "client steering" at the load-balancer layer when hosted as a cluster with a partition key? Can any database platforms work with an in-line cache equivalent to a HTTP proxy? Digitally sign responses so that even un-trusted caches can be safely used? Etc...

Sure, some database platforms have solutions for some of those issues, but it's hit & miss at best.

And then, you have the problem that fundamentally all of them return tabular data, or go through some legacy thing like ODBC that expects row-oriented tabular data. If the source data isn't really tabular, it'll get expanded into a tabular form anyway.

At risk of sounding like the Dropbox-guy: it's not that hard to implement these yourself. I've had to write a service mimicking a PostgreSQL database for... reasons, and expanding the Postgres connection protocol to have functionalities for load balancing/caching/cookies would definitely be possible and not even crazy hard, especially if you at some point in your connection can just start proxying to an actual PostgreSQL server so you only have to intercept the handshake.

The bigger question is: what would that solve? Caching is mostly relevant if you are repeatedly executing the same, relatively heavy, query. Load balancing is mostly interesting if you have read-only queries that you can easily execute against any of X replicas, in which case you might as well do that application side instead of having some transparent layer in between. And I don't get why you'd need cookies for something that is a persistent connection instead of individual requests?

The tabular data thing is tricky though, you're right. The impedance mismatch between tables and nested object structures isn't easy to solve.

The reality of the situation is that ODBC isn't going away any time soon, and proposing a breaking change on top of protocols like that is not realistically going to see lots of adoption unless you come up with a really great alternative AND some good marketing around that.

I've come to the conclusion that the best way to spend my efforts is to work with the ecosystem and build tools that try and fix the impedance mismatch is easier than hoping for something better to come along. Making it easier to write performant code is yielding more results for me than trying to adapt SQL-protocols to horizontally scale. A single box with some well placed indexes and well written queries is ridiculously fast.

Ideally a query could return multiple cte tables.

A lot of ORMs default eager loading to select-in loading with multiple queries because it performs best. First A is loaded and then the B query has a `WHERE B.foreign_key in (...all A id's loaded in the first step..)`.

Still has overhead, but usually less than duplicating data or using another serialization format like returning hierarchical json objects

Orm's do a lot for you if you require related data from multiple tables:

- Eager loading the data efficiently is annoying, most ORM's can do batched select-in loading now

- Detecting changes with the DB is annoying if you update more than a single row, requires change tracking or a diff

- Saving changes is a topo sort so you don't delete while foreign keys exists. Also, gotta wait for a foreign key to exist when a new row is inserted

But just let me tell the ORM a spanning tree of the schema and load that! Why do all ORM's default to cyclic object graphs and lazy loading.