Skip to content

SQLite in production

One file on a real disk, streamed to your own bucket every second.

Deploy on exe.dev

Shelley handles the setup. About 10 minutes.

Prefer a terminal?
curl -fsSL https://exe.dev/deploy/sqlite/prompt.txt | ssh exe.dev new --prompt=/dev/stdin

SQLite is the database inside more apps than any other, and on a server it's often the right one: a single file, no server to run, queries that take microseconds because nothing crosses a network. It needs one thing serverless platforms don't give you, a disk that stays. An exe.dev VM has one, on local NVMe, and keeps running whether or not anyone is using it.

This page sets SQLite up for production on that VM: settings that let readers and a writer get along, Litestream streaming every change to your own bucket, a checked snapshot every night, and a web UI only you can open. Your app runs on the same VM, on port 8000, behind exe.dev's login until you share it.

What you get#

  • A SQLite database at ~/data/app.db in WAL mode, readable only by exedev, and a sqlite3 set to wait for locks.
  • Litestream 0.5.18 streaming it to your bucket as litestream.service, started at boot on this VM only, with a copy restored from the bucket and checked.
  • Nightly snapshots of every database in ~/data into ~/sqlite/backups, 14 kept, one restored and checked.
  • sqlite-web at https://<vm>.exe.xyz:8001/, open only to the owner's exe.dev email.
What's running, and where
Private https://my-sqlite.exe.xyz:8001/ sqlite-web, for the emails in ~/sqlite/web/allowed-emails
Private https://my-sqlite.exe.xyz/ Port 8000, left free for your app
my-sqlite.exe.xyz exe.dev VM
SQLite 3.45 Ubuntu 24.04's sqlite3; your app brings its own library ~/data/app.db
Litestream 0.5.18 Streams every change to your S3-compatible bucket, keeps a week
Nightly snapshots Every database in ~/data, checked, 14 nights kept ~/sqlite/backups
sqlite-web 0.8.2 Browse and edit in the browser, behind exe.dev login and an email allowlist :8001

Deploy with Shelley#

Click Deploy and Shelley will set up SQLite on a new VM, then check that it works. No config-file wrangling required.

Have these ready; Shelley asks for them:

  • LITESTREAM_BUCKET: an empty bucket for Litestream's copy (get one), or none to set up everything else and add the bucket later
  • LITESTREAM_ENDPOINT: R2: https://<ACCOUNT_ID>.r2.cloudflarestorage.com; Backblaze B2: https://s3.<region>.backblazeb2.com; Tigris: https://t3.storage.dev; Amazon S3: leave empty
  • LITESTREAM_REGION: auto for R2 and Tigris, the bucket's region for B2, empty for Amazon S3
  • LITESTREAM_ACCESS_KEY_ID: a key that can read and write that bucket, and nothing else
  • LITESTREAM_SECRET_ACCESS_KEY: the key's secret
Prefer a terminal?
curl -fsSL \
  https://exe.dev/deploy/sqlite/prompt.txt \
  | ssh exe.dev new --prompt=/dev/stdin
What Shelley is told
LITESTREAM_BUCKET=<fill-this-in>
LITESTREAM_ENDPOINT=<fill-this-in>
LITESTREAM_REGION=<fill-this-in>
LITESTREAM_ACCESS_KEY_ID=<fill-this-in>
LITESTREAM_SECRET_ACCESS_KEY=<fill-this-in>

Set up SQLite on this VM by following the exe.dev recipe at https://exe.dev/deploy/sqlite.md from top to bottom.

The values at the top of this message are mine:
- LITESTREAM_BUCKET: an empty bucket for Litestream's copy ([get one](#get-a-bucket)), or `none` to set up everything else and add the bucket later
- LITESTREAM_ENDPOINT: R2: `https://<ACCOUNT_ID>.r2.cloudflarestorage.com`; Backblaze B2: `https://s3.<region>.backblazeb2.com`; Tigris: `https://t3.storage.dev`; Amazon S3: leave empty
- LITESTREAM_REGION: `auto` for R2 and Tigris, the bucket's region for B2, empty for Amazon S3
- LITESTREAM_ACCESS_KEY_ID: a key that can read and write that bucket, and nothing else
- LITESTREAM_SECRET_ACCESS_KEY: the key's secret
Ask me for any that still say <fill-this-in> before you need them.

