Arel: The Ruby Relational Algebra

Talks

Bryan Helmkamp

#database-integration

#object-relational-mapping-orm

#query-optimization

Arel: The Ruby Relational Algebra

by Bryan Helmkamp

The video titled "Arel: The Ruby Relational Algebra" features Bryan Helmkamp, CTO of Efficiency 2.0, speaking at the GoGaRuCo 2010 event. In this presentation, Helmkamp introduces Arel, the Ruby relational algebra engine that supports ActiveRecord in Rails 3, emphasizing its significant impact on simplifying SQL query generation and improving object-relational mapping capabilities.

Key Points Discussed:
- Introduction to Arel:
- Arel is an object-oriented interpretation of relational algebra, developed as a toolkit to build ORMs rather than being an ORM itself.
- The project has been around since December 2007, with several community contributors enhancing its functionality.

Relational Algebra Basics:
- A relation consists of a header (with column names and types) and tuples (rows) resembling a SQL table.
- Common operations include selection (analogous to SQL's WHERE clause), projection (selecting specific columns), and various set operations (like intersection and difference).
- The concept of closure allows for continuous transformations of relations into new ones, which is foundational for Arel's operation.
Integration with ActiveRecord 3:
- ActiveRecord 3 incorporates Arel for improved internal structure and ease of querying.
- Helmkamp presents a high-level architecture diagram showing how ActiveRecord and Arel interact, explaining the role of connection adapters in executing queries against databases.
New Syntax and Querying Power:
- The introduction of Arel has led to a new querying syntax in ActiveRecord, making it easier to construct complex queries without relying solely on raw SQL.
- Example queries demonstrate the streamlined approach to retrieving data using Arel in Rails 3, highlighting the shift from cumbersome hash syntax to a more intuitive method.

In conclusion, Helmkamp emphasizes that Arel significantly enhances the capabilities of ActiveRecord in Rails 3, allowing developers to write more efficient and expressive database queries. The presentation advocates for the ongoing exploration of Arel's functionality in both current and future web applications. The insights shared showcase Arel's potential to reshape how developers interact with data within Ruby applications.

00:00:09.200 Thank you everyone. Can you hear me in the back? Okay, Jim, can you hear me? Good, cool. As Josh said, my name is Bryan Helmkamp, and I'm the CTO of a company called Efficiency 2.0 in New York. We have a pretty cool project where we use Ruby software to help people be more energy efficient, especially with their home power bill. By the way, we're also hiring Ruby engineers in New York, so if that's interesting to you, come find me. The slides for this talk are available online through a Bitly link that will take you to SlideShare. My Twitter handle is @brianhelmkamp, and my blog, which is now a 404 page, will be back up and running at some point soon. So that's aspirational. This is the agenda. I've given this talk once before at a Ruby group, and I think it took me about 45 minutes. So, I have two weapons at my disposal: the first is coffee to try to make this go quicker, and the second is to get a sense from you about what you're most interested in learning about and your familiarity with the subject material so far. I’m going to start there.

00:00:58.559 How many people have heard of Arel? Okay, that's pretty much everybody. How many have used Arel, not through ActiveRecord? So, you know, looked at the code or manipulated the object? Let’s see. Okay, just a few. So that's kind of the audience I’m going to target: people who have heard of it but maybe haven't used it directly. I'm going to show off some of the power available in ActiveRecord 3 now that Arel has been integrated into Rails 3. We'll look at what Arel is, delve into relational algebra—the conceptual framework that Arel is built on—and examine how Arel is integrated into ActiveRecord 3. We'll pop the hood to explore the implementation, how that works, and some of the features that are maybe a little lesser-known but can still be advantageous in your Rails 3 applications today. Finally, we'll get into the 'rainbows and unicorns' section of the talk, if there's time, and look at some interesting possibilities I think might be achievable in the future now that ActiveRecord has this relational algebra base.

00:01:47.439 So, what is Arel? It has nothing to do with mermaids. It is an object-oriented interpretation of relational algebra written in Ruby. That’s how Nick Kallen described it when he was originally working on it. Nick is the original author of Arel, and this project actually dates back further than most people realize. As I was preparing for this talk, I went through the git history, and the first commit on Arel is actually from December 2007. So, while Arel is being presented as a relatively new piece of code that you can leverage, it has existed in some form for quite some time. The concepts that it’s built on go back even further than that when looking at things like game scope, which we’ll talk about shortly.

00:02:02.960 One critical point I wanted to emphasize is that Arel is not an ORM (Object Relational Mapper). ActiveRecord is an object-relational mapping engine. Arel could be considered a toolkit on which to build an ORM but isn't an ORM itself. This means that if another framework, let’s say DataMapper, decided they needed a better way to integrate with relational data stores, they could use Arel, and that would be just fine. That’s an important distinction; this project is not simply a sub-project of Rails—it’s its own unique project with applicability far beyond ActiveRecord over time, although ActiveRecord is the first production-ready ORM to implement Arel, which is a great accomplishment.

00:02:38.400 If you’re interested in learning more about the origins of Arel, Nick Kallen has a blog post titled 'Magic, Scale, and Sprinkles' on why he wrote it, and I recommend checking that out. I also wanted to emphasize that Arel is a largely community-contributed project. There are many contributors, including people in this room, who have done significant work on making Arel production-ready. Without them, we wouldn’t be able to take advantage of this today. I want to take a moment to thank all the Arel contributors, especially Nick for being the original author. We could not be benefiting from this functionality without the efforts of these individuals.

00:03:22.400 So, how does Arel integrate with ActiveRecord 3? This is a high-level architecture diagram. ActiveRecord 3 sits on top as the interface that you know and love, which is an evolution of the Rails 2 ActiveRecord interfaces. Below that, you have ActiveRelation, split into two components: the algebra, which implements a relational offer engine, and the engines, which allow you to execute those relational concepts against the database. Finally, you need a connection adapter; something in the stack must communicate with your SQL database, like MySQL or Postgres, on a socket. In this case, we’re still using the ActiveRecord connection adapters, which are largely unchanged from Rails 2, and that all sits atop the database, which runs in a separate process, as we all know.

00:04:05.400 Relational algebra 101: let’s cover the first part, which is 'what is a relation?' A relation is composed of two components: a header, which is essentially a list of columns. This should be familiar to anyone who has seen a database diagram before. Each column has a name and a type, followed by a set of tuples. For instance, this relation has four tuples associated with it, and we can start to see how this maps to SQL concepts. This isn't strictly an SQL concept; you could represent a relation with an array of arrays in Ruby, which can sometimes be useful. If you consider how the concept of a relation relates back to what you’re used to in SQL, you’ll see that it resembles a table, much like a view or just a result set from a query that you run on an ad-hoc basis and then throw away. It maps to a relation in ActiveRecord, similar to the concept of a class and an association.

00:05:00.000 So, what can you do with relations? Selection is one of the most common operations performed on a relation. This is analogous to the WHERE clause in SQL, where you're going to restrict a relation to create a new relation, which is essentially a subset. Projection limits the columns associated with the relation. For example, let’s say you have a Users table with 15 columns, and you're only interested in the first name and last name. You might say, 'SELECT id, first_name, last_name FROM users.' That’s called a projection in relational algebra terms. There are joins, which I won’t dive deeply into, but know they are part of relational algebra and behave largely like they do in SQL.

00:05:44.480 Additionally, there are set operations, such as intersection and difference, which should be familiar to everyone. The most interesting aspect of relations is the concept of closure, meaning if you take a relation and perform an operation on it, you get a relation out the other side. This allows you to continue transforming relations into new relations infinitely while maintaining the properties and functions you have at your disposal. The following slide illustrates how this is reflected in code in Rails 3. This is actual Rails 3 code. If you have a Rubyist that maps to an ActiveRecord-based class, you can extract the relation by calling .scope, add joins, where clauses, limit, etc., and eventually run the query. It works just as you would expect. This is implemented in the code using a composite pattern, which I find quite fascinating. Moreover, relations are also immutable; this is why I must perform assignments on each line to ensure we capture the new results as they come out the other side.

00:06:48.000 So, what’s in it for you today? The fact that ActiveRecord is now based on Arel means the internals of ActiveRecord have been cleaned up quite a bit. However, I’m going to assume you largely don’t care about that—you’re not an ActiveRecord maintainer. But, in cases where the implementation of ActiveRecord can be cleaner and more useful to you, that’s interesting. There’s also a new, easier query syntax, which is probably the number one thing people have noticed when they heard about Arel in Rails 3. I’ll cover that first. Originally, there were hash queries that used a syntax I’ve known for a long time. You would execute a find_all and then follow it with a hash where everything in the query needed to be represented as keys within this hash, conditions being one of the most critical aspects. Conditions can also be a hash when you need to do equality lookups without caring about order. However, when you need something more sophisticated, like querying for an age greater than 18, the hash syntax becomes limiting.

00:08:35.440 How many times have you had a query starting with a conditions hash, only to realize that it can't express something straightforward, and so you had to rewrite the entire query using the raw SQL syntax with substituted values? This happened frequently when working with Rails 2.1 that we had to reiterate the conditions. We have name scopes now, which is a convenient way to define reusable queries, but they still suffer from the same limitations that the original hash syntax did. This is a supplement, not a replacement. Fast forward to today, and as you all know, Rails 3 has shipped. Here’s an example of the new syntax you can leverage: If you have a Rubyist model, you can say 'Rubyist.where(city:

GoGaRuCo 2010