# Database Configuration

Configure MyGarage to use SQLite (default) or PostgreSQL for data storage. Learn about migration, backup, and performance optimization.

---

## Database Options

MyGarage supports two database backends:

| Database | Use Case | Performance | Setup Complexity | Multi-User Support |
|----------|----------|-------------|------------------|-------------------|
| **SQLite** | Single-user, homelab, <1000 records | Good | ✅ Zero config | ⚠️ Limited |
| **PostgreSQL** | Several users writing at once, or you already run PostgreSQL | Excellent | ⚠️ Requires setup | ✅ Full |

---

## SQLite (Default)

### Overview

- **File-based database** - No separate database server required
- **Zero configuration** - Works out of the box
- **Lightweight** - Single `.db` file
- **Best for**: Single-user deployments, small garages (<5 vehicles), low traffic

### Default Configuration

MyGarage automatically creates SQLite database on first run:

**Database Location**:
```
/data/mygarage.db
```

**Docker Volume Mapping**:
```yaml
volumes:
  - ./data:/data  # mygarage.db stored here
```

### SQLite Advantages

✅ **Zero setup** - No configuration needed
✅ **Simple backup** - Copy single file
✅ **Low resource usage** - <50MB RAM
✅ **Portable** - Move `.db` file between systems

### SQLite Limitations

⚠️ **Concurrent writes** - Only one write operation at a time (readers not blocked)
⚠️ **Network access** - Cannot be accessed remotely (file-based)
⚠️ **Large datasets** - Performance degrades >10,000 service records
⚠️ **Multi-user** - Limited concurrent user support

### When to Use SQLite

✅ **Recommended**:
- Single user or family (2-4 users)
- Small vehicle garage (1-10 vehicles)
- Low traffic (< 100 requests/day)
- Homelab/personal use

❌ **Not Recommended**:
- 10+ concurrent users
- Large garage (50+ vehicles)
- High traffic (thousands of requests/day)

---

## PostgreSQL

### Overview

- **Client-server database** - Separate database container/server
- **Best for**: several people writing at once, large garages, or a PostgreSQL server you already run and back up

### PostgreSQL Advantages

✅ **Concurrent access** - Many writers at once without lock waits
✅ **Reliability** - ACID compliance, data integrity
✅ **Tooling** - Your existing `pg_dump`, replication and monitoring

### PostgreSQL Setup

#### Docker Compose (Recommended)

**Full `docker-compose.yml` with PostgreSQL**:

```yaml
services:
  postgres:
    image: postgres:16-alpine
    container_name: mygarage-postgres
    environment:
      POSTGRES_DB: mygarage
      POSTGRES_USER: mygarage
      POSTGRES_PASSWORD: CHANGEME_SECURE_PASSWORD
    volumes:
      - postgres_data:/var/lib/postgresql/data
    restart: unless-stopped
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U mygarage"]
      interval: 10s
      timeout: 5s
      retries: 5

  mygarage:
    image: ghcr.io/homelabforge/mygarage:latest
    container_name: mygarage
    depends_on:
      postgres:
        condition: service_healthy
    ports:
      - "8686:8686"
    volumes:
      - ./data:/data
    environment:
      - MYGARAGE_DATABASE_URL=postgresql+asyncpg://mygarage:CHANGEME_SECURE_PASSWORD@postgres:5432/mygarage
    restart: unless-stopped

volumes:
  postgres_data:
```

**Start Services**:
```bash
docker-compose up -d
```

#### External PostgreSQL Server

If you have existing PostgreSQL server:

**MyGarage Configuration**:
```yaml
services:
  mygarage:
    image: ghcr.io/homelabforge/mygarage:latest
    environment:
      - MYGARAGE_DATABASE_URL=postgresql+asyncpg://username:password@postgres-server:5432/mygarage
```

**Connection String Format**:
```
postgresql+asyncpg://username:password@hostname:port/database
```

