Docker Database Access — Coolify / Same-Host Pattern

Access and manipulate databases running in Docker containers (PostgreSQL, MariaDB, SQLite) from the same host where Docker is running. This avoids API token setup entirely for administration tasks.

Prerequisites

• Docker installed and running on the same host
• Permission to run docker exec on the database container
• Coolify (optional): Use the Hermes Coolify MCP tools to find the service UUID first

Workflow Overview

Coolify MCP (find service)
  → Docker inspect (get credentials from env)
  → Docker exec / psql (run SQL)
  → Update config files / create API tokens

Phase 1: Find the Database Container

Via Coolify MCP (if available)

# List all services
result = mcp_coolify_list_services()
# Find your service (e.g., "vikunja" → UUID: h0o40g8kcws8wwoo8kk8o00g)

# Get service details to confirm
result = mcp_coolify_get_service(uuid="h0o40g8kcws8wwoo8kk8o00g")
# Look for: service_type = "vikunja-with-postgresql" (indicates bundled DB)

Via Docker (direct)

# List all running containers
docker ps --format "table {{.ID}}\t{{.Names}}\t{{.Image}}"

# Find PostgreSQL/MariaDB/MySQL containers
docker ps --format "{{.Names}} {{.Image}}" | grep -i -E "postgres|mariadb|mysql|database"

Typical Coolify PostgreSQL names: postgresql-<service_uuid> or <project>-postgresql-<id>

Phase 2: Extract Database Credentials

From Docker Environment Variables

# Dump all env vars (JSON format is safest)
docker inspect <container_name> --format '{{json .Config.Env}}'

Output example:

[
  "POSTGRES_USER=bf",
  "POSTGRES_PASSWORD=4hRpbb...geKA",
  "POSTGRES_DB=vikunjadb",
  "VIKUNJA_DATABASE_USER=bf",
  "VIKUNJA_DATABASE_PASSWORD=4hRpbb...geKA"
]

Key env vars to extract:

Docker Network IPs

# Get IP address and aliases for the database container
docker inspect <container_name> --format '{{range .NetworkSettings.Networks}}{{.IPAddress}} {{.Aliases}}{{end}}'

Each container has its own IP on the Docker bridge network (e.g., 10.0.8.3). Use this IP + internal port for connections.

# Also get the associated app container (same network)
docker inspect <app_container_name> --format '{{range .NetworkSettings.Networks}}{{.IPAddress}} {{.Aliases}}{{end}}'

Phase 3: Connect to PostgreSQL (Password with Special Chars)

⚠️ Critical: Password Escaping

