MYSQL QUERY

Database Query

Overview

Query databases through a centralized configuration file with automatic SSH tunnel management. Handles connection details, SSH tunnel setup/teardown, and query execution.

Security

Passwords are never exposed in process lists. The skill uses environment variables for credentials:

Recommended: Store credentials in environment variables instead of the config file for better security.

Configuration

Setup

1. Create config file at ~/.config/clawdbot/db-config.json:

   mkdir -p ~/.config/clawdbot
   # Copy example config and edit
   cp /usr/lib/node_modules/clawdbot/skills/db-query/scripts/config.example.json ~/.config/clawdbot/db-config.json
   

2. Add database entries with these fields: - name: Description used to find the database (required) - host: Database host (required) - port: Database port (default: 3306) - database: Database name (required) - user: Database user (required) - password: Database password (optional, can use env var) - ssh_tunnel: Optional SSH tunnel configuration

3. SSH tunnel configuration (if needed): - enabled: true/false - ssh_host: Remote SSH host - ssh_user: SSH username - ssh_port: SSH port (default: 22) - local_port: Local port to forward (e.g., 3307) - remote_host: Remote database host behind SSH (default: localhost) - remote_port: Remote database port (default: 3306)

Environment Variables (Recommended)

Instead of storing passwords in the config file, use environment variables:

# Format: DB_PASSWORD_ (spaces replaced with underscores, uppercase)
export DB_PASSWORD_PRODUCTION_USER_DB="your_db_password"
Format: SSH_PASSWORD_ for SSH tunnel password
export SSH_PASSWORD_PRODUCTION_USER_DB="your_ssh_password"

Example Config

{
  "databases": [
    {
      "name": "Production User DB",
      "host": "localhost",
      "port": 3306,
      "database": "user_db",
      "user": "db_user",
      "password": "",
      "ssh_tunnel": {
        "enabled": true,
        "ssh_host": "prod.example.com",
        "ssh_user": "deploy",
        "local_port": 3307
      }
    }
  ]
}

Set environment variables (recommended):

export DB_PASSWORD_PRODUCTION_USER_DB="your_db_password"
export SSH_PASSWORD_PRODUCTION_USER_DB="your_ssh_password"

Usage

List Databases

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py --list

Query a Database

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py \
  --database "Production User DB" \
  --query "SELECT * FROM users LIMIT 10"

The script will: 1. Find database by matching description in config 2. Start SSH tunnel (if configured) 3. Execute query 4. Automatically close SSH tunnel (important for cleanup)

With Custom Config Path

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py \
  --config /path/to/custom-config.json \
  --database "test" \
  --query "SHOW TABLES"

Requirements

Notes

List Databases

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py --list

Query a Database

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py \
  --database "Production User DB" \
  --query "SELECT * FROM users LIMIT 10"

The script will: 1. Find database by matching description in config 2. Start SSH tunnel (if configured) 3. Execute query 4. Automatically close SSH tunnel (important for cleanup)

With Custom Config Path

python3 /usr/lib/node_modules/clawdbot/skills/db-query/scripts/db_query.py \
  --config /path/to/custom-config.json \
  --database "test" \
  --query "SHOW TABLES"

Setup

1. Create config file at ~/.config/clawdbot/db-config.json:

   mkdir -p ~/.config/clawdbot
   # Copy example config and edit
   cp /usr/lib/node_modules/clawdbot/skills/db-query/scripts/config.example.json ~/.config/clawdbot/db-config.json
   

2. Add database entries with these fields: - name: Description used to find the database (required) - host: Database host (required) - port: Database port (default: 3306) - database: Database name (required) - user: Database user (required) - password: Database password (optional, can use env var) - ssh_tunnel: Optional SSH tunnel configuration

3. SSH tunnel configuration (if needed): - enabled: true/false - ssh_host: Remote SSH host - ssh_user: SSH username - ssh_port: SSH port (default: 22) - local_port: Local port to forward (e.g., 3307) - remote_host: Remote database host behind SSH (default: localhost) - remote_port: Remote database port (default: 3306)

Environment Variables (Recommended)

Instead of storing passwords in the config file, use environment variables:

# Format: DB_PASSWORD_ (spaces replaced with underscores, uppercase)
export DB_PASSWORD_PRODUCTION_USER_DB="your_db_password"
Format: SSH_PASSWORD_ for SSH tunnel password
export SSH_PASSWORD_PRODUCTION_USER_DB="your_ssh_password"

Example Config

{
  "databases": [
    {
      "name": "Production User DB",
      "host": "localhost",
      "port": 3306,
      "database": "user_db",
      "user": "db_user",
      "password": "",
      "ssh_tunnel": {
        "enabled": true,
        "ssh_host": "prod.example.com",
        "ssh_user": "deploy",
        "local_port": 3307
      }
    }
  ]
}

Set environment variables (recommended):

export DB_PASSWORD_PRODUCTION_USER_DB="your_db_password"
export SSH_PASSWORD_PRODUCTION_USER_DB="your_ssh_password"