---
description: Database CLI for AI agents with permission-gated access to MySQL, PostgreSQL, MariaDB, MongoDB, Redis, and Elasticsearch. Trigger when: wiring up a connection (`.dbcli` / `.env`, v1 single vs v2 multi-connection, auth mode); running SQL / MongoDB JSON / Redis commands / Elasticsearch DSL; inspecting table, collection, key, or index structure; writing rows or exporting results; building a report, dashboard, or HTML UI; authoring or reviewing a schema design; protecting sensitive data with the blacklist; or recovering after a failed command. For exhaustive flags and examples, read the companion `../skills/dbcli/reference.md`.
globs:
alwaysApply: false
---
# 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:**

| Task | Path |
| --- | --- |
| A named workflow fits ("diagnose slow query", "audit permissions") | `skill tasks list` → `skill tasks plan <pack>` — **prefer this; do not invent steps** |
| A fixed diagnostic goal | `guide <goal>` (`slow-query` / `capacity` / `health` / `index-usage` / `permissions` / `schema-overview`; `guide --list`) |
| A DB report / dashboard / HTML UI | `blacklist list` → `queries search <keywords>` or `queries suggest <intent>` → `queries show @<name>` → browser: `q @<name> --param k=v --ui`; file: `q @<name> --format html > report.html` or `export "<SQL>" --format html --output report.html` |
| Setting up a connection | see **Connection setup** |
| Anything else | run commands manually; consult the **Developer workflows** cheat-sheet |

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](../skills/dbcli/reference.md#recovery-cookbook-agent-walkthroughs).

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 --format json`. 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.

## 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.**

```bash
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.

| Situation | Minimum safe path |
| --- | --- |
| DB-backed feature | `blacklist list` → `schema <object>` → `queries suggest <intent>` |
| DB report / dashboard request | `blacklist list` → `queries search <keywords>` / `queries suggest <intent>` → `queries show @<name>` → `q @<name> --ui` or `--format html` |
| Application data bug | `audit tail --for-agent --n 10` → `blacklist list` → `schema <object>` → narrow query |
| ORM or migration work | `schema --format json` → `diff --against-orm <orm-schema>` → review error-level drift → proposals via `migrate` (dry-run) → `migration-review` task pack → `diff --against <snapshot>` after applying. |
| Schema design, no database yet | `design init --output ./dbcli.design.json` → edit → `design validate` → `design render --format mermaid`. With existing ORM models, reconcile via `design diff --against-orm <path>` first. |
| Design drift on a live database | `blacklist list` → `schema --format json` → `design diff --against-cache` → `design propose --against-cache`, then hand the plan to a human before any migration. |
| PR schema-change review | `blacklist list` → `impact assess --design ./dbcli.design.json --against-cache --output ./impact.json --fail-on warn`; optionally add explicit `--events ./.dbcli/proxy/events.jsonl` for advisory redacted workload table evidence (never SQL/log rendering or a blocker), then review declared findings, coverage gaps, and the optional reviewed `dbcli.data-access.json` (declared operations only; never source parsing). |
| PR database review | Review changed persistence paths, then propose concrete `schema` / `plan` / `dry-run` / `report` / `guide` commands per material claim. |
| Slow endpoint or query | `report --section perf` → task pack `analyze-table-perf` → `lint "<query>"` → `guide missing-index-for "<query>"`; use `proxy analyze` when logs exist. |
| Safe data backfill | `blacklist list` → `schema <object>` → count/scope query → `update … --dry-run` → read-back or snippet `--verify`. |
| Environment validation | `status --format json` → `doctor --format json` → `inspect --for-agent --no-connect`. |

Copy-paste command anchors:

```bash
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](../skills/dbcli/reference.md#commands).

## Audit log

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

```bash
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](../skills/dbcli/reference.md#audit).

## Quick start

```bash
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

```bash
# 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)

```bash
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:

```bash
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:

```bash
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
(../skills/dbcli/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:

```bash
# 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](../skills/dbcli/reference.md#init).

## Command overview

| Command | Min permission | Summary |
|---------|-----------------|---------|
| `init` | n/a | Create `.dbcli` (v1 single or v2 multi via `--conn-name` / `--env-file`). **Usually run by the human** — do NOT re-run to strip `{"$env"}` references; that format is intentional. |
| `use` | n/a | Show/switch default named connection (v2 only). |
| `list` | query-only+ | Tables (SQL), collections (MongoDB), keys (Redis), or indices (Elasticsearch). |
| `schema` | query-only+ | SQL: per-table or full scan into `.dbcli/schemas/`. MongoDB: sampled. ES: flattened mapping. Redis: per-key only (type/TTL/size). Supports `--recovery`. |
| `query` | query-only+ | SQL, Mongo JSON (`--collection`), Redis command, or ES DSL/Lucene (`--collection`). `--format table\|json\|csv\|html`, `--ui` to open the interactive dashboard in a browser. `--fields` (projection), `--truncate` (cell width), `-f/--query-file` (read query from file or stdin), `--use a,b` (read-only fan-out). Supports `--recovery`. `--slow-ms <n>` sets the passive slow-query hint threshold (default 1000, `0` off): at or above it, table output gains a `Performance hint` footer and JSON gains `metadata.performanceAdvisory`; it runs no extra diagnostics and is suppressed under `--recovery`. Distinct from the `proxy` flag of the same name. See **Query workflow flags**. |
| `explain` | query-only+ | Read-only query plan with annotations. SQL only. Single query, `@saved-query`, `@file.sql`, or `--bulk @glob/*`. `--analyze` (EXPLAIN ANALYZE / MariaDB ANALYZE SELECT), `--format markdown\|json\|table`. |
| `lint` | n/a | Static SQL anti-pattern advisor (no DB connection). 9 rules incl. schema-aware implicit-cast / NOT IN-nullable checks via the layered `.dbcli/schemas/` cache; global `--use <conn>` selects a named cache. Findings carry rewrite drafts + guarded `explain` verify commands (`--analyze` only for proven read-only SQL) — report-only, never executes. `--format text\|json\|markdown`, `--min-severity`, `--no-schema`, `--bulk`. Supports `--recovery`. |
| `plan` | n/a | Static SQL risk analyzer (`--format text\|json`); classifies a statement without connecting to the database. |
| `q` | query-only+ | Run a saved snippet by `@name` with `--param k=v`. Supports `--verify` to run assertions and `--slow-ms <n>` (same passive slow-query hint as `query`). |
| `queries` | n/a | Manage saved snippets: `list` / `show` / `search` / `suggest` / `new` / `edit` / `check` / `delete` / `rename` / `copy` / `import` / `export`. |
| `insert` / `update` | read-write+ | SQL or MongoDB only. JSON `--data` / `--set`; `--where` required on `update`; `--dry-run` first. Redis writes go through `query`. Supports `--recovery`. |
| `delete` | data-admin+ | SQL or MongoDB; Redis has a basic implementation (see Redis section). `--where` required; `--dry-run` first. Supports `--recovery`. |
| `export` | query-only+ | SQL, MongoDB, or Elasticsearch (DSL `--index` or whole-index scroll). Query → `--format json\|jsonl\|csv\|html` file or stdout. `html` emits a standalone interactive dashboard. **Fails closed rather than truncating silently**: if the auto-limit would drop rows, the export errors out and you must pass `--no-limit` or `--limit N`. Supports `--recovery`. |
| `blacklist` | n/a | `list` / `table` / `column` subcommands redact sensitive data from query results. |
| `check` | query-only+ | SQL only (best on MySQL/MariaDB). |
| `diff` | query-only+ | SQL only. Save/compare schema snapshots. `--against-orm <path>` compares a Prisma schema / DDL file / normalized JSON against the local schema cache (no DB connection): categorized drift (`missing_in_db` = error, `missing_in_orm` = warn, `mismatch` per tolerance table, `unmanaged`) with dry-run `migrate` proposals; exit 1 on error-level drift. `--orm-format prisma\|ddl\|json\|drizzle\|typeorm\|sequelize`, `--ignore <globs>`, `--format json\|table\|markdown`. Drizzle: point at `drizzle/meta/<NNNN>_snapshot.json` (run `drizzle-kit generate` first; `.ts` sources are rejected with a hint). TypeORM/Sequelize: feed tool-generated DDL (`schema:log` / a schema-only dump); source files are rejected with the exact generation command to run. |
| `design` | n/a | Offline SQL design assistant over a version-controlled `dbcli.design.json`: never connects, never runs DDL, never calls a provider. `init --output <path>` is the only writer and refuses to overwrite; `validate` is fail-closed, so `render` / `diff` / `propose` refuse to run while `error` findings remain. `diff` / `propose` need exactly one of `--against-cache` or `--against-orm <paths>`. **`propose` is review-only — it plans, it never writes.** Naming rules, finding codes, and the artifact shape are in [reference.md](../skills/dbcli/reference.md#design). |
| `snapshot` | query-only+ | SQL only. Capture a result fingerprint (`rowCount` + per-column null/distinct/min/max/sum + order-independent checksum). `--out` (default `.dbcli/snapshots/snap-<ts>.json`), `--rows`, `--stdout`, `--format`, `--no-limit`. Baseline for `assert --against`. |
| `assert` | query-only+ | SQL only. Verify an invariant; exit 1 on failure unless `--no-fail`. `--expect "rows>0\|value==X\|col:c not null\|unique\|between a and b\|>= n"`, `--vs <query> --compare rows\|value` (reconcile), `--against <snapshot> --tolerance <pct>`. |
| `verification` | n/a | Inspect and manage local verification artifacts. `list` / `show <id-or-path>` / `summary` are read-only; `prune` is dry-run by default and deletes only with `--execute --force`. Reads `<cwd>/.dbcli/verification/`; no DB connection, no audit writes. |
| `backfill artifact` | n/a | Build a bounded, reviewable source-to-SQL backfill artifact from JSON. Includes source/target identity, blacklist/schema preflight, read-back verification, and rollback hints; dry-run only and never executes writes. |
| `proxy` | n/a | MySQL/MariaDB/PostgreSQL only. Local-dev observability proxy — relays app traffic to the real DB and appends query/latency/byte/error events to `.dbcli/proxy/events.jsonl`. Observe-only. `proxy analyze` aggregates that log offline (summary, byFingerprint, slowest, errors, hotTables, N+1; `--format markdown` produces the QueryLens report) and errors out if no events exist. Act on it: run each finding's `suggestedCommands`, read its `hints`, then propose the fix — never guess a table name, confirm with `schema`. Protect the log itself with `--redact literals`. [Flags](../skills/dbcli/reference.md#proxy). |
| `status` | query-only+ | Safe JSON/text summary (no credentials). |
| `inspect` | query-only+ | Read-only context snapshot (connection, permission, blacklist, objects, snippets, context-aware `suggestedCommands`, and human-readable `hints`). `--for-agent` / `--brief` / `--no-connect` / `--require-schema-cache`. Supports `--recovery`. |
| `report` | query-only+ | Diagnostic report built from `@diag/*` snippets. `--section <health\|capacity\|perf>` (comma-separated to combine), `--brief`, `--for-agent`, `--no-connect`. |
| `guide` | query-only+ | Deterministic next-command plan for a fixed goal (`slow-query`, `capacity`, `health`, `index-usage`, `permissions`, `schema-overview`). `--list` to enumerate. `guide missing-index-for <query>` suggests composite indexes for a single SELECT (`--format yaml\|json\|markdown`, `--min-confidence`). |
| `recovery` | n/a | Look up the structured `RecoveryEnvelope` for a known error code (`--code <CODE>` or `--list`). Standalone synthesizer; does not require a real failure. |
| `recover` | n/a | Inspect (default) or `--apply` the auto-saved recovery plan in `.dbcli/last-recovery.json`. `--allow-write=readonly-cmd\|write-cmd`, `--no-verify`, `--from <file>`, `--next --after-step <n> --result <json\|@file>` for multi-turn step-at-a-time. |
| `doctor` | n/a | Environment/runtime identity, config, connection, SRV diagnostics (Mongo), schema cache age. `--format json --remediation` emits candidate-only blacklist/schema/bounded-sample plans (SQL: `dbcli plan` → human-confirmed bounded `dbcli query`; MongoDB/Elasticsearch: `dbcli schema` preflight → human-confirmed bounded query); it never applies them. |
| `completion` | n/a | bash / zsh / fish scripts. |
| `upgrade` | n/a | Self-update from npm; 24h-cached version hints on every command. |
| `shell` | (same as query+) | Interactive REPL. SQL engines, MongoDB, and Redis (single-line; `.no-limit on/off`). Elasticsearch opens a Kibana Dev Tools-style REPL (`<METHOD> /<path>` + optional JSON body, blank line submits). |
| `skill` | n/a | Generate / install AI skill docs (`--install <claude\|gemini\|antigravity\|copilot\|cursor\|codex\|windsurf>`); `skill tasks list/show/plan` for Agent Task Packs; `skill context` for an LLM prompt-context payload (for injecting into another LLM, not needed for normal operation). |
| `semantic` | n/a | Validate, search, inspect drift, migrate to v2, or print the optional project-root `dbcli.semantic.json`. Give its reviewed context to an external agent, but keep provider credentials, prompts, and agent context outside dbcli. `semantic draft validate --input <file|-> [--format text\|json]` validates only the explicit untrusted `QueryDraft` offline against local semantic/schema/saved-query metadata; it returns safe hashes/references/violation codes, never executes or echoes candidate SQL. Review the original draft, then invoke `explain` or `query` separately if intended. |
| `contract` | n/a | Validate, inspect approved context, search, or inspect drift for optional project-root `dbcli.contracts.json`. Contracts add ownership and a descriptive evidence policy to canonical semantic references; they are offline, never execute SQL, and cannot create verification or query authority. `skill context` includes only valid approved contracts. |
| `migrate` | admin | SQL only. **DDL; dry-run by default** — needs `--execute`. |

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=val1 AND col2=val2` — **not** full SQL
  (no `>=`, `!=`, `LIKE`, `OR`). MongoDB `--where` takes a full JSON filter
  (`'{"status":"pending"}'`), falling back to `col=val` when it is not valid JSON.
- `--dry-run` prints the parameterized SQL (with `$1` / `?` placeholders, not real values)
  and `status:"dry_run"` with `rows_affected: 0` — never `success`, which now means the
  write really ran. Proceed once the SQL shape matches the intended `--where` / `--set`.
  Declining at the confirmation prompt reports `status:"cancelled"`, also not `success`.
  MongoDB prints a shell-style preview.
- `--force` skips the confirmation prompt. Every `insert` / `update` / `delete` asks
  first — SQL, MongoDB and Redis alike — and a non-interactive run cannot answer, so an
  unattended write without `--force` ends as `status:"cancelled"` having changed nothing.
- `--recovery` is recommended for automated agent pipelines (enables `dbcli recover --apply`
  after a failure); optional for one-off manual writes.

## Query workflow flags

These exist so you do not have to pipe output through `head` / `jq` / `python3`
to make it usable. Reach for them instead of post-processing.

| Need | Flag | Notes |
|------|------|-------|
| Only some columns | `--fields sn,bet,created_at` | SQL and MongoDB. Mongo pushes a real `projection` / `$project` to the driver; `_id` is dropped unless you ask for it. A field the result lacks comes back as `null`, so verify spellings with `schema` before reading meaning into an all-null column. |
| Everything except a huge column | `--fields=-raw_response` | Exclusion form. Include and exclude cannot be mixed. |
| One field is a giant JSON blob | `--truncate 120` | Table output truncates cells at 120 chars **by default** and marks them `…(+3412 chars)`. `--no-truncate` disables it. Explicit truncation flags are rejected on JSON, CSV, HTML, and `--ui` output. |
| Query has quotes / newlines / `$regex` | `-f pipeline.json` or `-f -` | Reads the query from a file or stdin; use a heredoc for Mongo pipelines. Passing both a file and positional query text is an error, never a silent pick. `-f -` needs piped input — it refuses an interactive terminal rather than hanging. |
| Same query across connections | `--use hub-prod,site-a` | Read-only fan-out. Per-connection results, one failure does not cancel the others. Exit `0` all-ok, `2` mixed, `1` all-failed. Rejects writes, `--recovery`, `--ui`, CSV/HTML. |
| Pick a connection for one call | `DBCLI_CONNECTION=hub-prod dbcli query …` | Env var, or `--use` on the subcommand. Priority: `--use` > `DBCLI_CONNECTION` > saved default. Neither writes the default back to disk, so parallel shells never fight. Requires a v2 config — a single-connection (v1) project rejects both rather than silently running its only connection. **Do not** use `dbcli use <name>` just to switch for one query. |

**Truncation is reported, never implied.** When the query-only auto-limit trims a
result, the table footer reads `Rows: 1000 (truncated; limit 1000)`, `--format json`
carries `metadata.truncated` / `metadata.limit_applied`, and CSV appends a `#`
comment. `Rows: 1000` with no marker means exactly 1000 rows exist — do not infer
truncation from a round number. This applies to `query` and to `q` snippets
(whose own 1000-row guard reports the same way). `export` refuses to truncate at all. Redis replies trimmed by the size guard report the same way, and each size-guard warning is printed on stderr.

## Permission levels

| Level | Allowed |
|-------|---------|
| query-only | SELECT, list, schema, export |
| read-write | + INSERT, UPDATE |
| data-admin | + DELETE (DML, no DDL) |
| admin | + DDL via `migrate` and destructive ops |

## MongoDB

- `query` takes a JSON filter object (`find`) or array (`aggregate`); SQL is rejected.
  `--collection <name>` is required on `query`.
- **Supported:** `init`, `list`, `schema` (sampled), `query`, `insert`, `update`, `delete`,
  `export`, `q`, `status`, `use`, `shell`, `doctor`. **Not supported:** `diff`, `migrate`, `check`.
- Schema is **sampled** by `$sample` (default 100 docs, max 1000; `--sample-method natural`
  uses `find().limit()`). Columns surface as dot-paths (e.g. `profile.tokens.access`) with
  `presence` (0..1) and `redacted` flags.
- Writes: `--set` / `--data` JSON is auto-wrapped as `$set` when no `$` operator is present;
  explicit operators (`$set`/`$inc`/`$push`/…) pass through. Nested blacklist accepts dotted
  paths (`profile.email`) and trailing wildcards (`profile.tokens.*`). Saved snippets end in
  `.mongodb.sql` (frontmatter `engine: mongodb`, `operation: find|aggregate`). Full
  write-planner tiers and syntax: [reference.md](../skills/dbcli/reference.md#mongodb-support).

## Redis

- `query` runs a single **whitelisted** Redis command (e.g. `GET`, `SET`, `HSET`, `DEL`).
  The full whitelist and the per-command permission tier are defined in [reference.md](../skills/dbcli/reference.md#redis-support).
- **Supported:** `init`, `list` (keys via SCAN), `schema <key>` (type / TTL / size / sample),
  `query`, `q` (saved snippets — **read-only commands only**), `delete` (basic implementation:
  `DEL` / `HDEL` / `LREM` / `SREM` / `ZREM`, needs `data-admin`; `query "DEL <key>"` also
  works), `shell`, `status`, `use`, `doctor`. **Not supported:** `schema` full scan,
  `insert`, `update`, `check`, `diff`, `migrate`.
- **Permission tiers:** reads (`GET`/`HGET`/`SCAN`/…) → `query-only`; mutators
  (`SET`/`HSET`/`INCR`/`EXPIRE`/`SETEX`/`RENAME`/…) → `read-write`; `DEL`/`UNLINK`/`HDEL`/`XDEL`
  → `data-admin`. A command not in the whitelist is refused.
- **No `--dry-run` for Redis `query`** — write safety comes from the permission gate and key
  blacklist (matching reads/writes are rejected). To preview a delete, use `delete <key> --dry-run`.
- `database` is the logical DB index (default `0`). `dbcli blacklist table add 'secrets:*'`
  registers a key glob; an optional `redis.mask` block masks values on read. Size guards
  (SCAN/HGETALL truncation, `--no-limit` to bypass) and masking details: [reference.md](../skills/dbcli/reference.md#redis-support).

## Elasticsearch

**dbcli is read-only against Elasticsearch — `insert` / `update` / `delete` are not supported.**

```bash
dbcli query '{"query":{"match":{"status":"active"}}}' --collection orders
```

- `query` takes a DSL (JSON body) or Lucene query string; `--collection <index>` is required.
- **Supported:** `init`, `list` (indices with doc count), `schema [index]` (flattened mapping),
  `query`, `q` (snippets use the `.elasticsearch.sql` extension), `export`,
  `shell`, `status`, `use`, `doctor`. **Not supported:**
  `insert`, `update`, `delete`, `check`, `diff`, `migrate`.
- `export` takes a search DSL with `--index <index>`, or an index name as the query to scroll
  the whole index via `match_all`. Query-only caps at 1000 hits; `--no-limit` streams the whole
  index via the scroll API. (The 10 000 bound belongs to `query`, not `export`.)
- Schema flattens nested fields (`a.b.c`) and surfaces `.fields` multi-fields. `shell` opens a
  Kibana Dev Tools-style REPL. Full syntax and examples: [reference.md](../skills/dbcli/reference.md#elasticsearch-support).

## Saved queries

Run reusable parameterised snippets stored in your repo.

| Step | Command |
|------|---------|
| 1. Discover | `dbcli queries list` (or `queries search <keywords>` / `queries suggest <intent>`) |
| 2. Inspect | `dbcli queries show @<name>` |
| 3. Run     | `dbcli q @<name> --param k=v` (blacklist always enforced) |

Common intents: `perf.slow-query`, `perf.cache-hit`, `capacity.size`, `safety.connections`,
`monitor.cluster-health`.

Snippets resolve **local > shared > builtin** (local wins): `builtin` (bundled `@diag/*`,
read-only) / `.dbcli-shared/queries/` (team) / `.dbcli/queries/` (personal). Manage local
snippets with `queries new | edit | delete | rename | copy | import | export`. Each `.sql`
file declares YAML frontmatter inside `-- ---` blocks (name, description, engine, params, tags,
optional `intent`, optional `visual`).

Body format by `engine`:

| Engine            | Body format            | Notes |
|-------------------|------------------------|-------|
| postgres / mysql  | Single SELECT or WITH  | `:name` → driver bind (`$1` / `?`) |
| elasticsearch     | JSON DSL               | `:name` → JSON-aware substitution; `index:` field required |
| redis             | Single Redis command   | `:name` → raw text; **only read commands allowed** |

Mixed-family `engine` arrays (e.g. `[postgres, elasticsearch]`) are rejected at parse time.

### Built-in diagnostic snippets

Run with `dbcli q @diag/<topic>` (engine variant auto-picked by the active connection):

| key                     | purpose                                  |
|-------------------------|------------------------------------------|
| `@diag/connections`     | active sessions                          |
| `@diag/long-running`    | queries above `min_seconds` (`--param min_seconds=N`, default 30) |
| `@diag/table-sizes`     | table data/index size with row counts    |
| `@diag/index-usage`     | indexes by scan count                    |
| `@diag/missing-indexes` | tables dominated by sequential scans     |
| `@diag/locks`           | lock-wait chains                         |
| `@diag/db-size`         | database size summary                    |
| `@diag/cache-hit`       | buffer cache hit ratios                  |
| `@diag/es-cluster-health` | document counts per index (ES)         |
| `@diag/redis-key-stats`   | sample SCAN over keyspace (Redis)      |

## Interactive HTML dashboard

`query`, `q`, and `export` can render results as a standalone, self-contained HTML report
(bundled React + Recharts template).

```bash
dbcli query "SELECT day, dau FROM dau_daily" --ui          # open in browser
dbcli q @analytics/revenue --param days=30 --ui            # snippet metadata + charts/KPIs
dbcli q @analytics/revenue --param days=30 --format html > report.html
dbcli query "SELECT * FROM orders" --format html > out.html # pipe HTML to stdout
dbcli export "SELECT * FROM orders" --format html --output orders.html
```

`--ui` implies `--format html` and opens the file; `--format html` alone prints to stdout.
When a saved snippet exists, prefer `q @<name> --ui` / `q @<name> --format html` because snippet
metadata can drive titles, KPI cards, and charts. Blacklist redaction is applied **before**
rendering. To get KPIs and charts instead of a plain table, add a `visual:` block (`title`,
`kpis[]`, `charts[]`) to the snippet frontmatter — see [reference.md](../skills/dbcli/reference.md#interactive-html-dashboard) for the full `visual:`
schema. Raw `query` / `export` invocations render a sortable table only.

## Common workflows

- **Debug odd state:** `schema` → `check` → `query` with tight `WHERE` → follow FKs from schema JSON. Evidence over theory.
- **After INSERT/UPDATE:** follow the write sequence in **How to use dbcli** (`--dry-run` → run → `query` read-back); explain mismatches via triggers, defaults, or blacklist.
- **Migrations:** `diff --snapshot` → `migrate` (dry-run → `--execute`) → `diff --against` → `check` affected tables. DROP requires `--force`.
- **Health / growth:** `check --all` (huge tables skipped unless `--include-large`); consult schema `sizeCategory` before ad-hoc queries.
- **Codegen from live DB:** `schema --format json` to drive an ORM; cross-check once with `dbcli query`.
- **Integration truth:** `query` before → run app → `query` after. Unit-test mocks are not a substitute.
- **Natural language requests** (e.g. "update order to shipped"): follow **Business-language discovery** first when the request uses business terminology; then pick `query` vs DML, map terms → columns via `schema` (and enum values in data), respect blacklist and `sizeCategory`, **always `--dry-run` writes first**.

## Notes

- Query-only mode auto-appends `LIMIT 1000`; add `--no-limit` for `information_schema` or statements that break with `LIMIT`.
- Blacklisted tables and columns are redacted from query output.
- `schema` reports `estimatedRowCount` and `sizeCategory` (small / medium / large / huge). For large/huge tables add `WHERE` or `LIMIT` — bands in [reference.md](../skills/dbcli/reference.md#schema).
- `doctor` on `mongodb+srv://` reports whether SRV resolves natively or through the DoH fallback — useful when the runtime restricts DNS.
- **Global flags:** `--version`, `--config <path>`, `--global`, `--use <name>`, `--timeout <ms>`, `--statement-timeout <ms>`, `-v` / `--verbose` / `-vv`, `-q` / `--quiet`, `--no-color` (also honours `NO_COLOR`). Root-level flags must precede the command unless the command explicitly declares a command-level option.