I recently shipped a small Flask app, a scoring platform for a fitness competition, with SQLite as its only database. Not as a stopgap until the real database arrives: the app refuses to start with anything else, because a half-supported second database is worse than none. The conventional advice says this is a mistake. For this class of app, the conventional advice is wrong, but the deployment details will bite you if nobody warns you about them.

This is the warning I wish I had read first. The platform here is Railway, but most of it applies anywhere you deploy containers.

Why SQLite was the right call

Be honest about the write load. Mine is a handful of activity approvals and fixture results per day, from a user base that fits in one room. SQLite in WAL mode handles that without noticing: readers keep working while a writer holds the lock, and every write goes through SQLAlchemy anyway.

The dishonest version of this decision is “SQLite for now, Postgres when we scale”. The honest version is knowing exactly what the ceiling is (more on that at the end) and accepting the trade deliberately.

The five things that actually bite

1. The filesystem is ephemeral

Railway’s default filesystem is wiped on every redeploy. SQLite is a file. Without a volume, your database quietly ceases to exist each time you push. Add a volume, note its mount path, and make sure the database file lives on it. This sounds obvious; it is also the single most common way people lose their first SQLite deployment.

2. Three slashes versus four

This is the one that hurts. In a SQLAlchemy connection string,

sqlite:///newman_cup.db

is a relative path, resolved against the working directory, which is not on your volume. It works perfectly in every test, then evaporates on redeploy.

sqlite:////data/newman_cup.db

with four slashes is an absolute path on the mounted volume, and survives. One character is the difference between a persistent database and one that vanishes. I sidestepped the whole trap by not accepting a DATABASE_URL at all: the app takes a SQLITE_DIR environment variable and builds the URI itself, so nobody, including future me, can get the slash count wrong.

3. Nothing written at build time persists

The volume is not mounted during build or pre-deploy, only at run time. If your Dockerfile or build step creates the database or writes anything to the data directory, it is writing to a filesystem that will not be there later. Schema creation belongs at application start, inside create_app in my case, when the volume is actually attached.

4. The volume may not be writable

If the container runs as a non-root user, the mounted volume may not be writable, and SQLite fails with SQLITE_READONLY: attempt to write a readonly database. On Railway the fix is setting RAILWAY_RUN_UID=0. Whatever your platform, test a write immediately after first deploy rather than assuming.

5. One replica, and a Procfile that matches

A volume attaches to a single instance. Two replicas cannot share a SQLite file, so horizontal scaling is the one thing this setup genuinely trades away. Accept that fully and configure accordingly:

web: gunicorn --workers 1 --threads 8 --timeout 60 wsgi:app

One worker, many threads, on purpose. Multiple gunicorn workers are separate processes that would each open their own connection pool to the same file and race each other on schema creation at boot. Threads inside one worker share a pool and serialise writes cleanly under WAL. Eight threads is far more than a small app needs.

Backups are your job

There is no managed backup for a file on a volume. The database is the file plus its -wal and -shm sidecars while WAL mode is on: copy all three together, or better, run VACUUM INTO for a clean single-file snapshot. Either way, schedule it; a backup that depends on you remembering is a wish, not a backup.

Knowing where the exit is

The ceiling you hit first is not throughput, it is the single-replica constraint. If the app ever outgrows that, every write already goes through SQLAlchemy, so the move to Postgres is a connection-string change and a data copy, not a rewrite. That is the real discipline of running SQLite in production: not pretending the limits do not exist, but knowing precisely which limit arrives first and keeping the exit clearly marked.

Until then, the whole database is one file I can copy, inspect, and reason about. For a small app, that is not a compromise. It is the feature.