Ruby ActiveRecord (without Rails) Tutorial

Submitted by nanodano on Fri, 07/12/2019 - 19:41

Introduction

The Ruby ActiveRecord gem provides easy-to-use abstractions for working with databases and allows you to easily swap out the database backend. For example to switch from SQLite3 to MySQL without changing code. It has built-in support for database abstractions to SQLite3, MySQL, and PostgreSQL. One of the primary aspects of ActiveRecord is that there is very little to no configuration needed. It follow convention over configuration.

ActiveRecord is commonly used with the Ruby-on-Rails framework but you can use it with Sinatra or without any web framework if desired.This tutorial will focus on using it independently, outside of any other framework.

See my SQLite3 Tutorial and Ruby SQLite3 Tutorial for more background on using SQLite3 directly. It is good to have a solid foundation with SQL before trying to work with ActiveRecord.

Versions used when writing these examples:

ActiveRecord 5.2.3
SQLite 3.26.0
Ruby 2.6.3p62
Fedora 30

Install ActiveRecord gem

This assumes you already have Ruby installed on your system. You can install the ActiveRecord gem by using the gem tool or using your system's package manager if a package is available. Here are a few examples:

# Install with gem
gem install activerecord

# In Ubuntu
apt install ruby-activerecord

# In Fedora
dnf install rubygem-activerecord
dnf install rubygem-activerecord-doc

Read the documentation

You can access the documentatin online or offline locally. Offline documentation is particularly helpful if you are on a plane or somewhere without an internet connection. It is good to be familiar with both.

Offline

To view the local documentation, run the gem server then use your browser to visit http://localhost:8808 and navigate to the ActiveRecord documentation.

gem server

You can also use ri to get information. For example:

ri ActiveRecord::Base
ri ActiveRecord::Result

You can also use interactive mode by simply running ri by itself

ri
# Then you can use tab completion
>> ActiveRecord::<tab><tab>

Online

If you run gem server to get the local documentation, the activerecord entry will have a link to the online documentation labeled [www].

Here are a few links to online documentation for convenience:

Basic usage

Let's look at some of the common tasks with ActiveRecord like:

Establishing a database connection
Creating a model
Performing CRUD operations

Establish a database connection

Before using any models, you will need to establish a database connection.

SQLite3 example:

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter: 'sqlite3',
  database: 'test.db'
)

MySQL example:

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter: 'mysql2',
  host: 'localhost',
  username: 'my_user',
  password: 'p@ssw0rd',
  database: 'my_db'
)

PostgreSQL example:

require 'active_record'

ActiveRecord::Base.establish_connection(
  adapter: 'postgresql',
  host: 'localhost',
  username: 'my_user',
  password: 'p@ssw0rd',
  database: 'my_db'
)

You can also load the configuration from a file, like a YAML file. This example shows how to load the configuration from a local YAML file.

require 'yaml'
require 'active_record'

db_config_file = File.open('database.yaml')
db_config = YAML::load(db_config_file)
puts db_config

ActiveRecord::Base.establish_connection(db_config)

And the contents of the .yaml file would look like this:

# database.yaml
adapter: 'sqlite3'
database: 'test.db'

Create a model

To create a model, you simply need to create a class and inherit ApplicationRecord class. Table names are assumed based on the class name of the model being created. For example, a model named User would expect to have a table named users. A model named PremiumUser would expect a table named premium_users. It converts everything to lowercase, and adds an underscore between capitals.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

# Expects a table named `users`  by convention
# Example SQLite3 statement to create table:
# CREATE TABLE IF NOT EXISTS users (name TEXT, age INT);
class User < ActiveRecord::Base
end

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

# Can override table name and primary key
class User < ActiveRecord::Base
  self.table_name = 'user'
  self.primary_key = 'user_id'
end

Technically, that's all you need to do. By default, it will map the existing database fields to objects in the model. You do not have to define each field in the source code. It will automatically create fields based off of the columns in the database.

List table columns

Once you have a model, you can access the columns as an object attached to the model. Here is an example that will print out the columns of a table:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base
end

# Print information about the database table
User.columns.each { |column|
  puts column.name
  puts column.type
}

Create records

