SQLite in production
One file on a real disk, streamed to your own bucket every second.
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/stdinSQLite 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.dbin WAL mode, readable only byexedev, and asqlite3set 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
~/datainto~/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
https://my-sqlite.exe.xyz:8001/
sqlite-web, for the emails in ~/sqlite/web/allowed-emails
https://my-sqlite.exe.xyz/
Port 8000, left free for your app
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), ornoneto set up everything else and add the bucket laterLITESTREAM_ENDPOINT: R2:https://<ACCOUNT_ID>.r2.cloudflarestorage.com; Backblaze B2:https://s3.<region>.backblazeb2.com; Tigris:https://t3.storage.dev; Amazon S3: leave emptyLITESTREAM_REGION:autofor R2 and Tigris, the bucket's region for B2, empty for Amazon S3LITESTREAM_ACCESS_KEY_ID: a key that can read and write that bucket, and nothing elseLITESTREAM_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/stdinWhat 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:
sqlite3over 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:
- 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).
- Nightly snapshots, in
~/sqlite/backups, 14 kept. The quickest way back from a bad migration, and they work with no bucket at all. - 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:
- R2 object storage → Create bucket. Pick a location near your VM.
- On Overview, choose Manage next to API Tokens, and create a token with Object Read & Write for that bucket only.
- 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 lockedafter 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