PostgreSQL passwords in Coolify often contain special characters (!, ?, #, $, &, etc.) that break shell quoting. NEVER use inline `PGPASSWORD=*** ls” - all special chars cause bash expansion issues.

✅ Method A: PGPASSFILE (Recommended)

# Write pgpass file inside the container
docker exec -i <container> sh << 'HEREDOC'
echo "10.0.8.3:5432:vikunjadb:bf:YOUR_PASSWORD_HERE" > /tmp/.pgpass
chmod 600 /tmp/.pgpass
export PGPASSFILE=/tmp/.pgpass
psql -h 10.0.8.3 -U bf -d vikunjadb -c "SELECT 1;" -t -A
HEREDOC

The key is using a heredoc (<< 'HEREDOC') to avoid shell interpretation of special chars in the password.

✅ Method B: Base64 SQL Injection (For complex SQL with single quotes)

When your SQL contains single quotes (e.g., inserting bcrypt hashes, JSON strings), use base64 encoding to bypass ALL shell escaping:

# 1. Build your SQL
SQL="INSERT INTO table (col) VALUES ('value_with_'quotes'');"

# 2. Base64 encode it
SQL_B64=$(echo "$SQL" | base64 -w0)

# 3. Execute inside container — base64 decode → pipe to psql
docker exec <container> sh -c '
echo "10.0.8.3:5432:db:user:pass" > /tmp/pg && chmod 600 /tmp/pg
export PGPASSFILE=/tmp/pg
echo "'"$SQL_B64"'" | base64 -d > /tmp/s.sql
psql -h 10.0.8.3 -U user -d db -f /tmp/s.sql -t -A
'

❌ Method C: Avoid — PGPASSWORD inline via sh -c

# DANGER: This WILL break with !, $, and other special chars
docker exec <container> sh -c 'PGPASSWORD=My!Pass123 psql -U user -d db -c "SELECT 1;"'

Using Python’s execute_code (Cleaner for Complex Operations)

from hermes_tools import terminal
import json, base64

# Read password from file
result = terminal("cat /tmp/password_file")
pg_pass = result['output'].strip()

# Build SQL safely
sql = f"INSERT INTO table VALUES ('{value}');"
sql_b64 = base64.b64encode(sql.encode()).decode()

# Execute via Docker
cmd = f"sh -c 'export PGPASSFILE=/tmp/pg && echo \"{sql_b64}\" | base64 -d > /tmp/s.sql && psql -h IP -U user -d db -f /tmp/s.sql'"

result = terminal(f"docker exec <container> {cmd}")
print(result['output'])

Phase 4: Schema Exploration

List All Tables

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' ORDER BY table_name;

Explore Table Columns

SELECT column_name, data_type FROM information_schema.columns
WHERE table_name = 'mytable' ORDER BY ordinal_position;

Quick Row Count

SELECT MAX(id) as total FROM tasks WHERE project_id = 19;

Phase 5: Common Operations by Target App

Vikunja (v1.0.0-rc2+)

Schema reference: See references/vikunja-db-schema.md for complete table details.

Key tables for task management:

• users — user accounts (id, username, email, password)
• projects — all projects (id, title, owner_id)
• tasks — all tasks (id, title, description, done, priority, due_date, project_id)
• task_buckets — task ↔ bucket mapping (task_id, bucket_id)
• buckets — kanban columns (id, title, project_view_id, position)
• project_views — project views/boards (id, title, project_id, view_kind)
• labels — labels (id, title, hex_color, created_by_id)
• label_tasks — label ↔ task mapping (task_id, label_id)
• api_tokens — API tokens (id, title, token_salt, token_hash, token_last_eight, owner_id)

Create tasks:

INSERT INTO tasks (title, description, done, priority, project_id, created_by_id, created)
VALUES ('Task title', 'Description', false, 1, 19, 3, NOW())
RETURNING id;

Assign to bucket:

INSERT INTO task_buckets (task_id, bucket_id) VALUES (999, 56);

Add labels:

INSERT INTO label_tasks (task_id, label_id, created) VALUES (999, 14, NOW());

API token hash format: blake2b(full_token, digest_size=50) where full_token = <10-char-salt><32-char-random><8-char-suffix>. Token is 50 chars total, hash is 100 hex chars.

Reset user password:

UPDATE users SET password = '<bcrypt_hash>' WHERE id = 3;

Generate bcrypt: python3 -c "import bcrypt; print(bcrypt.hashpw('password'.encode(), bcrypt.gensalt(rounds=11, prefix=b'2a')).decode())"

Other self-hosted apps

Each app has its own schema. The general approach is the same:

1. Find the DB container via Docker
2. Extract credentials from env vars
3. Connect and explore schema
4. Manipulate as needed

Pitfalls & Gotchas

1. Passwords with special chars (!, $, &, #, ', ") break shell quoting — always use PGPASSFILE inside the container or base64 encoding
2. Docker inspect --format may truncate long values — write to temp file for inspection: docker inspect ... > /tmp/env.json && python3 -c "import json; ..."
3. No python3 inside postgres containers — it’s minimal (only sh + psql + base64). Use sh commands only
4. Internal Docker IPs change on restart — always check current IP before connecting
5. PostgreSQL containers often have no port bindings — connect via Docker network (internal IP), not localhost
6. Use -t -A flags for clean psql output when scripting: -t removes headers, -A removes alignment padding
7. JWT tokens vs API tokens — Vikunja’s JWT (from /login) signs with VIKUNJA_SERVICE_JWTSECRET but may not work for API access if the secret at runtime differs from the env var. Use DB-level API token creation instead.
8. JSON columns in PostgreSQL require properly escaped double quotes. Use bash heredoc (<< 'EOF') or Python to build the SQL.
9. Coolify agent SSH unreachable -> standalone DB down — If Coolify shows “Underlying server is not functional”, standalone databases can’t be started via Coolify UI. The Docker container may not exist at all. Workaround: docker run -d with the existing volume + update status in Coolify DB. See coolify-gateway reference agent-ssh-unreachable.md.

Related Skills

• suivi-taches-automatique — for automated task logging via Google Sheets
• claude-code — for delegating complex DB restructuring to Claude Code CLI