There are a few ways to create a record:

Create a new object, then call save() explicitly
Use a block to populate the object, then call save()
Call create() which will create and save in a single step.

Here are a few examples of how that looks:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base
end

# Create a new user object then save it to store in database
new_user = User.new(name: 'Dano', age: 30)
new_user.save

User.new { |u|
    u.name = 'NanoDano'
    u.age = 60
}.save

# Create and save in one step with `.create()`
User.create(name: 'John Leon', age: 90)

Find records

There are many methods you can use to query for records. Some of them include:

first()
last()
second(), third(), fourth(), fifth()
all()
where()
find_by()
find_by_sql()
find_by_*()

Here is an example of these:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base
end

# Get first user
first_user = User.first
puts first_user.name

# Get last user
last_user = User.last
puts last_user.n

# Also available: User.second, User.third, User.fourth, User.fifth

# Find first user that matches query
dano = User.find_by(:name => 'Dano')
puts dano.name

# Find all users that match query, then pull the first from the list
adults = User.where('age > ?', 18)
puts "Adults: #{adults.length}"

# Get all users
puts "Total users: #{User.all.length}"

# Get all users and sort
sorted_users = User.all.order(age: :desc)
sorted_users.each do |u|
  puts "#{u.name}: #{u.age}"
end

# You can combine multiple fields dynamically in find_by_* queries
dano = User.find_by_name_and_age('Dano', 30)
puts "#{dano.name} is #{dano.age}"

# Query using custom SQL
users = User.find_by_sql('select * from users')
users.each do |u|
  puts "#{u.name} is #{u.age} years old"
end

You can learn more about querying and available methods at https://guides.rubyonrails.org/active_record_querying.html

Update records

To update a record you also have a few options. One option is to obtain the record, then modify it, and call save() explicitly. Another option is to call update() which will make the change and save in a single action. Here are a couple examples:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base
end

# Update by modifying a user object and then calling save
u = User.first
u.name = 'new_name'
u.save

# Update and save in one step
User.first.update(name: 'new_name_again')

Delete records

To delete a record, you can call delete() on an individual record or call delete_all() to clear every record. Here are some examples:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base
end

# Inefficient, but functional way to delete all records:
User.all.each { |u| u.delete }
# Equivalent
User.all.each(&:delete)

# More efficient option:
User.delete_all

Callback functions

There are a number of methods you can override in a model class that will get triggered automatically when certain actions are taken, like creating a record, updating a record, deleting a record, and querying for a record.

Learn more about callbacks at https://guides.rubyonrails.org/active_record_callbacks.html#available-callbacks

These apply to create actions:

after_initialize() - Called before before_create()
before_create()
around_create() - around runs before and after
after_create()

These apply to both create and update actions:

before_validation()
after_validation()
before_update()
around_update()
after_update()
before_save()
around_save()
after_save()

These apply to delete action:

before_destroy()
around_destroy()
after_destroy()

These apply to create, update, and delete actions:

after_commit()
after_rollback()

These apply to find actions:

after_initialize() - called when record created or pulled from database
after_find() - called after pulling a record from database

Here is an example of setting up an before_create() and after_create() callback by using blocks:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base

    before_create do |u|
    puts "About to create user: #{u.name}"
  end

  after_create do |u|
    puts "New user object created: #{u.name}"
  end

end

User.create(name: 'John Leon', age: 60)

Another way to do callbacks is like this where you specify the callback functions to be called using symbols. Instead of operating on the object provided in the block, you operate on self.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base

  before_create :before_create_callback
  after_create :after_create_callback

  def before_create_callback
    puts "About to create user: #{self.name}"
  end

  def after_create_callback
    puts "New user object created: #{self.name}"
  end

end

User.create(name: 'John Leon', age: 60)

One of the more confusing callbacks is the "around" callbacks like around_create(). These callbacks are called before and after. You can either pass two separate functions to handle the before and after, or pass a single function with a yield call in the middle to wait for the save to happen. The around_create function happens after before_create but before after_create. The around callbacks can be useful for doing performance benchmarking or profiling database actions.

I have noticed however, if you define BOTH before_create and around_create and you define around_create before you define before_create, then the first half of around_create runs even before before_create. I'm not sure if it is good practice to use them together since they serve a similar purpose.

