Do you use Ruby (on and off Rails) and have objects holding data which you need to store and retrieve later? Then Active Record might be the perfect tool for you!

In this article I give you a short overview of Active Record and explain how to use it with a few examples which I found to be essential. I will also share some points I found to be quite difficult to get right.

What is Active Record?

Ruby’s Active Record is an Object-Relational-Mapping layer: ORM lets you store data in a relational database and interact with it as objects.

For example, instead of manually querying data using SQL statements and then converting it manually into an array, using Active Record’s User.all returns an array already filled with the wanted data.

With the help of ORM’s it’s also possible to store and load data with complex relationships as objects, resolving the relationships to connections between objects.

For a more in-depth overview of what an ORM is, I can recommend the video Introduction to Object-Relational Mapping.

If you are unfamiliar with relational databases, have a look at my previous Database Basics series.

Active Record and databases

Active Record (AR) handles the connection to the database. It’s a simple configuration:

require 'sqlite3'
require 'active_record'

# Set up a database that resides in RAM
ActiveRecord::Base.establish_connection(
    adapter: 'sqlite3',
    database: ':memory:'
)

We’re going to be using an in-memory SQLite3 database for the following examples. Please note that the content of your database will be lost after each script run. If you want the data to be persistent, you can replace the second to last line above with database: db/ar_basics.sqlite3.¹

Using minitest to check query results

I decided to write minitest snippets. minitest is a testing framework and allows us to easily declare our expectations of the the code written. This might be like shooting canons at sparrow but it is a nice tool to get familiar with easily.

Assuming you are familiar with RubyGems (a gem is packaged Ruby library) and already have gem installed (otherwise look here), run this terminal command in your tutorial directory to install the minitest gem:

gem install minitest

Now we extend the above Ruby script set-up by two more lines

require "minitest/autorun"
require 'minitest/hooks/default' # This is needed to have run `before(:all) do` only once per test suite, instead of once per `it` test which results in duplicate DB entries.

and save the file as helpers/activerecord_setup_helper.rb so we can simply reference it in our tests.

has_many and belongs_to

Going back to my very first example from Database Basics, Part 1, where we want to save data about Employees and their Departments.

Here is an example with inline explanations:

# 01_employee_department.rb
# The setup is done in a single file to provide a quick one-glance-overview

require_relative '../helpers/activerecord_setup_helper'

# Set up database tables and columns
ActiveRecord::Schema.define do
  create_table :departments do |t|
    t.string :department_name
    t.string :department_head
  end

  create_table :employees do |t|
    t.string :name
    t.references :department # This adds the Foreign Key department_id
  end
end

# Set up model classes
class Department < ActiveRecord::Base
  has_many :employees
end

class Employee < ActiveRecord::Base
  belongs_to :department
end

describe "An employee works in one department" do
  before do
    # Run this setup only once per suite since we `require 'minitest/hooks/default'` in the helper.
    # Without the require, this block would be run before each `it` block.
  
    # Create employees and department before test
    eric = Employee.create(name: "Eric")
    hospitation = Department.create(department_name: "Hospitation", department_head: "Olivia")
    eric.department = hospitation
    eric.save!
  end

  describe "when provided employee Eric who works in Hospitation" do
    it "must return employee Eric" do
      eric = Employee.where(name: 'Eric').first
      _(eric.name).must_equal "Eric"
    end

    it "must return department as Hospitation" do
      eric = Employee.where(name: 'Eric').first
      _(eric.department.department_name).must_equal "Hospitation"
    end
  end
end

That’s a lot to take in at first glance but we’re looking at it step by step. The setup is done in a single file to provide a quick one-glance-overview, there are three parts to it:

ActiveRecord::Schema.define do defines the co-called Active Record schema. The schema is essentially the description of your database structure. Two tables, employees and departments, are being created in the database. Each block defines that tables columns:
- The first one describes the columns department_name and department_head and both only accept strings as valid data.
- The second block describes name and city as string, and postal_code as integer. t.references :department adds the foreign key department_id to the table (see Foreign key as a refresher). This serves as a relational reference to the departments table.
We have two model class definitions which tells AR how to handle the database relations as Ruby object.
- The class Department uses the keyword has_many to indicate that it’s a to many relation, so each Department entity can have zero to many Employees.
- The class Employee on the other hand indicates with belongs_to that each Employee has exactly one Department it is related to.
The third part of the script is the minitest part where we get to play with the just set up AR models.
- The before block is run once before the following test suite to set up the database entries that will be tested:
  - First we define an Employee instance called Eric.
  - Then we create an instance of Department called Hospitation and the head of his department is called Olivia.
  - With eric.department = hospitation we set the relation between Employee Eric and Department Hospitation. Behind the scenes AR adds the id of the row Hospitation in Department table to Eric’s row in the department_id column).
  - And since we changed the object eric, we need to save! it to the database again.
