Some years ago I got deep into working with ActiveRecord & Arel because there was supposed to be a hot new way to compose more complex queries in the then-upcoming Rails 3.0… this was before it was officially declared off-limits as an internal interface. I eventually had to give up trying to bend it to my will.
But, I was still curious about what Arel was originally promising in its name, before any mention of “relational algebra” got purged from the README (the name is officially just “Arel Really Exasperates Logicians” now).
I eventually figured out that the Arel library itself couldn’t approach a true relational algebra, and even a patch to untangle something basic like double-negatives —
NOT(NOT(...)) — was really just a spot-fix.
Regardless, I’m still interested in the idea of having a functional interface for working with relations as composable units.
Yes, it’s possible to do some restricted kinds of query composition in Rails but it’s often easier to drop directly into SQL for anything more complex: the tradeoff is that once you reduce to a SQL string, it becomes opaque to your program and is no longer reusable for combination with other relations. Also, the fact that SQL injection still leads the OWASP Top 10 should be telling us to prefer secured interfaces for building queries; dropping to raw SQL in limited cases as a last resort. (Of course there are ways of using SQL securely with ActiveRecord, but you have to always remain aware of them.)
On the other hand, the Sequel gem and similar syntax builders are excellent for writing queries securely and functionally, but they still expect us to write with the resulting SQL in mind. It still can’t compose two abstract domain concepts together, say like intersecting two scopes between associated models. The algorithm for performing the intersection has to be defined as a concern of one domain object knowing about the data semantics of other objects, not as a separate, composable concept between them.
(SQL calls itself a declarative language, but you actually do need to think about the procedural ways of fetching data, whether from subqueries or joins. Common Table Expressions can help a lot for defining some abstractions though.)
I came across a couple of research projects with higher goals for composable data relations, as more pure implementations of a relational algebra:
These projects attempt to move beyond some of the limitations of providing direct mappings to SQL from the host language, and exposing relations as first-class objects. This is a layer above the query language: Alf actually uses Sequel under the hood; Axiom has its own Arel-like AST visitor.
Examples like this one from Alf look compelling:
# Get the cities where at least one supplier is located, provided # at least one part is located there too. cities_from_suppliers = project(suppliers, [:city]) cities_from_parts = project(parts, [:city]) intersect(cities_from_suppliers, cities_from_parts)
The author of Alf summarizes the situation pretty clearly:
- SQL has not been designed with composition and separation of concerns in mind,
- Avoiding strong coupling between subqueries tends to be very difficult in practice,
- Coupling hurts separation of concerns and software design.
…and an ORM or query builder doesn’t solve this.
I’m sure it would need more investigation and lots of work before implementations like this could become practical (let alone used as an underpinning for anything like ActiveRecord), but the ideas still look fascinating to me. I’m curious if anyone has worked/played with similar ideas or has opinions about the goal of actually implementing a relational algebra for data models completely above “SQL thinking”.