The recipe's example VM name is my-sqlite. This VM's name is the output of `hostname`: use it wherever the recipe writes my-sqlite. Skip the step that creates the VM; you are on it.

You are done when all of these are true. Check each one yourself:
- A SQLite database at `~/data/app.db` in WAL mode, readable only by `exedev`, and a `sqlite3` set to wait for locks.
- Litestream 0.5.18 streaming it to your bucket as litestream.service, started at boot on this VM only, with a copy restored from the bucket and checked.
- Nightly snapshots of every database in `~/data` into `~/sqlite/backups`, 14 kept, one restored and checked.
- sqlite-web at `https://<vm>.exe.xyz:8001/`, open only to the owner's exe.dev email.

Read the whole recipe, including the closed details. For initial setup,
follow "Install by hand" from top to bottom, skipping "Create a VM". The
walkthrough sections are for the owner afterwards: don't run the Back up
and restore or Update instructions, and don't install an app.
If LITESTREAM_BUCKET is `none` or the owner has no bucket yet, skip
"Give Litestream your bucket", "Start Litestream", and "Test a restore
from the bucket", run the plain `sqlite3` line of "Create the database",
and tell the owner how to add a bucket later. Otherwise write the owner's
values into ~/sqlite/litestream.env without printing the secret, and never
overwrite an existing litestream.env. If `~/sqlite/litestream ltx
~/data/app.db` lists files before ~/data/app.db exists, the bucket
already holds a database: ask the owner whether to restore it (only if no
other VM is still replicating to it) or to use an empty bucket.
Allow only the owner's email in sqlite-web, read from the reflection
integration; if that fails, ask the owner for it. Check sqlite-web on
localhost with no email, a wrong email, and the owner's email.
Leave the VM private and do not reboot. Finish with the sqlite-web URL,
the database path, and the connection settings for the owner's language
from "Connect your app".

Finish with the URLs I should open.

Also at /deploy/sqlite/prompt.txt. The recipe it follows is /deploy/sqlite.md.

Why one VM is enough#

A single VM with SQLite on its disk runs most web apps for a long time. Modern disks and CPUs are fast, and SQLite reads straight from the page cache: no connection pool to size, no network round trip per query, no database server to patch. exe.dev bets on this stack itself; see The GUTS stack and Persistent disks, not serverless.

What makes it production-grade rather than a toy:

  • The right settings, so a busy app doesn't hit database is locked. Connect your app has them for Go, Python, and Node.
  • A copy off the VM, within a second. Litestream reads SQLite's write-ahead log and uploads each change to S3-compatible storage. Lose the VM and you restore from the bucket, to the latest second or to a moment before a bad migration.
  • A way to look inside: sqlite3 over SSH, and sqlite-web in the browser, behind exe.dev login.

Open sqlite-web#

When Shelley finishes, open https://my-sqlite.exe.xyz:8001/ and sign in to exe.dev. sqlite-web shows the tables in ~/data/app.db: browse rows, edit them, run SQL, and export CSV or JSON. A new database is empty, so try it:

CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT NOT NULL);
INSERT INTO notes (body) VALUES ('hello from sqlite-web');

Only the exe.dev emails in ~/sqlite/web/allowed-emails get in, even if you share the VM. Add a teammate's email, one per line, and they're in; no restart needed. Anyone on the list can change or drop anything, so keep it short.

Connect your app#

Your database is the file /home/exedev/data/app.db. Give your app that path, for example as DATABASE_URL=sqlite:////home/exedev/data/app.db (SQLAlchemy) or file:/home/exedev/data/app.db (Go, Prisma, Drizzle). Keep every database in ~/data: the nightly snapshot copies whatever SQLite files are there.

SQLite allows one writer at a time and, in WAL mode, any number of readers alongside it. Every connection your app opens should set:

