---
title: "SQLite in production"
description: "Run SQLite in production on an exe.dev VM: the right settings, Litestream streaming every change to your S3 bucket, nightly snapshots, and a private web UI."
url: https://exe.dev/deploy/sqlite
category: "Databases"
updated: 2026-11-16
---

# SQLite in production

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

- **Software:** SQLite (https://sqlite.org)
- **Source:** https://sqlite.org/src/
- **License:** Public domain (Litestream Apache-2.0, sqlite-web MIT)
- **Time:** 10 minutes
- **Example VM name:** `my-sqlite` (yours will differ; on the VM, `hostname` prints it)
- **Machine-readable:** https://exe.dev/deploy/sqlite/manifest.json

## 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.

Running on the VM:

- **SQLite 3.45**: Ubuntu 24.04's sqlite3; your app brings its own library, data in `~/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, data in `~/sqlite/backups`
- **sqlite-web 0.8.2**: Browse and edit in the browser, behind exe.dev login and an email allowlist, port 8001

How it is reached:

- https://my-sqlite.exe.xyz:8001/ (private): sqlite-web, for the emails in ~/sqlite/web/allowed-emails
- https://my-sqlite.exe.xyz/ (private): Port 8000, left free for your app

## Deploy with Shelley

Open https://exe.dev/new?prompt=LITESTREAM_BUCKET%3D%3Cfill-this-in%3E%0ALITESTREAM_ENDPOINT%3D%3Cfill-this-in%3E%0ALITESTREAM_REGION%3D%3Cfill-this-in%3E%0ALITESTREAM_ACCESS_KEY_ID%3D%3Cfill-this-in%3E%0ALITESTREAM_SECRET_ACCESS_KEY%3D%3Cfill-this-in%3E%0A%0ASet%20up%20SQLite%20on%20this%20VM%20by%20following%20the%20exe.dev%20recipe%20at%20https%3A%2F%2Fexe.dev%2Fdeploy%2Fsqlite.md%20from%20top%20to%20bottom.%0A%0AThe%20values%20at%20the%20top%20of%20this%20message%20are%20mine%3A%0A-%20LITESTREAM_BUCKET%3A%20an%20empty%20bucket%20for%20Litestream%27s%20copy%20%28%5Bget%20one%5D%28%23get-a-bucket%29%29%2C%20or%20%60none%60%20to%20set%20up%20everything%20else%20and%20add%20the%20bucket%20later%0A-%20LITESTREAM_ENDPOINT%3A%20R2%3A%20%60https%3A%2F%2F%3CACCOUNT_ID%3E.r2.cloudflarestorage.com%60%3B%20Backblaze%20B2%3A%20%60https%3A%2F%2Fs3.%3Cregion%3E.backblazeb2.com%60%3B%20Tigris%3A%20%60https%3A%2F%2Ft3.storage.dev%60%3B%20Amazon%20S3%3A%20leave%20empty%0A-%20LITESTREAM_REGION%3A%20%60auto%60%20for%20R2%20and%20Tigris%2C%20the%20bucket%27s%20region%20for%20B2%2C%20empty%20for%20Amazon%20S3%0A-%20LITESTREAM_ACCESS_KEY_ID%3A%20a%20key%20that%20can%20read%20and%20write%20that%20bucket%2C%20and%20nothing%20else%0A-%20LITESTREAM_SECRET_ACCESS_KEY%3A%20the%20key%27s%20secret%0AAsk%20me%20for%20any%20that%20still%20say%20%3Cfill-this-in%3E%20before%20you%20need%20them.%0A%0AThe%20recipe%27s%20example%20VM%20name%20is%20my-sqlite.%20This%20VM%27s%20name%20is%20the%20output%20of%20%60hostname%60%3A%20use%20it%20wherever%20the%20recipe%20writes%20my-sqlite.%20Skip%20the%20step%20that%20creates%20the%20VM%3B%20you%20are%20on%20it.%0A%0AYou%20are%20done%20when%20all%20of%20these%20are%20true.%20Check%20each%20one%20yourself%3A%0A-%20A%20SQLite%20database%20at%20%60~%2Fdata%2Fapp.db%60%20in%20WAL%20mode%2C%20readable%20only%20by%20%60exedev%60%2C%20and%20a%20%60sqlite3%60%20set%20to%20wait%20for%20locks.%0A-%20Litestream%200.5.18%20streaming%20it%20to%20your%20bucket%20as%20litestream.service%2C%20started%20at%20boot%20on%20this%20VM%20only%2C%20with%20a%20copy%20restored%20from%20the%20bucket%20and%20checked.%0A-%20Nightly%20snapshots%20of%20every%20database%20in%20%60~%2Fdata%60%20into%20%60~%2Fsqlite%2Fbackups%60%2C%2014%20kept%2C%20one%20restored%20and%20checked.%0A-%20sqlite-web%20at%20%60https%3A%2F%2F%3Cvm%3E.exe.xyz%3A8001%2F%60%2C%20open%20only%20to%20the%20owner%27s%20exe.dev%20email.%0A%0ARead%20the%20whole%20recipe%2C%20including%20the%20closed%20details.%20For%20initial%20setup%2C%0Afollow%20%22Install%20by%20hand%22%20from%20top%20to%20bottom%2C%20skipping%20%22Create%20a%20VM%22.%20The%0Awalkthrough%20sections%20are%20for%20the%20owner%20afterwards%3A%20don%27t%20run%20the%20Back%20up%0Aand%20restore%20or%20Update%20instructions%2C%20and%20don%27t%20install%20an%20app.%0AIf%20LITESTREAM_BUCKET%20is%20%60none%60%20or%20the%20owner%20has%20no%20bucket%20yet%2C%20skip%0A%22Give%20Litestream%20your%20bucket%22%2C%20%22Start%20Litestream%22%2C%20and%20%22Test%20a%20restore%0Afrom%20the%20bucket%22%2C%20run%20the%20plain%20%60sqlite3%60%20line%20of%20%22Create%20the%20database%22%2C%0Aand%20tell%20the%20owner%20how%20to%20add%20a%20bucket%20later.%20Otherwise%20write%20the%20owner%27s%0Avalues%20into%20~%2Fsqlite%2Flitestream.env%20without%20printing%20the%20secret%2C%20and%20never%0Aoverwrite%20an%20existing%20litestream.env.%20If%20%60~%2Fsqlite%2Flitestream%20ltx%0A~%2Fdata%2Fapp.db%60%20lists%20files%20before%20~%2Fdata%2Fapp.db%20exists%2C%20the%20bucket%0Aalready%20holds%20a%20database%3A%20ask%20the%20owner%20whether%20to%20restore%20it%20%28only%20if%20no%0Aother%20VM%20is%20still%20replicating%20to%20it%29%20or%20to%20use%20an%20empty%20bucket.%0AAllow%20only%20the%20owner%27s%20email%20in%20sqlite-web%2C%20read%20from%20the%20reflection%0Aintegration%3B%20if%20that%20fails%2C%20ask%20the%20owner%20for%20it.%20Check%20sqlite-web%20on%0Alocalhost%20with%20no%20email%2C%20a%20wrong%20email%2C%20and%20the%20owner%27s%20email.%0ALeave%20the%20VM%20private%20and%20do%20not%20reboot.%20Finish%20with%20the%20sqlite-web%20URL%2C%0Athe%20database%20path%2C%20and%20the%20connection%20settings%20for%20the%20owner%27s%20language%0Afrom%20%22Connect%20your%20app%22.%0A%0AFinish%20with%20the%20URLs%20I%20should%20open.%0A to create a VM whose agent, Shelley, follows this recipe. From a terminal:

```sh
curl -fsSL https://exe.dev/deploy/sqlite/prompt.txt | ssh exe.dev new --prompt=/dev/stdin
```

Or follow the steps below by hand.

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](https://litestream.io)
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.

## 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](/docs/guts) and [Persistent disks, not serverless](/docs/serverful).

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](#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](https://github.com/coleifer/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:

```sql
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](https://pkg.go.dev/modernc.org/sqlite)
(pure Go, no cgo; tested with v1.62):

```go
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`:

```python
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](/deploy/django) has them. **Node**, with the built-in
`node:sqlite` (Node 24 or later):

