skip to content

Database Adapters

Rails talks to SQLite, PostgreSQL and MySQL through adapters set up in database.yml, with replicas, shards and transactions on top. Interviewers probe engine trade-offs and connection setup.

part ofRuby on Railsread it →
on this pageshow

explore

questions

29

In a Rails 8.1 app, how does config/database.yml declare each environment's connection, and what do its ERB tags and &default anchor do?

level: juniorimportance: must knowfreq 58%

answer

  1. one top-level block per RAILS_ENV
  2. ERB renders first, YAML parses second
  3. &default anchor, <<: *default merge
  4. max_connections, the 8.1 name for pool
  5. production: primary, cache, queue, cable

basics

~20 s

config/database.yml holds one top-level block per environment, picked by RAILS_ENV. Rails renders the file as ERB before parsing the YAML, so ENV lookups fill in values, and a &default anchor merged with <<: *default shares the common keys.

solid answer

~40 s

Each top-level key is an environment (`development`, `test`, `production`), and Active Record connects with the block that matches `RAILS_ENV`. A block carries `adapter`, `database`, credentials and pool settings; in Rails 8.1 the pool ceiling is `max_connections:`, which replaced the older `pool:` name. Rails renders the whole file as ERB first, so `<%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>` or `<%= ENV["PAYROLL_DATABASE_PASSWORD"] %>` is plain text by the time YAML parses it. The `&default` anchor plus `<<: *default` copies shared keys into each environment, and any key written beside the merge overrides the copied one. By default a new 8.1 app's production block is three-tier: `primary`, `cache`, `queue` and `cable` entries, each its own database with its own `migrations_paths`, so Solid Cache, Solid Queue and Solid Cable keep their tables out of the app's database.

code

yaml · 27 lines
yaml
default: &default
  adapter: postgresql
  encoding: unicode
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  <<: *default
  database: payroll_development

test:
  <<: *default
  database: payroll_test

production:
  primary: &primary_production
    <<: *default
    database: payroll_production
    username: payroll
    password: <%= ENV["PAYROLL_DATABASE_PASSWORD"] %>
  cache:
    <<: *primary_production
    database: payroll_production_cache
    migrations_paths: db/cache_migrate
  queue:
    <<: *primary_production
    database: payroll_production_queue
    migrations_paths: db/queue_migrate

go deeper

for a junior

Know that each environment has its own block, that RAILS_ENV picks it, and that ERB tags let you read passwords and thread counts from ENV.

for a middle

Explain the load order (ERB, then YAML, then shared:), how anchors and merge keys resolve, and why 8.1 says max_connections instead of pool.

for a senior

Read a three-tier production block confidently: which entry is the default, why cache, queue and cable are separate databases, and how a bad ERB value fails at boot.

for a principal

Weigh how much connection config lives in database.yml versus environment variables, so each deploy target overrides only what differs without forking the file.