- Then we describe the context that the tests will run (which for us right now is more to put in words what is going to be tested).
  - In the first test (marked with minitest’s keyword it) we then query the database by using the method where of AR model Employee to find the entry that has the name Eric. And since .where returns an array (even if there is only one result) we select the first result. We then tell the test that we expect the object’s name to be indeed Eric.
  - Likewise, in the seconds test, we again query the first Eric entry to check if the name of the department he’s working in is indeed Hospitation.

Only points 1 and 2 are needed to setup the AR magic. Note that the names of AR tables are always plural and lower case (employees) while the model names are always singular and capitalized (Employee). This is part of one of the guiding principles of Ruby on Rails, “convention over configuration.”

Now we can execute the script via ruby employee_department.rb. You should see something like this:

$ ruby 01_employee_department.rb
-- create_table(:departments)
   -> 0.0033s
-- create_table(:employees)
   -> 0.0005s
Run options: --seed 15458

# Running:

..

Finished in 0.021561s, 92.7601 runs/s, 92.7601 assertions/s.
2 runs, 2 assertions, 0 failures, 0 errors, 0 skips

We can see that both tables departments and employees are being created. The two . indicate that two tests have run and since there are no failures listed, it worked exactly as expected.

Foreign keys in the schema

Let’s change this example to have a bit more finesse and normalise it into the Third Normal Form!

# 02_employee_department_with_references_head.rb

require_relative '../helpers/activerecord_setup_helper'

ActiveRecord::Schema.define do
  create_table :employees do |t|
    t.string :name
    t.references :department
  end

  create_table :departments do |t|
    t.string :department_name
    t.references :department_head, foreign_key: { to_table: :employees }
  end
end

class Employee < ActiveRecord::Base
  belongs_to :department
end

class Department < ActiveRecord::Base
  has_many :employees
  belongs_to :department_head, class_name: 'Employee'
end

describe "Two employees work in one department, one is head of department" do
  before(:all) do
    @olivia = Employee.create(name: "Olivia")
    hospitation = Department.create(department_name: "Hospitation", department_head: @olivia)
    @olivia.update(department: hospitation)
    Employee.create(name: "Eric", department: hospitation)
  end

  describe "when provided employee Eric who works in Hospitation" do
    it "must return Eric's department head as Olivia" do
      eric = Employee.where(name: 'Eric').first
      _(eric.department.department_head.name).must_equal "Olivia"
      _(eric.department.department_head).must_equal @olivia
    end
  end
end

The main difference here is t.references :department_head, foreign_key: { to_table: :employees } in the schema. This adds a column called department_head to the table which references the employees table, alongside with belongs_to :department_head, class_name: 'Employee' in the Department model.

With these two changes we can then do the following: Department.create(department_name: "Hospitation", department_head: @olivia). In the previous example, we were only able to save Olivia’s name as the department head’s value. Now we can reference the entity that represents Olivia’s entry. And as you can see in _(eric.department.department_head).must_equal @olivia, the query returns an object with all the data of the original object @olivia.

Unary relationships

It took me some time to get unary relationships right in AR, so let’s have a look at that.

In this example we only have one model, Employee. And an employee can have a supervisor, which is, of course, also an employee. Therefore the model references itself.

# 03_unary.rb
require_relative '../helpers/activerecord_setup_helper'

ActiveRecord::Schema.define do
  create_table :employees true do |t|
    t.string :name
    t.references :supervisor
  end
end

class Employee < ActiveRecord::Base
  belongs_to :supervisor, class_name: 'Employee'
  # This allows an Employee to have another Employee as manager.

  has_many :supervisee, class_name: 'Employee', foreign_key: "supervisor_id"
  # This model has a foreign key to itself, which enables AR to compute
  # a supervisor's subordinates based on their respective `manager_id`.
end