Setting Why
journal_mode = WAL Readers don't block the writer, or it them. Litestream needs it. Stored in the file; already on.
busy_timeout = 5000 A writer waits up to 5 seconds for its turn instead of failing at once with database is locked.
synchronous = NORMAL Safe in WAL mode and much faster than the default FULL; a power cut can lose the last transactions, never corrupt the file.
foreign_keys = ON Off by default in SQLite, per connection.
BEGIN IMMEDIATE A transaction that reads, then writes, takes the write lock up front. Otherwise it can fail with SQLITE_BUSY no matter what busy_timeout says.

Go, with modernc.org/sqlite (pure Go, no cgo; tested with v1.62):

import (
	"database/sql"

	_ "modernc.org/sqlite"
)

func openDB(path string) (*sql.DB, error) {
	// _txlock=immediate makes db.Begin take the write lock up front.
	return sql.Open("sqlite", "file:"+path+"?_txlock=immediate"+
		"&_pragma=busy_timeout(5000)&_pragma=journal_mode(WAL)"+
		"&_pragma=synchronous(NORMAL)&_pragma=foreign_keys(ON)")
}

Python, with the standard library's sqlite3:

import sqlite3

def connect(path="/home/exedev/data/app.db"):
    # timeout is busy_timeout in seconds. IMMEDIATE: each transaction
    # takes the write lock when it starts.
    conn = sqlite3.connect(path, timeout=5, isolation_level="IMMEDIATE")
    conn.execute("PRAGMA journal_mode = WAL")
    conn.execute("PRAGMA synchronous = NORMAL")
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

Django 5.1 and later take the same settings in DATABASES; the Django page has them. Node, with the built-in node:sqlite (Node 24 or later):

import { DatabaseSync } from "node:sqlite";

export const db = new DatabaseSync("/home/exedev/data/app.db", { timeout: 5000 });
db.exec("PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; PRAGMA foreign_keys = ON;");

// Run fn in a transaction that takes the write lock up front.
export function transaction(fn) {
  db.exec("BEGIN IMMEDIATE");
  try {
    const result = fn();
    db.exec("COMMIT");
    return result;
  } catch (err) {
    db.exec("ROLLBACK");
    throw err;
  }
}

With better-sqlite3, pass { timeout: 5000 }, set the same pragmas with db.pragma(), and run writes with db.transaction(fn).immediate().

Keep write transactions short: do slow work (an HTTP call, an LLM request) before BEGIN, not inside it. One process with many threads or goroutines is fine, and so are several processes on the VM, such as a web server and a worker.

Put your app on this VM#

The framework pages deploy an app with git push, under systemd, with SQLite in ~/data: Go, FastAPI, Django, Rails, Laravel, and Node.js. Follow one on this VM, and point it at ~/data/app.db or add its database to Litestream (see Another database). Or ask Shelley on the VM to build the app; it starts from Go and SQLite unless you say otherwise.

Apps that already use SQLite, such as Uptime Kuma or Open WebUI, get the same protection: add their database file to ~/sqlite/litestream.yml (see Another database).

Look inside over SSH#

sqlite3 is on the VM, set to wait for locks rather than fail. On your computer:

ssh -t my-sqlite.exe.xyz sqlite3 data/app.db

Then .tables, .schema notes, .mode box and a SELECT, or .quit. To work on a copy with a desktop app such as DB Browser for SQLite, TablePlus, or DBeaver, take a consistent copy first. Never scp the live file: recent changes may still be in app.db-wal beside it.

ssh my-sqlite.exe.xyz "sqlite3 data/app.db \"VACUUM INTO '/tmp/app-copy.db'\""
scp my-sqlite.exe.xyz:/tmp/app-copy.db .
ssh my-sqlite.exe.xyz rm /tmp/app-copy.db

VACUUM INTO writes a compact, consistent copy while the app keeps running. Shelley on the VM can query the database for you, too: ask it which tables grow fastest or why a query is slow.

Keep your data#

Three copies, each for a different disaster:

  1. Litestream, to your bucket. Every change, about a second behind, with a week of history. Restore the latest state on a new VM, or the database as it was at 14:02 yesterday (to within about 30 seconds; to the second for the last few minutes).
  2. Nightly snapshots, in ~/sqlite/backups, 14 kept. The quickest way back from a bad migration, and they work with no bucket at all.
  3. The VM's disk. exe.dev copies it to another server in the region (architecture), which covers hardware failure. For a full disk image in your own bucket, with no keys on the VM, see exe.dev's VM backups (in preview).