**Components**:
- `postgresql+asyncpg` - Driver (required for async support)
- `username` - PostgreSQL user
- `password` - User's password
- `hostname` - PostgreSQL server address (or container name)
- `port` - PostgreSQL port (default: 5432)
- `database` - Database name

#### PostgreSQL Database Creation

If using existing PostgreSQL server, create database first:

```sql
CREATE DATABASE mygarage;
CREATE USER mygarage WITH PASSWORD 'secure-password-here';
GRANT ALL PRIVILEGES ON DATABASE mygarage TO mygarage;
```

Or using psql:
```bash
docker exec -it postgres-container psql -U postgres
```

```sql
CREATE DATABASE mygarage;
CREATE USER mygarage WITH ENCRYPTED PASSWORD 'secure-password';
GRANT ALL PRIVILEGES ON DATABASE mygarage TO mygarage;
\q
```

#### Server Timezone

From 3.10.0, MyGarage pins every PostgreSQL session to UTC, so the server's own timezone (a `TZ` on its container, or `timezone` in `postgresql.conf`) no longer matters. Times show in your household's timezone in the app.

Before 3.10.0, a server that wasn't on UTC stored the times the database stamps itself, like most created and updated times, in its own zone. On the first start of 3.10.0, migration 134 converts those stored times to UTC once. Seven columns the app also writes itself (such as users' and vehicles' updated times) are left as they are, since old rows there can't be told apart. A server already on UTC has nothing to convert.

- **Back up first.** Migrations 134 and 135 are FATAL: if either fails, MyGarage refuses to start. On PostgreSQL, any migration that fails before 134 has run also stops startup.
- **`PGTZ` or `PGOPTIONS` in MyGarage's environment:** if the session zone came from MyGarage's side, 134 converts nothing, logs a warning naming the zone, and doesn't run again. Times from before the upgrade stay in that zone. To have them converted, restore the backup, take the zone setting out of MyGarage's environment, and start 3.10.0 again.

---

## Migrating from SQLite to PostgreSQL

### Why Migrate?

- Growing number of users (5+)
- Large dataset (1,000+ service records)
- Performance issues (slow queries)
- Need concurrent write access

### Migration Steps

MyGarage has no built-in way to move your data from SQLite to PostgreSQL:

- The **Full Data Backup** in **Settings → Backup & Restore** restores only into the same kind of database. On PostgreSQL the restore is done with `pg_restore`, not from the app.
- A vehicle's JSON **Export** and **Import** (on the vehicle page) carry only some of its records, and no photos, documents or settings.

To start on PostgreSQL, point `MYGARAGE_DATABASE_URL` at an empty database and restart. MyGarage creates its tables on first startup.

#### Database Migration Tool

**Using pgloader** (advanced, not tested with MyGarage):

```bash
# Install pgloader
docker run --rm -it dimitri/pgloader:latest pgloader \
  sqlite:///data/mygarage.db \
  postgresql://mygarage:password@postgres:5432/mygarage
```

**Note**: May require schema adjustments.

---

## Migrating from PostgreSQL to SQLite

There is no built-in way to move data from PostgreSQL back to SQLite.

---

## Database Backups

### SQLite Backup

> SQLite runs in WAL mode, so copying `mygarage.db` while the container is running is a torn copy (it misses committed data in the `-wal` sidecar). Stop the container first, or use **Settings → Backup & Restore** (admins only), which snapshots via SQLite's Online Backup API.

**Method 1: File Copy (Simple)**:

```bash
# Stop MyGarage first to ensure clean backup
docker-compose stop mygarage

# Copy database file
cp data/mygarage.db data/mygarage-backup-2024-12-02.db

# Restart MyGarage
docker-compose start mygarage
```

**Automated Backup Script**:

