SQLite

SQLite is Windshift's default database. Start the binary and Windshift creates the database file.

Default Configuration

# Uses windshift.db in the current directory
./windshift

# Specify a custom path
./windshift --db /var/lib/windshift/data.db

Or via environment variable:

export DB_PATH=/var/lib/windshift/data.db
./windshift

WAL Mode

Windshift configures SQLite with Write-Ahead Logging (WAL) for concurrent reads. At startup, it applies these pragmas:

journal_mode    = WAL
synchronous     = NORMAL
foreign_keys    = ON
busy_timeout    = 5000
txlock          = immediate
temp_store      = MEMORY
mmap_size       = 0         (disabled for Docker compatibility)
journal_size_limit = 6144000

These settings optimize for:

• Concurrent reads: Multiple readers do not block each other.
• Write serialization: One write connection prevents lock contention.
• Docker compatibility: Windshift disables memory-mapped I/O because scratch containers may lack the required filesystem support.

Connection Pooling

Windshift uses separate connection pools for reads and writes:

Windshift uses one write connection because SQLite performs best with serialized writes. Do not increase this setting.

Backups

Cold File Copy

Stop Windshift before you make a file-copy backup. A copy made while the server runs can capture the database, WAL, and shared-memory files at different times. The backup can then be inconsistent.

# Stop Windshift first, then copy the database and its WAL files.
cp /data/windshift.db /backup/windshift.db
cp /data/windshift.db-wal /backup/windshift.db-wal
cp /data/windshift.db-shm /backup/windshift.db-shm

SQLite Backup Command

For a consistent snapshot:

sqlite3 /data/windshift.db ".backup /backup/windshift.db"

Automated Backups

Example cron job for daily backups:

0 2 * * * sqlite3 /data/windshift.db ".backup /backup/windshift-$(date +\%Y\%m\%d).db"

Docker Considerations

When running SQLite in Docker:

1. Persistent volume: Mount a volume for the database directory:

   volumes:
     - windshift-data:/data

2. tmpfs for /tmp: The scratch image has no /tmp directory. If you enable coding agents, mount a tmpfs for large uploads and git scratch space:

   tmpfs:
     - /tmp:exec,size=64M

   The exec option is required. See The /tmp tmpfs mount in the Docker guide.

3. File permissions: The container runs as UID 65534. Make sure this user can write to the data directory.

When to Use PostgreSQL

SQLite works well for most teams. Consider switching to PostgreSQL if you need:

• Heavy concurrent write workloads
• Replication or high-availability setups
• Integration with existing PostgreSQL infrastructure
• Deployments with more than ~50 concurrent users writing simultaneously