SQL needed structure

Here are two pages from the internet movie database:

There are two things to note about these pages.

1. The data on the page is presented in a hierarchichal structure. The movie page contains a director, a list of genres, a list of actors, and each actor in the list contains a list of characters they played in the movie. You can't sensibly fit all of this into a single flat structure like a relation.

2. The order of the hierarchy isn't the same on both pages. On one page we have movie->actors and on the other page we have actor->movies. So you can't just directly store the hierarchy in your database - you need to be able to traverse relationships in both directions.

So we store all our data in a relational database in flat tables and then whenever we need to render some UI we transform the flat data into whatever hierarchy we need.

Doing this transformation by hand is tedious and error-prone. We call this tedium "the object-relational mismatch" but it isn't really about objects or relations. The fundamental problem is that fitting complex relationships to human vision usually requires constructing some visual hierarchy, but different tasks require different hierarchies.

Whatever database and programming language you use, you will have to deal with this. But it's particularly painful in sql because sql wasn't designed to produce hierarchical data.

sql wasn't built to yield structure

Let's grab the imdb public dataset and try to reproduce the source data for that movie page (or at least a subset of it, because I didn't bother importing all the tables). We want to see an output that looks like this:

{
  "title": "Baby Driver",
  "director": ["Edgar Wright"],
  "writer": ["Edgar Wright"]
  "genres": ["Action", "Crime", "Drama"],
  "actors": [
    {"name": "Ansel Elgort", "characters": ["Baby"]},
    {"name": "Jon Bernthal", "characters": ["Griff"]},
    {"name": "Jon Hamm", "characters": ["Buddy"]},
    {"name": "Eiza González", "characters": ["Darling"]},
    {"name": "Micah Howard", "characters": ["Barista"]},
    {"name": "Lily James", "characters": ["Debora"]},
    {"name": "Morgan Brown", "characters": ["Street Preacher"]},
    {"name": "Kevin Spacey", "characters": ["Doc"]},
    {"name": "Morse Diggs", "characters": ["Morse Diggs"]},
    {"name": "CJ Jones", "characters": ["Joseph"]}
  ],
}

Let's grab the title first:

postgres=# select primaryTitle from title where tconst = 'tt3890160';
 primarytitle
--------------
 Baby Driver

And now we need the director:

postgres=# select primaryTitle, person.primaryName 
from title, principal, person
where title.tconst = 'tt3890160' 
and title.tconst = principal.tconst 
and principal.nconst = person.nconst 
and principal.category = 'director';

 primarytitle | primaryname  
--------------+--------------
 Baby Driver  | Edgar Wright

And the writer:

postgres=# select 
  primaryTitle, 
  director.primaryName as director,
  writer.primaryName as writer
from title, 
principal as principal_director, person as director, 
principal as principal_writer, person as writer
where title.tconst = 'tt3890160' 
and title.tconst = principal_director.tconst 
and principal_director.nconst = director.nconst 
and principal_director.category = 'director'
and title.tconst = principal_writer.tconst 
and principal_writer.nconst = writer.nconst 
and principal_writer.category = 'writer';

 primarytitle |   director   |    writer    
--------------+--------------+--------------
 Baby Driver  | Edgar Wright | Edgar Wright

We're already in trouble. If this movie had 2 directors and 2 writers, this query would return 4 rows:

 primarytitle |   director   |    writer    
--------------+--------------+--------------
 Baby Driver  | Edgar Wright | Edgar Wright
 Baby Driver  | Edgar Wright | A. Writer
 Baby Driver  | A. Director  | Edgar Wright
 Baby Driver  | A. Director  | A. Writer

If there was no director in the database then this query would return 0 rows, no matter how many writers there were. Now we don't even know what the movie is called.

 primarytitle |   director   |    writer    
--------------+--------------+--------------

We can't sensibly fit the data we want into a single relation, and we can't return more than one relation per query. So we have to issue multiple queries:

postgres=# select primaryTitle from title where tconst = 'tt3890160';
 primarytitle
--------------
 Baby Driver

postgres=# select person.primaryName 
from title, principal, person
where title.tconst = 'tt3890160' 
and title.tconst = principal.tconst 
and principal.nconst = person.nconst 
and principal.category = 'director';

 primaryname  
--------------
 Edgar Wright

postgres=# select person.primaryName 
from title, principal, person
where title.tconst = 'tt3890160' 
and title.tconst = principal.tconst 
and principal.nconst = person.nconst 
and principal.category = 'writer';

 primaryname  
--------------
 Edgar Wright