## What the file is for `config/database.yml` tells **Active Record** how to connect. Rails loads it at boot, picks the block whose top-level key matches the current environment (`RAILS_ENV`, which defaults to `development`), and opens connection pools from it. Other environments' blocks sit in the same file and are simply not used by that process for its own connections, which is why one file can describe a laptop's SQLite database and a production PostgreSQL cluster side by side. A block is a mapping of connection keys. The ones you meet in every app: - `adapter`: which driver Active Record loads (`postgresql`, `mysql2`, `trilogy`, `sqlite3`). - `database`: the database name, or for SQLite a file path such as `storage/development.sqlite3`. - `username`, `password`, `host`, `port`: where and as whom to connect. - `encoding`: the PostgreSQL template writes `unicode`. - `max_connections`: the most connections one process's pool may open. Rails 8.1 renamed it from `pool`, and the generated file reads it from `RAILS_MAX_THREADS` with a fallback of 5. - `migrations_paths`: where migrations for a non-primary database live. ## How Rails reads it: ERB first, then YAML The load order matters, because it explains most of the surprises: 1. Rails finds `config/database.yml` through `paths["config/database"]`. 2. `ActiveSupport::ConfigurationFile` renders the file as **ERB** when it contains `<%`, so every `<%= ... %>` tag is replaced by its output as raw text. 3. The rendered text is parsed as **YAML**, with anchors and aliases allowed. 4. A top-level `shared:` key, if you wrote one, is removed and reverse-merged into every environment, so the environment's own keys win. 5. Active Record turns each entry into a database configuration object and, for the current environment, merges in URL environment variables such as `DATABASE_URL`. Because ERB output is pasted in as text, a value that contains YAML-significant characters can break the parse or change the value's type. Rails then fails at boot with "YAML syntax error occurred while parsing" and the path of the file. ## Anchors and merge keys YAML anchors keep the file short. `default: &default` names a mapping, and `<<: *default` inside another mapping copies its keys in. Keys written explicitly next to the merge **override** the copied ones, so this works as expected: ```yaml default: &default adapter: postgresql max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> production: <<: *default max_connections: 10 # wins over the anchored value ``` The generated PostgreSQL file uses a second anchor, `&primary_production`, so the `cache`, `queue` and `cable` entries inherit the primary's username and password and change only `database` and `migrations_paths`. ## Two-tier and three-tier blocks | Shape | What it looks like | What Rails builds | |---|---|---| | **Two-tier** | `development:` holding `adapter`, `database`, ... | one configuration named `primary` | | **Three-tier** | `production:` holding `primary:`, `cache:`, `queue:`, `cable:` | one named configuration per entry | In a three-tier block, the entry called `primary` is the default connection; without one, Rails uses the first entry. A new Rails 8.1 app writes a three-tier production block (unless you pass `--skip-solid`): **Solid Cache**, **Solid Queue** and **Solid Cable** store their data in database tables, and each gets its own database (`payroll_production_cache`, `payroll_production_queue`, `payroll_production_cable` for PostgreSQL, or files under `storage/` for SQLite) and its own `migrations_paths` (`db/cache_migrate`, `db/queue_migrate`, `db/cable_migrate`). Each library's own config file points at its entry by name. Development and test stay two-tier in the generated file. ## Mistakes interviewers listen for - Treating the file as static YAML and loading secrets in an initializer instead of with an ERB `ENV` lookup. - Writing `pool:` in a new 8.1 file. Rails 8.1 still reads `pool:` as a fallback, but setting both `pool` and `max_connections` to different values raises an "Ambiguous configuration" error at boot. - Expecting the anchored `default` to beat a key written in the environment block. It is the other way round. - Committing a production password as a literal instead of reading it from the environment or credentials. - Assuming a fresh app keeps cache and jobs in Redis: in 8.1 they are the `cache` and `queue` database entries. ## Checking what Rails actually loaded Reasoning about anchors, ERB and environment variables in your head is error-prone, so ask the running app. In `bin/rails console`, `ActiveRecord::Base.configurations.configs_for(env_name: "production")` lists every entry Rails built for that environment, each with its `name` and `configuration_hash`, and `ActiveRecord::Base.connection_db_config` shows the one the current model connection uses. Comparing that hash with the file is the quickest way to spot an override you did not expect, such as a merged `DATABASE_URL` or a `shared:` key leaking in.

  • What does a top-level shared: key in database.yml do?
    Rails removes `shared:` after parsing and reverse-merges it into every environment, so keys an environment sets itself win. In three-tier blocks, if `shared:` is keyed by entry name (`shared: { primary: {...} }`) each entry gets its matching part; otherwise every entry gets the whole hash. It removes repetition without anchors.
  • Why does a new Rails 8.1 app's production block list cache, queue and cable entries?
    Rails 8 ships Solid Cache, Solid Queue and Solid Cable, which keep cache entries, jobs and cable messages in database tables. The generator gives each its own database entry with its own `migrations_paths` (`db/cache_migrate`, `db/queue_migrate`, `db/cable_migrate`), so that load and schema stay apart from `primary`. Each library's config names its entry, and `--skip-solid` writes a single production block instead.

  • database.yml is plain YAML, so ENV values must be injected from an initializer.
  • A new Rails 8.1 database.yml sets the pool ceiling with the pool: key.
  • Keys pulled in by <<: *default override keys written in the environment block.
  • A fresh Rails 8.1 app has one production database and keeps cache and jobs in Redis.
  • ERB tags in database.yml are evaluated per value after YAML parsing, so quoting never matters.

