skip to content

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