In a new Rails 8.1 app on SQLite, where do the database files live, and why must that directory survive every deploy?
answer
- one directory, ignored by git
- storage/development.sqlite3 and storage/test.sqlite3
- production.sqlite3 plus cache, queue, cable files
- path expanded against Rails.root
- container filesystem is thrown away
basics
~20 sSQLite 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 sThe 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
Recall the storage/ paths in database.yml and that the file is the whole database, ignored by git and Docker.
Explain the four production files, the Rails.root path expansion and directory creation, and why WAL adds companion files.
Show you would mount storage/ persistently, keep every writer on that host, and back the volume up with a safe copy method.
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