Assumes Rails 8.1: the generated file writes max_connections: (pool: before 8.1) and a three-tier production block for the Solid libraries (since 8.0).

open as a page

In a Rails 8.1 app on MySQL, why does database.yml set encoding: utf8mb4, and what does adding collation: change?

level: juniorimportance: must knowfreq 45%

basics

~20 s

utf8mb4 is MySQL's full UTF-8, so emoji and other four-byte characters survive; Rails sends SET NAMES utf8mb4 on each connection. collation: adds COLLATE to that, choosing how strings compare and sort, and the common _ci collations are case-insensitive.

open as a page

In a Rails 8.1 app on PostgreSQL, how do you add a jsonb column for sneaker details and find sneakers by one key inside it?

level: juniorimportance: must knowfreq 55%

basics

~20 s

Add it with t.jsonb :details, null: false, default: {} and Active Record hands you a Ruby Hash with string keys. Query a key with a SQL fragment such as where("details ->> 'brand' = ?", "Nike"); where(details: {...}) compares the whole document.

open as a page

In a new Rails 8.1 app on SQLite, where do the database files live, and why must that directory survive every deploy?

level: juniorimportance: must knowfreq 50%

basics

~20 s

SQLite databases live as files under storage/ (storage/development.sqlite3, storage/test.sqlite3, storage/production.sqlite3 and its cache, queue and cable siblings). The file is the database, so production's storage/ must sit on a persistent disk or a redeploy wipes the data.

open as a page

In Active Record, what does wrapping two wallet updates in a transaction block guarantee, and which failures inside the block actually roll it back?

level: juniorimportance: must knowfreq 70%

basics

~20 s

A transaction block makes the writes inside it commit together or not at all on one connection. It rolls back when an exception leaves the block; update returning false is not an exception, so use update! or raise yourself.

open as a page

In a Rails 8.1 app on PostgreSQL, how do you give tables UUID primary keys, and what else must change once you do?

level: middleimportance: must knowfreq 55%

basics

~20 s

Use create_table :sneakers, id: :uuid; the adapter defaults the key to gen_random_uuid(). Foreign keys must then be uuid too (t.references :sneaker, type: :uuid), and first/last need implicit_order_column because random UUIDs do not sort by age.

open as a page

In Rails 8.1, which pragmas does the SQLite3 adapter set on every connection, and how do you override one in database.yml?

level: middleimportance: must knowfreq 40%

basics

~10 s

SQLite3Adapter::DEFAULT_PRAGMAS turns on foreign_keys, journal_mode WAL, synchronous NORMAL, a 128 MB mmap_size, a 64 MB journal_size_limit and cache_size 2000 for every connection. A pragmas: hash in database.yml is merged over those defaults.

open as a page

In a Rails payroll service, how do replica: true, connects_to and connected_to(role: :reading) send report queries to a read replica safely?

level: seniorimportance: must knowfreq 40%

basics

~20 s

Mark the replica entry replica: true, map roles in the abstract class with connects_to database: { writing: :primary, reading: :primary_replica }, and wrap report code in ActiveRecord::Base.connected_to(role: :reading). Inside, queries use the replica pool and any write raises ActiveRecord::ReadOnlyError.

open as a page

A Rails 8.1 migration fails halfway on MySQL after working on PostgreSQL; why is the schema left half-changed, and how do you recover and prevent it?

level: seniorimportance: must knowfreq 45%

basics

~20 s

MySQL commits each DDL statement immediately, so Rails does not wrap MySQL migrations in a transaction. Steps before the failure stay applied while the version is never recorded; recover by finishing or undoing those steps by hand, and prevent it with one schema change per migration.

open as a page

A Rails 8.1 invoicing app for a carpentry business runs on SQLite on one server: what tells you one writer per file is no longer enough, and what do you change?

level: seniorimportance: must knowfreq 45%

basics

~20 s

The signs are recurring ActiveRecord::StatementTimeout (database is locked) errors, writes that queue behind long transactions, and any need for a second app server or failover. Shorten transactions first; move to PostgreSQL or MySQL when you need more than one host.

open as a page

