Postgres's lateral joins allow for quite the good eDSL

Postgres's lateral joins allow for quite the good eDSL

Lateral joins are quite neat and you can build a query eDSL with them.

Postgres (and a few other databases(?)) has a lesser known or used join type known as the lateral join. They allow columns from preceding FROM clauses to be used in subqueries that are being joined.

As a (bad) example, take this pretty standard query joining two tables:

SELECT *
FROM users u
INNER JOIN posts p ON u.id = p.user_id

This can be rewritten as a lateral join with:

SELECT *
FROM users u
CROSS JOIN LATERAL (select * from posts p where u.id = p.user_id) p2

Notice that the join type changed to CROSS, normally this would result in a cartesian product, but the filter inside the subquery means each post is still paired up only with its user.

In fact, both queries actually get the same query plan:

+----------------------------------------------------------------+
| QUERY PLAN                                                     |
|----------------------------------------------------------------|
| Hash Join (cost=37.00..60.52 rows=1070 width=88)               |
| Hash Cond: (p.user_id = u.id)                                  |
| -> Seq Scan on posts p (cost=0.00..20.70 rows=1070 width=48)   |
| -> Hash (cost=22.00..22.00 rows=1200 width=40)                 |
| -> Seq Scan on users u (cost=0.00..22.00 rows=1200 width=40)   |
+----------------------------------------------------------------+

This is actually really useful as it provides a way to solve an expressivity problem that I think most ORMs and query builders have: that queries are difficult to compose.

I think most composition techniques in other query builders boil down to either passing some query builder object through a sequence of functions, each of which appends a table to be joined and a where clause, or having functions that return subqueries which you then have to handle joining with your query yourself.

Neither of these are very good, the first way probably only works in dynamically typed languages, and in the latter you lack any way to provide a posts_of_users(user_id) -> [Post] function.

Worst of all IMO are ORMs which abstract away relationships. It’s cool at first to be able to write select(User).with(Post) and have it build the join automatically, or even handle a M2M join. But as soon as you need to update something it becomes even more tedious than managing the join table yourself. I’ve witnessed before the need to first load all values before you can add or remove an entry using the normal interface, lest you accidentally instruct the ORM to delete all entries before inserting yours, or try to create duplicates on one side of a M2M.

Also any ORM with some .with() method is going to be hell in a typed language, it either needs to use some macro magic to summon types such as FooWithBar for every possible combination, or maybe it produces some With<Foo, bar> type that results in some horrible types With<With<Foo, Bar>, Baz> that are impossible to work with without IDE assistance.

I want to present a type of query builder library that is all of:

• Expressive: Queries doing complicated joins shouldn’t be inscrutable.
• Composable: Queries should be reusable, and parameterisable.
• Type safe: The eDSL works within the type system of the host language.
• Always generates valid SQL: Particularly useful for aggregations.
• Works with user types: Handles the boilerplate of making user types work with the database.

The first library I’ve encountered that provides a way to build queries compositionally using lateral joins is the Haskell library Rel8:

postsForUser :: Expr UserId -> Query (Post Expr)
postsForUser userId = do
  post <- each postSchema
  where_ $ post.userId ==. userId
  pure post

usersAndPosts :: Query (User Expr, Post Expr)
usersAndPosts = do
  user <- each userSchema
  post <- postsForUser user.id
  pure (user, post)

This interface is extremely expressive to the point that it feels like you’re actually just manipulating data that is already in the host language, but in actuality you’re building a sql query using LATERAL joins.

I’ve also written a Rust library which replicates the behaviour of Rel8. It allows for aggregating results or using .optional() for left outer joins in a type-safe manner.