Dbcli

dbcli

Database CLI for AI agents with permission-based access control.

If the dbcli executable is not available in PATH, use bunx @carllee1983/dbcli <command> as the command prefix. This is the expected fallback for Codex plugin installs where the skill is installed by the plugin but the CLI package has not been installed globally.

How to use dbcli

Safety baseline — apply to every operation:

1. dbcli blacklist list — confirm sensitive-data boundaries.
2. dbcli schema <object> --format json — confirm real column/field names. Never guess.
3. All writes: --dry-run (SQL/Mongo) → run → query read-back to confirm. Redis query has no --dry-run (see Redis); Elasticsearch is read-only.

Environment and mutation boundary: In v2, inspect dbcli use --list --format json before selecting a named connection. A connection labelled environment: "production" must be explicitly selected; it is never silently used through the saved default. To persist a production default, a human must repeat the exact name with --confirm-production. When DBCLI_AGENT_MODE=1, configuration, permission, and credential mutations are blocked unconditionally. Run human/admin changes in a separate process with agent mode disabled; do not treat a same-process environment variable as approval. Trusted config writes maintain an integrity record and secure file modes where supported, and agent reads fail closed on missing, replaced, non-regular, or tampered records. Agent mode refuses legacy single-file .dbcli configs until a human/admin migration to V2 home storage. For a same-user hostile process, a host can set DBCLI_CONFIG_INTEGRITY_ANCHOR_DIR to a protected or read-only directory containing detached digests.

update / delete --where is equality-only (SQL). It accepts only col=val or col1=v1 AND col2=v2. A comparison / pattern operator (>, >=, <, !=, LIKE, IN) is a parse error; worse, OR is silently swallowed into the value — a=1 OR b=2 parses as a = "1 OR b=2" and matches the wrong rows (or none). For a range or compound condition, first query / export the target rows' primary keys, then run one update / delete --where "id=<pk>" per key — or escalate to a human. (MongoDB --where takes a full JSON filter and is exempt.)

Write gate (2.0.0) — the rule that will refuse you. Every write is classified into two tiers. Ordinary writes (INSERT, UPDATE / DELETE with a WHERE, CREATE, ALTER) run unattended exactly as before; --yes skips the terminal prompt a human would see. Statements that are not limited to specific rows are refused outright when nobody can answer a prompt — UPDATE / DELETE with no WHERE, DROP, TRUNCATE, a statement the SQL parser cannot read, several statements in one string, and update / delete --where matching on no primary key and no unique index. The process exits 1 with reason=no_where, reason=ddl_destruction, reason=unparseable, reason=multi_table, reason=nested_write or reason=non_unique_where, and nothing reaches the database. In dbcli shell, a subcommand whose name is a SQL keyword needs a \ prefix (\delete users --where id=1) — a bare delete … is read as SQL. A write that joins a second table is always tier two: whether it is limited to particular rows depends on the data, not on the statement. So is a statement carrying a second write inside it — a data-modifying CTE (WITH x AS (DELETE FROM t RETURNING *) INSERT INTO …) or a MERGE with a WHEN … THEN DELETE / THEN UPDATE action. **No flag bypasses this** — not --yes, not --force. To write every row on purpose, put the intent in the SQL itself: add WHERE 1=1 or a LIMIT. DROP / TRUNCATE have no unattended route at all; escalate to a human.

report and guide already embed an inspect snapshot — you do not need to run dbcli inspect first. Run dbcli inspect --for-agent manually only when you want the audit-recent context or to diagnose a connection problem.

Then route by task:

Slow-query diagnosis has three canonical paths (pick by what you already know):

• Known slow SQL → skill tasks plan diagnose-slow-query --param query="<SQL>" → lint "<SQL>" → guide missing-index-for "<SQL>"
• Known hot table → skill tasks plan analyze-table-perf --param table=<table>
• Whole-environment scan → report --section perf → guide slow-query