In a Rails arcade app, two concurrent credit transfers read the same Wallet balance and one debit is lost; how do lock!, with_lock and Wallet.lock fix it?

level: seniorimportance: must knowfreq 48%

basics

~20 s

Lock the row before reading the balance. Inside a transaction, wallet.lock! reloads it with SELECT ... FOR UPDATE, wallet.with_lock opens the transaction and locks in one call, and Wallet.lock.find(id) locks while loading, so the second transfer waits and reads the new balance.

open as a page

In Rails, when DATABASE_URL is set and config/database.yml also defines that environment, which values win and how are they merged?

level: middleimportance: should knowfreq 40%

basics

~20 s

Rails merges DATABASE_URL on top of the current environment's primary entry: the URL's adapter, host, port, credentials and database replace the YAML values, YAML keys the URL leaves out (such as max_connections) survive, and query parameters become extra keys.

open as a page

In Rails 8.1, what do max_connections and checkout_timeout in database.yml control, and when does ActiveRecord::ConnectionTimeoutError appear?

level: middleimportance: should knowfreq 42%

basics

~20 s

max_connections caps how many connections one process's pool may open (default 5), and checkout_timeout is how many seconds a thread waits for a free one (default 5). When every connection is busy past that wait, Active Record raises ActiveRecord::ConnectionTimeoutError.

open as a page

In Rails 8.1, how do the mysql2 and trilogy adapters differ for a MySQL or MariaDB app, and what changes when you switch?

level: middleimportance: should knowfreq 35%

basics

~20 s

Both talk to MySQL and MariaDB through the same AbstractMysqlAdapter, so SQL, strict mode and schema behaviour match. mysql2 wraps the C libmysqlclient; trilogy is a self-contained client, so builds need no MySQL dev headers, and it refuses prepared_statements: true.

open as a page

In a Rails 8.1 MySQL database.yml, what do strict: and variables: sql_mode change, and what happens to a too-long string in each mode?

level: middleimportance: should knowfreq 35%

basics

~20 s

Rails defaults to strict: true, appending STRICT_ALL_TABLES to the session's sql_mode, so a string longer than its column raises ActiveRecord::ValueTooLong. strict: false strips strict modes and MySQL truncates silently; variables: sql_mode sets the mode verbatim instead.

open as a page

In a Rails 8.1 app on PostgreSQL, how does an array: true column for sneaker sizes behave in Ruby and in where queries?

level: middleimportance: should knowfreq 40%

basics

~20 s

array: true makes a PostgreSQL array column that Active Record reads as a Ruby Array, casting each element to the column's type. where(sizes: ["9", "10"]) matches only that exact array; membership needs a SQL fragment with ANY or @>.

open as a page

In a Rails 8.1 SQLite database.yml, what does timeout: 5000 do, and what error does a request get when it runs out?

level: middleimportance: should knowfreq 35%

basics

~20 s

timeout: 5000 makes a connection that finds the database locked by another writer retry for up to 5000 ms instead of failing at once. When the wait runs out, SQLite's busy error surfaces as ActiveRecord::StatementTimeout.

open as a page

In Active Record, how does adding a lock_version column give a Wallet optimistic locking, and what should code do when ActiveRecord::StaleObjectError is raised?

level: middleimportance: should knowfreq 52%

basics

~20 s

With an integer lock_version column, every update and destroy adds WHERE lock_version = n and bumps it to n + 1. If another write changed the row first, zero rows match and Rails raises ActiveRecord::StaleObjectError; reload, then retry or show the conflict.

open as a page

In Rails, how does raising ActiveRecord::Rollback inside a transaction differ from raising another exception, and why can it silently do nothing in a nested block?

level: middleimportance: should knowfreq 50%

basics

~20 s

Both roll back a transaction that the block itself opened, but transaction swallows ActiveRecord::Rollback and returns nil, while any other exception is re-raised. In a nested block that joined an outer transaction, Rollback is swallowed without any ROLLBACK, so the outer block commits.

open as a page

Since Rails 7.2, what does after_commit on the transaction object yielded by Wallet.transaction do, and when does its block run inside nested transactions?

level: middleimportance: should knowfreq 30%

basics

~20 s