The bucket's key sits in ~/sqlite/litestream.env, readable only by exedev. S3 clients sign every request with it, so it can't stay behind an exe.dev integration the way an API token can; make it a key for this one bucket, and nothing else.

Check on Litestream any time, on the VM:

systemctl status litestream --no-pager
~/sqlite/litestream ltx ~/data/app.db | tail -n 3

The newest files should be seconds old while the app writes. To restore, see Back up and restore.

Get a bucket#

Any S3-compatible storage works. Cloudflare R2 has a free tier and no fees to download your data back. In the Cloudflare dashboard:

  1. R2 object storage → Create bucket. Pick a location near your VM.
  2. On Overview, choose Manage next to API Tokens, and create a token with Object Read & Write for that bucket only.
  3. Copy the Access Key ID, the Secret Access Key, and the S3 endpoint, https://<ACCOUNT_ID>.r2.cloudflarestorage.com, without the bucket name on the end.

Give those to Shelley when it asks, with auto as the region. We tested this page against Garage, an S3-compatible store, running on the VM, not against a hosted provider; Litestream detects R2, B2, Tigris, and the others by their endpoint and adjusts for each. On Backblaze B2, create a bucket and an application key for it; the endpoint is on the bucket's page. On Amazon S3, create a bucket and an IAM user whose policy allows only that bucket. Use one bucket per VM, or at least per database: two VMs replicating to the same place can leave neither restorable.

A staging copy#

Want to try a migration on real data first? Copy the whole VM. On your computer:

ssh exe.dev cp my-sqlite my-sqlite-staging

The copy has the app, the database as it was a moment ago, and the snapshots, at https://my-sqlite-staging.exe.xyz/. Litestream doesn't start there: it runs only on the VM it was set up on, so the copy can't write into production's bucket. Break the copy as much as you like, then delete it with ssh exe.dev rm my-sqlite-staging. Share it with a teammate with ssh exe.dev share add my-sqlite-staging their@email.com. The dev, test, prod page has more on working this way.

If the data is sensitive, scrub it on the copy before you share it.

When to move to PostgreSQL#

Stay on SQLite while one VM serves your app; give it more CPU and memory as it grows. Move to PostgreSQL when:

  • More than one machine needs to write, for example app servers on several VMs, or serverless functions elsewhere.
  • Writes queue up: long transactions, or a sustained stream of writes in the thousands per second, with database is locked after 5 seconds.
  • You need what Postgres has and SQLite doesn't: roles and per-user permissions, LISTEN/NOTIFY, pgvector at scale, PostGIS, or tools that only speak the Postgres protocol.

The move is usually an ORM setting plus a data copy; pgloader copies a SQLite file into PostgreSQL in one command.

The nuts and bolts#

Happy clicking Deploy? You can stop here. These are the same setup steps Shelley follows, plus the maintenance commands for later.

Install by hand

Create a VM#

On your computer (pick another name if my-sqlite is taken):

ssh exe.dev new --name=my-sqlite
ssh my-sqlite.exe.xyz

Adding SQLite backups to an app's VM? Skip this step and run the rest there. All remaining commands run on the VM, as exedev.

Set up sqlite3#

The exeuntu image comes with SQLite 3.45 from Ubuntu 24.04. Make its shell wait up to 5 seconds for a lock, as your app will, and make a home for the databases and for this page's files:

sudo apt-get update && sudo apt-get install -y sqlite3 jq
grep -qs '^\.timeout' ~/.sqliterc || echo '.timeout 5000' >> ~/.sqliterc
mkdir -p ~/data ~/sqlite
chmod 700 ~/data ~/sqlite
sqlite3 --version