Here is an example of using a single function:

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base

  around_create :around_create_callback

  def around_create_callback
    puts 'A user is about to be created'
    yield # Do not continue until save has occured
    puts 'A user has been created.'
  end

end

User.create(name: 'John Leon', age: 60)

Here is an example of passing it two separate functions

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class User < ActiveRecord::Base

  around_create :around_create_begin, :around_create_end

  def around_create_begin
    puts '(around_create) A user is about to be created'
    yield
  end

  def around_create_end
    puts '(around_create) A user has been created.'
  end

end

User.create(name: 'John Leon', age: 60)

Field validation

You can add validations to a model to ensure that any object saved meets certain standards. There are a variety of built-in validations you can use. Some examples of validations you can do are:

Ensure a field is empty or not empty
Ensure a field contains a unique value
Ensure the length of a field
Ensure a field is a numeric value
Ensure a field matches a regular expression
Custom validation functions

Once validations are in place, you can call .valid? and .invalid? on the model to perform the validations and generate the error messages, which are accessible from .errors.messages on the model. Calling .save will also perform the validations and generate messages. .save will return false if the save did not work.

You can learn more about validations at https://guides.rubyonrails.org/active_record_validations.html.

Here is an example of a model that has a handful of validations and demonstrates how to check for errors.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

# Example SQLite3 statement to create table:

# CREATE TABLE users (
#   name TEXT, age INT, admin INT, email TEXT,
#   credit_card TEXT, tshirt_size TEXT
# );
class User < ActiveRecord::Base

  # Ensure a field is empty
  validates_absence_of :admin

  # Ensure name and age fields are present
  validates_presence_of :name, :age
  # Use a regular expression to limit field values
  validates_format_of :name, { with: /\A[a-zA-Z]+\z/,  message: "Only letters allowed" }
  # Ensure minimum and maximum length
  validates_length_of :name, { minimum: 2, maximum: 64 }
  # Another way to specify length
  validates_length_of :name, { in: 2..64 }

  # Ensure a value is exactly a certain length
  validates_length_of :credit_card, { is: 16 }

  # Ensure field is unique
  validates_uniqueness_of :email

  # Ensure value matches a specific set
  validates_inclusion_of :tshirt_size, { in: %w(S M L XL XXL), message: "Invalid t-shirt size: %{value}" }

  # Ensure a number is provided
  validates_numericality_of :age, { only_integer: true}
end

u = User.new
u.email = 'test'
u.admin = 1
u.name = "Dano123"
u.age = 50.5
u.tshirt_size = 'XS'

# Alternatively, call `u.valid?` or `u.invalid?` to generate errors
unless u.save
  u.errors.messages.each do |field, messages|
    puts "#{field}: #{messages}"
  end
end

Transactions

Using a transaction block allows you to perform multiple database operations and ensure they all go through or none of them do. For example, if you want to decrement the item count of an inventory table and deduct money from someone's account then you don't want only one of those to go through while the other one errors and leaves the database in a weird state that it should not be in. You can do both of these steps in a transaction and then the databse will only update if all of the operations are successful. If an exception is raised it will rollback.

You can create a transaction block like this:

# Perform a transaction and either commit or rollback depending on
# whether an exception was raised.
ActiveRecord::Base.transaction do
  # Do stuff here like updating, creating, or deleting records.

  # If there are any exceptions raised, the database commit will not happen,
  # and it will perform a rollback restoring the database.

  # Nested transactions are also possible.

  # However, the actual Ruby object will be in a dirty state and not restored
  # to the original state before the attempted transaction was started
end

You can catch exceptions inside the transaction block and handle them or ignore them yourself if you want the transaction to continue. You can rethrow the exception if you want to rollback and exit the transaction block.

You can also call .transaction() on the model object like User.transaction or a model instance like some_user.transaction. You are not restricted to operating on only that model though in the block.

Relationships

Associating models to each other is a critical aspect of ActiveRecord. These include relationships like one-to-one, one-to-many, and many-to-many.

The available relationships between models are:

belongs_to
has_one
has_many
has_many :through
has_one :through
has_and_belongs_to_many

