This article is a response to the highly rated on the orange website article by Cal Paterson on why the “Active Record” pattern is “Troublesome”.

So you want to be confused by ActiveRecord

Skip to the next section if you’re already setup/don’t want a rails tutorial and want to get confused by ActiveRecord even faster. First thing you do is install rails. Then:

rails new cal

You want an Author? With a nationality, state and name???

rails g model Author name:string nationality:string state:string

What are you crazy??? You want a book? Related to the Author??? With an ISBN?????

rails g model Book name:string isbn:string author:references

Ok. Phew. Now add those to your database.

rails db:migrate

On your Author model add this line has_many :books. This may confuse, but indicates that an author may have many books associated with them.

Now, get ready for the confusion.

I am ready to be confused!

Ok, so open up the rails console, to let us generate confusion ASAP.

rails c

Now, start writing your query.

Author # this query starts on the "Author" object
  # Where the author has some qualities
  .where(nationality: "Italian", state: "deceased")
  # Whoa hold on!!!! WHERE? Now? This isn't SQL syntax!!!
  # Never fear, the ORM will confusingly handle this and 
  # the next clauses in any order, so pure, sweet And
  # Logically Ordered SQL syntax can be retained if you want
  .joins(:books) # We join on the books table
  .select(:isbn) # We select the ISBN column

This confusing code simply must, given the Troublesome Active Record Pattern, create horribly inefficient SQL.

Let’s see:

SELECT "isbn"
FROM "authors"
  INNER JOIN "books"
    ON "books"."author_id" = "authors"."id"
WHERE "authors"."nationality" = ? AND "authors"."state" = ?

[["nationality", "Italian"], ["state", "deceased"]]

What the hell? That looks pretty optimal but confusing. Surely there has been some error.

The above code looks something like a “querybuilder” proposed by cal as a solution to “Active Record”. But, the “Active Record” “querybuilder” not only builds the query but returns the resulting array, meaning one less programming step than a “querybuilder”. Confusingly, it seems like the “querybuilder” pattern proposed wouldn’t add anything to the code above.

But what about sql string literals?

Well, I mean, you can:

sql <<-EOF
SELECT "isbn"
FROM "authors"
  INNER JOIN "books"
    ON "books"."author_id" = "authors"."id"
WHERE "authors"."nationality" = "Italian" AND "authors"."state" = "deceased"
EOF

records = ActiveRecord::Base.connection.execute(sql)

But why? That is however, a little Troublesome.

We can get to the less confusing but more Troublesome “Active Record” pattern, if we want, too.

Here’s a Ruby one-liner for that:

Author
  .where(nationality: "Italian", state: "deceased")
  .map { |a| Book.where(author: a).select(:isbn) }

This generates the two troublesome queries… oh no! It doesn’t! the select means we only select the one column. I almost have to be obstinate to get to the core of the Troublesome “Active Record”.

Author
  .where(nationality: "Italian", state: "deceased")
  .map { |a| Book.where(author: a).map(&:isbn) }

Ok phew. This returns the two whole objects, with all columns, and iterates through them in the Troublesome manner. With effort we finally got there.

It may appear that I’d have to have actively and obnoxiously not read the “Active Record” documentation to arrive at this point of Troublesome “Active Record” patterns. Yeah.

Or maybe I just confused ActiveRecord with NoSQL.

Cheers, all in good fun.

On a More Serious Note

I started out in web development coming from a data analysis background. I immediately loathed ORMs, and wished I had access to SQL directly. However, I’ve come to appreciate ORMs.

OH: sql is the javascript of databases.

Silvia Botros, Twitter

SQL syntax is… not great. Lets all admit it!!!! The reason it has stuck around is kinda-sorta that everyone uses it. There’s nothing particularly optimal about SQL, and ORMs often provide some remedy for that.

In the above examples, ActiveRecord (and now I’m speaking specifically about the Rails implementation) allows for moving clauses around in a way that SQL isn’t very good at. Maybe I don’t want my joins before my where clauses in a way that changes the query plan or the words I need. ActiveRecord also is very good at helping return objects. In SQL I need to write functions if I want to munge data a certain way consistently, and then call those functions in queries. In rails I can do both those things on the model, which I think is a much clearer concept than the separation of functions, queries and tables.

And then there’s the counterpoint that ActiveRecord can enable some bad behaviour.

Yes. SQL can too. If you want to write a bad query in SQL you absolutely can do that. And yeah, it is up to the Query Planner in the DB to optimize what can be an awful loop, and if the DB programmers are clever enough it does. I haven’t read the changelogs of PostgreSQL for a while, but WITH clauses were an optimization fence for this for a while. I think those DB programmers are quite clever (I love you). No system is immune from a programmer’s ability to write suboptimal code for it.