Skip to main content

Back up SQLite

SQLite is a single file, but you should not just copy that file while the app is running — a copy taken mid-write can be corrupt. Use SQLite's online backup, which produces a consistent copy safely.

Prerequisites​

  • The sqlite3 CLI:
    • macOS: ships with the OS, or brew install sqlite
    • Debian/Ubuntu: sudo apt-get install sqlite3
  • Read access to the database file.

1. Sign in & create an API key​

You need a Lighthouse account, a workspace, and an API key before you can upload a backup.

  1. Sign in to the portal and claim your free 5 GB workspace — see Web Portal & Free Workspace.

  2. Create an API key scoped backup:write, backup:read, snapshots:read — see API Keys.

  3. Export the credentials so the upload step can read them:

    export LH_API_KEY="lh_xxxxxxxxxxxxxxxxxxxxxxxx"
    export LH_WORKSPACE_ID="your-workspace-uuid"

Already have a key? Continue to the backup step below.

2. Create the backup​

Create the local dump directory once:

mkdir -p ./db-dumps

Option A — .backup (recommended)​

Produces a consistent binary copy even while the database is in use:

sqlite3 /path/to/app.db ".backup './db-dumps/app.sqlite'"

This uses the online backup API, so it is safe against concurrent readers/writers.

Option B — .dump (portable SQL text)​

Emits a plain-SQL reconstruction — useful if you want a human-readable, diffable, cross-version backup:

sqlite3 /path/to/app.db ".dump" > ./db-dumps/app.sql
WAL mode

If your database uses Write-Ahead Logging (journal_mode=WAL), .backup already produces a consistent result. Avoid copying app.db together with stray -wal/-shm files manually.

3. Verify the backup​

ls -lh ./db-dumps/app.sqlite
sqlite3 ./db-dumps/app.sqlite "PRAGMA integrity_check;"   # should print: ok

4. Restore (recovery test)​

The .backup output is a database file — just open it:

sqlite3 ./db-dumps/app.sqlite "SELECT count(*) FROM sqlite_master;"

For the .dump SQL form, recreate the database:

sqlite3 ./restore/app.db < ./db-dumps/app.sql

5. Upload to Lighthouse​

Your backup is now in ./db-dumps. After creating an authenticated SDK client with the API key from step 1, upload the directory as a snapshot:

snapshot, err := client.Backup([]string{"./db-dumps"}, &sdktypes.BackupOptions{})
if err != nil {
	log.Fatal(err)
}
log.Printf("snapshotId=%s", snapshot.SnapshotID)

To run this on a schedule, use Automated backup with scheduling.

The backup command for this database, ready to drop into the scheduled job's make_dump():

sqlite3 /path/to/app.db ".backup './db-dumps/app.sqlite'"