The Rails Guide states: “Active Record insulates you from the need to use SQL in most cases.” Rails magic is awesome, but if you’re expanding queries beyond User.find(), understanding the SQL powering Active Record will hone your database chops to a razor-fine edge!
In this article, I’ll introduce a couple of ways you can quickly construct an environment for SQL experimentation, and a few commonly encountered SQL queries you should be able to understand.
If you have a local Rails app, start the Rails console from the terminal via rails c.
Inside the console, you can take a gander at the SQL statements that Active Record generates. For example, entering the #where method will output the query below.
User.where(name: “John”)
SELECT * FROM users WHERE name = ‘John’
You can go a layer below the Active Record method and run this query directly.
result = ActiveRecord::Base.connection.execute("SELECT * FROM users WHERE name = ‘John’")
The results of this query can be output in a simple loop. I prefer this method for displaying the query, since it can be read line-by-line, instead of #inspect, which jumbles everything together into one line.
result.each do |result|
puts result
end
Thanks to the console, it’s possible to see the SQL that Active Record generates, and if we are so inclined, run our own custom SQL queries against the data that already exists in the app we are building.
If you don’t have a Rails app handy, there are other ways to create a database for SQL experiments.
Unless you have a CSV file, or some other source with preexisting values for your database, I recommend the Try-SQL Editor by w3schools. Along with a simple interface for entering queries and displaying results, you’ll find several datasets, ranging from lists of addresses to customer orders. It’s easy to reference a database and start playing right away!
If you have a CSV file with database values, check out SQLFiddle.
If you look at the documentation for the Active Record query methods, you will see many methods that are analogous to their SQL counterparts. #where, #joins, #select and #group are the Active Record equivalents of SQL’s WHERE, INNER JOIN, SELECT and GROUP BY.
If you click through to the definition of a method, you will see examples of SQL formed when the method is used.
An exhaustive reference to the Rails query and finder methods with the generated SQL for each is found in the Rails Guide, however this will not help if you’re trying to understand basic SQL syntax.
A full introduction to SQL is beyond the scope of this article, but there are several queries you will encounter frequently. I’ve listed them below, describing the behavior of each.
SELECT * FROM posts ORDER BY created_at DESC
Here, ORDER BY sorts posts by their created_at date in descending (DESC) order. ASC would return posts in ascending order.
SELECT COUNT(*) FROM posts
COUNT(*) returns the total number of posts.
SELECT * FROM posts LIMIT 5 OFFSET 30
Instead of returning all posts, LIMIT specifies that only 5 posts should be returned. OFFSET moves the selection so that 5 posts are returned, beginning at the 31st post (posts 31-35 are returned, rather than 1-5).
For explanation of SQL syntax, I recommend looking up definitions on the w3schools site. There are resources for learning SQL on HackerRank, a great Udemy course, a Khan Academy course, and this freeCodeCamp course.