Skip to content
Tutorials Center
My learning

Topics

Lesson 115 of 134 ยท Web and Ecosystem

Database Access Ideas

Learn the ideas behind Ruby database access. Build a data mapper and a tiny ActiveRecord-style model over plain arrays, count queries, spot N+1 problems, and see the real sqlite3 and Rails code.

Almost every real application keeps its data in a database, and Ruby programmers rarely write SQL strings by hand all day long. Instead they use a layer that turns table rows into Ruby objects and Ruby method calls back into SQL. The most famous layer is ActiveRecord, the database library of Rails. Others include Sequel and ROM. They look different, but they all answer the same questions: how do rows become objects, how do queries get built, and how do you avoid making hundreds of tiny queries by accident?

This lesson teaches those ideas without needing a database server. Our runner has no network and no third-party gems, so the sqlite3 gem and ActiveRecord are shown read-only. In their place we build a data mapper and a miniature ActiveRecord by hand, over plain arrays. When you later open the real library, nothing in it will surprise you. If you need SQL itself first, the SQL track covers SELECT, WHERE, INSERT, joins and transactions.

Talking to a database from Ruby

Ruby does not ship with a database driver for SQLite, MySQL or PostgreSQL. You install one as a gem: sqlite3, mysql2 or pg. A driver gives you a connection object, lets you run SQL text, and returns rows as arrays or hashes. Here is the idea with the sqlite3 gem. This is a read-only example because the gem is not installed on this page:

Ruby
# read-only example: needs the sqlite3 gem
require "sqlite3"

db = SQLite3::Database.new(":memory:")
db.results_as_hash = true

db.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, year INTEGER)")
db.execute("INSERT INTO books (title, year) VALUES (?, ?)", ["Dune", 1965])

db.execute("SELECT * FROM books WHERE year < ?", [1990]).each do |row|
  puts "#{row['id']}: #{row['title']}"
end

Two things matter here. The ? placeholders keep user input separate from the SQL text, which is your protection against SQL injection. And what comes back is a hash of strings and numbers, not a Book object. What happens on this runner if you try?

Ruby Requiring the sqlite3 gem (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

The runner prints main.rb:1:in 'Kernel#require': cannot load such file -- sqlite3 (LoadError). On your computer the cure is gem install sqlite3 or a gem "sqlite3" line in the Gemfile (RubyGems and Bundler).

Never build SQL by gluing strings: "SELECT * FROM users WHERE name = '#{name}'" lets a visitor named ' OR '1'='1 read your whole table. Always pass values through placeholders (? or named binds) or through your library's query builder.

From rows to objects: the data mapper

A row is only a bundle of values. Your program wants objects that have meaning and behaviour. The pattern called data mapper keeps those two worlds apart: a plain Ruby object knows nothing about databases, and a separate mapper (or repository) knows how to load and save it.

We can build one without a database by treating an array of hashes as the table. That is exactly the shape a driver would give us. A Data class makes a good immutable entity.

Ruby A data mapper over an array of rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

Three Ruby ideas do the work. value === row[key] uses case equality, so a condition can be a value, a range (..1970) or a regular expression, just as in a case expression. The **row splat turns a hash into keyword arguments for the Data constructor. And Book is immutable: changing a book means asking the mapper to save a new version, which keeps bugs about "who changed this object?" away.

The ActiveRecord idea

ActiveRecord takes a different route, and its name is the pattern: one object is one row, and it knows how to save itself. A Book class is tied to a books table; Book.find(2) returns an instance; book.update(title: "New") writes to the database. The big advantage is how little code you write. The cost is that your domain objects know about the database, which can make them harder to test and grow into large classes.

Here is what it looks like with the real library. This is a read-only example (it needs the activerecord and sqlite3 gems):

Ruby
# read-only example: needs the activerecord and sqlite3 gems
require "active_record"

ActiveRecord::Base.establish_connection(adapter: "sqlite3", database: ":memory:")

ActiveRecord::Schema.define do
  create_table :authors do |t|
    t.string :name, null: false
  end
  create_table :books do |t|
    t.string :title, null: false
    t.integer :year
    t.references :author, foreign_key: true
  end
end

class Author < ActiveRecord::Base
  has_many :books
end

class Book < ActiveRecord::Base
  belongs_to :author
  validates :title, presence: true
  scope :classics, -> { where(year: ...1980) }
end

author = Author.create!(name: "Frank Herbert")
author.books.create!(title: "Dune", year: 1965)

Book.classics.order(:title).each { |book| puts book.title }
Book.where(year: 1965).first.author.name

Notice the vocabulary: has_many and belongs_to describe relations, validates declares rules, scope names a reusable query, and where/order chain. Every one of these is a class-level method that your class calls while it is being defined, which is the macro style you saw in define_method and dynamic methods and hooks.

Queries are built lazily

A subtle feature: Book.where(...) does not hit the database. It returns a relation, an object describing the query. Only when you read the result (loop over it, call first, to_a) does the SQL run. That lets you chain conditions in any order. We can build the same trick, a lazy chainable query plus a tiny model with attribute macros and validations, in one example.

Ruby A tiny ActiveRecord-style model

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

Look at the log. Building classics produced no query; the single SELECT appeared only when each ran. Relation includes Enumerable, so map, first, select and many more work for free once each is defined. Real ActiveRecord is a far bigger version of precisely this design.

The N+1 problem

The most common database performance bug in Ruby apps is the N+1 query. You load a list of N things (one query), then, inside a loop, touch a related thing for each (N more queries). With ten books that is eleven queries; with ten thousand, the page falls over. Because relations look like ordinary method calls (book.author.name), the problem is invisible in the code.

We can make it visible by counting queries in our stand-in database.

Ruby Counting queries: N+1 and its fix

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with gets), it gets the next line from this box.

