Database

The Ellemment Stack uses SQLite with LiteFS for a powerful, distributed database solution that offers exceptional performance and simplicity.

Primary Instance Architecture

Overview

LiteFS implements a primary-replica architecture where:

• One "primary instance" handles all write operations
• Other instances serve as read-only replicas
• Fly's consul service manages primary instance selection
• Configuration prioritizes primary region for optimal performance

Regional Configuration

The primary region is configured in fly.toml:

primary_region = "sjc"

This setting determines where write operations occur. Choose a region closest to most of your users for optimal performance.

Instance Management

Check primary instance status:

fly status --app [YOUR_APP_NAME]

Deploy multiple regions:

# Deploy two instances in primary region
fly scale count 2 --region sjc
# Add replica in another region
fly scale count 1 --region ams

Database Access

Development Access

During development, connect via:

/litefs/data/sqlite.db

Production Access

Access the production database through Fly.io:

1. Direct CLI access:

fly ssh console -C database-cli

2. Prisma Studio access:

# Terminal 1: Start Prisma Studio
fly ssh console -C "npm run prisma:studio" --app [YOUR_APP_NAME]
,[object Object],
fly proxy 5556:5555 --app [YOUR_APP_NAME]

Then access Prisma Studio at http://localhost:5556

Primary Instance Selection

When working with multiple instances:

1. Identify primary:

fly status --app [YOUR_APP_NAME]

2. Connect to primary:

fly ssh console -C "npm run prisma:studio" -s --app [YOUR_APP_NAME]

Database Migrations

The stack uses Prisma for database migrations, executed only by the primary instance during deployment.

Zero-Downtime Migration Strategy

To ensure zero downtime during schema changes, follow the "widen then narrow" approach:

1. Widen Application

   • Add support for new schema
   • Maintain backward compatibility

2. Widen Database

   • Add new fields/tables
   • Maintain existing structure
   • Populate new fields

3. Narrow Application

   • Update code to use new schema
   • Remove old schema support

4. Narrow Database

   • Remove old fields/tables
   • Complete migration

Example: Name Field Split

Converting a name field to firstName and lastName:

1. Widen App:

const name = user.firstName && user.lastName 
  ? `${user.firstName} ${user.lastName}`
  : user.name

2. Widen Database:

ALTER TABLE User ADD COLUMN firstName TEXT;
ALTER TABLE User ADD COLUMN lastName TEXT;
UPDATE User SET 
  firstName = substr(name, 1, instr(name, ' ') - 1),
  lastName = substr(name, instr(name, ' ') + 1)
WHERE name IS NOT NULL;

3. Narrow App:

const name = `${user.firstName} ${user.lastName}`

4. Narrow Database:

ALTER TABLE User DROP COLUMN name;

Production Seeding

Initial Setup

Initialize production data through migration files:

1. Create seed script:

cp prisma/seed.ts ./prod-seed.local.ts

2. Generate test database:

DATABASE_URL=file:./seed.local.db npx prisma migrate reset --skip-seed --force

3. Run seed script:

DATABASE_URL=file:./seed.local.db npx tsx ./prod-seed.local.ts

4. Export data:

sqlite3 ./prisma/seed.local.db .dump > seed.local.sql

Backup Management

Create manual backups:

1. Access instance:

fly ssh console --app [YOUR_APP_NAME]

2. Create backup:

mkdir /backups
litefs export -name sqlite.db /backups/backup-$(date +%Y%m%d).db

3. Download backup:

fly ssh sftp get /backups/backup-*.db --app [YOUR_APP_NAME]

Database Restoration

1. Upload backup:

fly ssh sftp shell --app [YOUR_APP_NAME]
put backup-file.db

2. Import data:

fly ssh console --app [YOUR_APP_NAME]
litefs import -name sqlite.db /backup-file.db

Troubleshooting

Failed Migrations

If you encounter migration failures:

1. Option 1: Clean Reset

   • Suitable for development/staging
   • Delete and redeploy application
   • Loses all data

2. Option 2: Backup Restore

   • Requires recent backup
   • Restore backup after fixing migration
   • Maintains data integrity

3. Option 3: Manual Fix

   -- Remove failed migration
   DELETE FROM _prisma_migrations WHERE name = 'migration_name';

For more information about deployment configuration, check the deployment guide.