There is a log 'magic' that happens when it comes to naming of tables. You can override table names and foreign key column names, but I recommend you try to follow the conventions to avoid configuration. For example, if a Profile belongs to a User, it will assume there is a users table, a profiles table, and the profiles table will have a user_id column.

Join tables for many-to-many relationships user the name of both models in alphabetical order. For example, if a User has a many-to-many relationship with Clan then the join table is expected to be named clans_users and contain a user_id and a clan_id columns that reference tables named clans and users.

In this example, pay special attention the plurality or singularity of the words used in the model names, relationship names, and the database table names. When using a has_and_belongs_to_many relationship, the join table uses plurals of both names in alphabetical order.

In this example, I create one of each of the basic relationships.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

# SQLite3 statements to create needed tables for example:

=begin
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);
CREATE TABLE profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INT,
    bio TEXT
);
CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INT,
    content TEXT
);
CREATE TABLE clans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);
CREATE TABLE clans_users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INT,
    clan_id INT
);
=end

class User < ActiveRecord::Base
  has_one :profile
  has_many :posts
  has_and_belongs_to_many :clans
end

class Profile < ActiveRecord::Base
  belongs_to :user
end

class Post < ActiveRecord::Base
  belongs_to :user
end

class Clan < ActiveRecord::Base
  has_and_belongs_to_many :users
end


dano = User.create(name: 'NanoDano')

# A few ways to create the profile for the user
# Since this is a one-to-one relationship,
# only one will actually be tied to user,
# and the others will end up with user_id = null,
# but the different options are provided for reference.
Profile.create(bio: 'A l3t3 haker', user: dano)
dano.profile = Profile.create(bio: 'A l33t haker')
dano.create_profile(bio: 'Leet!')

# A few ways to add a post to user (one-to-many relationship)
dano.posts.create(content: 'Sample post')
Post.create(content: 'Another post', user: dano)
dano.posts.append(Post.create(content: 'A third post'))

# Create the clans & relationships (many-to-many relationship)
dano.clans.create(name: 'Belgian ROFLs')
Clan.create(name: 'Hax0rs', users: [dano])
dano.clans.append(Clan.create(name: 'Lone Rangers'))

# Pulling the related objects
u = User.find_by_name('NanoDano')
puts u.inspect
puts u.profile.inspect
puts u.post_ids.inspect
puts u.posts.inspect
puts u.clan_ids.inspect
puts u.clans.inspect

You can read more about associates at https://guides.rubyonrails.org/association_basics.html.

Migrations

To avoid having to write SQL statements to create, modify, and destroy database schemas, ActiveRecord provides a mechanism for performing migrations. This allows you to write Ruby code to specify what the database structure should look like without writing raw SQL.

There are a few benefits from this. For example, you can use the same migration to create the database schema for SQLite, MySQL, and PostgreSQL even though the actual SQL statements may vary from database to database. It also allows you to easily perform updates, tear down, and rebuild a database just by running the Ruby migrations scripts which can in turn be set up in a Rakefile for convenience.

I will demonstrate a couple very simple examples that show how to create and drop a database. There are many useful functions available that can help modify indicies, modify foreign keys, and modify table columns, but I will focus on the most basic example to give a high level overview.

Available methods

When defining migration classes, these are some of the available methods that you can use to perform database operations. You can read more about available methods at https://api.rubyonrails.org/classes/ActiveRecord/Migration.html

create_table()
change_table()
rename_table()
drop_table()
create_join_table()
drop_join_table()
add_column()
change_column()
change_column_default()
change_column_null() (allow/disallow null)
rename_column()
remove_column()
remove_columns()
add_timestamps() (created_at and updated_at fields)
remove_timestamps()
add_foreign_key()
remove_foreign_key()
add_index()
rename_index()
remove_index()
add_reference()
remove_reference()

Difference between change() and up()/down()

If you define a migrations using the change method, then it will automatically determine what needs to be done for the up and down migrations to perform or undo the actions specified.

If you want to specify an action that only works in one direction, or have more control, you can explicitly define the up and down methods instead. I would default to using change unless you have some special needs.

Create and drop tables

