skip to content

SQLite in Production

Rails 8 runs SQLite in production with WAL mode, IMMEDIATE transactions and a busy timeout, keeping database files under storage/. Interviewers ask when one writer per file stops being enough.

part ofRuby on Railsread it →
on this pageshow

explore

questions

6

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%

answer

  1. one directory, ignored by git
  2. storage/development.sqlite3 and storage/test.sqlite3
  3. production.sqlite3 plus cache, queue, cable files
  4. path expanded against Rails.root
  5. container filesystem is thrown away

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.

solid answer

~40 s

The generated `config/database.yml` points every environment at a file under `storage/`: `storage/development.sqlite3`, `storage/test.sqlite3`, and in production `storage/production.sqlite3` plus `production_cache`, `production_queue` and `production_cable` files for the Solid trio. The SQLite3 adapter expands a relative `database:` path against `Rails.root` and creates the missing directory itself. The generated `.gitignore` and `.dockerignore` exclude `/storage/*` (keeping only `.keep`), so the data never reaches the repo or the image. With SQLite there is no database server: the file **is** the data. A container's filesystem is replaced on every deploy, so production's `storage/` has to be a persistent volume on the host, and that volume is what you back up.

go deeper

for a junior

Recall the storage/ paths in database.yml and that the file is the whole database, ignored by git and Docker.

for a middle

Explain the four production files, the Rails.root path expansion and directory creation, and why WAL adds companion files.

for a senior

Show you would mount storage/ persistently, keep every writer on that host, and back the volume up with a safe copy method.

for a principal

Weigh the simplicity of a file on one disk against what it costs in recovery planning and in the option to add servers later.

## Where Rails 8.1 puts SQLite data A new Rails 8.1 app uses **SQLite** unless `rails new` is told otherwise, and SQLite is an *embedded* database: there is no server process to connect to, only a file that the `sqlite3` gem opens inside the Rails process. So "where is the database?" has a literal answer: a path on disk. The generator writes these paths into `config/database.yml`: | Environment | Database entry | File | |---|---|---| | development | (single) | `storage/development.sqlite3` | | test | (single) | `storage/test.sqlite3` | | production | `primary` | `storage/production.sqlite3` | | production | `cache` | `storage/production_cache.sqlite3` | | production | `queue` | `storage/production_queue.sqlite3` | | production | `cable` | `storage/production_cable.sqlite3` | The production block is **multi-database** because Solid Cache, Solid Queue and Solid Cable each keep their tables in their own database. For a single-server invoicing tool for a carpentry business, that means four files in one directory: invoices and customers in `production.sqlite3`, cached fragments, pending jobs and broadcast messages in the other three. ## How the adapter treats the path The `SQLite3Adapter` handles `database:` itself when a connection is set up: - a **relative path** is expanded against `Rails.root`, so `storage/production.sqlite3` means `<app>/storage/production.sqlite3` whatever the working directory; - if the **parent directory is missing**, the adapter creates it with `FileUtils.mkdir_p`; if it cannot (no permission, read-only filesystem), it raises `ActiveRecord::NoDatabaseError`; - `":memory:"` gives a throwaway in-memory database, and a `file:` URI is passed through untouched. With the default write-ahead-log journal, SQLite also keeps two companion files beside each database while it is in use, ending in `-wal` and `-shm`. They belong to the database just as much as the main file does. ## What keeps the files out of git and the image The generator also writes the ignore rules that keep data out of places it must not go: - `.gitignore` ignores `/storage/*` but keeps `/storage/.keep`, so the directory exists in a fresh clone while no developer ever commits their database; - `.dockerignore` carries the same lines, so `docker build` never bakes a laptop's development database into the production image; - the same directory is also the root of the `local` Disk service in `config/storage.yml`, so locally stored Active Storage uploads share it. ## Why the directory must outlive a deploy A deploy of a containerised Rails app starts a **new container from a new image** and removes the old one. Anything written to the old container's own filesystem goes with it. For a client-server database that is harmless, because the data lives on the database server. For SQLite it is fatal: the invoices *are* `storage/production.sqlite3`. So production needs `storage/` mounted from **persistent storage on the host**. The generated deploy configuration already mounts a named volume at `/rails/storage` for exactly this reason (how that volume is declared and managed belongs to the deploy tool's own documentation). Two practical consequences follow: 1. **Every process that writes must see the same directory on the same machine.** Web workers, the job supervisor and a `bin/rails console` session all open the same files. 2. **The volume is the unit you back up.** Copying it safely while the app runs takes the right tool, not a plain file copy of a live database. When `rails new` runs with `--skip-kamal`, the generator cannot assume a volume, so it comments the production paths out and warns that many managed hosts do not provide disk persistence at all. ## Permissions and the first boot - The generated Dockerfile runs the app as a non-root `rails` user (uid 1000), so the mounted directory must be **writable by that user**. SQLite creates the `-wal` and `-shm` files beside the database, so write access to the file alone is not enough. - If the adapter cannot create the directory at all, it raises `ActiveRecord::NoDatabaseError`; a directory that is readable but not writable fails later, on the first write. - On an empty volume no file exists until something opens it. Preparing the database at boot creates the file and loads the schema, which is why a fresh server starts with empty tables rather than an error. ## Mistakes interviewers listen for | Belief | What actually happens | |---|---| | The database lives in `db/` | `db/` holds `schema.rb`, migrations and seeds; the data files are in `storage/` | | `db/schema.rb` lets you recover the data | It recreates empty tables; rows exist only in the `.sqlite3` files | | Committing `production.sqlite3` is a backup | The generated `.gitignore` excludes it, and a repo is not a backup of a live file | | Only the main file matters | Recent commits can sit in the `-wal` file until a checkpoint |

  • Why does the production section of a Rails 8.1 SQLite database.yml have four entries instead of one?
    Because the Solid trio each use their own database: Solid Cache in `cache`, Solid Queue in `queue`, Solid Cable in `cable`, with `migrations_paths` such as `db/queue_migrate`. On SQLite each entry becomes its own file under `storage/`, so cache writes and job bookkeeping do not compete for the write lock on the file that holds the business data.
  • What happens if the storage/ directory is missing when a Rails 8.1 app first connects to SQLite?
    The SQLite3 adapter expands the relative path against `Rails.root` and creates the missing parent directory with `FileUtils.mkdir_p`. If it cannot create it, it raises `ActiveRecord::NoDatabaseError`. The file itself appears when `db:prepare` or `db:create` runs, or on first open.

  • Says SQLite data lives in db/ next to schema.rb
  • Treats db/schema.rb as a way to restore production rows
  • Thinks the database survives a container replacement without a mounted volume
  • Suggests committing the production .sqlite3 file as a backup
  • Copies only production.sqlite3 and ignores the -wal companion file
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

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 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

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