# 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

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

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

Or via environment variable:

```bash
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:

```bash
# 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:

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

### Automated Backups

Example cron job for daily backups:

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

## Docker Considerations

When running SQLite in Docker:

1. **Persistent volume** - Always mount a volume for the database directory:
   ```yaml
   volumes:
     - windshift-data:/data
   ```

2. **tmpfs for WAL** - In scratch-based containers, mount tmpfs for temporary files:
   ```yaml
   tmpfs:
     - /tmp
   ```

3. **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](/docs/02-deployment/03-postgres) 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