report --section perf already runs the slow-query, index-usage, and cache-hit diagnostics — afterwards add only the @diag/* it does not cover (missing-indexes, locks, connections, table-sizes). Once you have a specific slow statement, explain --analyze "<SQL>" shows its plan.

On failure: pass --recovery to query / q / insert / update / delete / export / schema / inspect / lint / diff --against-orm. The command emits a RecoveryEnvelope to stdout and saves it to .dbcli/last-recovery.json; then dbcli recover inspects it and dbcli recover --apply runs the saved plan under risk gating. Multi-turn --next, connection branching, and the post-apply verify probe are documented in reference.md.

When reporting a check's outcome use the vocabulary verified (evidence matched) / not_verified (check ran and contradicted) / indeterminate (ran but ambiguous) / blocked (could not run due to config, permission, schema, placeholder, or safety gate).

Prefer --format json for agent-friendly output. Diagnostics (auto-limit notices, warnings) go to stderr so stdout stays parseable — when piping JSON into a parser, use 2>/dev/null or leave stderr alone. Never 2>&1: it merges those lines back into stdout and the parse fails.

Intent confirmation: Treat auto, confirm, and guided as conversational preferences for the current request, not as dbcli flags or persistent configuration. Do not ask the user a meta-question about whether they want questions.

• auto (default): autonomously use governed semantic context and schema discovery. If unresolved ambiguity would materially change the result, ask one compact batch of questions before querying; otherwise state the assumptions and proceed.
• confirm: first state the proposed interpretation and wait for the user's approval before issuing the task's data query.
• guided: resolve the request through short, focused questions, carrying confirmed answers forward rather than asking again.

For business requests, material ambiguity includes the requested result shape or grain, metric definition, time boundary and timezone, inclusion/exclusion rules (such as order status or refunds), grouping, or selected connection. Example: for “yesterday's sales,” do not guess whether the user needs a total or detail, which timezone defines yesterday, or whether cancelled and refunded orders count. Summarize the candidate interpretation and ask only the unresolved, result-changing questions.

When the user explicitly says to decide without further questions, proceed in auto mode and disclose the material assumptions. This never bypasses blacklist, schema, permission, dry-run, production-selection, or write-confirmation gates; an agent must still stop where those gates require human confirmation.

Business-language discovery: When a user uses a business alias, metric, recurring term, or relationship/join intent instead of a physical table or field name, first run dbcli skill context --context-version 2 --format json. This offline, bounded contract does not read project source, open a connection, scan Redis, or interpret natural language. Read project code only through this agent's own workspace safety checks; never pass source paths or contents to dbcli. If gaps reports missing evidence, do not guess metadata: inspect permitted code or ask for it. If it includes semantic, treat that reviewed section as the governed vocabulary; use dbcli semantic search <terms> --format json to look up a specific term. If contracts is present, use only its approved terms and their descriptive evidence policy; it never authorizes an assertion or query. If no semantic section exists or search returns no result, fall back to blacklist → schema mapping and tell the user that optional dbcli.semantic.json can make future requests consistent. Never create, update, or migrate that file without an explicit human request; semantic vocabulary never replaces schema confirmation or the normal query/write safety gates.

Capability discovery

Before composing a workflow, ask dbcli what it can do here rather than assuming. The catalog is static and the check reads only the local config — neither opens a database connection.

dbcli capabilities --format json                                  # full catalog
dbcli capabilities --format markdown                              # table for docs
dbcli capabilities check --require schema.read,query.read         # gate your workflow
dbcli capabilities check --require data.delete --format json      # machine-readable

For a copyable Bun/TypeScript consumer with strict parsing, schema pins, exit handling, Operation Envelope handling, Task Pack safety.requires, and correlation/evidence guidance, start with assets/integration-kit/README.md.

A capability is one atomic dbcli ability (schema.read, data.delete), never a job or a method. dba.tune-production and crud.scaffold belong to the Role or Method Skill that composes dbcli — this Tool Skill only covers operating dbcli safely.

• schemaVersion is the capability contract version, not the npm package version.
• Statuses are available, unavailable (reason engine, agent-mode, permission, context-unavailable or context-unresolvable) and unknown. A misspelled id fails closed and is never guessed.
• Under DBCLI_AGENT_MODE=1, capabilities that change configuration are unavailable with reason agent-mode whatever the permission level says.
• Exit codes: 0 all available, 1 any unavailable or unknown, 2 bad input.
• available is not approval. Blacklist, write gate, confirmation and audit still run at execution time, and admin in a config file is a permission level, not a DBA sign-off.
• v1 covers the commands the engine capability matrix governs; anything outside it returns unknown rather than an unaudited engine claim.

Agent output v1 (PLAT-005): use dbcli --agent-output capabilities or dbcli --agent-output capabilities check --require <ids>; the root flag must precede the subcommand, and no other operation is supported yet. It writes one compact UTF-8 JSON envelope plus newline to stdout, with empty stderr. Its ten keys are schemaVersion, ok, operation, status, context, data, warnings, evidence, recovery, and error; schemaVersion is 1, operation is capabilities.list or capabilities.check, and status is succeeded or failed. Do not combine it with explicit --format or --for-agent: conflicts, invalid placement/input, and unsupported operations exit 2; success exits 0; unmet requirements and internal failures exit 1. The envelope is capped at 64 KiB including newline, and consumers use field names rather than order. The optional root --correlation-id <id> must also precede the subcommand. It accepts 1–160 ASCII letters, numbers, ., _, :, or -; never put secrets or free-form input in it. For supported non-static output it appears as context.correlationId and existing audit metadata.correlation_id; command summaries redact it. Static capabilities stays context: null. It creates no evidence receipt.

Code/message vocabulary is locale-independent English: errors are INVALID_AGENT_OUTPUT_OPTIONS (invalid/misplaced/conflicting options), INVALID_CORRELATION_ID (invalid or missing --correlation-id value), UNSUPPORTED_AGENT_OUTPUT_OPERATION (outside PLAT-004), INVALID_CAPABILITY_REQUIREMENTS (invalid --require), CAPABILITY_REQUIREMENTS_UNMET (completed negative result), AGENT_OUTPUT_LIMIT_EXCEEDED (64 KiB limit), and AGENT_OUTPUT_INTERNAL_ERROR (unexpected safe failure). Warnings are DUPLICATE_CAPABILITY_REQUIREMENT, CAPABILITY_CONTEXT_UNAVAILABLE, CAPABILITY_CONTEXT_UNRESOLVABLE, and AGENT_MODE_RESTRICTION_ACTIVE.

Agent Task Packs

When the user asks for a database workflow ("diagnose this slow query", "audit permissions", "review long-running operations"), prefer published task templates over inventing steps from memory.

dbcli skill tasks list --format json                              # discover
dbcli skill tasks show <task>                                     # inspect
dbcli skill tasks plan <task> --param key=value --format json     # generate plan

The plan is an ordered list of dbcli commands with rationale and risk labels. Execute them one at a time — task plans do not override blacklist, schema, dry-run, or confirmation requirements.

Builtin packs (SQL — postgres/mysql): diagnose-slow-query (targets a specific SQL), analyze-table-perf (targets a specific table; dbcli inspect auto-suggests it for the hottest table in recent audit activity), audit-permissions, safe-backfill, schema-drift-review, orm-drift-review (ORM definition vs cached DB schema), connection-health. Review/verify packs: pr-database-review, migration-review, safe-backfill-verify, slow-endpoint-investigation. MongoDB packs: mongo-safe-backfill (dry-run–previewed backfill), mongo-schema-drift-review (sampled dot-path drift). All are read-only plan-only — pick the pack matching the situation, and run any index/DDL proposal through migration-review before writing. Redis/Elasticsearch have no packs yet — lead with guide / report there.

Tasks live under assets/tasks/ (builtin), .dbcli-shared/tasks/ (shared), and .dbcli/tasks/ (local override).

Developer workflows

Use these workflows when database impact is implicit in a development task. The safety baseline in How to use dbcli still applies.

Copy-paste command anchors:

dbcli inspect --for-agent --format json
dbcli blacklist list --format json
dbcli schema <object> --format json
dbcli queries suggest <intent> --format json
dbcli queries search <report keywords> --format json
dbcli queries show @<name> --format json
dbcli q @<name> --param k=v --ui
dbcli q @<name> --param k=v --format html > report.html
dbcli export "<SQL>" --format html --output report.html
dbcli audit tail --for-agent --n 10
dbcli diff --snapshot <name>
dbcli diff --against-orm prisma/schema.prisma --format json
dbcli diff --against-orm "migrations/*.sql" --format markdown
dbcli skill tasks plan orm-drift-review --param orm_path=prisma/schema.prisma --format json
dbcli report --section perf --format json
dbcli skill tasks plan analyze-table-perf --param table=<table> --format json
dbcli guide missing-index-for "<query>" --format json
dbcli lint "<SQL>" --format json
dbcli update <object> --where "<bounded predicate>" --set '<json>' --dry-run --format json
dbcli inspect --for-agent --no-connect --format json

Guardrails:

• Never invent table, collection, key, index, or field names. Confirm with schema.
• Separate database facts from application-code inference. Report which dbcli output shaped the conclusion.
• For writes and backfills, include scope count, dry-run preview, execution command, and read-back.
• Do not create indexes directly from a performance suggestion; turn them into reviewed migrations.
• Do not execute the commands in a design propose plan, and do not create or rewrite dbcli.design.json unless a human asked for it.
• Do not print credentials, copied connection strings, or blacklisted values.
• Durable evidence: assert … --write-verification-artifact --verification-subject <kind:name>; inspect with verification summary / list / show <id>. The verify safe-backfill / migration / rollback --kind <ddl|dml> / constraint --check <fk|not-null|unique|custom> family runs preflight + --after-write checks and never executes the write. Add --evidence-receipt <workspace-relative-path> only after after-write for a safe provenance receipt; it is never approval to execute a write. Full flags and the per-command blocks are in reference.md.

Audit log

Use the audit log for cross-session history or failure forensics instead of re-querying live DB state.

dbcli audit tail --for-agent --n 10          # last N entries (JSON envelope, metadata-only)
dbcli audit show <id-prefix>                 # full entry by id prefix (≥4 chars)
dbcli audit show --recovery-ref <env-id>     # find the entry that emitted an envelope

The inspect / guide / recover agent JSON embeds audit_recent (last 5 entries) — a fresh session has immediate history. An envelope's audit_ref and an audit entry's recovery_ref point at each other, so you can pivot either way. Audit is on by default (audit.enabled = false to opt out); entries are metadata-only (never SQL bodies, --param values, or result cells) and rotate at ~10 MB / 1000 entries. Full flags: reference.md.

Quick start

dbcli init                          # Create .dbcli config (parses .env automatically)
dbcli schema                        # Scan all tables → .dbcli/schemas/
dbcli query "SELECT * FROM users"   # Execute SQL (auto LIMIT 1000)

If .dbcli does not yet exist, route through Connection setup below before touching schema / query.

Connection setup (helping the user wire up a database)

When the user asks "how do I connect to X?", "set up dbcli for our staging DB", or doctor / status reports a missing or invalid config, follow this flow.

Default to guiding, not running. init writes credentials to disk. Only execute it for the user with explicit permission and confirmed values. If a .dbcli already contains {"$env": "..."} references, do not rerun init to "fill them in" — the env-ref form is intentional for CI/multi-env.

Decision tree (ask before writing)

1. One DB or many environments? One → v1 (single connection). Multiple environments / tenants / replicas → v2 (--conn-name <name>, optionally --env-file <path> per connection).
2. Where do credentials live?
   • Already in a .env (DATABASE_URL or DB_HOST / DB_PORT / DB_USER / DB_PASSWORD / DB_NAME | DB_DATABASE) → init parses it automatically.
   • Need to keep secrets out of .dbcli (CI/CD, multi-env) → --use-env-refs (see below).
   • Plain values are acceptable → pass --host / --port / --user / --password / --name (and --system).
3. What permission tier? Default to the lowest that satisfies the task: query-only → read-write → data-admin → admin. Set with --permission (defaults to query-only). Tiers judge what a statement does, not how it opens: below admin, multi-statement SQL is rejected; snippets must be free of write and DDL keywords; MongoDB $out / $merge need data-admin and are refused entirely in snippets and export.
4. Verify, never assume. After init: dbcli status (system + permission + blacklist summary, no creds) and dbcli doctor --format json (env, config shape, connectivity, schema-cache age, Mongo SRV path).

Per-engine essentials

# PostgreSQL / MySQL / MariaDB (v1, plain values)
dbcli init --system postgresql --host localhost --port 5432 \
  --user app --password '<secret>' --name appdb --permission query-only

# Reuse an existing .env (DATABASE_URL=postgresql://user:pw@host:5432/db)
dbcli init                                                # parses .env in cwd

# MongoDB — field-by-field (no auth = omit --user/--password)
dbcli init --system mongodb --host localhost --port 27017 --name mydb
dbcli init --system mongodb --host localhost --port 27017 \
  --user admin --password '<secret>' --auth-source admin --name mydb
# MongoDB — full URI (advanced escape hatch: multi-host, non-standard driver options)
dbcli init --system mongodb \
  --uri "mongodb+srv://user:pw@cluster.example.mongodb.net/mydb?authSource=admin"

# Redis — `--name` is the LOGICAL DB INDEX ("0".."15"), not a database name
dbcli init --system redis --host localhost --port 6379 --password '<secret>' --name 0

# Elasticsearch — basic auth, Cloud ID, or API key
dbcli init --system elasticsearch --host localhost --port 9200 \
  --user elastic --password '<secret>'
dbcli init --system elasticsearch \
  --cloud-id "myCluster:dXMtZWFzdC0xLmF3..." --api-key "<base64>"
# Multi-node / custom CA / self-signed: edit `.dbcli` directly to add
# `nodes: [...]`, `protocol: https`, `caPath`, `rejectUnauthorized: false`.

Multi-connection (v2)

dbcli init --conn-name staging --env-file .env.staging --permission query-only
dbcli init --conn-name prod    --env-file .env.production --use-env-refs --skip-test
dbcli use --list --format json            # safe identity inventory: name/env/permission/server/database
dbcli use prod                            # switch default (persists — avoid for one-off queries)
dbcli query --use staging "SELECT 1"      # one-shot override on any subcommand
DBCLI_CONNECTION=staging dbcli query "SELECT 1"   # one-shot via env; parallel-safe
dbcli --use staging,prod query "SELECT count(*) FROM users"   # read-only fan-out
dbcli init --rename staging:stg           # rename
dbcli init --remove stg                   # remove

Rotating one connection's password — nothing else in the config moves:

dbcli password prod                       # masked prompt
rotate-secret | dbcli password prod --stdin   # for scheduled rotation scripts

The value goes to the env var the config actually references (a literal password is converted to { "$env": ... } on first use, and a connection with no envFile gets one recorded so the reader loads it), is verified by connecting before it is saved (--skip-test to opt out), and the env file is written 0600 on POSIX.

For a connection shared across projects, use the explicit root-level --global scope. It stores a v2 registry at ~/.config/dbcli/config.json; it does not create or modify a project binding:

dbcli --global init --conn-name shared --system postgresql --host db.example.com \
  --port 5432 --user app --password '<secret>' --name appdb \
  --skip-test --no-interactive --force
dbcli --global use --list --format json
dbcli --global query "SELECT 1"

--global must appear before the command. Without it, commands continue to use the current project's .dbcli binding; global and project registries are independent.

Each named connection has its own schema cache at .dbcli/schemas/<connection>/. Run dbcli schema --use <name> once per connection before schema <table> — otherwise the cache may serve another connection's columns. schema --refresh / --reset manage the cache (reference.md). --skip-test skips the init-time TCP connection test; it is implied automatically when --use-env-refs is set (the $env refs have no value to connect with yet). --system is optional for v2 — without it the engine is inferred from --env-file / .env (DATABASE_URL scheme), defaulting to postgresql.

env-refs (keep secrets out of .dbcli)

Store credentials as { "$env": "VAR" } references resolved at runtime, never plaintext:

# Default key names: DB_HOST / DB_PORT / DB_USER / DB_PASSWORD / DB_DATABASE
dbcli init --use-env-refs

# Non-default key names — name each one explicitly (required in CI):
dbcli init --conn-name prod --env-file .env.production --use-env-refs --skip-test \
  --env-host PROD_DB_HOST --env-port PROD_DB_PORT \
  --env-user PROD_DB_USER --env-password PROD_DB_PASSWORD --env-database PROD_DB_NAME

In an interactive terminal, omitting the --env-* flags prompts for each key name (defaults above) — you can type a non-default name like PROD_DB_PASSWORD and it is stored as a $env ref. In a non-interactive / CI run you must pass all five --env-* flags; otherwise init exits with an error — it never silently falls back to plaintext. --env-file <path> is the path to the env file, independent of the $env key names.

MongoDB is the exception: only --env-host is required non-interactively. --env-port / --env-user / --env-password / --env-database are optional — an omitted one is written as a literal value (empty string for user / password, the resolved value for port / database) instead of an $env ref, so a field the connection never needed doesn't later fail closed on an undefined variable. init also skips the connection test in this mode regardless of --skip-test — the $env refs have no value to connect with yet.

Common gotchas

• MongoDB mongodb+srv:// — dbcli doctor reports whether SRV resolves natively or via the DoH fallback; useful when the runtime restricts DNS.
• MongoDB authSource / replicaSet / tls / srv — init asks for these interactively (authSource only when a user is set; replicaSet / tls behind an "advanced options?" prompt); --auth-source <db> is the only one with a dedicated non-interactive flag, so set replicaSet / tls interactively or edit .dbcli afterward. If a config has both uri and per-field values, uri wins silently — dbcli doctor flags this and also warns when srv: true is combined with a non-default port.
• MySQL/Postgres password with @ : / — when using DATABASE_URL, percent-encode (@ → %40); discrete --password flags do not need encoding.
• Redis --name — accepts only the logical DB index string; non-numeric values are rejected.
• Elasticsearch TLS — caPath and rejectUnauthorized are not exposed as flags; edit .dbcli after init to add them.
• Re-running init — refuses to overwrite without --force; never use --force to "fix" a config full of { "$env": "..." } refs.

Full flags and edge cases: see reference.md.

Command overview

Use root-level dbcli --use <name> <command> for any command; query, schema, list, export, and check also accept command-level --use. Both target a v2 connection without changing the default. --recovery is honoured by query, q, insert, update, delete, export, schema, inspect, lint, and diff --against-orm (see On failure above).

Write & query flag semantics (SQL/Mongo insert/update):

• --set (update) / --data (insert) take a JSON object string, not a SQL fragment: dbcli update users --where "id=42" --set '{"email":"new@example.com"}'. For MongoDB, a JSON without $ operators is auto-wrapped as $set; explicit operators pass through. insert --data can also read the object from stdin.
• --where (SQL) accepts only col=val or `col1=va

…(truncated)