```bash
#!/bin/bash
# backup-mygarage.sh

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/mygarage"
mkdir -p "$BACKUP_DIR"

# WAL-safe online backup (no container stop needed). The image has no sqlite3
# command, so this uses Python's sqlite3 module.
docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').backup(sqlite3.connect('/data/backup-$DATE.db'))"
docker cp "mygarage:/data/backup-$DATE.db" "$BACKUP_DIR/mygarage-$DATE.db"
docker exec mygarage rm "/data/backup-$DATE.db"

# Keep last 30 days of backups
find "$BACKUP_DIR" -name "mygarage-*.db" -mtime +30 -delete
```

**Cron Job** (daily backups at 2 AM):
```bash
0 2 * * * /path/to/backup-mygarage.sh
```

### PostgreSQL Backup

**Method 1: pg_dump**:

```bash
# Backup to SQL file
docker exec mygarage-postgres pg_dump -U mygarage mygarage > mygarage-backup.sql

# Backup to compressed file
docker exec mygarage-postgres pg_dump -U mygarage mygarage | gzip > mygarage-backup.sql.gz
```

**Method 2: pg_dumpall** (includes users, roles):

```bash
docker exec mygarage-postgres pg_dumpall -U postgres > mygarage-full-backup.sql
```

**Automated PostgreSQL Backup**:

```bash
#!/bin/bash
# backup-postgres.sh

DATE=$(date +%Y-%m-%d)
BACKUP_DIR="/backups/mygarage"
mkdir -p "$BACKUP_DIR"

# Dump database
docker exec mygarage-postgres pg_dump -U mygarage mygarage | gzip > "$BACKUP_DIR/mygarage-$DATE.sql.gz"

# Keep last 30 days
find "$BACKUP_DIR" -name "mygarage-*.sql.gz" -mtime +30 -delete
```

**Cron Job**:
```bash
0 2 * * * /path/to/backup-postgres.sh
```

### Backups and secret settings

Secret settings (SSO client secret, notification tokens, API keys and the like) are stored encrypted, and every backup carries them encrypted: the settings backup (version 2.1), the full backup, and the `mygarage.db` copies and `pg_dump`s above. None of them holds the key, `settings.key`, or the login key, `secret.key`. Back those up separately; see [Authentication - Settings Encryption Key](Authentication.md#settings-encryption-key).

- **Restoring on a new host:** copy `settings.key` over first. Without it, a full restore brings every secret back unreadable, and Settings names them so you can re-enter them. Expect one sign-in, since `secret.key` doesn't travel either.
- **Settings restore:** a secret that no configured key opens is skipped. It keeps its current value, and the restore's warning names it. A secret under an older key still in the keyring is re-encrypted under the current one. An older settings backup (version 2.0) holds secrets in plaintext; the restore encrypts them.
- **Refused backups:** while a secret is still stored unencrypted and no key can encrypt it, creating or restoring a backup answers 503 and writes nothing. The Backup tab shows the fix: set `MYGARAGE_SETTINGS_KEY`, or make the data directory writable.

**Old plaintext inside the database files.** Copies of the old values can survive the upgrade: in SQLite's free space until a `VACUUM` (see [SQLite Maintenance](#sqlite-maintenance); run it once if the file may leave the host), and in PostgreSQL's files and WAL until they're reused (`pg_dump` doesn't carry them). Delete database copies taken before the upgrade, or keep them as safely as the live data.

---

## Restoring Backups

### Restore SQLite

**From Settings → Backup & Restore** (admins only):

Pick a full backup and choose **Restore**. MyGarage checks the backup, stages it, and keeps running on the current data. **Restart MyGarage to finish the restore.** Anything changed before the restart is replaced by the backup, and an older backup is migrated on that start.

