Database Configuration

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:[email protected]:5432/dockhand

# With SSL
DATABASE_URL=postgresql://user:[email protected]/dockhand?sslmode=require

# AWS RDS
DATABASE_URL=postgres://admin:[email protected]: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:[email protected]: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

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

Export data from SQLite:

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

Convert SQL to PostgreSQL:

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

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:

Connects to database
Reads migration journal
Compares applied vs pending migrations
Runs pending migrations in order
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

Overview

Getting Started

Core Features

Advanced Features

Authentication & Security

Deployment

Integrations

​Overview

​SQLite (Default)

​Configuration

​Docker Deployment

​Advantages

Zero Configuration

Simple Backups

Lightweight

Portable

​Limitations

​Performance Tuning

​Backup

​PostgreSQL

​Quick Start

​Connection URL Format

​SSL/TLS Options

​External PostgreSQL

​Environment File

​PostgreSQL Settings

​Backup

​Maintenance

​Migration Between Databases

​SQLite to PostgreSQL

​PostgreSQL to SQLite

​Migrations

​Migration Folders

​Migration Process

​Migration Errors

​Manual Migrations

​Schema Health

​Troubleshooting

​Connection Refused

​Authentication Failed

​Database Does Not Exist

​Migration Failed

​SQLite Locked

​Performance Comparison

Build docs developers (and LLMs) love

Overview

SQLite (Default)

Configuration

Docker Deployment

Advantages

Limitations

Performance Tuning

Backup

PostgreSQL

Quick Start

Connection URL Format

SSL/TLS Options

External PostgreSQL

Environment File

PostgreSQL Settings

Backup

Maintenance

Migration Between Databases

SQLite to PostgreSQL

PostgreSQL to SQLite

Migrations

Migration Folders

Migration Process

Migration Errors

Manual Migrations

Schema Health

Troubleshooting

Connection Refused

Authentication Failed

Database Does Not Exist

Migration Failed

SQLite Locked

Performance Comparison