> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify.com/Finsys/dockhand/llms.txt
> Use this file to discover all available pages before exploring further.

# Database Configuration

> Configure SQLite or PostgreSQL for Dockhand

## Overview

Dockhand supports two database backends:

* **SQLite** (default) - Zero-configuration, single-file database
* **PostgreSQL** - Production-grade, scalable relational database

## SQLite (Default)

### Configuration

By default, Dockhand uses SQLite with no configuration required. The database is automatically created at:

```
$DATA_DIR/db/dockhand.db
```

Default: `/app/data/db/dockhand.db` (Docker) or `./data/db/dockhand.db` (local)

### Docker Deployment

```yaml docker-compose.yaml theme={null}
services:
  dockhand:
    image: fnsys/dockhand:latest
    ports:
      - 3000:3000
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - dockhand_data:/app/data  # Database stored in volume

volumes:
  dockhand_data:
```

### Advantages

<CardGroup cols={2}>
  <Card title="Zero Configuration" icon="magic">
    No setup required. Database created automatically on first run.
  </Card>

  <Card title="Simple Backups" icon="floppy-disk">
    Backup by copying a single file: `dockhand.db`
  </Card>

  <Card title="Lightweight" icon="feather">
    No separate database server. Minimal resource usage.
  </Card>

  <Card title="Portable" icon="truck">
    Move database by copying file. No export/import needed.
  </Card>
</CardGroup>

### Limitations

<Warning>
  SQLite has limitations for production workloads:

  * **Concurrency**: Single writer at a time
  * **Network**: No remote connections
  * **Scale**: Not suitable for high-traffic or multi-instance deployments
</Warning>

