SQLite and Litestream

A Phoenix app does not always need a database server. For one app on one server, SQLite is one file, one process and no daemon to tune, and on a 1 GB server that is a third of the memory PostgreSQL would take. Potions makes it a first-class choice: pick SQLite file on this server when you create the app, and Potions creates DATABASE_PATH, keeps daily snapshots like it does for PostgreSQL, and, once you point it at a storage destination, streams every change to your bucket with Litestream so you can restore the database as it was at any moment in the last seven days.

The trade is real and simple: a SQLite app runs on exactly one server. It cannot join a cluster, sit behind a load balancer, or move its database to a database server. If you need any of those, choose PostgreSQL.

Choosing SQLite

There are two places SQLite shows up, and they are independent.

On the server. The Database select on the Add server form offers the PostgreSQL versions and SQLite (no database server). A SQLite server installs no PostgreSQL at all, only the sqlite3 command-line tool, the PostgreSQL client (so apps on it can still use a database server or an external database) and Litestream. It is the right server for a handful of small apps. Litestream is also installed on every new PostgreSQL app server, so the choice below is available on both.

On the app. The Database picker on the Add app form offers SQLite file on this server next to the PostgreSQL choices (it is the default on a SQLite server). Potions then:

  • creates DATABASE_PATH=/opt/potions/<app>/db/<app>.sqlite3 instead of DATABASE_URL, locked like STORAGE_DIR;
  • creates the db/ directory on the server, owned by the deploy user with mode 750, before the first deploy;
  • creates no PostgreSQL role or database, and shows the file's status on the app's Database tab.

The database file itself is created by your app the first time it connects, during the first deploy's migrations. The choice is made at creation: an app can't be switched between SQLite and PostgreSQL from the dashboard. Create a new app and move the data yourself if you need to change.

Your app's configuration

mix phx.new my_app --database sqlite3 generates a runtime.exs that reads DATABASE_PATH, which is exactly what Potions sets. Two settings must be added to it, because Potions deploys with two instances of your app briefly running side by side (see Zero-Downtime Deployments) and the adapter's defaults are wrong for that:

# config/runtime.exs, inside `if config_env() == :prod do`
database_path =
  System.get_env("DATABASE_PATH") ||
    raise "environment variable DATABASE_PATH is missing"

config :my_app, MyApp.Repo,
  database: database_path,
  pool_size: String.to_integer(System.get_env("POOL_SIZE") || "5"),
  busy_timeout: 5_000,
  default_transaction_mode: :immediate

default_transaction_mode: :immediate is the one that matters. With the default (deferred) mode, a transaction that has read and then tries to write while the other instance holds the write lock fails instantly with Database busy, without waiting at all, because SQLite refuses to busy-wait a read-to-write upgrade. That happens during every deploy's overlap, not only during migrations: on a busy app, a fifth to a quarter of such transactions fail. With immediate transactions the old instance simply waits behind the lock, up to busy_timeout, and carries on. Rails 8 made the same two changes for the same reason. The first deploy after you add the snippet still shows a few errors from the old instance (it runs the old config); they stop from the deploy after that.

Two more things:

  • exqlite 0.36.0 or newer. Older versions bundle a SQLite with a WAL corruption bug that two processes writing or checkpointing at the same instant can trigger, which is exactly "your app plus Litestream". The deploy log warns when the lock file is older, and when it has no ecto_sqlite3 at all.
  • Oban. Use engine: Oban.Engines.Lite. It forces the PG notifier and isolated peers, which means every node considers itself the leader, so run your queues and leader-elected plugins (Cron, Pruner, Lifeline) on exactly one node. If you enable a worker node for the app, that is the node to give them.

Deploys and migrations