- A backup whose database is damaged, or that has no database, is refused before anything is staged.
- Until the restart, the Backup tab shows the staged restore with a **Cancel restore** button. Without the tab, deleting `data/.restore-pending/manifest.json` cancels it too, and the next start throws the rest of the staging away.
- The restore request saves `mygarage-full-safety-<time>.tar.gz` in `data/backups/`, and the restart saves `mygarage-full-safety-prerestore-<time>.tar.gz`, a full backup of the data it replaces. Restore either one like any other backup to go back.
- Until the restart, a second copy of the restored data sits in `/data`, so check the disk has room for it.
- The `photos`, `documents` and `attachments` folders must be on the same filesystem as `/data` itself. If one is mounted separately, the restore is refused; restore that backup by file copy instead.
- A photo, document or attachment that was a link to a folder or to a file outside the backup is left out, and the log names it.
- The first start after restoring a large photo library can be slow: it backs up and swaps every file before it serves. The container reads unhealthy until it finishes. `restart: unless-stopped` acts on the exit code, not health, so just wait.
- If a start can't get past a restore (for example, the disk filled up halfway), MyGarage refuses to start, and its log says how to finish the restore, cancel it, or go back to the pre-restore safety backup.

**From File Copy**:
```bash
# Stop MyGarage
docker-compose stop mygarage

# Replace database file and clear stale WAL/SHM sidecars
cp mygarage-backup-2024-12-02.db data/mygarage.db
rm -f data/mygarage.db-wal data/mygarage.db-shm

# Restart MyGarage
docker-compose start mygarage
```

### Restore PostgreSQL

**From pg_dump**:

```bash
# Drop existing database (WARNING: deletes all data!)
docker exec mygarage-postgres psql -U postgres -c "DROP DATABASE mygarage;"

# Recreate database
docker exec mygarage-postgres psql -U postgres -c "CREATE DATABASE mygarage OWNER mygarage;"

# Restore from backup
docker exec -i mygarage-postgres psql -U mygarage mygarage < mygarage-backup.sql

# Or from gzip
gunzip < mygarage-backup.sql.gz | docker exec -i mygarage-postgres psql -U mygarage mygarage
```

---

## Database Maintenance

### SQLite Maintenance

The image has no `sqlite3` command, so these use Python's `sqlite3` module.

**Vacuum (Optimize)**:

Reclaim unused space and defragment:

```bash
docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').execute('VACUUM')"
```

**Analyze (Update Statistics)**:

```bash
docker exec mygarage python3 -c "import sqlite3; sqlite3.connect('/data/mygarage.db').execute('ANALYZE')"
```

**Check Integrity**:

```bash
docker exec mygarage python3 -c "import sqlite3; print(sqlite3.connect('/data/mygarage.db').execute('PRAGMA integrity_check').fetchone()[0])"
```

### PostgreSQL Maintenance

**Vacuum (Reclaim Space)**:

```bash
docker exec mygarage-postgres psql -U mygarage -c "VACUUM FULL;"
```

**Analyze (Update Statistics)**:

```bash
docker exec mygarage-postgres psql -U mygarage -c "ANALYZE;"
```

**Reindex (Rebuild Indexes)**:

```bash
docker exec mygarage-postgres psql -U mygarage -d mygarage -c "REINDEX DATABASE mygarage;"
```

**Check Database Size**:

```bash
docker exec mygarage-postgres psql -U mygarage -c "SELECT pg_size_pretty(pg_database_size('mygarage'));"
```

---

## Performance Optimization

### SQLite Optimization

MyGarage enables WAL mode, a 30-second `busy_timeout`, and `foreign_keys=ON` automatically on every connection (since v2.31). No manual PRAGMA tuning is required.

### PostgreSQL Optimization

**Connection Pooling**:

MyGarage uses SQLAlchemy connection pooling by default.

The PostgreSQL connection pool is fixed in code at 5 base connections plus 10 overflow, with `pool_pre_ping` enabled. It is not configurable via environment variables.

**PostgreSQL Configuration** (`postgresql.conf`):

```ini
# Memory
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 16MB
maintenance_work_mem = 64MB

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 16MB

# Query Planning
random_page_cost = 1.1  # SSD
```

**Indexes**: MyGarage creates its own indexes when it creates its tables. No manual indexes are needed.

---

## Database Monitoring

### SQLite Monitoring