That's the command-line shell. Your app's SQLite library is its own (Python's sqlite3 module, a Go driver, an npm package), and the settings in Connect your app apply to it.

Install Litestream 0.5.18#

Litestream ships a Debian package with a systemd unit. Check it against the release's checksums and install it:

v=0.5.18
cd /tmp
curl -fsSLO https://github.com/benbjohnson/litestream/releases/download/v$v/litestream-$v-linux-x86_64.deb
curl -fsSL https://github.com/benbjohnson/litestream/releases/download/v$v/checksums.txt |
  grep " litestream-$v-linux-x86_64.deb\$" | sha256sum -c
sudo apt-get install -y ./litestream-$v-linux-x86_64.deb
litestream version

Expect litestream-0.5.18-linux-x86_64.deb: OK and 0.5.18. The package's service stays off until the next steps configure it.

Give Litestream your bucket#

Put the bucket's details in a file only exedev can read. Run this, then fill in your values with nano ~/sqlite/litestream.env (the examples show the endpoint and region for each provider):

(umask 077; [ -e ~/sqlite/litestream.env ] || cat > ~/sqlite/litestream.env <<'EOF'
LITESTREAM_BUCKET=
LITESTREAM_ENDPOINT=
LITESTREAM_REGION=
LITESTREAM_ACCESS_KEY_ID=
LITESTREAM_SECRET_ACCESS_KEY=
EOF
)

The config says what to stream, and to where. Each database gets its own path in the bucket. Litestream uploads changes every second, takes a full snapshot daily, and keeps a week of history:

cat > ~/sqlite/litestream.yml <<'EOF'
# Litestream config. The bucket and its key come from litestream.env.
snapshot:
  interval: 24h
  retention: 168h
dbs:
  - path: /home/exedev/data/app.db
    replica:
      type: s3
      bucket: ${LITESTREAM_BUCKET}
      path: app.db
      endpoint: ${LITESTREAM_ENDPOINT}
      region: ${LITESTREAM_REGION}
EOF

A small wrapper runs litestream with both files, so commands on this page don't need flags:

cat > ~/sqlite/litestream <<'EOF'
#!/bin/sh
# litestream, with this VM's config and bucket key.
set -a
. /home/exedev/sqlite/litestream.env
set +a
export LITESTREAM_CONFIG=/home/exedev/sqlite/litestream.yml
exec /usr/bin/litestream "$@"
EOF
chmod +x ~/sqlite/litestream

Check that the bucket answers and is empty:

~/sqlite/litestream ltx ~/data/app.db

Expect only a header line, level min_txid max_txid size created. An error here means a wrong endpoint, region, or key; fix litestream.env and run it again. If it lists files, this bucket already holds a database; to get it back, see Restore everything on a new VM.

Create the database#

Create ~/data/app.db and switch it to WAL mode. The mode is stored in the file, so every connection gets it:

sqlite3 ~/data/app.db 'PRAGMA journal_mode = WAL;'

Expect wal. Already have a database? Copy it to ~/data with VACUUM INTO on the old machine (see Look inside over SSH) and run the same PRAGMA on it.

Start Litestream#

Litestream should stream from this VM only. A copy of the VM (a staging copy, say) writing into the same bucket would leave neither copy restorable, so the service checks that it runs on the VM named in ~/sqlite/primary-vm. It runs as exedev, like your app, so the files it touches stay yours:

hostname > ~/sqlite/primary-vm
sudo mkdir -p /etc/systemd/system/litestream.service.d
sudo tee /etc/systemd/system/litestream.service.d/exedev.conf >/dev/null <<EOF
[Service]
User=$USER
ExecCondition=/bin/sh -c 'test "\$(hostname)" = "\$(cat $HOME/sqlite/primary-vm)"'
ExecStart=
ExecStart=$HOME/sqlite/litestream replicate
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now litestream
systemctl is-active litestream

Expect active. Its log is journalctl -u litestream. Litestream adds two small tables of its own to the database, _litestream_seq and _litestream_lock; leave them be.

Test a restore from the bucket#

Write a row, give Litestream a few seconds, then restore the bucket's copy to a scratch file and check it:

sqlite3 ~/data/app.db "CREATE TABLE IF NOT EXISTS restore_test (at TEXT);
  INSERT INTO restore_test VALUES (datetime('now'));"
sleep 5
rm -f /tmp/restore-test.db*
~/sqlite/litestream restore -integrity-check full -o /tmp/restore-test.db ~/data/app.db
sqlite3 /tmp/restore-test.db 'PRAGMA integrity_check; SELECT * FROM restore_test;'
rm -f /tmp/restore-test.db*
sqlite3 ~/data/app.db 'DROP TABLE restore_test;'

Expect ok and the time you wrote the row, in UTC.

Set up nightly snapshots#

This script copies every SQLite database in ~/data with VACUUM INTO, which gives a consistent, compacted copy while apps keep writing, and checks each copy's integrity. It keeps the 14 newest:

cat > ~/sqlite/backup.sh <<'EOF'
#!/usr/bin/env bash
# Snapshot every SQLite database in ~/data into
# ~/sqlite/backups/<UTC time>/. Keeps the 14 newest.
set -euo pipefail
umask 077
src="$HOME/data"
dest="$HOME/sqlite/backups"
keep=14
mkdir -p "$dest"
out="$dest/$(date -u +%Y%m%d-%H%M%S)"
trap 'rm -rf "$out.part"' EXIT
mkdir "$out.part"
for db in "$src"/*; do
  [ -f "$db" ] && [ "$(head -c 15 "$db" | tr -d '\0')" = "SQLite format 3" ] || continue
  copy="$out.part/$(basename "$db")"
  sqlite3 -cmd '.timeout 60000' "$db" "VACUUM INTO '$copy'"
  check=$(sqlite3 "$copy" 'PRAGMA integrity_check;')
  [ "$check" = ok ] || { echo "$db: $check" >&2; exit 1; }
done
mv "$out.part" "$out"
ls -1d "$dest"/2*/ | head -n -"$keep" | xargs -r rm -rf --
echo "$out"
EOF
chmod +x ~/sqlite/backup.sh

Schedule it, then take a first snapshot:

sudo tee /etc/systemd/system/sqlite-backup.service >/dev/null <<EOF
[Unit]
Description=Snapshot SQLite databases to ~/sqlite/backups

[Service]
Type=oneshot
User=$USER
ExecStart=$HOME/sqlite/backup.sh
EOF
sudo tee /etc/systemd/system/sqlite-backup.timer >/dev/null <<'EOF'
[Unit]
Description=Snapshot SQLite databases every night

[Timer]
OnCalendar=*-*-* 03:30:00
RandomizedDelaySec=30m
Persistent=true

[Install]
WantedBy=timers.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now sqlite-backup.timer
sudo systemctl start sqlite-backup.service
systemctl list-timers sqlite-backup.timer
ls -l ~/sqlite/backups/*/

Expect a next run time and an app.db. For failures: journalctl -u sqlite-backup.service. Check that a snapshot opens:

latest=$(ls -1d ~/sqlite/backups/2*/ | tail -n 1)
sqlite3 "$latest/app.db" 'PRAGMA integrity_check;'

