I recently published a post about running SQLite in production, and the heart of it was a warning: on a platform with an ephemeral filesystem, a mis-pointed SQLite file means your database quietly ceases to exist on every redeploy. I even described my defence with some pride. The app refuses to accept a raw connection string and builds its own from a SQLITE_DIR environment variable, so nobody can get the path wrong.

The same day, announcements started vanishing from my deployed app after a redeploy.

What actually happened

The diagnosis took longer than it should have, because everything looked right. The Railway volume existed. It was mounted at /data. The app was running, writing, reading, behaving perfectly. Nothing was visibly wrong except that data kept disappearing whenever I shipped.

The cause: the volume was mounted, but SQLITE_DIR itself was never set on the service. My carefully designed URI resolver, finding no SQLITE_DIR, fell back to the Flask instance directory, which lives on the container’s throwaway filesystem. Every deploy had been silently starting a brand-new, empty database. The app never crashed, never warned, never behaved oddly within any single deployment. The database was persistent right up until the moment it was not.

The irony is worth sitting with. My defence against the misconfigured-path trap was an environment variable, and the failure simply moved up one level: the environment variable was the thing that was missing. A safety mechanism you have to remember to configure is, in the end, another thing you can forget.

The recovery

The running container still held the live database, so the fix was a careful copy rather than a disaster:

  1. SSH into the running service and copy the database onto the volume. Not just the .db file: the -wal and -shm sidecars too, because in WAL mode the most recent writes live in the write-ahead log, not the main file. Copy the trio or lose the tail of your data.
  2. Set SQLITE_DIR=/data and restart.
  3. Verify properly: not “the app works”, but “the new container has no instance directory at all”, proving nothing can fall back to the ephemeral path again.

Everything written before that morning’s deploy was already gone and had to be retyped. Everything since has survived every deploy.

What changed afterwards

Two layers of backup, because the incident made plain that I had none:

  • A boot snapshot. Every production boot takes a WAL-safe sqlite3.backup() copy into a dated file on the volume before any migrations run, keeping the newest ten. If a deploy ever eats the database again, the previous boot’s copy is sitting next to it.
  • An off-box copy. A download button in the admin area produces a consistent snapshot, and pulling one is now a weekly habit. A backup that lives on the same volume as the database it protects is only half a backup.

And one operational rule: after any deployment change, verify persistence, not just function. Write something, redeploy, and check it is still there. A green deploy proves the app starts. It proves nothing about where your data lives.

The honest moral

I wrote that the real discipline of running SQLite in production is knowing exactly which limit arrives first. It turns out the first limit was not SQLite at all. It was me, assuming that because I had designed the safe path, I must be standing on it. The warning in that first post stands, but it needs one addition: after you build the guard rail, walk over and check you are actually behind it.