This example shows how to make a simple migration that will create a table named users with a few fields: name, age, created_at, and updated_at.

Call migrate on the migration class to update the database. You must provide an :up or :down direction to specify whether you want it to perform the changes or undo the changes. It will automatically determine what statements need to be executed to do each action.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class CreateUserTable < ActiveRecord::Migration[5.2]
  def change
    create_table :users do |table|
      table.string :name
      table.integer :age
      table.timestamps
    end
  end
end

# Create the table
CreateUserTable.migrate(:up)

# Drop the table
CreateUserTable.migrate(:down)

If you want to create or drop the table only when it is present, you can wrap the operations with a conditional statement like the next example shows. This is a case where you would want to explicitly specify the up and down actions instead of relying on change to automatically provide both statements.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

class CreateUserTable < ActiveRecord::Migration[5.2]
  def up
    unless ActiveRecord::Base.connection.table_exists?(:users)
      create_table :users do |table|
        table.string :name
        table.integer :age
        table.timestamps
      end
    end
  end

  def down
    if ActiveRecord::Base.connection.table_exists?(:users)
      drop_table :users
    end
  end
end

# Create the table
CreateUserTable.migrate(:up)

# Drop the table
CreateUserTable.migrate(:down)

Logging

You can utilize the standard library logger to output ActiveRecord log information to STDOUT or to a file. Provide STDOUT or a filename like log.txt. This will log all the queries made with ANSI colors.

require 'active_record'

ActiveRecord::Base.establish_connection(adapter: 'sqlite3', database: 'test.db')

# Set up STDOUT or provide a filename like 'log.txt'
ActiveRecord::Base.logger = ActiveSupport::Logger.new(STDOUT)

# To disable the ANSI color output:
ActiveSupport::LogSubscriber.colorize_logging = false

class User < ActiveRecord::Base
end

User.create(name: 'NanoDano')

Using in IRB & Pry

When using Ruby-on-Rails you can run rails console to get in to a shell that lets you start querying your ActiveRecord models. If you aren't using Rails you will have to require the Ruby modules where your models are defined.

You can use the -r argument to require a module on startup. This saves you a little bit of typing since you don't have to type require 'my_model' inside the interpreter. Be sure to import whatever modules contain your models and database connection information.

# Launch IRB or Pry with your modules already imported.
irb -r ./my_models -r ./my_other_module

You could manually import the modules by entering for example: require './my_user_model' but scripting it out with the require statements in the launch command saves you some time. Once in, you can just use your models as you would expect like:

irb(main):001:0> User.first

Using with Rake

You can create a Rakefile to make common tasks easy to run. To learn more about using rake, check out my Ruby Rake Tutorial.

This Rakefile example will demonstrate how to create a task to run an IRB session with your models loaded and how to set up some basic migration tasks. This example assumes you have the establish_connection call in a file named my_db_connection.rb, your model classes in my_models.rb, and your migrations in a file named my_migrations.rb in the same directory as the Rakefile.

This is a simple example to get you started.

# Rakefile
require 'active_record'
require './my_db_connection.rb'

# Run with: rake shell
task :shell do
  require './my_models.rb'
  require 'irb'
  require 'irb/completion'

  ARGV.clear
  IRB.start
end

# Assuming you have a migration named `CreateUserTable` like
# the example from the migrations section above
namespace :migrate do
  task :up do
    require './my_migrations.rb'
    CreateUserTable.migrate(:up)
  end

  task :down do
    require './my_migrations.rb'
    CreateUserTable.migrate(:down)
  end
end

# Or try to apply a directory of migrations with migrator
# migrator = ActiveRecord::Migrator.open(Dir["migrations"])
# puts "Unapplied migrations: #{migrator.pending_migrations}"
# migrator.migrate

Then run the tasks with the following commands:

rake migrate:up
rake shell
rake migrate:down

Conclusion

After reading this tutorial you should have a solid understanding of ActiveRecord fundamentals. You should be able to install ActiveRecord and find the documentation. You should be able to define models and relationships, perform queries and create records. You should also understand callbacks, transactions, logging, and migrations as well as how to use ActiveRecord in an IRB or Pry session as well as utilize it with rake.