Expect ok. Snapshots are plain SQLite files, not in WAL mode; the restore steps switch them back.

Add a web UI#

sqlite-web is a Flask app for browsing and editing a SQLite database. Install it in its own virtualenv with uv, which the exeuntu image includes:

mkdir -p ~/sqlite/web && cd ~/sqlite/web
uv venv -q venv
uv pip install -q --python venv/bin/python sqlite-web==0.8.2
[ -s allowed-emails ] || curl -fsS https://reflection.int.exe.xyz/email | jq -r .email > allowed-emails
cat allowed-emails

The last line should print your exe.dev email. If it doesn't, put your email in ~/sqlite/web/allowed-emails yourself. sqlite-web loads a "startup hook" module; this one puts it behind exe.dev's login and sets the same lock timeout as your app:

cat > ~/sqlite/web/exe_login.py <<'EOF'
"""sqlite-web behind exe.dev login.

exe.dev's proxy sends X-ExeDev-Email for signed-in visitors; only emails
listed in allowed-emails get in.
"""
import os
from pathlib import Path
from urllib.parse import quote

from flask import request
from sqlite_web.sqlite_web import app

ALLOWED = Path(__file__).with_name("allowed-emails")
app.secret_key = os.urandom(32)


def check_email():
    email = request.headers.get("X-ExeDev-Email", "").strip().lower()
    if not email:
        target = request.full_path if request.query_string else request.path
        return "", 302, {"Location": "/__exe.dev/login?redirect=" + quote(target, safe="")}
    try:
        lines = ALLOWED.read_text().splitlines()
    except OSError:
        lines = []
    if email not in {line.strip().lower() for line in lines}:
        return (f"{email} isn't in ~/sqlite/web/allowed-emails on this VM.\n",
                403, {"Content-Type": "text/plain"})