For production, use [PostgreSQL](#postgresql).

### Performance Tuning

Dockhand automatically enables these SQLite optimizations:

```javascript theme={null}
// Enabled by default in drizzle.ts:658-662
rawClient.pragma('journal_mode = WAL');        // Write-Ahead Logging
rawClient.pragma('synchronous = NORMAL');      // Balanced durability/speed
rawClient.pragma('busy_timeout = 5000');       // 5s wait on lock
```

**WAL Mode** (Write-Ahead Logging):

* Allows concurrent reads during writes
* Better performance than default rollback journal
* Creates `dockhand.db-wal` and `dockhand.db-shm` files

### Backup

Backup SQLite database:

```bash theme={null}
# Stop Dockhand
docker compose down

# Backup database file
cp $DATA_DIR/db/dockhand.db dockhand-backup-$(date +%Y%m%d).db

# Or use docker cp
docker run --rm -v dockhand_data:/data -v $(pwd):/backup alpine \
  cp /data/db/dockhand.db /backup/dockhand-backup.db

# Restart Dockhand
docker compose up -d
```

Restore backup:

```bash theme={null}
docker compose down
cp dockhand-backup.db $DATA_DIR/db/dockhand.db
docker compose up -d
```

## PostgreSQL

### Quick Start

Deploy Dockhand with PostgreSQL:

```yaml docker-compose-postgresql.yaml theme={null}
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: dockhand
      POSTGRES_PASSWORD: changeme
      POSTGRES_DB: dockhand
    volumes:
      - postgres_data:/var/lib/postgresql/data

  dockhand:
    image: fnsys/dockhand:latest
    ports:
      - 3000:3000
    environment:
      DATABASE_URL: postgres://dockhand:changeme@postgres:5432/dockhand
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - dockhand_data:/app/data
    depends_on:
      - postgres

volumes:
  postgres_data:
  dockhand_data:
```

Start:

```bash theme={null}
docker compose -f docker-compose-postgresql.yaml up -d
```

### Connection URL Format

```
postgres://[user]:[password]@[host]:[port]/[database][?options]
```

Examples:

```bash theme={null}
# Local PostgreSQL
DATABASE_URL=postgres://dockhand:secret@localhost:5432/dockhand

# Remote PostgreSQL
DATABASE_URL=postgres://admin:p4ssw0rd@192.168.1.100:5432/dockhand

# With SSL
DATABASE_URL=postgresql://user:pass@db.example.com/dockhand?sslmode=require

# AWS RDS
DATABASE_URL=postgres://admin:pass@dockhand.abc123.us-east-1.rds.amazonaws.com:5432/dockhand
```

### SSL/TLS Options

PostgreSQL connection string supports SSL modes:

```bash theme={null}
# Require SSL (recommended for remote databases)
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=require

# Verify certificate
DATABASE_URL=postgres://user:pass@host:5432/db?sslmode=verify-full

# Disable SSL (local only)
DATABASE_URL=postgres://user:pass@localhost:5432/db?sslmode=disable
```

### External PostgreSQL

Connect to an existing PostgreSQL instance:

```yaml docker-compose.yaml theme={null}
services:
  dockhand:
    image: fnsys/dockhand:latest
    ports:
      - 3000:3000
    environment:
      DATABASE_URL: postgres://dockhand:password@192.168.1.100:5432/dockhand
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - dockhand_data:/app/data

volumes:
  dockhand_data:
```

### Environment File

Store `DATABASE_URL` in `.env` file:

```bash .env theme={null}
DATABASE_URL=postgres://dockhand:secure-password@postgres:5432/dockhand
ENCRYPTION_KEY=your-32-byte-hex-key
```

```yaml docker-compose.yaml theme={null}
services:
  dockhand:
    # ...
    env_file:
      - .env
```

<Warning>
  Add `.env` to `.gitignore` to prevent committing database credentials.
</Warning>

### PostgreSQL Settings

Recommended PostgreSQL configuration:

```bash theme={null}
# postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
wal_buffers = 16MB
```

For Docker:

```yaml theme={null}
postgres:
  image: postgres:16-alpine
  command:
    - postgres
    - -c
    - max_connections=100
    - -c
    - shared_buffers=256MB
```

### Backup

Backup PostgreSQL database:

```bash theme={null}
# Using pg_dump
docker exec postgres pg_dump -U dockhand dockhand > dockhand-backup.sql

# Using docker compose
docker compose exec postgres pg_dump -U dockhand dockhand > dockhand-backup.sql

# Compressed backup
docker exec postgres pg_dump -U dockhand dockhand | gzip > dockhand-backup.sql.gz
```

Restore backup:

```bash theme={null}
# Restore from SQL
docker exec -i postgres psql -U dockhand dockhand < dockhand-backup.sql

# Restore from compressed
gunzip -c dockhand-backup.sql.gz | docker exec -i postgres psql -U dockhand dockhand
```

### Maintenance

Routine PostgreSQL maintenance:

```bash theme={null}
# Vacuum (reclaim space)
docker exec postgres psql -U dockhand dockhand -c "VACUUM ANALYZE;"

# Check database size
docker exec postgres psql -U dockhand dockhand -c "SELECT pg_size_pretty(pg_database_size('dockhand'));"

# List tables and sizes
docker exec postgres psql -U dockhand dockhand -c "
SELECT tablename, pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
"
```

## Migration Between Databases

### SQLite to PostgreSQL

1. **Export data from SQLite**:
   ```bash theme={null}
   # Dump schema and data
   sqlite3 dockhand.db .dump > dockhand-sqlite.sql
   ```

2. **Convert SQL to PostgreSQL**:
   ```bash theme={null}
   # Use pgloader or manual conversion
   # SQLite uses different syntax for some types
   ```

3. **Set `DATABASE_URL` and restart**:
   ```yaml theme={null}
   environment:
     DATABASE_URL: postgres://dockhand:password@postgres:5432/dockhand
   ```

<Note>
  **Recommendation**: Start fresh with PostgreSQL instead of migrating. Use Dockhand's Git integration or Compose file exports to preserve stack configurations.
</Note>

### PostgreSQL to SQLite

Downgrading from PostgreSQL to SQLite is not recommended. SQLite lacks features required for production workloads.

## Migrations

Dockhand automatically runs database migrations on startup.

### Migration Folders

* **SQLite**: `./drizzle/`
* **PostgreSQL**: `./drizzle-pg/`

Migrations are detected based on `DATABASE_URL`:

```typescript theme={null}
// From drizzle.ts:38-49
const isPostgres = !!(DATABASE_URL && 
  (DATABASE_URL.startsWith('postgres://') || 
   DATABASE_URL.startsWith('postgresql://')));

const migrationsFolder = isPostgres ? './drizzle-pg' : './drizzle';
```

### Migration Process

On startup, Dockhand:

1. Connects to database
2. Reads migration journal
3. Compares applied vs pending migrations
4. Runs pending migrations in order
5. Seeds initial data (registries, roles, settings)

Logs:

```
============================================================
DATABASE INITIALIZATION
============================================================
     Database: PostgreSQL
     Connection: postgres://dockhand:***@postgres:5432/dockhand
     Total migrations: 45
     Applied: 45
     Pending: 0
[OK] Database schema is up to date
[OK] Database initialized (PostgreSQL)
============================================================
```

### Migration Errors

If migrations fail, Dockhand exits by default:

```bash theme={null}
DB_FAIL_ON_MIGRATION_ERROR=true  # Default: exit on error
DB_FAIL_ON_MIGRATION_ERROR=false # Continue anyway (dangerous)
```

Skip migrations (debugging only):

```bash theme={null}
SKIP_MIGRATIONS=true
```

### Manual Migrations

Run migrations manually:

```bash theme={null}
# SQLite
npx drizzle-kit migrate

# PostgreSQL
DATABASE_URL=postgres://... npx drizzle-kit migrate
```

## Schema Health

Check database health via API:

```bash theme={null}
curl http://localhost:3000/api/health
```

Response:

```json theme={null}
{
  "healthy": true,
  "database": "postgresql",
  "connection": "postgres://dockhand:***@postgres:5432/dockhand",
  "migrationsTable": true,
  "appliedMigrations": 45,
  "pendingMigrations": 0,
  "schemaVersion": "0045_add_pending_updates",
  "tables": {
    "expected": 28,
    "found": 28,
    "missing": []
  }
}
```

## Troubleshooting

### Connection Refused

**Error**: `Error: connect ECONNREFUSED`

**Solution**:

* Check PostgreSQL is running: `docker compose ps postgres`
* Verify connection URL host matches service name
* Ensure `depends_on: postgres` in compose file

### Authentication Failed

**Error**: `password authentication failed for user "dockhand"`

**Solution**:

* Check `POSTGRES_PASSWORD` matches `DATABASE_URL` password
* Recreate PostgreSQL container if password changed

### Database Does Not Exist

**Error**: `database "dockhand" does not exist`

**Solution**:

```bash theme={null}
# Create database manually
docker exec postgres psql -U dockhand -c "CREATE DATABASE dockhand;"
```

Or ensure `POSTGRES_DB=dockhand` in postgres service.

### Migration Failed

**Error**: `MIGRATION FAILED: table already exists`

**Solution**:

```bash theme={null}
# Option 1: Reset database (DELETES ALL DATA)
docker compose down -v
docker compose up -d

# Option 2: Use emergency script
docker exec dockhand /app/scripts/emergency/reset-db.sh
```

### SQLite Locked

**Error**: `database is locked`

**Solution**:

* Stop all Dockhand instances accessing the database
* Ensure only one Dockhand container uses the SQLite file
* Migrate to PostgreSQL for multi-instance deployments

## Performance Comparison

| Feature     | SQLite          | PostgreSQL                 |
| ----------- | --------------- | -------------------------- |
| Setup       | Zero config     | Requires server            |
| Concurrency | Single writer   | Multiple writers           |
| Connections | Local only      | Remote supported           |
| Backup      | Copy file       | pg\_dump                   |
| Scale       | Small/medium    | Large/enterprise           |
| Resources   | \~50MB RAM      | \~256MB+ RAM               |
| Best For    | Single instance | Multi-instance, production |