```js
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](/deploy/go), [FastAPI](/deploy/fastapi),
[Django](/deploy/django), [Rails](/deploy/rails), [Laravel](/deploy/laravel),
and [Node.js](/deploy/nodejs). Follow one on this VM, and point it at
`~/data/app.db` or add its database to Litestream (see [Another
database](#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](/deploy/uptime-kuma)
or [Open WebUI](/deploy/open-webui), get the same protection: add their
database file to `~/sqlite/litestream.yml` (see [Another
database](#another-database)).

## Look inside over SSH

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

```bash
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.

```bash
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](/docs/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](/docs/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:

```bash
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](#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](https://garagehq.deuxfleurs.fr), 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:

```bash
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](/deploy/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](/deploy/postgres) 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](https://pgloader.io) 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.

<details id="create-the-vm" data-manual-setup class="dp-details">
<summary>Install by hand</summary>

### Create a VM

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

```bash
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:

```bash
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](#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:

```bash
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](#get-a-bucket) show the endpoint and region for each
provider):

```bash
(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:

```bash
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:

```bash
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:

```bash
~/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](#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:

```bash
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](#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](#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:

```bash
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:

```bash
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:

```bash
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:

```bash
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:

```bash
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](https://github.com/coleifer/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:

```bash
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:

```bash
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`.

```bash
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):