app.before_request_funcs.setdefault(None, []).insert(0, check_email)


def connect(db):
    db.execute_sql("PRAGMA busy_timeout = 5000")
    db.execute_sql("PRAGMA foreign_keys = ON")
EOF

Run it as a service, as exedev, on port 8001, leaving 8000 for your app. To show more databases, add their paths to the end of ExecStart.

sudo tee /etc/systemd/system/sqlite-web.service >/dev/null <<EOF
[Unit]
Description=sqlite-web for ~/data, behind exe.dev login

[Service]
User=$USER
WorkingDirectory=$HOME/sqlite/web
Environment=PYTHONPATH=$HOME/sqlite/web
ExecStart=$HOME/sqlite/web/venv/bin/sqlite_web --host 0.0.0.0 --port 8001 --no-browser --startup-hook exe_login.connect $HOME/data/app.db
Restart=always

[Install]
WantedBy=multi-user.target
EOF
sudo systemctl daemon-reload
sudo systemctl enable --now sqlite-web

Check the three cases: no email (sent to exe.dev login), someone else (refused), and you (sqlite-web):

email=$(head -n 1 ~/sqlite/web/allowed-emails)
timeout 30 sh -c 'until curl -so /dev/null http://localhost:8001/; do sleep 1; done'
curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8001/
curl -s -o /dev/null -w '%{http_code}\n' -H 'X-ExeDev-Email: someone@example.com' http://localhost:8001/
curl -sL -H "X-ExeDev-Email: $email" http://localhost:8001/ | grep -o '<title>[^<]*</title>'

Expect 302, 403, and <title>SQLite Web: app.db</title>. sqlite-web trusts exe.dev's login header, so keep untrusted software off this VM: anything running here could send the header itself.

Back up and restore

Each restore below writes to a new file first, so you can look before you change anything. ~/sqlite/litestream ltx ~/data/app.db shows how far back the bucket goes.

Undo a mistake#

Restore the database as it was just before the mistake, to a scratch file. Times are UTC; date -u shows the time now.

~/sqlite/litestream restore -timestamp 2026-11-16T14:00:00Z \
  -integrity-check quick -o /tmp/app-before.db ~/data/app.db

Or take last night's snapshot: cp ~/sqlite/backups/<time>/app.db /tmp/app-before.db. Often the easiest fix is to copy the lost rows back into the live database, with no downtime:

sqlite3 ~/data/app.db "ATTACH '/tmp/app-before.db' AS before;
  INSERT INTO notes SELECT * FROM before.notes WHERE id NOT IN (SELECT id FROM notes);"

To roll the whole database back instead, stop everything that writes to it, swap the file in, and start Litestream with a fresh history (the bucket keeps the old one until it ages out). myapp stands for your app's service:

sudo systemctl stop myapp sqlite-web litestream
mv ~/data/app.db ~/data/app.db.broken
rm -f ~/data/app.db-wal ~/data/app.db-shm
mv /tmp/app-before.db ~/data/app.db
sqlite3 ~/data/app.db 'PRAGMA journal_mode = WAL;'
~/sqlite/litestream reset ~/data/app.db
sudo systemctl start litestream sqlite-web myapp

Delete ~/data/app.db.broken once you're happy.

Restore everything on a new VM#

If the VM is gone, or you're moving: make sure the old VM no longer runs Litestream (ssh exe.dev rm it, or sudo systemctl disable --now litestream there). Then on a new VM, follow Install by hand up to and including "Give Litestream your bucket", with the same bucket and key, and instead of "Create the database":

~/sqlite/litestream restore -integrity-check full ~/data/app.db
sqlite3 ~/data/app.db 'PRAGMA journal_mode;'

Expect wal. Carry on from "Start Litestream", which makes this VM the one that streams. Then bring your app over.

No bucket? Copy a snapshot from your computer instead: scp app.db my-new-vm.exe.xyz:data/app.db.

Another database#

