SQLite Operations

This page covers operational concerns for SQLite in production: LiteFS replication, connecting to the production database, backups, restoration, and troubleshooting.

LiteFS replication

LiteFS maintains a primary instance that handles all writes. All other instances are read replicas. The primary is determined by Fly's Consul service and is restricted to the primary region (configured in other/litefs.yml).

Deploy two instances in the primary region for zero-downtime deploys. Check which instance is primary:

fly status --app your-app-name

The primary instance has ROLE set to primary.

Scaling regions

For adding read replicas in additional regions, see the Fly deployment page.

Connecting to production

Connect to the live database via SSH:

fly ssh console -C database-cli

To use Prisma Studio on your deployed database, run these in two separate terminals:

# Terminal 1: Start Prisma Studio on the primary instance
fly ssh console -C "npm run prisma:studio" -s --app your-app-name

# Terminal 2: Proxy local port to Prisma Studio
fly proxy 5556:5555 --app your-app-name

Open http://localhost:5556 in your browser. Use the -s flag to select the primary instance when multiple instances are running.

Seeding production

The recommended approach for production seeding is to add INSERT statements directly to migration SQL files. For complex seeding:

  1. Create a seed script similar to prisma/seed.ts
  2. Create a temporary database and run migrations against it
  3. Run your seed script against the temporary database
  4. Dump the database with sqlite3 seed.db .dump
  5. Copy the INSERT statements into your migration SQL file
  6. Deploy and verify

Backups

LiteFS Cloud backups

LiteFS Cloud offers point-in-time recovery for the last 30 days with 5-minute granularity. Set it up:

  1. Create a LiteFS Cloud cluster in the Fly.io dashboard
  2. Set the token: fly secrets set LITEFS_CLOUD_TOKEN="..." --app your-app-name
  3. Restore backups from the LiteFS dashboard

Manual backups

# SSH into the instance
fly ssh console --app your-app-name

# Create and export backup
mkdir /backups
litefs export -name sqlite.db /backups/backup-2024-01-15.db
exit

# Download the backup
fly ssh sftp get /backups/backup-2024-01-15.db --app your-app-name

Store backups in a secure location. They contain user information and password hashes.

Manual restoration

Take a fresh backup before restoring. Then upload and import:

# Upload via SFTP
fly ssh sftp shell --app your-app-name
put backup-2024-01-15.db

# SSH in and restore
fly ssh console --app your-app-name
litefs import -name sqlite.db /backup-2024-01-15.db
exit

Troubleshooting: faulty migration

If a faulty migration was applied to production and you see migration failure in the logs, you have three options:

  1. No important data — delete the Fly app, fix/delete the migration, and redeploy
  2. Recent backup available — restore the backup, fix the migration, and redeploy
  3. Data must be preserved — comment out the exec section in litefs.yml, deploy to get SSH access, take a backup, remove the failed migration entry from _prisma_migrations, restore the cleaned backup, fix the migration, uncomment exec, and redeploy

To remove a failed migration entry directly:

DELETE FROM _prisma_migrations WHERE name = 'migration_name';