dbctl

dbctl is a small Python CLI for monitoring, controlling, and administering multiple databases through a single declarative config. It is built for operators who manage a database explosion — the same application deployed across several environments (dev / test / int / prod) and several tenants (Germany / USA / India / …) — where administering a row means opening a GUI client, navigating a connection tree, opening a tunnel, and clicking through to the right schema. dbctl replaces that multi-click flow with one command:

dbctl prod-de increase-quota alice 10 --apply     # bump a user's quota, in prod, Germany
dbctl prod-us increase-quota alice 10 --apply     # same op, different tenant
dbctl int-in  doctor                              # healthcheck integration India

Every connection's tunnel (ssm / ssh / k8s / direct), credentials, safety policy, and introspection queries live in one versioned ~/.dbctl/connections.yaml; every common SQL action lives in one ~/.dbctl/operations.yaml. dbctl synthesises one Click subcommand per connection × operation, opens the tunnel, runs the SQL with bind parameters, writes an audit-log entry, and tears the tunnel down — without you ever leaving the terminal.

Why not DBeaver / a GUI client?

dbctl is not a schema browser or a query playground — it deliberately has no ad-hoc query command. Declaring operations in YAML keeps "what can be run against this DB" discoverable from a versioned file instead of buried in your shell history. (For ad-hoc exploration open the tunnel with dbctl tunnel open <conn> and point your favourite client at the local bind.)

How it reaches a database

Each connection declares its SQLAlchemy URL scheme (postgresql+psycopg, mysql+pymysql, mariadb+pymysql, mssql+pyodbc, …), a healthcheck query, optional introspection (info) queries, and a safety policy.

Operations are declared separately in operations.yaml. Each operation is a parameterised SQL block (using $name placeholders) with declared parameters; dbctl builds one Click subcommand per operation, so every connection automatically exposes a subcommand for every declared single-DB operation.

dbctl pg add-user stephen 12 --apply
dbctl pg list-users 20
dbctl pg info row_counts
dbctl diff user-count pg my
dbctl diff compare-quotas pg my Daily
dbctl doctor
dbctl history list

A query-only operation runs immediately. A DML operation (mode: execute, upsert, or script) on a connection with safety.confirm: true runs as a dry-run by default — it shows the resolved SQL + bound values and writes nothing, until you pass --apply. --yes skips the final [y/N] confirmation. Every run is appended to ~/.dbctl/history.jsonl (with secret-typed parameters redacted), and dbctl <conn> again re-runs the last operation with the same params.

Why "the operations are declarative"

There is no ad-hoc query command. To run SQL through dbctl you must declare an operation in operations.yaml first — its name, its parameters (with types, descriptions, positional vs keyword), its SQL, and its mode (execute / fetch / fetch_one / script / upsert for single-DB; diff / compare / sync for multi-DB). The CLI then synthesises a Click subcommand per operation, so:

• the --help for <conn> <op> is generated from the declared parameters;
• parameters are type-checked before the SQL is ever rendered;
• secret-typed parameters are identified for audit redaction;
• the audit log is queryable by operation name (dbctl history list).

This keeps "what can be run against this DB" discoverable from a versioned config file, instead of buried in shell history.

Install (uv)

uv is the recommended workflow:

# Install dbctl as a tool (all dialect drivers ship as dependencies)
uv tool install ./dbctl

# Or hack locally:
cd dbctl
uv sync --extra dev
uv pip install -e .
uv run dbctl --help

The aws and ssh binaries are expected on PATH. No boto3, no paramiko — dbctl always shells out so you keep your existing SSO session, key agents, and MFA flows.

Quick start with the bundled docker-compose fleet

First time here? Follow the step-by-step tutorial instead — it walks through install, the dashboard, single-DB operations, the audit log, multi-DB diff, writing your own operation, and pointing dbctl at real AWS RDS via SSM.

docker-compose.yml brings up three databases (postgres on :5433, mysql on :3307, mssql on :1434) with the same four-table schema (users, quotas, usage, logs) and slightly different sample data — perfect for trying the multi-connection diff commands.

docker compose up -d

# install the sample config (uses plaintext passwords out of the box for dev;
# swap to `password_env: VARNAME` or `prompt: true` for real environments)
mkdir -p ~/.dbctl
cp .dbctl/connections.yaml ~/.dbctl/connections.yaml
cp .dbctl/operations.yaml  ~/.dbctl/operations.yaml

dbctl                                  # dashboard
dbctl doctor                           # healthcheck all three

dbctl pg health
dbctl pg info row_counts
dbctl pg list-users 10
dbctl pg add-user stephen 12 --show-sql        # dry-run (prints SQL)
dbctl pg add-user stephen 12 --apply --yes     # commit (no prompt)
dbctl pg increase-quota alice 10 --apply -y    # +10% on alice's quotas

dbctl diff user-count pg my                    # multi-DB diff
dbctl diff compare-quotas pg my Daily

dbctl pg history                               # per-connection audit log
dbctl pg again                                 # re-run last op on pg

dbctl tunnel open pg                           # hold tunnel for ad-hoc psql

SQL Server needs an installed ODBC driver on the host (ODBC Driver 18 for SQL Server). The sample ms connection is read_only: true and is only used for the diff operations against the smaller users table.

Config layout

~/.dbctl/
├── connections.yaml     # named connections (ssm / ssh / direct)
├── operations.yaml      # parameterised SQL operations (single + multi)
└── history.jsonl        # audit log (one JSON event per run)
~/.dbctl/profiles/<name>/ # switch config dir with --profile <name>

dbctl init walks you through adding a new connection interactively and writes it back to ~/.dbctl/connections.yaml — it will test the tunnel + healthcheck before saving (for direct connections).

