If you work with database systems, I bet you’ve written a SQL query to
retrieve rows in some specific order at least once in your career.
Moreover, you may even have applied an
ORDER BY with two or more
columns, so how would you go about testing that kind of query?
Let’s take a look at a common attempt in Ruby:
it "fetches appointments sorted by date_time asc, provider first name asc, and city asc" do freeze_time do location1 = create(:location, city: "Atlanta") location2 = create(:location, city: "Nevada") provider_z = create(:provider, first_name: "Zed") provider_a = create(:provider, first_name: "Albert") appointment2 = create(:appointment, date_time: 5.hours.from_now) appointment3a = create(:appointment, date_time: 2.days.from_now) appointment1 = create(:appointment, date_time: 1.hour.from_now) appointment3d = create( :appointment, date_time: 2.days.from_now + 1.hour, provider: provider_z, location: location2 ) appointment3c = create( :appointment, date_time: 2.days.from_now + 1.hour, provider: provider_z, location: location1 ) appointment3b = create( :appointment, date_time: 2.days.from_now + 1.hour, provider: provider_a, location: location1 ) expected_sort_order = [ appointment1, appointment2, appointment3a, appointment3b, appointment3c, appointment3d ] expect(AppointmentsQuery.call).to eq expected_sort_order end end
In this example, we are creating a few records at the top, and at the
expect the appointments to come out in the order specified
by the test description.
This test works, but it has some serious shortcomings – and that’s what we will discuss next.
The three traits of a good test
A good test may have further traits, but here we will focus on the three main ones:
Is that test correct? In my opinion, it is. After reading it for a
while you will probably notice that the appointments are shuffled to
avoid false positives. If we had created them in the same order we
expected them to come out, and if our
SELECT query had omitted the
ORDER BY clause, our test could still have passed! That’s because in
most situations they will come out in the same order they were
created. However, the sort order is unspecified
and may depend, among other factors, on the order in which the rows
were created on disk.
Also, we are freezing the time to avoid equal dates from differing by milliseconds, since we are working with relative dates. Otherwise, we’d introduce flakiness and random failures that would compromise the reliability of our test suite.
While I might trust this test to ensure that my query is working correctly, it is very difficult to understand, which lowers my confidence. It’s also very easy to break because of its complexity. Which leads us to Readability!
The appointment variables denote an explicit sense of ordering, which
may become clear after looking at the
If we pay even more attention, we will notice that
appointment3d are named that
way because they group four records under the same date umbrella
2.hours.from_now + 1.hour). Hence,
three different groups of dates, while
designate the expected order within the third group.
However, you may have noticed the existence of an implicit group of
rows in the third group of dates, since our
ORDER BY has three
columns. Should we name our variables
appointment3a1, etc, to account for the third column? Maybe not! I’m
sure that would get rather messy, so the tradeoff is not worth it.
Now, is that test readable? Maybe just a little. But it’s complicated, easy to misunderstand, and not visually appealing. What I mean is that the setup is lengthy and there’s a lot of noise with the variables and in the test setup, which gets in the way of understanding what is actually being tested. Thereby, readability is impaired.
“Maintainability” is where the code example certainly gets the lowest grade. Given the following test failure, would we actually know what is wrong with the code and how to fix the problem?
1) AppointmentsQuery fetches appointments sorted by date_time asc, provider first name asc, and city asc Failure/Error: expect(AppointmentsQuery.call).to eq expected_sort_order expected: [#<Appointment id: 81, date_time: "2022-03-25 20:16:50.000000000 +0000", provider_id: 70, location_id...intment id: 82, date_time: "2022-03-27 20:16:50.000000000 +0000", provider_id: 66, location_id: 67>] got: #<ActiveRecord::Relation [#<Appointment id: 84, date_time: "2022-03-27 20:16:50.000000000 +0000", pro...ntment id: 81, date_time: "2022-03-25 20:16:50.000000000 +0000", provider_id: 70, location_id: 70>]> (compared using ==) Diff: @@ -1,7 +1,31 @@ -[#<Appointment id: 81, date_time: "2022-03-25 20:16:50.000000000 +0000", provider_id: 70, location_id: 70>, - #<Appointment id: 79, date_time: "2022-03-26 00:16:50.000000000 +0000", provider_id: 68, location_id: 68>, - #<Appointment id: 80, date_time: "2022-03-27 19:16:50.000000000 +0000", provider_id: 69, location_id: 69>, - #<Appointment id: 84, date_time: "2022-03-27 20:16:50.000000000 +0000", provider_id: 67, location_id: 66>, - #<Appointment id: 83, date_time: "2022-03-27 20:16:50.000000000 +0000", provider_id: 66, location_id: 66>, - #<Appointment id: 82, date_time: "2022-03-27 20:16:50.000000000 +0000", provider_id: 66, location_id: 67>] +[#<Appointment:0x00007fe0adf708b0 + id: 84, + date_time: Sun, 27 Mar 2022 20:16:50.000000000 UTC +00:00, + provider_id: 67, + location_id: 66>, + #<Appointment:0x00007fe0adf70770 + id: 83, + date_time: Sun, 27 Mar 2022 20:16:50.000000000 UTC +00:00, + provider_id: 66, + location_id: 66>, + #<Appointment:0x00007fe0adf706a8
Output omitted for brevity…
We would probably have a hard time debugging that failure, and it would get even worse due to the relative dates and the foreign key mystery guests. If we were testing a complex query where the sorting is just a small part of the logic, good luck figuring that out!
And what if we wanted to introduce another appointment in our setup to
be the second expected record? We would have to rename all of the
appointment variables downstream:
appointment4a, and so on.
Finally, any changes to the code as it stands are likely to introduce hard-to-debug issues, or worse, have the test pass even when the underlying SQL is broken
So, is this test maintainable? I believe it is not.
Back to basics: input and output (I/O)
Let’s get down to the basics. What are we actually testing here? Not the appointment objects, that’s for sure. We are actually sending data to a store beyond the boundaries of our application and retrieving it back, which means we’re dealing with side effects in discrete steps. Since we are not coupled to the format of any particular objects, that makes our testing environment even more controllable for the table formats we’re aiming to achieve down below. And most importantly: it’s just input and output.
Let’s organize our shuffled input as an input table:
Given our input table, it’s pretty easy to sort our records so let’s recall our desired sort order:
- First, by
- Second, by
- Third, by
Given that information, here’s our sorted output table:
Couldn’t our tables have been written down with pen and paper?
Along with the description of our test, that’s pretty straightforward and much easier to undestand. How might we write our test as close as possible to that?
Refactoring our test
Since our goal is for our test to read exactly like a table of inputs and outputs, we will skip the intermediate steps that led us to the end result:
def create_appointments(rows) rows.each do |(date_time, first_name, city)| create( :appointment, date_time: date_time, location: create(:location, city: city), provider: create(:provider, first_name: first_name) ) end end def appointments_query_result AppointmentsQuery.call.map do |appointment| [ appointment.date_time.strftime("%Y-%m-%d %H:%M"), appointment.provider.first_name, appointment.location.city ] end end it "fetches appointments sorted by date_time asc, provider first name asc, and city asc" do create_appointments( [ ["2022-03-22 15:00", "Zyler", "Texas"], ["2022-03-24 10:00", "Zyler", "Texas"], ["2022-03-22 11:00", "Zyler", "Texas"], ["2022-03-24 11:00", "Zed", "Nevada"], ["2022-03-24 11:00", "Zed", "Atlanta"], ["2022-03-24 11:00", "Albert", "Atlanta"] ] ) expect(appointments_query_result).to eq( [ ["2022-03-22 11:00", "Zyler", "Texas"], ["2022-03-22 15:00", "Zyler", "Texas"], ["2022-03-24 10:00", "Zyler", "Texas"], ["2022-03-24 11:00", "Albert", "Atlanta"], ["2022-03-24 11:00", "Zed", "Atlanta"], ["2022-03-24 11:00", "Zed", "Nevada"] ] ) end
That’s much better looking than our first attempt! We are even aligning the matrix items so that they read like tables. I wouldn’t do that with variable assignments, but our intent here is that it reads like a table, literally.
Note that we don’t even need to specify the name of our columns because in this case, they are self-revealing; it’s very noticeable that the first column holds date times, that “Albert” is a name, and that “Atlanta” is a city. Otherwise, we could have resorted to vertically layed-out hashes or introduced a comment at the top of our table to serve as a header.
Finally, we no longer need to freeze the time because we are using absolute dates as pure data rather than relative dates.
Here are a few takeaways1 of our refactoring:
- There are no convoluted names or mystery guests;
- The helper functions remove a lot of the noise of repeated object construction;
- The tabular layouts make reading everything easier;
- Expecting raw data instead of variable names has three huge
- You can clearly see why the expected sort order is what it is;
- You can quickly identify missing test cases;
- You can understand the test failure messages without looking at a wall of object arrays.
Most of the takeaways also apply to the I/O table technique in general!
I really recommend this approach when you’re dealing with tabular data and side effects, but in no way should you limit yourself to that. Generally, I would recommend not being afraid to use raw data in your tests, as data duplication is usually not a big deal because clarity is more important than DRY when it comes to tests.
When I originally paired with a fellow developer on this code, Louis, this was how he summarized the benefits of transforming his code into this I/O Table format. ↩