postgres=# select person.nconst, person.primaryName
from title, principal, person
where title.tconst = 'tt3890160' 
and title.tconst = principal.tconst 
and principal.nconst = person.nconst 
and principal.category = 'actor'
limit 10;

  nconst   |  primaryname  
-----------+---------------
 nm5052065 | Ansel Elgort
 nm1256532 | Jon Bernthal
 nm0358316 | Jon Hamm
 nm2555462 | Eiza González
 nm8328714 | Micah Howard
 nm4141252 | Lily James
 nm3231814 | Morgan Brown
 nm0000228 | Kevin Spacey
 nm1065096 | Morse Diggs
 nm1471085 | CJ Jones

postgres=# select principal_character.nconst, principal_character.character
from title, principal, principal_character
where title.tconst = 'tt3890160' 
and title.tconst = principal.tconst 
and principal.nconst = person.nconst 
and principal.category = 'actor'
and principal_character.tconst = principal.tconst
and principal_character.nconst = principal.nconst;

  nconst   |     character      
-----------+---------------------
 nm5052065 | Baby
 nm8328714 | Barista
 nm0358316 | Buddy
 nm2555462 | Darling
 nm4141252 | Debora
 nm0000228 | Doc
 nm1256532 | Griff
 nm1471085 | Joseph
 nm1065096 | Morse Diggs
 nm3231814 | Street Preacher

Through the magic of joins we have retrieved all the data we need and it only required holding a transaction open for 4 network roundtrips.

All that's left to do now is... the same joins, but inside the backend web server. Because we have to re-assemble these flat outputs into the structure of the page.

Also note that fully half of the data returned is the nconst column which we didn't even want in the output. We only returned it because we need it as a key so we can repeat the joins that we already did in the database. The more paths you traverse, the more useless join keys you need to send to the backend web server.

All of this is pretty tedious so we invented ORMs to automate it. But:

• Almost all ORMs end up sending multiple queries for the output that we want. If you have a good ORM and you use it carefully it'll send one query per path in the output, like the raw sql above. If you're less careful you might get one query per actor in the film.
• Many ORMs also make a mess of consistency by lazily loading data in separate transactions. So we might generate a page where different parts of the data come from different points in time, which is confusing for users.
• Using an ORM locks you into only using one specific programming language. What if you need to query your data from a different language? You'll probably end up talking to the same ORM through a microservice.

old dogs can sort of learn new tricks

These days sql actually can produce structured data from queries.

A lot of people are mad about this. Whenever I talk about it they reflexively yell things like "structured data doesn't belong in the database" as if there was a universal system of morality that uniquely determined the locations of various data processing tasks.

But I can't help but note again that the structure has to happen somewhere because that's what the output page looks like and that doing it outside the database isn't working very well.

Whenever we're building a UI for humans, whether on the web or native, the main use of the query language is to turn relational data into structured data for the client to render. So it would be really nice if the query language was actually able to produce structured data.

Like this:

select jsonb_agg(result) from ( 
  select 
    primaryTitle as title, 
    genres,
    (
      select jsonb_agg(actor) from (
        select
          (select primaryName from person where person.nconst = principal.nconst) as name, 
          (
              select jsonb_agg(character)
              from principal_character
              where principal_character.tconst = principal.tconst
              and principal_character.nconst = principal.nconst
          ) as characters
        from principal
        where principal.tconst = title.tconst 
        and category = 'actor'
        order by ordering 
        limit 10
      ) as actor
    ) as actors,
    (
      select jsonb_agg(primaryName)
      from principal, person
      where principal.tconst = title.tconst
      and person.nconst = principal.nconst
      and category = 'director'
    ) as director,
    (
      select jsonb_agg(primaryName)
      from principal, person
      where principal.tconst = title.tconst
      and person.nconst = principal.nconst
      and category = 'writer'
    ) as writer
  from title
  where tconst = $1
) as result;

It's not perfect. You can definitely see the duct tape, and the query plan often suffers from the lack of decorrelation. But we can grab all the data needed for the entire page in a single query, with one network roundtrip. Whether you use these features directly or as the output of your ORM, this is a sizable improvement for one of the main usecases of relational databases!

It doesn't matter that this isn't the way things have always worked. Sql is not relational algebra and relational algebra is not math, and neither was carved into stone tablets handed down from Codd.

We make tools to serve our purposes, and our purposes have changed a hell of a lot since the 70s, when the main client of a database was a human typing sql character by character into an interactive transaction on a teletype connected to a mainframe with 500kb of RAM, almost 20 years before the invention of the world wide web.

Maybe it's ok for our tools to evolve to meet new demands. And we can evolve with them.