Wallet.transaction yields an ActiveRecord::Transaction, and its after_commit registers a block that runs once the outermost real transaction commits. Registered in a savepoint, it moves up on success and is dropped on rollback; with no transaction open, it runs immediately.

open as a page

With Rails' automatic role switching (config.active_record.database_selector = { delay: 2.seconds }), how does each request end up on the primary or the replica?

level: seniorimportance: should knowfreq 30%

basics

~20 s

The DatabaseSelector middleware treats GET and HEAD as reads. Other verbs write to the primary and stamp a last-write time in the session; a read uses the replica unless that session wrote within the delay, then the primary, with writes blocked.

open as a page

In Rails 8.1, why does upsert_all code ported from PostgreSQL break on MySQL, and which row does MySQL update without unique_by:?

level: seniorimportance: should knowfreq 25%

basics

~20 s

MySQL has no conflict target, so the MySQL adapters raise ArgumentError for unique_by: and generate ON DUPLICATE KEY UPDATE. That fires on a clash with the primary key or any unique index, so with two unique indexes MySQL may update a different row than intended.

open as a page

In a Rails 8.1 app on PostgreSQL, when would you use create_enum and t.enum for a sneaker's condition, and what does it cost later?

level: seniorimportance: should knowfreq 30%

basics

~20 s

create_enum defines a PostgreSQL enum type and t.enum ... enum_type: uses it, so the database rejects any other value. Adding values later takes add_enum_value, which cannot be reverted in change, and removing a value means rebuilding the type.

open as a page

In Rails 8.1 on PostgreSQL, how do you build sneaker search with a generated tsvector column and a GIN index, and why does stored: matter?

level: seniorimportance: should knowfreq 30%

basics

~20 s

Declare t.virtual :search_vector, type: :tsvector, as: "to_tsvector(...)", stored: true, index it with using: :gin and query with a @@ fragment. Rails 8.1 makes a generated column VIRTUAL on PostgreSQL 18 unless stored: true, and older servers reject it without stored: true.

open as a page

Why does the Rails 8.1 SQLite3 adapter begin transactions with BEGIN IMMEDIATE rather than SQLite's default deferred mode, and what does that cost?

level: seniorimportance: should knowfreq 25%

basics

~20 s

BEGIN IMMEDIATE takes the write lock when the transaction starts, where the busy handler can wait for it. A deferred transaction that reads first and then writes can fail with database is locked at once, with no wait at all.

open as a page

How do you back up the SQLite production database of a running Rails 8.1 app, and why is a plain cp of storage/production.sqlite3 unsafe?

level: seniorimportance: should knowfreq 28%

basics

~20 s

Copy through SQLite itself, with VACUUM INTO or the online backup API the sqlite3 gem exposes as SQLite3::Backup, then ship the copy off the server. A cp of the main file can miss commits still in the -wal file or catch a write halfway.

open as a page

In Rails, when does transaction(requires_new: true) give you a savepoint, and what do the isolation: and joinable: options change for nested transaction calls?

level: seniorimportance: should knowfreq 32%

basics

~20 s

Inside an open transaction, requires_new: true wraps the block in a savepoint, so its failure undoes only that block. isolation: applies only to a new top-level transaction and raises TransactionIsolationError when joining with another level or nesting; joinable: false makes later nested calls take savepoints.

open as a page

In a Rails 8.1 app on PostgreSQL, what Ruby value does a daterange column hold for a sneaker drop window, and how do you query it?

level: middleimportance: nice to knowfreq 15%

basics

~20 s

A daterange column holds a Ruby Range of Dates, read back with an exclusive end, so an inclusive 1..7 window returns as 1...8. where(drop_window: range) compares whole ranges; to find drops open today, use where("drop_window @> ?::date", Date.current).

open as a page

In Rails, how do connects_to shards: and connected_to(shard:) route an abstract class's models to horizontal shards, and what does the ShardSelector middleware add?

level: seniorimportance: nice to knowfreq 18%

basics

~20 s

Each shard gets its own database.yml entries, and an abstract class maps them with connects_to shards: { shard_one: { writing:, reading: }, ... }. Code picks one with connected_to(role:, shard:) { }, and ShardSelector does it per request from config.active_record.shard_resolver, locking the choice by default.

open as a page