```bash
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.

</details>

<details id="back-up-and-restore" class="dp-details">
<summary>Back up and restore</summary>

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.

```bash
~/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:

```bash
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:

```bash
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](#create-the-vm) up to and including "Give Litestream your bucket",
with the same bucket and key, and instead of "Create the database":

```bash
~/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`:

```yaml
  - 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](#create-the-vm). Litestream uploads the existing database first.

</details>

<details id="update" class="dp-details">
<summary>Update</summary>

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](https://github.com/benbjohnson/litestream/releases),
then run [Install Litestream](#create-the-vm) with the new version in
`v=`, and `sudo systemctl restart litestream`. The package keeps the
override in `litestream.service.d`.

**sqlite-web**:

```bash
uv pip install -q --python ~/sqlite/web/venv/bin/python -U sqlite-web
sudo systemctl restart sqlite-web
```

</details>

<details id="troubleshooting" class="dp-details">
<summary>Something not working?</summary>

**`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.

</details>

## 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](#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](/docs/integrations-http-proxy) 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](/docs/vm-backups) need no key on the VM at all. On Amazon S3, exe.dev's [AWS integration](/docs/integrations-aws-wif) 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](https://exe.dev/pricing)) and for your bucket's storage, which for most databases fits in a provider's free tier.


## Related

- [Self-host PostgreSQL](https://exe.dev/deploy/postgres.md): Your own Postgres on a real disk, with backups you've actually restored.
- [Deploy a Go web app](https://exe.dev/deploy/go.md): One Go binary on a VM of its own. git push to deploy.
- [Deploy a FastAPI app](https://exe.dev/deploy/fastapi.md): Your API on a VM of its own. Push to deploy; the data stays put.
- [Deploy a Django app](https://exe.dev/deploy/django.md): Your Django app on a computer of its own. git push to deploy.
- [Deploy a Node.js app](https://exe.dev/deploy/nodejs.md): Your Express app on a server of its own. git push to deploy.
- [Dev, Test, Prod: Choose One, Two, or Three](https://exe.dev/deploy/dev-test-prod.md): You can build, test, and deploy on exe.dev.
