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.
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 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.)
{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.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?
It's kind of odd to use that to dismiss the solution that was established and widely available for 30 years longer, and which more people are probably familiar with.
We played with wrapping our dB layer with graphql recently and I would say that the experience was pretty good. In part it’s because you’re working a layer up where you can have a more pluggable model. In the first run you can model your local dB structure, but it’s easy to glue more stuff into it from other sources and generally evolve the structure.
select id, count(*) - 1 as nduplicates
from parts where color = 'red'
group by id
having count(*) > 1;
WHERE cannot filter on the count(*) because WHERE is applied during the query before the count is known.I agree SQL is kind of warty, and it would be great if it were easier to compose from programs, but HAVING is not redundant to WHERE.
select * from (select id, count(*) - 1 as nduplicates
from parts where color = 'red'
group by id) _inner
where nduplicates > 0;
Now obviously that doesn't read as easily, but WHERE can filter out the count(*) perfectly.Two things I'd fix:
* make it explicit when a join is expected to have 1:1 semantics vs 1:n, because it's very easy to end up with duplicated rows otherwise
* increase composability of the syntax; why do we need HAVING, why isn't WHERE enough? We have relational algebra, projections, filters, joins, folds, union, intersect etc. But the syntax of SQL is so idosyncratic, and at times antagonistic to the stream and set-like intuitions behind relational algebra.
To do it right, you'd need to mark up the relations with expected cardinality (effectively unique constraints), so that the DB would be able to verify ahead of time whether a join is going to be 1:1 or 1:n. That would be a better solution than a join working fine up until the cardinality expectation is violated and it suddenly stops working.
If we had to stick with SQL syntax, it might be something like:
select a.*, b.*
from a
join one b on b.id = a.b_id
(Unique constraint from primary key on b.id. It may still filter down the set of rows in a, but it won't duplicate.)Or:
select a.*, b.*
from a
join many b on b.some_key = a.some_key
(No unique constraint on b.some_key)I think the interesting cardinality distinctions are 1:1, 1:n and 1:0.
(The above syntax is ambiguous with aliases, so it wouldn't fly as is. But it gives a flavour.)
What I was saying was that often people have T1::T2 (1-1) and then throw in a later join T2::T3 (1-many) without considering the implications for the T1 (maybe they were doing a count(T1.id)). At least that seems to be an aspect that confuses developers newer to sql (that the many join is duplicating other parts of the data).
Think something like measurements samples that come in an envelope with metadata, or account updates that come in a statement, that kind of thing.
I got a high performance award after that project was completed and I don't consider myself a Rockstar developer
"Most devs" don't directly deal with SQL these days, because ORMs are good enough for day-to-day operations (and the non-day-to-day ends up being handled by people who actually know SQL). In fact, "most devs" rarely interact with databases. A lot of data sourcing is API driven these days.
Now, I don't claim to like or dislike that particular state. But you have two choices: You can innovate, which carries risks (of being wrong; of not selling your idea correctly; of reinventing the wheel; of making the same mistakes others did; ...). Or, you can play it safe, and create APIs that most people will know how to use without having to go through a 1-week course, because short as that may sound, that's 167.5 more hours than anyone will reasonably be willing to invest in understanding your API.
So at the end of the day: Use the right tool for the job. SQL for APIs is usually not the right tool.
{ getOrganisation(args){ name, employees{ Name, Position }}
(I added commas to make it easier to read, but they are not supposed to be there)
I know SQL, so I don’t think it’s necessarily easier, but what is easier, is non-sql-savvy people making efficient queries because GraphQL does a lot of the heavy lifting for you.
You still want to be declaring the fields and properties of the data that users will be querying, and that usually sits a layer above your raw data.
You can make it a SQL view if you like, and give access to those instead. But it's just a lot of hassle to solve a solved problem in an unfamiliar way.
_______
Edit: Clarifying my stance on SQL views. I'm not dismissing it as a "bad tool", but there's good reasons it's not a popular one today. Writing SQL is not easy for everyone, and it's especially not easy for potential API consumers.
The best APIs are those you grok. If you grok SQL, congratulations, but most devs don't, and APIs are usually designed for most people.
Obviously, if SQL is the right tool for the job, use SQL. But don't start doing stuff like this for ideological reasons.