00:00:09.620
Hi, I'm Eric Hayes. I lead development for a small business that provides a reporting app for nonprofits like churches. We have a medium-sized database compared to other organizations, with several tables containing millions or even tens of millions of rows. Therefore, we need to do everything we can inside the database. That's why a couple of years ago, I started seriously learning SQL and everything we could do with Postgres. A lot of that is what led to this talk.
00:00:17.900
To jump in, we need some target SQL to write. Imagine we work for a charity, and the donor relations department wants us to help identify their donor retention metrics. This concept could easily be adapted for various product metrics, like return visits. However, I usually like to begin by experimenting with SQL first to find the results I want. Here's an example; I'm going to assume you know the basics of SQL, but even for a pro, this query could look daunting. Let's highlight the interesting bits.
00:01:01.340
First, we want to get the first time a donor donated using the MIN aggregate function. We use the date_trunc function to convert whatever date that is into just a month, which groups all of our donors by the first month they donated. Then, we select one as a marker indicating that they donated in that month. Next, we combine several similar statements together with a LEFT JOIN LATERAL. This special join allows us to reference values in the original query. When experimenting with SQL, there's a pretty cool Mac app called Postico. Here, I've loaded the query and run it, displaying the results. For each donor ID, we see the first gift they gave and the last three columns indicate one month out from their first month. If they made four donations, they would appear in one of the four columns.
00:02:41.970
Now, there's duplication here that we want to automate, and there are useful snippets that we may want to rearrange or recombine on-the-fly. For example, this version gives us individual donor timelines with just a few changes. We can aggregate the data into counts, yielding the foundation of a cohort table. Ideally, we should have at least 12 of these columns, and you can see how the sums have aggregated the results. We can begin to observe retention over time.
00:03:01.260
There are many more ways we can use this SQL; we can select different things, add WHERE clauses, change dates, and obtain entirely different insights. However, you can see how this starts to become unwieldy to write by hand. One approach is to build this with strings using loops and string interpolation, and if this were the only special query, that would probably suffice. But as the number of different queries grows, it becomes increasingly difficult to maintain them as they are rearranged and used in various ways.
00:03:30.300
My team inherited an app littered with impenetrable blocks of unformatted SQL. We ended up writing around those files rather than attempting to maintain them. Alternatively, we could use what Active Record does, which employs Arel under the hood, but there's a slight catch: Arel is considered a private API. In this context, 'private' means it’s regarded as an implementation detail that may change without notice. Take the following example in Rails 6.0: if we pass multiple conditions, we will receive a warning indicating that the way this method works today will change in Rails 6.1. It also provides hints about changing our code to maintain expected functionality after upgrading.
00:04:14.310
We should not expect the same warning from Arel code; it's not likely to break since it is tested with the rest of Rails. However, the Arel code we write comes with a greater responsibility for testing. This might pose an unacceptable risk for your team, but my team weighed the trade-offs and found that with a little test coverage, we were shielded from unexpected changes. We’ve successfully carried our Arel code from Rails 4.2 to Rails 6.0.
00:05:14.250
Now, let's take a closer look at Arel by comparing it with some Active Record code. Here's an Active Record query, and here's the equivalent query written in Arel. Notice that, in the Active Record code, we called .to_sql, while in the Arel equivalent, we start by calling the .arel method on an Active Record model and storing that in a variable. We’ll use this variable in several places.
00:05:56.310
Next, we call the project method to set up our SELECT clause. I believe it’s named this way to avoid collisions with other methods also called select. To reference a column, we use square brackets on the table, just as we would with a hash. We utilize the shortcut Arel.star to obtain a properly quoted star and pass it to the table since we want it to match the Active Record version. If we only wanted a star, we could simply use Arel.star alone. In the WHERE method, we reference the 'created_at' column on the table and utilize the 'lt' predicate method for less-than comparisons with the date. Finally, we call .to_sql to output the SQL.
00:06:35.610
Let's compare the output from each. The primary distinction is the string we passed is neither quoted nor does it reference the table. While this may not be crucial here, it becomes important once we start composing queries together. We want Arel to handle all the naming and quoting for us.
00:06:56.940
What are these objects? The table is an Arel table, and the columns are instances of Arel attributes. The entire query is an instance of Arel’s SelectManager. It has an AST variable containing many nested nodes; AST stands for Abstract Syntax Tree, which is a computer science term for a data structure that encapsulates the meaning or intention of code. This allows Arel to convert our Ruby code into SQL appropriate for Postgres, MySQL, SQLite, or any other database. In other words, we ask the system to retrieve certain records from the database, and Arel determines the SQL needed to accomplish that.
00:07:43.480
When we call .to_sql on this object, Arel employs the visitor pattern to convert the AST into SQL. Essentially, a visitor object traverses each node in the tree and decides the required output. It collects these pieces of SQL and constructs the total query needed for the database. This is a simplification, but it provides a foundational understanding for building our query objects.
00:08:42.060
Next, let’s build a simple query object. Here’s our target SQL again, similar to how we would refactor Ruby by extracting methods. We want to take inner queries, wrap them in classes, and create an object to organize them all together. The first class will be called FirstDonationQuery. We begin with an execute method that does precisely what its name suggests: it executes the SQL against the database. We delegate to SQL in the query method. The real work happens there, similar to Active Record, where calling methods multiple times might lead to duplicates. Therefore, we memoize results to ensure we only make the necessary passes through these steps.
00:09:29.790
Donations is the Arel table from our Donation model, just like in our initial example. Next, we call `project` to set up our SELECT clause. It expects an array, and I prefer to define that in another method for clarity. Sometimes, we may need to construct the columns based on a condition. The really interesting part here is the `date_trunc` method; to use a Postgres function that Arel doesn’t define, we can build a named function node. We simply pass the function's name as a normal string, wrapping the arguments to the function in an array. We use Arel::Nodes.build_quoted to ensure our string is properly quoted in the SQL output.
00:10:32.970
A common pitfall is passing strings to Arel methods that expect other Arel nodes. Usually, you need to wrap it in an Arel SQL or an Arel::Nodes.build_quoted. This is also true if we want to select just the integer '1' with a given name—essentially, we need to cast '1' as a string and wrap it with Arel.sql. Lastly, other classes will refer to the table and the 'gift_date' column. If we need to change those names in the future, it shouldn’t break the queries down the line.
00:11:43.210
Here’s what our test for this class looks like. First, we want to execute the .to_sql method and confirm that it works, then test more complex constructs. Finally, we execute our query in Postgres; even if the test table is empty, Postgres will still parse the query and raise an error if there are syntax issues. This gives us confidence that if something changes in Arel, we'll be alerted before we push it to production.
00:12:07.330
Next, we'll create the LateralDonationQuery, which will repeat some components and be initialized with a couple of arguments, depending on the FirstDonationQuery. By injecting that, we can imagine that if we alter the initial query later, we could pass in something different. It’s important to note in this class that we modify naming based on the position variable and build a month_offset that uses the gift date from the first query, adding an interval of X months to it.
00:12:40.50
To construct the interval, we employ a unary operation that simply concatenates the string 'interval' with the month string we specify. This is how the test for this class looks. Although we can’t execute this class as is, that portion will be addressed by the next query. Now, we’ll build our target SQL with the TimelineQuery, referencing the two queries we just constructed and dynamically building a query with as many lateral joins as needed. The first point to note here is that we’re using a SelectManager instead of a table from an Active Record object for various reasons.
00:13:56.040
A SelectManager possesses more capabilities, such as being able to build a CTE or set a named window to be called elsewhere. The next noteworthy aspect is that we reference the query from FirstGiftQuery as the from source; Arel is smart enough to interpret this as a subquery. We then call `as` on it to assign it a name and apply the name method of the FirstGiftQuery’s table. This allows us to update the name in the other class without causing issues here.
00:14:37.480
Next, we build a complex join by invoking `join` on the manager for each month we want. The lateral join segment takes our LateralDonationQuery object and calls lateral on its query output. You'll see why it’s beneficial to have the lateral query and join segment methods separate in the upcoming example. This is what the tests for our TimelineQuery look like; we want to ensure that each lateral join builds correctly without failure, and that the entire query is executable. Let’s examine what it looks like to obtain the SQL output from our TimelineQuery.
00:15:30.260
Here’s what our query looks like when we execute it. Now that we have the TimelineQuery, I’ll duplicate that file, modify a few settings, and we arrive at the CohortTableQuery. Here’s how that query looks: we accumulate the projection with a sum for each month. Also, note that we set the WHERE, GROUP, and ORDER clauses in an order separate from our joins; this works seamlessly with Arel.
00:15:53.930
Once we’ve constructed smaller reusable blocks, we can utilize them in various combinations. You might have noticed several opportunities to DRY up our code since this is all Ruby; it’s conducive to refactoring. Now that we’ve created our SQL using query objects, how do we integrate this with Rails?
00:16:31.090
What does executing our query objects look like? This demonstrates how to execute our CohortTableQuery. The execute call returns a result object, functioning a bit like an array. We can loop through it using `each`, or convert it into an array. Here, we get an array of hashes with the column name and value for each row. How might we use this to fetch Active Record objects?
00:16:51.140
In this variation, I added a donors method that uses find_by_sql to load donor objects. For this to work, I’m selecting * (star) here and performing an INNER JOIN with the donor table. When we execute this in the console, we can see it returns our donor model objects as a result.
00:17:35.340
Furthermore, Active Record has also loaded the additional columns from our SQL as attributes on each object. This has been a brief overview of how you can manage your complex SQL strings and organize them with Arel and query objects. Usually, at this point, a speaker would direct you to more resources for further learning.
00:17:57.120
There are many blog posts available, but be aware that some are somewhat outdated. Google is your ally in this endeavor. Personally, I learned by examining GitHub pull requests and collaborating with other developers. The source code is relatively straightforward to understand; if you grasp the AST and visitor pattern concepts, you should navigate it successfully.
00:18:27.440
You'll find the Arel library within the Active Record gem. If you have any questions, please feel free to reach out to me on Twitter; I'm @ekhayes, and I'd love to assist if I can. Thank you for watching!