**Check Database Size**:

```bash
ls -lh data/mygarage.db
```

**Row Counts**:

```bash
docker exec mygarage python3 -c "import sqlite3; c=sqlite3.connect('/data/mygarage.db'); [print(t, c.execute(f'SELECT COUNT(*) FROM {t}').fetchone()[0]) for t in ('vehicles', 'service_visits', 'fuel_records')]"
```

### PostgreSQL Monitoring

**Connection Count**:

```bash
docker exec mygarage-postgres psql -U mygarage -c "SELECT count(*) FROM pg_stat_activity WHERE datname='mygarage';"
```

**Table Sizes**:

```bash
docker exec mygarage-postgres psql -U mygarage -d mygarage -c "
SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;
"
```

**Slow Queries** (requires `pg_stat_statements` extension):

```sql
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
```

---

## Database Security

### SQLite Security

- ✅ **File permissions** - Ensure only MyGarage container can access `.db` file
- ✅ **Backup encryption** - Encrypt backup files before storing remotely
- ⚠️ **No built-in encryption** - SQLite stores data in plain text

### PostgreSQL Security

**Strong Passwords**:
```yaml
environment:
  POSTGRES_PASSWORD: "Use-Long-Random-Password-Here-32-Chars-Min"
```

**SSL/TLS Connections** (for remote PostgreSQL):

Leave SSL options out of the URL and set `PGSSLMODE` instead. MyGarage runs its migrations through a second driver, which refuses `?ssl=require` in the URL, so the migrations would not run.

```yaml
environment:
  - MYGARAGE_DATABASE_URL=postgresql+asyncpg://user:pass@host:5432/mygarage
  - PGSSLMODE=require
```

**Network Isolation** (Docker):

```yaml
networks:
  mygarage-internal:
    internal: true  # No external access

services:
  postgres:
    networks:
      - mygarage-internal  # Only accessible by MyGarage
```

**Restrict PostgreSQL Access** (`pg_hba.conf`):

```
# Only allow MyGarage container
host    mygarage    mygarage    172.20.0.0/16    md5
```

---

## Troubleshooting

### "Database is locked" (SQLite)

WAL mode and a 30-second `busy_timeout` are enabled automatically (v2.30), so this should not occur under normal use. If it does, the data volume is likely on a network filesystem (NFS/SMB) that doesn't support SQLite locking, or the disk is full. Use local storage and check `df -h`.

### "Connection refused" (PostgreSQL)

**Cause**: PostgreSQL container not running or wrong hostname

**Fix**:
```bash
# Check PostgreSQL container status
docker ps | grep postgres

# Check connection
docker exec mygarage-postgres psql -U mygarage -c "SELECT 1;"

# Verify hostname in DATABASE_URL matches container name
```

### "Authentication failed" (PostgreSQL)

**Cause**: Wrong username/password in connection string

**Fix**:
1. Verify `POSTGRES_USER` and `POSTGRES_PASSWORD` in docker-compose.yml
2. Ensure `MYGARAGE_DATABASE_URL` uses same credentials
3. Reset PostgreSQL password:
   ```bash
   docker exec mygarage-postgres psql -U postgres -c "ALTER USER mygarage WITH PASSWORD 'new-password';"
   ```

### Slow Queries

**SQLite**:
- Run `VACUUM` and `ANALYZE`
- Consider migrating to PostgreSQL

**PostgreSQL**:
- Check indexes exist
- Run `VACUUM ANALYZE`
- Increase `shared_buffers` in postgresql.conf
- Review slow query log

---

## Need Help?

- **Database Errors** - See [Troubleshooting Guide](Troubleshooting.md#database-issues)
- **Migration Questions** - [FAQ](FAQ.md#database-questions)
- **Community** - [GitHub Discussions](https://github.com/homelabforge/mygarage/discussions)

---

**Next**: Configure [Reverse Proxy](Reverse-Proxy) or read [FAQ](FAQ).