SQLite

SQLite is the default database for Windshift. It requires zero configuration - just start the binary and a database file is created automatically.

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) mode for concurrent read access. The following pragmas are applied automatically at startup:

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

These settings optimize for:

Concurrent reads - Multiple readers don't block each other
Write serialization - A single write connection prevents lock contention
Docker compatibility - Memory-mapped I/O is disabled since scratch containers may lack the required filesystem support

Connection Pooling

Windshift uses separate connection pools for reads and writes:

Pool	Default	Flag	Env Var
Read connections	120	`--max-read-conns`	`MAX_READ_CONNS`
Write connections	1	`--max-write-conns`	`MAX_WRITE_CONNS`

The single write connection is intentional - SQLite performs best with serialized writes. Increasing it is not recommended.

Backups

File Copy

The simplest backup method. Since WAL mode is enabled, you can safely copy the database while Windshift is running:

# Copy the database and 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:

Persistent volume - Always mount a volume for the database directory:
```
volumes:
  - windshift-data:/data
```
tmpfs for WAL - In scratch-based containers, mount tmpfs for temporary files:
```
tmpfs:
  - /tmp
```
File permissions - The container runs as UID 65534. Ensure the data directory is writable by this user.

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