The fix is called eager loading or preloading: ask for all the related rows at once (WHERE id IN (...)), store them in a hash keyed by id, and look them up from memory. In ActiveRecord that is one word: Book.includes(:author). The gem bullet (read-only, third party) warns you in development when it detects an N+1.

Things professionals watch for

  • Transactions. Several writes that must succeed or fail together belong in ActiveRecord::Base.transaction do ... end. If an exception is raised inside the block, everything rolls back. See transactions for the SQL side.
  • Validations are not constraints. A validates :email, uniqueness: true check can race with another request. Put a unique index in the database as well.
  • find vs. find_by. Book.find(99) raises ActiveRecord::RecordNotFound; Book.find_by(id: 99) returns nil. Pick on purpose, and handle nil (safe navigation).
  • Loading everything. Book.all.each loads every row into memory. Use find_each to process large tables in batches.
  • Callbacks. before_save and friends are convenient but hide behaviour. Keep them small, and prefer explicit method calls for anything important.
  • Missing indexes. A query that scans a million rows is slow in any language; read about indexes.

Choosing a style

Use ActiveRecord (or Sequel's model layer) when your application is mostly forms and tables, because the conventions save weeks. Reach for a data mapper (ROM, or your own repository classes) when the domain logic is rich and you want it free of persistence code, or when several data sources sit behind one interface. Use a bare driver for scripts, migrations and one-off reports. For connecting from other languages, compare Connecting to SQL from code.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why should you pass values through ? placeholders instead of interpolating them into SQL text?

    With placeholders the database receives the SQL and the values separately, so a malicious value can never change the meaning of the statement.
  2. 2. What is the main difference between the data mapper and ActiveRecord patterns?

    In ActiveRecord a model is a row plus persistence behaviour. In a data mapper the domain object is plain Ruby and a mapper or repository handles the database.
  3. 3. Why does Book.where(year: ..1970) not run a query right away?

    A relation just records conditions. The SQL runs when you iterate, call first, to_a and so on, which is what makes chaining possible.
  4. 4. You load 50 books and call book.author.name for each one, and see 51 queries. What is this called, and what is the cure?

    One query for the books plus one per author is the N+1 pattern. Preloading the authors in one extra query, as includes(:author) does, brings it down to two.
  5. 5. Which error does require 'sqlite3' raise when the gem is not installed?

    A missing file on the load path raises LoadError with the message "cannot load such file -- sqlite3".

Summary

  • Ruby needs a driver gem (sqlite3, pg, mysql2) to talk to a database; always pass values through placeholders.
  • A data mapper keeps plain objects (often Data classes) separate from the code that loads and saves them.
  • ActiveRecord makes each object a row that saves itself, and describes relations, validations and scopes with class-level macros.
  • Relations are lazy: they describe a query and only run it when you read the results.
  • N+1 queries come from touching related records inside a loop; counting queries reveals them, and eager loading fixes them.
  • Add database constraints and indexes as well as Ruby validations, and use transactions for related writes.

Next: Scripting and Command-Line Tools