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

docker-compose.yaml

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

Zero Configuration

No setup required. Database created automatically on first run.

Simple Backups

Backup by copying a single file: dockhand.db

Lightweight

No separate database server. Minimal resource usage.

Portable

Move database by copying file. No export/import needed.

Limitations

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

For production, use PostgreSQL.

Performance Tuning

Dockhand automatically enables these SQLite optimizations:

// 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:

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

docker compose down
cp dockhand-backup.db $DATA_DIR/db/dockhand.db
docker compose up -d

PostgreSQL

Quick Start

Deploy Dockhand with PostgreSQL:

docker-compose-postgresql.yaml

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:

docker compose -f docker-compose-postgresql.yaml up -d

Connection URL Format

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

Examples:

# 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:[e-mail]/dockhand?sslmode=require

# AWS RDS
DATABASE_URL=postgres://admin:[e-mail]:5432/dockhand

SSL/TLS Options

PostgreSQL connection string supports SSL modes:

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

docker-compose.yaml

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:

.env

DATABASE_URL=postgres://dockhand:secure-password@postgres:5432/dockhand
ENCRYPTION_KEY=your-32-byte-hex-key

docker-compose.yaml

services:
  dockhand:
    # ...
    env_file:
      - .env

Add .env to .gitignore to prevent committing database credentials.

PostgreSQL Settings

Recommended PostgreSQL configuration:

# postgresql.conf
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
wal_buffers = 16MB

For Docker:

postgres:
  image: postgres:16-alpine
  command:
    - postgres
    - -c
    - max_connections=100
    - -c
    - shared_buffers=256MB

Backup

Backup PostgreSQL database:

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

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

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

   # Dump schema and data
   sqlite3 dockhand.db .dump > dockhand-sqlite.sql
   

2. Convert SQL to PostgreSQL:

   # Use pgloader or manual conversion
   # SQLite uses different syntax for some types
   

3. Set DATABASE_URL and restart:

   environment:
     DATABASE_URL: postgres://dockhand:password@postgres:5432/dockhand
   

Recommendation: Start fresh with PostgreSQL instead of migrating. Use Dockhand’s Git integration or Compose file exports to preserve stack configurations.

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:

// 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:

DB_FAIL_ON_MIGRATION_ERROR=true  # Default: exit on error
DB_FAIL_ON_MIGRATION_ERROR=false # Continue anyway (dangerous)

Skip migrations (debugging only):

SKIP_MIGRATIONS=true

Manual Migrations

Run migrations manually:

# SQLite
npx drizzle-kit migrate

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

Schema Health

Check database health via API:

curl http://localhost:3000/api/health

Response:

{
  "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:

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

# 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