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. Construct the SDK client with the API key from step 1 and upload the directory as a snapshot. This reads LH_API_KEY and LH_WORKSPACE_ID from the environment (exported in step 1):

package main

import (
	"log"
	"os"

	sdkclient "github.com/lighthouse-web3/baas-go-sdk/client"
	sdktypes "github.com/lighthouse-web3/baas-go-sdk/types"
)

func main() {
	client, err := sdkclient.NewBackupClient(sdkclient.BackupClientOptions{
		APIURL:      "https://baas-api.lighthouse.storage", // API host, not the portal
		APIKey:      os.Getenv("LH_API_KEY"),
		WorkspaceID: os.Getenv("LH_WORKSPACE_ID"),
	})
	if err != nil {
		log.Fatalf("client init: %v", err)
	}

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

Run it from the directory containing db-dumps:

go run .

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'"