Deploys work exactly as for PostgreSQL apps: build, migrate while the current instance is still serving, start the new instance, health check, switch. The difference is that both instances share one file rather than one server. Migrations that add tables, columns or indexes hold the write lock for as long as they take and cost the old instance that much latency, nothing else. A migration that rebuilds a table (SQLite's ALTER TABLE cannot drop or change columns, so the adapter recreates the table) holds the lock for the whole copy. That copy is fast: in our tests a 300,000-row table rebuilt in a tenth of a second under a write loop, with the old instance's writes waiting it out and none failing. Only a table with millions of rows needs a short maintenance window.

Before migrations run, the deploy checks that the app's db/ directory exists and is writable by the deploy user and fails with a clear message if not. Without that check, the adapter would retry the connection forever and the migration step would hang.

What Potions manages on the server

Path What it is
/opt/potions/<app>/db/<app>.sqlite3 The database, with its -wal and -shm files beside it
/opt/potions/<app>/db/.<app>.sqlite3-litestream/ Litestream's local position in the replica
/opt/potions/<app>/litestream.yml The replication config Potions renders, readable by the deploy user only
/opt/potions/<app>/litestream.env The destination's access key, mode 600, deploy user only
/etc/systemd/system/<app>@litestream.service The replication service, running as the deploy user
/home/deploy/backups/<app>/<app>_<timestamp>.sqlite3.gz Daily snapshots, next to where PostgreSQL dumps go

The app's Database tab reads the file on demand: size, WAL size, journal mode, page count, whether the replication service is running, and a Check integrity button that runs PRAGMA quick_check on the live file, which is safe while the app is running.

Continuous replication

Snapshots are once a day. Replication is continuous: Litestream watches the database's write-ahead log and ships every change to your bucket within seconds, keeps a snapshot there once a day and enough history to reconstruct any moment in the last seven days.

Continuous replication is part of the offsite backups feature, on the Team plan and above. Daily snapshots work on every plan.

Turning it on

You need one of your storage destinations first: Backblaze B2, Cloudflare R2, DigitalOcean Spaces, Hetzner Object Storage or Amazon S3. Wasabi is not offered for replication, because it bills every deleted object for 90 days and Litestream creates and deletes small files constantly.

Then either tick Replicate this database to … (it names your destination) on the Add app form, or open the app's Database tab, pick a destination in the Continuous replication card, tick the consent box and click Turn on replication. Replication and offsite dump copies share the app's destination.

The consent box exists because replication is the one place Potions puts a storage credential on your server. Offsite dump copies use short-lived presigned links and the key never leaves Potions. Litestream cannot work that way: it writes to your bucket every second for as long as the app runs, so it needs a real key pair, which Potions writes to litestream.env, readable by the deploy user only, next to the app's own secrets. Use a key scoped to one bucket:

  • Backblaze B2: an application key restricted to the bucket, optionally with a name prefix.
  • Cloudflare R2: an API token with Object Read & Write on that bucket only.
  • DigitalOcean Spaces: a per-bucket access key with read and write on that bucket.
  • Amazon S3: an IAM user whose policy allows s3:PutObject, s3:GetObject, s3:DeleteObject and s3:ListBucket on that bucket and its objects.
  • Hetzner Object Storage: keys are project-wide; there is no per-bucket scope. Use a project that holds only this bucket if that matters to you.

Potions checks that the key can list the bucket, verifies it with a forced snapshot before starting the service (once the database file exists), installs the service and waits for it to run. The card shows Starting until the first heartbeat arrives, usually within half a minute, then Replicating. If the service is not running, the card says so under the chip.

What it costs

Litestream syncs every second (every 10 seconds on Cloudflare R2, where each request counts against a quota). Measured on a busy app writing about 18 transactions a second:

Busy hour Idle hour
Requests to the bucket roughly 3,800 PUT, 3,500 DELETE, 300 LIST roughly 240 LIST
Litestream memory about 40 MB about 38 MB
Litestream CPU under 1% of one core about none

Backblaze B2 charges nothing for API calls, and Hetzner and DigitalOcean charge nothing per request, so on those the cost is storage alone: one snapshot a day plus a week of deltas, cents for the databases this is aimed at. Cloudflare R2 counts PUT, DELETE and LIST as Class A operations, which is why it gets the 10 second interval. Amazon S3 bills per request; a busy database costs a few dollars a month there.

Heartbeats and stall alerts

Litestream pings Potions after every interval in which it synced successfully. If no ping arrives for 20 minutes, the card turns to Stalled, Potions emails you (the SQLite replication stalled alert, at most once an hour) and restarts the service once. If it recovers, the card returns to Replicating on its own. If it doesn't, check the destination's key and bucket; Turn off and turn it back on with a fresh key resolves most cases.

One thing to know: the ping keeps coming while the app is idle, so a destination that stops working while nothing is written is noticed within about 20 minutes of the app's next write, not of the failure. Nothing is lost in between; the changes wait on the server and ship when the bucket answers again.

Generations

Every restore, import and reset gives the file a fresh path in the bucket, litestream/<app id>/g2, g3 and so on, so the history that led up to the restore is never overwritten. The Earlier replica copies list on the Database tab shows them, with a Delete from bucket button for each one that is no longer live. Potions never deletes them on its own.

Restore to a point in time

From the Continuous replication card, click Restore to a point in time, pick a moment in the last seven days (to the second within the last five minutes, to 30 seconds before that; Litestream lands on the nearest earlier sync) and type the app's name to confirm. Then:

  1. Litestream checks that the replica covers that moment. If it doesn't, nothing else happens.
  2. Potions takes a snapshot of the current file and records it as a manual backup.
  3. The app and its replication service are stopped.
  4. The database is rebuilt from the bucket into a candidate file and checked with PRAGMA quick_check.
  5. The candidate replaces the live file and the app is started again.
  6. Replication resumes into a new generation.

The app is stopped for the duration: a few seconds for a small database. The outcome appears on the Database tab.

Snapshots, offsite copies, restores, imports and resets

Everything on the Automated Backups, Offsite Backups and Restoring a Backup pages applies, with these differences:

  • Snapshots are VACUUM INTO copies of the file, gzipped, named <app>_<timestamp>.sqlite3.gz. They are consistent point-in-time copies taken while the app runs.
  • Restore, import and reset stop the app. The file is the database, so it is swapped out rather than reloaded. Potions snapshots the current file first, stops the app and the replication service, does the work, checks the result, restarts the app and starts a new replica generation.
  • Imports accept a SQLite database file (.sqlite3, .db or .sqlite, gzipped or not) or a SQL script (.sql, .sql.gz, as produced by sqlite3 app.sqlite3 .dump). A lone -wal or -shm file is refused; upload the database file itself.
  • Reset deletes the file. Redeploy afterwards to run migrations again.
  • Confirmations ask you to type the app's name, since a SQLite app has no database name.

Deleting the app

Deleting a SQLite app removes the file, the snapshots and the replication service with the app. The replicated copies in your bucket are yours: the destroy form offers an optional box to delete them too, and leaves them where they are otherwise.

Single server only

A SQLite app cannot be clustered, put behind a load balancer, or moved to a database server; the pages that offer those say so for a SQLite app. Multi-tenant apps and worker nodes work as usual, with the Oban note above.