describe "One employee is supervisor and has two supervisees" do
  before(:all) do
    olivia = Employee.create(name: "Olivia")
    Employee.create(name: "Eric", supervisor: olivia)

    # Another way to associate an employee with a supervisor:
    jordan = Employee.create(name: "Jordan")
    olivia.supervisee << jordan
  end

  describe "when provided employee Olivia who is supervisor" do
    it "must return Eric's supervisor as Olivia" do
      eric = Employee.where(name: 'Eric').first
      _(eric.supervisor.name).must_equal "Olivia"
    end

    it "must return Eric and Jordan as subordinates of Olivia" do
      olivia = Employee.where(name: 'Olivia').first
      _(olivia.supervisee.count).must_equal 2
      _(olivia.supervisee.pluck(:name)).must_equal ["Eric", "Jordan"]
    end
  end
end

The Employees table gets a column called supervisor_id via t.references :supervisor.
The definitions belongs_to :supervisor, class_name: 'Employee' in the Employee model states that an Employee has one supervisor which is of the same class.
An Employee can also have multiple Employee entities as supervisees: has_many :supervisee, class_name: 'Employee', foreign_key: "supervisor_id". The foreign key in this case is the one we defined using belongs_to.

This relation is also called a “Self Referential” or “Self Join” in Active Record.²

When we then test if Olivia has two supervisees in the second test, that is correct. olivia.supervisee.pluck(:name) returns an array with both employees’ names.³

has_many :through

The concept of an entity having other entities can sometimes be a bit confusing, especially when the entities are only “connected” through something else.

Like in the case of doctors and their patients: Although a doctor sees many patients, his only happens when the patient has an appointment with that doctor. So, both doctors and patients have many appointments, and only through those appointments a relation with one another is made:

# 04_has_many_through.rb

require_relative '../helpers/activerecord_setup_helper'

ActiveRecord::Schema.define do
  create_table "doctors" do |t|
    t.string "name"
  end

  create_table "patients" do |t|
    t.string "name"
  end

  create_table "appointments" do |t|
    t.integer "doctor_id"
    t.integer "patient_id"
    t.datetime "appointment_date"
  end
end

class Doctor < ActiveRecord::Base
  has_many :appointments
  has_many :patients, through: :appointments
end

class Patient < ActiveRecord::Base
  has_many :appointments
  has_many :doctors, through: :appointments
end

class Appointment < ActiveRecord::Base
  belongs_to :doctor
  belongs_to :patient
end

describe "Two doctors have three appointments with two patients" do
  before(:all) do
    @patient_john = Patient.create(name: "John Lawn")
    @patient_logan = Patient.create(name:"Logan Five")
    @doc_brown = Doctor.create(name:"Dr. Emmett Brown")
    @doc_yueh = Doctor.create(name:"Dr. Wellington Yueh")

    Appointment.create(doctor: @doc_brown, patient: @patient_john, appointment_date: "2020-09-17 09:00")
    Appointment.create(doctor: @doc_yueh, patient: @patient_logan, appointment_date: "2020-09-17 13:00")
    Appointment.create(doctor: @doc_yueh, patient: @patient_john, appointment_date: "2020-10-05 08:00")
  end

  describe "when querying for all appointments Dr. Yueh has on 2020-09-17" do
    it "must return a single appointment with Logan" do
      selected_date = Date.parse("2020-09-17")
      selected_date = selected_date.beginning_of_day..selected_date.end_of_day
      appointments = Appointment.where(doctor: @doc_yueh, appointment_date: selected_date)
      _(appointments.count).must_equal 1
      _(appointments.first.patient).must_equal @patient_logan
    end
  end
end

As you can see, the models Doctor and Patient both have many :appointments, while Appointment belongs to the doctor and patient entities. By extend, that is to say through: :appointments, a doctor has patients and a patient has doctors. The relation between the two is indirect.

In the Doctor model we are saying that the doctor has many appointments and at the same time has many patients but only because s/he has appointments. A doctor can’t have any patients without there being an appointment to bring the them together.
In the Appointment model we are saying that appointments only belong to a doctor and a patient. One appointment can only have one doctor and one patient at a time. One appointment can’t have many doctors or have many patients.

When querying the appointments Dr. Wellington Yueh has on the 17th of September, it will correctly return the one with Logan Five.

Conclusion

Active Record is a mighty tool for saving and querying related data entities. And there are many, many more ways to query than I haven’t touched upon. I suggest you have a look at the Ruby on Rails guides entries for Active Record basics and Active Record querying for further information about it.