Each database needs its own entry in ~/sqlite/litestream.yml, with its own path in the bucket. For ~/data/jobs.db:

  - path: /home/exedev/data/jobs.db
    replica:
      type: s3
      bucket: ${LITESTREAM_BUCKET}
      path: jobs.db
      endpoint: ${LITESTREAM_ENDPOINT}
      region: ${LITESTREAM_REGION}

Then sudo systemctl restart litestream. Make sure the database is in WAL mode (sqlite3 ~/data/jobs.db 'PRAGMA journal_mode = WAL;'). The nightly snapshot picks it up on its own.

Add a bucket later#

Set up without a bucket? Follow "Give Litestream your bucket", "Start Litestream", and "Test a restore from the bucket" from Install by hand. Litestream uploads the existing database first.

Update

Take a snapshot first: ~/sqlite/backup.sh.

sqlite3 updates with Ubuntu: sudo apt-get update && sudo apt-get upgrade. Your app's SQLite library updates with the app.

Litestream: check the release notes, then run Install Litestream with the new version in v=, and sudo systemctl restart litestream. The package keeps the override in litestream.service.d.

sqlite-web:

uv pip install -q --python ~/sqlite/web/venv/bin/python -U sqlite-web
sudo systemctl restart sqlite-web
Something not working?

database is locked. Another connection held the write lock for longer than busy_timeout. Check the timeout is set on every connection and that write transactions begin with BEGIN IMMEDIATE; then look for a long transaction, such as a slow loop or a network call inside one.

attempt to write a readonly database. A file in ~/data belongs to root, usually from running something with sudo. Fix it with sudo chown -R exedev:exedev ~/data.

Litestream isn't running on a VM copy. That's the guard in Start Litestream: systemctl status litestream shows the condition failed. To make the copy the one that streams, stop Litestream on the original first, then run hostname > ~/sqlite/primary-vm on the copy and sudo systemctl start litestream.

Litestream logs errors. journalctl -u litestream -n 50. Access denied or NoSuchBucket means litestream.env is wrong; edit it and restart. If the bucket fills up or the provider is down, Litestream keeps changes on the VM and catches up.

The -wal file keeps growing. Something holds a read transaction open, so SQLite can't checkpoint. Restarting the app usually clears it; then look for a connection that's never closed.

sqlite-web shows 403. Your email isn't in ~/sqlite/web/allowed-emails, exactly as you sign in to exe.dev.

Frequently asked questions#

Is SQLite good enough for production?

For most web apps, yes. It handles thousands of writes a second on the VM's local NVMe disk and many more reads, and with WAL mode readers never wait for the writer. What it can't do is take writes from more than one machine; see When to move to PostgreSQL.

How much data can I lose?

With Litestream, about a second of writes if the VM vanished outright: it ships changes to your bucket every second, and on a clean stop it sends the rest first. Without a bucket, up to a day, since the nightly snapshot is your newest copy (exe.dev also copies the VM's disk to other servers, which covers hardware failure).

Can exe.dev keep the bucket keys off the VM?

Not for an S3 key. exe.dev's HTTP proxy integration adds a fixed header to each request, but S3 clients sign every request with the secret key itself, so the key has to be where Litestream runs. That's why the page uses a key limited to one bucket, in a file only exedev can read. exe.dev's VM backups need no key on the VM at all. On Amazon S3, exe.dev's AWS integration can stand in for the key with short-lived role credentials; this page hasn't tested that with Litestream.

Can I run SQLite with Docker?

Yes. Keep the database on a bind mount such as ./data:/data, not in the container's own filesystem, and point Litestream on the VM at the file in ~/data. Don't put the same database on a mount shared by two machines; SQLite's locks only work on a local disk.

Why not Turso or Cloudflare D1?

They're good when your app runs as serverless functions with no disk of its own. An exe.dev VM has one, so the database is a file next to your app: no network hop per query, no per-row pricing, and plain sqlite3 to look inside.

Is SQLite free?

Yes. SQLite is in the public domain, Litestream is Apache-2.0, and sqlite-web is MIT. You pay for the VM (exe.dev plans) and for your bucket's storage, which for most databases fits in a provider's free tier.

Updated Nov 16, 2026 · tested end to end Nov 16, 2026 · Markdown · manifest.json · Something wrong? Tell us