Loader resilience

Each connection is validated independently, not as one big ConnectionsFile. A mis-configured connection (e.g. a reference template with all password sources commented out, or a typo'd driver) is skipped with a one-line warning on stderr — the rest of the registry still loads so the dashboard, --help, and the good connections keep working:

connections.yaml: 1 invalid connection skipped:
  pg-ssm: set 'password', 'password_env' or 'prompt: true' for each connection

dbctl operations validate checks operations.yaml the same way.

Adding an operation

Operations are global — every connection sees them. Declare one:

# ~/.dbctl/operations.yaml
operations:
  reset-quota:
    description: "Reset a user's daily quota back to their yearly baseline"
    scope: single
    mode: execute
    confirm: true
    parameters:
      - { name: name,   type: string,  required: true, position: 1, description: "User name" }
      - { name: floor,  type: integer, default: 10,   position: 2, description: "Don't reset below this floor" }
    sql: |
      UPDATE users
         SET quota_daily = GREATEST($floor, quota_yearly / 365)
       WHERE name = $name

Then:

dbctl pg reset-quota alice --show-sql        # preview
dbctl pg reset-quota alice --apply --yes     # commit

$name and $floor are rewritten to SQLAlchemy bind-params (:name, :floor) — they are always parameterised, never string-interpolated, so SQL injection through a parameter value is not possible. The Postgres cast idiom $name::type is rewritten to the portable CAST(:name AS type) form so the same operation YAML works cross-dialect:

sql: |
  SELECT level, COUNT(*) AS events
    FROM logs
   WHERE created_at >= $since::timestamp     # → CAST(:since AS timestamp)
     AND created_at <  $until::timestamp
   GROUP BY level

(For dynamic identifiers such as table names, write one operation per table for now; ${var} identifier interpolation is on the v2 roadmap.)

See docs/operations.md for the full parameter and mode reference.

Adding a connection

Edit ~/.dbctl/connections.yaml, or run dbctl init:

connections:
  prod-pg:
    description: "Production main Postgres (private RDS, SSM tunnel)"
    aliases: [prod]
    type: ssm
    driver: postgresql+psycopg
    database: app
    username: app_admin
    password_env: DBCTL_PROD_PG_PASSWORD
    ssm:
      region: eu-west-1
      profile: prod                              # AWS SSO profile; tokens cached in ~/.aws/cache
      bastion_instance_id: i-0abcd1234            # or use bastion_tags: { Name: bastion-prod }
      remote_host: mydb.xxxx.eu-west-1.rds.amazonaws.com
      remote_port: 5432
      local_port: 0                               # 0 = auto-pick a free port
    healthcheck: { query: "SELECT 1" }
    info:
      - name: row_counts
        query: "SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC LIMIT 10"
    safety:
      confirm: true        # dry-run-by-default for DML
      read_only: false
      allowed_operations: []   # empty = any

See docs/connections.md for the full reference.

Safety model

Each connection has a safety block:

dbctl confirms before opening the transaction — saying N at the prompt leaves the database untouched.

DB password sources are mutually exclusive — pick one per connection:

Secret-typed operation parameters (type: secret) are redacted in the audit log regardless of which DB password source the connection uses.

Shell completion

dbctl --install-completion bash         # prints the snippet
echo 'eval "$(_DBCTL_COMPLETE=bash_source dbctl)"' >> ~/.bashrc
# similarly for zsh / fish

Completion then covers connection names, operation names, and (for multi-DB verbs) dbctl diff <TAB> lists the available operations.

Project layout

dbctl/
├── pyproject.toml          # uv / hatchling; ruff + pytest + mypy config
├── docker-compose.yml      # postgres :5433, mysql :3307, mssql :1434
├── seed/                   # four-table schema + sample data for the fleet
├── .dbctl/                 # sample connections.yaml + operations.yaml
├── docs/
│   ├── connections.md      # connections.yaml reference
│   ├── operations.md       # operations.yaml reference
│   └── DESIGN.md           # architecture and design decisions
└── dbctl/
    ├── cli.py              # dynamic groups + per-op Click commands
    ├── config.py           # pydantic v2 models
    ├── connections.py      # loader + alias resolution + "did you mean?"
    ├── operations.py       # loader + scope filter
    ├── tunnels/{ssm,ssh,direct}.py
    ├── db.py               # URL builder + password resolution + healthcheck
    ├── execute.py          # $name → :name, mode routing, bind_params
    ├── multi.py            # per-role engine open + query runner for diffs
    ├── reports.py          # rich tables / json / csv / yaml + diff rendering
    ├── audit.py            # history.jsonl
    ├── runtime.py          # opened_conn() ctx-mgr (tunnel+engine+healthcheck)
    └── init.py             # dbctl init wizard

Development

uv sync --extra dev
uv run ruff check dbctl tests   # lint
uv run pytest tests/             # ~60 unit tests against in-memory SQLite
uv run mypy dbctl                # type-check (pre-existing debt; non-blocking in CI)

The unit tests use an in-memory SQLite database so they run without docker. For a live end-to-end smoke test, docker compose up -d and follow the quick start above.

Roadmap

• mode: upsert (autoload target table, dialect-aware ON CONFLICT / ON DUPLICATE KEY)
• mode: script (multi-statement support beyond single-statement passthrough)
• Multi-file operations loader (.dbctl/operations/<name>.yaml)
• Dynamic identifier interpolation (${var}) for table/column names in diff ops
• dbctl compare and dbctl sync modes (the framework is ready; sample ops not shipped)
• Tag-based operation filtering (dbctl pg --tag user-mgmt <op>)
• SSH agent / MFA support surfacing through the existing ssh subprocess

License

MIT