Build in public

Running a SaaS on One SQLite File: Fly.io and Litestream in Production

By Jeyanth · 12 November 2026 · 6 min read

Formpit has no database server. Every account, form, submission and reply lives in one SQLite file on one small virtual machine, and a tool called Litestream streams every change to object storage a few seconds after it happens. This post is the honest version of that setup: the exact config, the mistakes I made, how restoring works, and when you shouldn't copy it.

Why one file instead of a database server

When I started building Formpit, I wanted the cheapest, simplest thing that could run a real product safely. A managed Postgres database is the default answer, but for a solo project it adds another service to pay for, another set of credentials, network latency on every query, and one more thing that can be down while the app is up.

SQLite turns all of that into a file. Queries are function calls inside the app's own process, so they take microseconds. Backups, migrations and local development all get simpler. The trade-off is that only one process on one machine should write to it, which for a product at Formpit's stage is no restriction at all.

The missing piece used to be backups. Copying a live database file is unsafe, and nightly dumps mean losing up to a day of data. Litestream fixes that: it watches SQLite's write-ahead log and continuously copies changes to S3-compatible storage.

The whole stack

• App: Node.js with Express, pages rendered on the server.
• Database: SQLite through better-sqlite3, whose synchronous API is fast and pleasant to use.
• Hosting: one Fly.io machine (shared-cpu-1x, 256 MB of memory) in Singapore, with a persistent volume mounted at /data.
• Backups: Litestream, replicating to a Cloudflare R2 bucket.
• Email: Resend, for notifications and replies.

SQLite settings that matter

Two lines run when the app opens the database:

db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

WAL mode (write-ahead logging) lets reads continue while a write is in progress, which is what makes SQLite comfortable for a web app. Litestream also requires it, because the write-ahead log is what it replicates. Foreign keys are off by default in SQLite for historical reasons; turning them on makes things like "delete a form and all its submissions" reliable.

Beyond that, the rule is to keep write transactions short. SQLite allows one writer at a time, so a slow write blocks the next one. With small inserts and updates, that's never noticeable.

The Litestream config

The whole backup setup is one small YAML file. Credentials come from environment variables, set as Fly secrets, so nothing sensitive lives in the repository:

dbs:
  - path: ${DATA_DIR}/formpit.db
    replicas:
      - url: ${LITESTREAM_REPLICA_URL}
        endpoint: ${LITESTREAM_ENDPOINT}
        access-key-id: ${LITESTREAM_ACCESS_KEY_ID}
        secret-access-key: ${LITESTREAM_SECRET_ACCESS_KEY}
        retention: 72h
        sync-interval: 10s

sync-interval: 10s means changes reach R2 within about ten seconds. That is the worst case for data loss if the volume disappeared completely. retention: 72h keeps three days of history, so I can restore the database as it was at any point in that window.

Starting the app under Litestream

The container's entrypoint does two things. If the volume is empty (a brand new machine, or after a disaster), it restores the latest backup first. Then it starts Litestream, which starts the app as a child process:

#!/bin/sh
set -e
DB="${DATA_DIR:-/data}/formpit.db"
if [ -n "$LITESTREAM_REPLICA_URL" ]; then
  if [ ! -f "$DB" ]; then
    litestream restore -if-replica-exists \
      -config /etc/litestream.yml "$DB" || true
  fi
  exec litestream replicate -config /etc/litestream.yml \
    -exec "node src/server.js"
else
  exec node src/server.js
fi

Running the app through -exec ties the two together: if the app stops, Litestream stops too, and Litestream gets a chance to flush the last changes on shutdown. -if-replica-exists stops the very first boot from failing when there's no backup yet.

The Fly.io side

Two parts of fly.toml matter. The volume, so the database survives deploys and restarts:

[[mounts]]
  source = 'formpit_data'
  destination = '/data'

And keeping the machine running. Fly can stop idle machines to save money, but Formpit also runs scheduled jobs (reminders and snoozed messages), so it needs to stay up:

[http_service]
  internal_port = 3000
  force_https = true
  auto_stop_machines = 'off'

Mistakes I made, so you don't have to

1. Backups that silently never happened

After launching, I opened the R2 bucket expecting to see backups. It was empty. The logs showed TLS certificate errors from Litestream. The cause: I used a slim Debian base image, which doesn't include the system's root certificates. Node.js ships its own set, so the app worked perfectly, but Litestream is a Go program that relies on the system's certificates, so every upload failed. One line in the Dockerfile fixed it:

RUN apt-get update \
 && apt-get install -y --no-install-recommends ca-certificates \
 && rm -rf /var/lib/apt/lists/*

The lesson matters more than the fix: a backup you haven't looked at isn't a backup. Check the bucket after your first deploy.

2. Assuming the machine would just stay up

While my Fly account was still on its free trial, the machine kept being stopped after a few minutes. Adding a payment method fixed it. If your app mysteriously goes to sleep on a new account, check that before debugging your code.

3. Forgetting that one machine means brief downtime on deploy

A volume can only be attached to one machine, so a deploy stops the old machine and starts the new one. For a few seconds nothing is listening. For Formpit that means a form submitted at exactly that moment could fail, so I deploy at quiet times and keep deploys small. If you need zero-downtime deploys, this setup isn't for you yet.

How restoring works, and how to test it

There are two ways the data comes back:

1. Automatically, when a machine starts with an empty volume. The entrypoint above restores the latest copy before the app starts.
2. By hand, to check a backup or recover an older state. Run the restore with the same config and credentials on any computer, writing to a separate file:

litestream restore -config litestream.yml \
  -o restored.db /data/formpit.db

sqlite3 restored.db "PRAGMA integrity_check;"   # should print: ok

Litestream can also restore to a point in time within the retention window (see the -timestamp option in its docs), which is the real safety net for "I ran a bad query ten minutes ago". Fly also takes its own daily snapshots of volumes, which gives a second, independent layer.

Do a test restore before you need one. It takes two minutes and turns "I think I have backups" into "I know I can recover".

What it costs

I won't quote exact numbers, because hosting prices change and yours will depend on region and usage. The shape of the bill is the useful part:

• The machine is the biggest hosting cost, and it's small for a shared CPU with 256 MB of memory.
• The volume is billed per gigabyte, and a database of form submissions is tiny.
• R2 storage for the backups fits comfortably within Cloudflare's free allowance at this size, and R2 doesn't charge for downloads, which matters when you restore.
• Email sending costs more than all the hosting together once you send real volume.

The point isn't that it's free. It's that the database, usually one of the biggest line items, has effectively disappeared from the bill.

A note on Litestream versions

Formpit pins Litestream 0.3.13. The 0.5 series, released in late 2025, reworked how replication works and changes the configuration. If you're starting fresh, read the current Litestream docs and migration guide rather than copying my config line for line.

When this approach stops being enough

One file on one machine is a great default, not a forever answer. I'd move to Postgres or a distributed SQLite option when one of these becomes true:

• More than one server needs to write, for redundancy or to run in several regions.
• Writes become heavy and constant, so the single writer turns into a queue.
• A few seconds of downtime on deploy is no longer acceptable.
• Long analytics queries start getting in the way of the app.

Until then, it's hard to beat: one file, no database server, continuous backups, and nothing to tune at 2 a.m. If you're curious what runs on it, Formpit is a form backend where every submission lands in an inbox you can reply from, and you can try the free form tools without signing up.

Frequently asked questions

More from the blog