skip to content

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%

answer

  1. a constant on SQLite3Adapter
  2. WAL journal, NORMAL synchronous
  3. foreign_keys on, 128 MB mmap_size
  4. pragmas: hash merged over defaults
  5. unknown name warns, does not raise

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.

solid answer

~40 s

Every time the SQLite3 adapter configures a connection it applies `DEFAULT_PRAGMAS`: `foreign_keys` true, `journal_mode` `:wal`, `synchronous` `:normal`, `mmap_size` 134217728 (128 MB), `journal_size_limit` 67108864 (64 MB) and `cache_size` 2000. Foreign keys make the constraints Rails migrations create actually enforced; WAL lets readers keep reading while one writer commits; NORMAL trades a few fsyncs for throughput. To change one, add a `pragmas:` hash to the database.yml entry: Rails runs `DEFAULT_PRAGMAS.merge(pragmas)`, so your keys win and the rest keep their defaults. Each key must match a setter in the sqlite3 gem's `SQLite3::Pragmas`; an unknown name only prints `Unknown SQLite pragma` and is skipped, so a typo fails silently.

code

yaml · 7 lines
yaml
production:
  primary:
    <<: *default
    database: storage/production.sqlite3
    pragmas:
      mmap_size: 268435456
      cache_size: 4000

go deeper

for a junior

Recall that Rails turns on WAL and foreign keys for SQLite and that database.yml can change these settings.

for a middle

Explain each default in DEFAULT_PRAGMAS, the merge with pragmas:, and why it runs on every connection.

for a senior

Show you would verify overrides in a console, keep foreign keys on, and treat the unknown-pragma warning as a silent failure.

for a principal

Weigh synchronous NORMAL's durability trade against write throughput for the data the business can afford to lose.

## The defaults Rails applies SQLite ships with conservative settings meant for every possible embedder, and several of them are wrong for a web app. Since Rails 7.1 ("modern, performant defaults for SQLite3 database connections") the adapter fixes that itself. In Rails 8.1 the list is a constant, `ActiveRecord::ConnectionAdapters::SQLite3Adapter::DEFAULT_PRAGMAS`: | Pragma | Rails value | What it buys a web app | |---|---|---| | `foreign_keys` | `true` | SQLite enforces foreign keys only when this is on for the connection | | `journal_mode` | `:wal` | write-ahead log: readers are not blocked while one writer commits | | `synchronous` | `:normal` | fewer fsyncs per commit than `FULL`, still safe against an app crash | | `mmap_size` | `134217728` (128 MB) | reads through memory-mapped I/O | | `journal_size_limit` | `67108864` (64 MB) | caps the size the WAL file is left at after a checkpoint | | `cache_size` | `2000` | the per-connection page cache SQLite keeps in memory | A **pragma** is SQLite's statement for reading or changing a connection setting (`PRAGMA journal_mode = WAL`). Most pragmas are per connection, which is why Rails applies the list in `configure_connection`, the hook that runs on every new or re-established connection, not once at boot. ## Why each one matters in a Rails app - **`foreign_keys`**: `t.references :customer, foreign_key: true` writes a real constraint into the table, but SQLite ignores it unless the connection enables enforcement. With the default on, deleting a customer row that invoices still reference raises `ActiveRecord::InvalidForeignKey` instead of leaving orphan rows. - **`journal_mode: :wal`**: a single-server invoicing tool for a carpentry business has a few people reading invoice lists while someone saves a payment. In WAL mode those reads continue during the write. How WAL achieves that is the SQLite engine's business; the Rails point is that the adapter picks it for you. - **`synchronous: :normal`**: in WAL mode, `NORMAL` keeps the database consistent across an application crash; what it gives up is that the most recent commits can be lost if the *machine* loses power before the log is synced. - **`mmap_size`, `journal_size_limit`, `cache_size`**: performance and disk-housekeeping knobs, not correctness switches. ## Overriding a default with pragmas: The adapter reads an optional `pragmas:` hash from the database configuration and merges it over the defaults: ```yaml production: primary: <<: *default database: storage/production.sqlite3 pragmas: mmap_size: 268435456 cache_size: 4000 ``` What happens on each connection: 1. the hash's keys are stringified and merged: `DEFAULT_PRAGMAS.merge(pragmas)`, so **your value wins** for the keys you name and the other defaults stay; 2. for each pair, the adapter checks that `SQLite3::Pragmas` (the sqlite3 gem) defines a setter such as `mmap_size=`; 3. if it does, the setter runs on the raw connection; if it does not, the adapter calls `warn "Unknown SQLite pragma: ..."` and moves on. That third step is the trap: a misspelt key (`mmap_sise`) does not raise, it prints a warning that is easy to miss in production logs, and the default stays in force. ## What not to put there - **Turning `foreign_keys` off** to make a data import pass hides orphan rows that a server database would reject later. - **`journal_mode: delete`** (SQLite's rollback journal) is accepted, but it brings back readers blocked by writers, which is rarely what a web app wants. - **The `busy_timeout` pragma** exists as a gem setter, but the lock wait belongs in the top-level `timeout:` key, which installs a busy handler that releases Ruby's global VM lock while it waits. ## Why the generated database.yml shows none of this A new app's `database.yml` carries `adapter`, `max_connections`, `timeout` and the file paths, and **no pragmas at all**. The defaults live in the adapter's code, not in the template, so an empty `pragmas:` section does not mean SQLite runs with its own stock settings. A related switch sits outside the pragma list: `config.active_record.sqlite3_adapter_strict_strings_by_default`, set to `true` by `load_defaults 7.1` and later, puts the connection in strict mode so double-quoted string literals in SQL are rejected. It is an adapter option (`strict:`), not a pragma. ## Seeing what a connection actually runs with ```ruby ApplicationRecord.with_connection do |conn| conn.select_value("PRAGMA journal_mode") # => "wal" conn.select_value("PRAGMA foreign_keys") # => 1 end ``` Checking from `bin/rails console` in production is the quickest way to confirm an override took effect, because the warning for an unknown key never raises.

  • Why does Rails apply these pragmas in configure_connection instead of once when the app boots?
    Most SQLite pragmas are per connection, and Rails holds a pool of connections that can be dropped and re-established. `configure_connection` runs on every new connection and every reconnect, so each one gets `foreign_keys` and the other defaults. Journal mode WAL persists in the file, but the others would silently revert on a fresh connection.
  • A developer adds `pragmas: { mmap_sise: 0 }` to disable memory mapping; what happens?
    Nothing changes. The adapter looks for a `mmap_sise=` setter on `SQLite3::Pragmas`, does not find one, prints `Unknown SQLite pragma: mmap_sise` with `warn` and skips it, so `mmap_size` keeps Rails' 128 MB default. Verify overrides with `PRAGMA mmap_size` in a console.

  • Says SQLite enforces foreign keys without any per-connection setting
  • Thinks a pragmas: hash replaces the whole default list
  • Expects a misspelt pragma name to raise at boot
  • Puts the lock wait in pragmas busy_timeout instead of timeout:
  • Claims synchronous NORMAL risks corruption on an application crash

Assumes Rails 8.1.4, where DEFAULT_PRAGMAS and the pragmas: merge live in SQLite3Adapter; the adapter has applied tuned connection defaults since Rails 7.1.