dbcli Documentation

Getting Started

Install globally using your preferred package manager:

# Using Bun (required at run time)
bun install -g @carllee1983/dbcli

# Using npm
npm install -g @carllee1983/dbcli

dbcli runs on Bun 1.4.0+. npm and npx are supported as distribution channels, but the installed dbcli executable requires Bun on your PATH; only the ./agent-core subpath export is importable from a plain Node process.

Standalone dbcli --version and dbcli -V checks use a lightweight launcher and do not load database drivers. Other invocations load the full command runtime as usual.

Initialize your first connection with the interactive wizard:

dbcli init

For MongoDB, init walks through connection fields one at a time (Host, SRV, Port, User, Password, authSource, then optional replicaSet / tls) — see "MongoDB connection configuration" in the Engine Support Matrix section below for the full field list and how --uri is decomposed.

Every new or additional connection needs a nonblank connection name. Pass --conn-name <name>, or run dbcli init in an interactive terminal to be asked for one. The name prompt has no suggested default; empty or whitespace-only answers explain the requirement and prompt again. Cancelling or ending the name prompt stops before any connection test or configuration/credential write. An explicit nonblank --conn-name skips this prompt, and --conn-name default is valid. An explicitly blank --conn-name is an error in every mode, before connection tests or writes. --name still means the database name (the logical DB index for Redis), never the connection name.

Without a TTY, with --no-interactive, or when --use-env-refs plus all five --env-* options automatically disables interaction, a missing or blank --conn-name fails with guidance to supply it, before database access or configuration/credential writes. Complete credentials, --name, --env-file, and --force do not supply a connection name.

New initialization always creates named v2 configuration, including plain init and explicit --config paths; it no longer creates an unnamed v1 configuration. Existing v1 configurations remain readable and their established named v2 import is retained. The first explicitly named connection is still the default in a new registry; adding a connection preserves an existing registry's default or no-default state. An explicitly chosen duplicate name still requires interactive overwrite confirmation or --force; a noninteractive duplicate without --force, or a declined confirmation, leaves files untouched. --remove and --rename do not ask for a new connection name.

Behind the scenes, init writes a small version: 3 binding stub to ./.dbcli/config.json in your project, while the real connection settings and any credentials are stored in your home directory at ~/.config/dbcli/projects/<project-name>-<sha1-12>/. This keeps recoverable secrets out of the project workspace, so tools or AI agents that scan the repo never see them. The project .dbcli/ only holds the binding plus non-sensitive caches (schema cache, audit log, snapshots, verification artifacts).

For a connection shared by several projects, use the explicit global scope. It stores a v2 registry in ~/.config/dbcli/config.json and never creates 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 status --format json

Root-level --global must appear before the command. Global and project registries are independent; commands continue to use the project registry unless --global is supplied. The global file is protected with the same integrity record and private file mode as home-stored project settings.

Masked credential entry

Interactive init collects the database password — and a pasted MongoDB connection string, which carries one — through a masked prompt, so the value never lands in terminal scrollback, a session recording, or a screen share. Hosts, ports, usernames, database names, and environment-variable names stay ordinary visible prompts.

There is no plain-text fallback. If masked input cannot be provided — no terminal attached, or the prompt implementation unavailable — init stops before writing any configuration and names an input you can use instead: --password, a credential parsed from .env or the process environment, --use-env-refs, or --uri for MongoDB. An explicit --password or --uri is kept exactly as given and is never offered back as a visible prompt default, and --no-interactive never reaches a secret prompt at all.

If the connection test then fails, the driver's own message and hints are redacted and bounded before they reach the terminal, because drivers routinely quote the credential or the whole connection URI back in an error.

This is about terminal echo, not storage: dbcli does not encrypt credentials at rest. Keeping them out of the config file is what --use-env-refs and home-directory storage are for.

Verified TLS for PostgreSQL, MySQL and MariaDB

Use both SQL TLS flags when creating a named PostgreSQL, MySQL or MariaDB profile. This example prompts for the password and tests the connection before saving:

dbcli init --conn-name verified-pg --system postgresql \
  --host db.example.com --port 5432 --user reader --name appdb \
  --permission query-only --sql-tls verify-full --sql-tls-ca ./certs/root-ca.pem
dbcli --use verified-pg blacklist list
dbcli --use verified-pg schema public.events --format json
dbcli --use verified-pg query "SELECT id FROM public.events LIMIT 10" --format json

MySQL and MariaDB use the same flags, with separate named profiles and CA paths. Select the corresponding profile, then check blacklist and schema before an authorized query:

dbcli init --conn-name verified-mysql --system mysql \
  --host mysql.example.com --port 3306 --user reader --name appdb \
  --permission query-only --sql-tls verify-full --sql-tls-ca ./certs/mysql-ca.pem
dbcli init --conn-name verified-mariadb --system mariadb \
  --host mariadb.example.com --port 3306 --user reader --name appdb \
  --permission query-only --sql-tls verify-full --sql-tls-ca ./certs/mariadb-ca.pem

Replace the host, database and table with your authorized target; confirm real columns through schema before querying. The saved connection includes this dedicated SQL object (shown as a connection fragment):

{
  "sqlTls": {
    "mode": "verify-full",
    "caPath": "/absolute/path/to/certs/root-ca.pem"
  }
}

--sql-tls verify-full and --sql-tls-ca <path> are required together. Empty values, other modes and unsupported engines fail explicitly. PostgreSQL, MySQL and MariaDB support this SQL TLS contract; all other engines reject an explicit request. MongoDB's tls and Elasticsearch's TLS settings remain separate. Existing profiles without sqlTls keep their previous behavior; no global TLS policy is added. V1 directory and v2 profile reads, normal init import from a V1 directory to v2, and the public migrateV1ToV2 helper preserve the TLS object. A legacy V1 single-file TLS profile remains readable and usable in human mode, but automatic init import explicitly refuses it before connecting or writing and leaves the original untouched. For that case, create a verified named profile in a separate configuration directory, verify it, then select it; preserve sqlTls throughout migration.

init resolves a relative CA path against its current working directory before saving. Saved caPath must be a nonempty absolute literal path to a PEM CA bundle; it accepts no environment-variable reference. Existing environment references in host/user and other supported fields remain usable. Explicit TLS uses only the selected profile’s CA and identity settings; ambient TLS settings cannot weaken verification or supply another profile’s trust. Each new connection reads the CA bundle again; an unreadable, empty or invalid bundle stops before connecting. --skip-test skips only the initialization test, not verification on subsequent use.

verify-full requires encryption, a trusted certificate chain, valid certificate dates and certificate identity matching the configured DNS name or IP address. Unix sockets are refused for explicit SQL TLS. Untrusted, expired or mismatched certificates and servers without TLS fail before the user query; there is no plaintext retry or verification downgrade. Correct the CA, target hostname or certificate validity before retrying. A cipher alone does not prove identity verification.

For TLS error recovery, keep TLS configured and repair the CA, hostname or certificate first. Then deliberately select the configuration with --config <path> (or --global) and, for v2, the named profile with --use <name> (DBCLI_CONNECTION is also accepted). The selected profile must still have sqlTls.mode: verify-full; missing, ambiguous or plaintext selections block live recovery steps instead of falling back to a default plaintext connection. Saved recovery envelopes redact the original selectors: your current selection is deliberate targeting, not proof of the original target. Recovery apply and verification use the current CLI binary, avoiding an older dbcli on PATH. For the MySQL example above:

bun run src/cli.ts --config .dbcli --use verified-mysql recover --apply --format json

Permitted reads through an already configured profile remain usable in agent mode; init and other configuration mutations remain refused under DBCLI_AGENT_MODE=1. TLS does not bypass explicit production selection, native database authorization, permission, blacklist, write-gate or audit controls.

For binary rollback, stop workflows that require verified TLS or keep a supporting version. Older binaries may strip sqlTls; retaining the profile after downgrading does not preserve the guarantee. Removing TLS settings or retrying in plaintext cannot restore compliance. Local isolated PostgreSQL, MySQL and MariaDB fixture results are not evidence of real RDS acceptance; RDS remains unverified.

Connection Management

Switch between Production, Staging, and Local environments:

Use --global to manage or run a named connection from the user-level registry, independent of the current project:

dbcli --global use --list
dbcli --global use shared
dbcli --global query "SELECT 1"

Without --global, dbcli continues to resolve the current project's .dbcli binding. This explicit scope prevents a global connection from being selected accidentally in an unrelated project.

Root-level --timeout <ms> overrides the connection timeout for one invocation (integer, 100–600000; must precede the command, like --global and --use). It overrides the connection config's timeout field; without either, adapters fall back to a built-in 5000ms default. The override applies only when the adapter is created for this invocation and is never written back to config.json — set the connection's timeout field instead for a value that persists across runs. Elasticsearch applies its timeout per request rather than once for the whole connection. This is useful when the default is too tight, such as a MongoDB connection over a VPN or to Atlas:

dbcli --timeout 20000 --use <conn> list

Connection time and statement time are two separate limits. --timeout caps both, so --timeout 3000 also aborts any SQL statement that runs longer than 3 seconds. To change only how long a statement may run, use root-level --statement-timeout <ms> (integer, 0–3600000; 0 removes the limit) or the connection's statementTimeout field. Without either, statements are not capped by dbcli — the server's own setting applies, and a six-second analytical query is no longer cut off just because the connection timeout defaults to five seconds:

dbcli --timeout 2000 --statement-timeout 120000 query "SELECT ... FROM big_table"

dbcli use --list         # List all connection names
dbcli use --list --format json # Credential-free JSON connection inventory
dbcli use --unset        # Clear the saved default without removing connections
dbcli use staging        # Switch your default connection
dbcli --use prod query "SELECT count(*) FROM users" # Global one-shot selector
dbcli query --use prod "SELECT count(*) FROM users" # Supported command-level form
DBCLI_CONNECTION=prod dbcli query "SELECT count(*) FROM users" # Environment selector

For an unambiguous, non-secret environment label, set the optional environment field on a v2 named connection, for example "environment": "production". dbcli use --list --format json returns each connection's name, environment label (or null), permission, system, server/database identity, and default marker. Environment-backed identity fields are null; URI-only MongoDB and Cloud ID-only Elasticsearch placeholders are too. It deliberately excludes users, passwords, URIs, Cloud IDs, API keys, and env variable names. A misspelled connection selector suggests nearby configured names.

The resolved v2 connection name is retained while commands run, so audit records are routed to that connection's own audit stream.

dbcli use production --confirm-production production

Selection precedence is explicit --use, then DBCLI_CONNECTION, then the configured default. Environment values are trimmed and empty values are ignored. One-shot selectors never change the persistent default. The command-level form is supported by query, schema, list, export, and check; use the global form before other commands. Conflicting root-level and command-level values fail clearly. Selectors require a v2 configuration: a single-connection (v1) project has no names to choose between, so --use or DBCLI_CONNECTION there is rejected rather than quietly running the only connection. If --use is placed after an unsupported command, dbcli keeps the request rejected and prints the copyable root-level form, such as dbcli --use <connection> status.

Working without a default

A v2 configuration may omit the top-level default field. If present, it must be a nonempty string naming an existing connection; null, "", and unknown names are invalid. A new registry defaults to its first explicitly named connection; adding another preserves the existing default or no-default state. Existing v1 reads and named v1-to-v2 imports remain supported.

dbcli use --unset
dbcli --use staging query "SELECT 1"
DBCLI_CONNECTION=staging dbcli query "SELECT 1"

Without a saved default, a command that needs a connection must select one with --use or DBCLI_CONNECTION. Otherwise it fails before database access and lists the available connection names, with no endpoints or credentials. It never picks the first connection. The selection precedence and conflict checks above still apply: blank CLI selectors are invalid; a blank environment selector is ignored. Unknown names and production connections retain their existing validation and explicit-selection safeguards.

dbcli use --unset removes only the persisted v2 default field and reports that it was cleared. It succeeds if the field is already absent, without changing connections. It rejects a connection name, --list, --confirm-production, or --format json in the same invocation without writing, and never converts or overwrites a v1 configuration. Clearing the default uses the same approved, integrity-protected configuration write path as switching it; it remains blocked in agent mode.

dbcli use describes the no-default state. Text inventory marks no connection as default, JSON inventory returns isDefault: false for every connection, and doctor accepts an omitted default as valid v2 configuration; its connection checks still need an explicit selector, such as dbcli --use staging doctor. dbcli init --remove <name> clears the default when removing its connection and reports that change; it does not choose a replacement. Removing another connection preserves the saved default or existing no-default state. Deleting the last connection is refused without changing the configuration. Renaming a connection preserves the no-default state, or updates the default name when renaming the current default. To set a default again, use dbcli use <name>; production confirmation requirements remain unchanged.

Legacy project configuration compatibility: if adding a connection would import a v2 registry stored directly in the project and an existing connection has an envFile other than .env.local, init refuses before writing. The existing configuration and env files remain intact. An administrator must migrate the registry and custom env files to supported home storage before retrying. Normal bound/home-storage v2 configurations and direct-project v2 configurations with no envFile or only .env.local remain supported.

Server-enforced SQL query-only mode

On PostgreSQL, MySQL, and MariaDB, every caller-controlled statement whose effective permission is query-only runs inside a fresh database-native read-only transaction on the physical connection that executes it. This covers query, export, saved-query bodies and verification, report diagnostics, the SQL shell, analyzed explain plans, and fan-out even when the selected connections store a higher permission tier. If dbcli cannot establish that boundary, it fails before sending the target statement. Classifier, blacklist, hidden-write, and multi-statement checks still run first. If the target completes but transaction cleanup fails, dbcli reports the uncertain outcome and discards the affected connection; the SQL shell reconnects for later statements without replaying the completed target.

The guarantee covers persistent, non-temporary data and schema. An engine may still permit temporary or session-local state under its native read-only rules, and the boundary cannot prevent effects outside the target database, such as a network call made by an unsafe extension. Database accounts and ACLs remain an important independent layer.

Read-only query fan-out

An explicit comma-separated --use runs one read-only query against several named connections. The root-level and command-level forms are equivalent:

dbcli --use primary,staging query "SELECT count(*) FROM users" --format json
dbcli query --use primary,staging "SELECT count(*) FROM users"

Names are trimmed and results preserve selector order. Empty or duplicate names are rejected. A selector containing one name follows the existing single-connection path. Fan-out is explicit-only: DBCLI_CONNECTION remains one literal connection name and is never split on commas. Neither form changes the persistent default connection.

Before any adapter connects, dbcli loads and validates every selected configuration. SQL fan-out permits only read-only classifications (SELECT, SHOW, DESCRIBE, and EXPLAIN); MongoDB permits filter objects and read-only aggregation pipelines but rejects top-level $out or $merge stages; Elasticsearch permits searches only. Redis fan-out is not supported. A multi-connection query also rejects --recovery, --ui, and CSV or HTML output; use --format table (default) or --format json.

Each connection runs independently with its own adapter, blacklist filtering, row-limit/truncation metadata, audit entry, timing, and disconnect. A failure on one connection does not cancel or hide the others. JSON returns an ordered results array with a labeled ok or error outcome per connection; table output renders a separate labeled section for each schema. The aggregate exit code is 0 when all succeed, 2 for mixed success and failure, and 1 when all fail or the request is rejected before execution.

Password rotation

dbcli password changes one connection's password without touching any other setting — built for environments where credentials rotate on a schedule.

dbcli password                       # Masked prompt, rotates the default connection
dbcli password prod                  # Masked prompt, rotates 'prod'
rotate-secret | dbcli password prod --stdin   # Non-interactive, nothing lands in shell history
dbcli password prod --password "$NEW" --skip-test --format json

Where the value lands is read from the config, never guessed: a connection whose password is { "$env": "NAME" } gets NAME rewritten in its envFile. A connection that declares no envFile has one recorded (.env.local) as part of the rotation — without it the reader would never load the file. A connection still holding a literal password is converted to { "$env": "DBCLI_<CONN>_PASSWORD" } once, so later rotations only touch the env file. Values are written quoted (NAME="..."), so leading and trailing whitespace survives the round trip.

v1 configs rewrite DBCLI_PASSWORD in .env.local, matching the v1 reader. A v1 config whose password comes from some other environment variable is refused with an explanation: v1 has no per-connection env file, so no file dbcli writes could make that variable resolve — set it in the environment, or migrate to v2.

The new password is verified by connecting with it before anything is written, so a bad rotation fails without leaving broken credentials behind. Pass --skip-test when the database is unreachable from where the command runs. The env file is written with 0600 permissions on POSIX systems (Windows has no equivalent mode bit — the file inherits the directory's ACL), and the value is never echoed or logged.

Options: [connection], --stdin, --password <value> (visible in shell history and the process list — prefer --stdin), --skip-test, --format <text|json>.

Blocked under DBCLI_AGENT_MODE=1 like every other credential mutation.

Command Reference

Discovery & Exploration

dbcli blacklist list --format json emits one machine-readable document with tables, columns, and warnings; malformed MongoDB blacklist paths are returned in the structured warnings array instead of being mixed into stdout as human diagnostics.

Inspect one qualified SQL object

For PostgreSQL, use schema.table; for MySQL and MariaDB, use database.table. This inspects one accessible table or view, including system views, using the same namespace and local name for columns, enums, keys, indexes, and foreign keys. It does not change the connection, PostgreSQL search_path, default database, or schema cache. The scope of list, scanning, and schema without an object stays the same.

dbcli schema pg_catalog.pg_roles --format json
dbcli schema information_schema.TABLES --format json
dbcli schema '"Audit"."Order.Items"' --format json
dbcli schema '`reporting`.`order.items`' --format json

The outer single quotes in these shell examples preserve the native identifier quotes: double quotes for PostgreSQL and backticks for MySQL/MariaDB. Quote each component separately to preserve case or a dot inside a name; double an embedded native quote to escape it (for example, PostgreSQL "Audit"."Order""Items"). PostgreSQL unquoted qualified components fold to lowercase; MySQL/MariaDB follow the server’s identifier case rules. Empty components, more than two components, and invalid or unclosed quotes are rejected without trying another interpretation.

Unqualified names keep their existing exact-name behavior: PostgreSQL uses public, and MySQL/MariaDB use the connection’s default database. A missing qualified object never falls back to a same-named default object. Compatibility note: a literal local name containing a dot must now be explicit, for example dbcli schema 'public."audit.orders"' --format json. This name parsing applies to single-object schema, not to other commands.

Qualified JSON output keeps the local name and supplies a separate schema string (the database name on MySQL/MariaDB); text output distinguishes both components, including quoted dots and case-sensitive names. tableType identifies table or view; PostgreSQL materialized views use view. Qualified inspection never runs COUNT(*) on relation data. Available metadata estimates are estimates, not exact counts; when unknown, JSON has estimatedRowCount: null and sizeCategory: null, without a fabricated metadata.rowCount: 0. Text output does not substitute zero rows or a small size classification. Existing unqualified counting behavior is unchanged.

Blacklist protection applies to both unqualified and qualified SQL single-object inspection. Either a bare or qualified rule can match, with existing glob and case matching retained. Protected tables are refused; protected columns are hidden along with any entire primary key or index that contains them. A foreign key is omitted entirely from both table and column metadata if its source or target table/column is protected; composite constraints are never trimmed into misleading partial constraints. For this single-object schema command, expression or partial indexes are also omitted entirely when their complete column dependencies cannot be represented safely. JSON reports metadataOmitted: true and text output shows an omission notice when metadata is withheld, so omission is distinguishable from metadata that does not exist; the returned metadata is not necessarily complete. Hiding all columns does not mean the object is missing. Required metadata failures, including insufficient native permissions, fail the command rather than produce successful empty collections. If the database cannot distinguish a missing object from an invisible one, the error preserves that uncertainty.

A qualified name cannot be combined with --refresh or --reset (including both together); the command rejects these combinations before scanning. Use a separate unqualified schema --refresh or schema --reset operation for cache maintenance within its existing scope. Qualified inspection does not elevate database privileges or bypass query-only, agent-mode, or blacklist protections.

Capability discovery for Skills

dbcli capabilities answers “what can this tool do?” in a versioned, parseable shape, so an external Skill can decide before it starts working. dbcli capabilities check --require <ids> then answers “are those available here?” against the local configuration. Neither opens a database connection.

dbcli capabilities --format json
dbcli capabilities check --require schema.read,query.read --format json

connection.tls reports PostgreSQL, MySQL and MariaDB support from the engine capability matrix and maps to the existing connection command surfaces; it adds no TLS subcommand. Check with dbcli --use verified-pg capabilities check --require connection.tls --format json. available means tool support only: it does not prove that this profile configures TLS, the CA is readable, the handshake succeeds, or the operation is authorized. Catalog/check remain offline, read no CA bundle, perform no handshake and change no configuration. Other engines do not report this capability as available; older binaries without the ID return unknown. Configuration mutation still requires its own authorization.

The packaged assets/integration-kit/ contains a copyable Bun/TypeScript consumer and Task Pack fixture. It pins both schema versions, strictly parses catalog and Operation Envelope output, and shows the exit, correlation, and evidence boundary. Its CRUD, CQRS, and DBA examples remain external consumer requirements; dbcli does not implement or approve those workflows.

Agent output v1

--agent-output is a root option and must appear before the subcommand. In PLAT-005, dbcli --agent-output capabilities and dbcli --agent-output capabilities check --require <ids> are supported; either emits one compact UTF-8 JSON document plus a newline on stdout and nothing on stderr. It is ephemeral: it is not evidence and creates no timestamp.

Use the optional root --correlation-id <id> before the subcommand to associate an invocation with a Story, incident, change request, migration, or backfill. IDs are 1–160 ASCII letters, numbers, dots, underscores, colons, or hyphens; never use a credential, SQL, personal data, or a free-form label. For a supported non-static response, the ID appears as context.correlationId and in the existing audit entry as metadata.correlation_id; command summaries redact it. Static capabilities output keeps context: null. The option creates no evidence and changes no receipt.

The v1 envelope always has these ten keys: schemaVersion, ok, operation, status, context, data, warnings, evidence, recovery, and error. schemaVersion is 1; operation is capabilities.list or capabilities.check; status is succeeded or failed; and consumers must address keys by name, not field order. It is capped at 64 KiB including the newline.

Do not combine --agent-output with an explicitly supplied --format or --for-agent; either conflict, placement after the subcommand, invalid input, or an unsupported operation fails with one envelope and exit 2. Success exits 0; unmet requirements and unexpected internal failures exit 1. Existing output behavior is unchanged when --agent-output is absent.

Error and warning code/message vocabulary is locale-independent English. Error codes are INVALID_AGENT_OUTPUT_OPTIONS (invalid, misplaced, or conflicting output options), UNSUPPORTED_AGENT_OUTPUT_OPERATION (operation outside PLAT-005), INVALID_CAPABILITY_REQUIREMENTS (invalid --require), CAPABILITY_REQUIREMENTS_UNMET (a completed negative capability result), AGENT_OUTPUT_LIMIT_EXCEEDED (the 64 KiB limit), AGENT_OUTPUT_INTERNAL_ERROR (an unexpected safe failure), and INVALID_CORRELATION_ID (an invalid root correlation ID). Warning codes are DUPLICATE_CAPABILITY_REQUIREMENT, CAPABILITY_CONTEXT_UNAVAILABLE, CAPABILITY_CONTEXT_UNRESOLVABLE, and AGENT_MODE_RESTRICTION_ACTIVE.

A capability is one atomic dbcli ability — schema.read, data.delete. It is never a job or a method: dba.tune-production and crud.scaffold belong to the Role Skill or Method Skill that composes dbcli. The layering is Story + AGENTS.md + Role Skill + Method Skill + Tool Skill + dbcli, and dbcli only ever answers for the last two.

Four things the output deliberately does not mean:

• Discovery is not a grant. A listed capability says the binary can do it, not that you may.
• available is not approval. It says engine and permission would not refuse. Blacklist, write gate, confirmation and audit all still run, and no human has agreed to anything. admin in a config file is a permission level, not a DBA sign-off.
• schemaVersion is not the package version. Pin the contract version, not 7.x.
• A Task Pack plan is not a result. status: "planned" remains distinct from a verification outcome.

Statuses are available, unavailable and unknown. The unavailable reasons are engine, agent-mode, permission, context-unavailable and context-unresolvable, reported least-fixable first so the reason names the blocker actually in the way. Under DBCLI_AGENT_MODE=1, capabilities that change configuration are refused whatever the permission level says. context-unresolvable is deliberately distinct from context-unavailable: a config whose {"$env": "..."} password names an unset variable is present and readable, and claiming there is no config would be false.

An unrecognised id fails closed and is never resolved to a similar-looking one. Exit codes: 0 all available, 1 any unavailable or unknown, 2 invalid input.

The catalog covers every public command, capabilities itself included, and every engine claim in it was read out of the implementation. Commands whose code refuses a non-SQL connection — explain, plan, assert, snapshot, verify, proxy — report unavailable with reason engine on MongoDB, Redis and Elasticsearch. Commands where only one mode needs SQL — --against-cache for impact assess and design, and draft validate for semantic — stay available and are marked limited on those engines. Nothing is marked supported on an engine the code does not settle.

required and results hold your ids in first-seen input order, so results[i] answers required[i] and a duplicate appears once. Reordering the arguments reorders the output — identical input is byte-identical output, but a reordered --require is different input. What reordering never changes is a capability's status, its reason, or the overall ok.

inspect output for agents

dbcli inspect returns two parallel arrays so an agent can orient on the very first call:

• suggestedCommands — executable next steps, ordered in three tiers:
  1. Bootstrap — dbcli schema --refresh (when the schema cache is missing or stale) and dbcli list --format json.
  2. Context-aware — driven by recent activity. When a hot table is detected in the audit log and task packs are available, it suggests dbcli skill tasks plan analyze-table-perf --param table=<table>, plus dbcli queries suggest <intent> from your snippet intents.
  3. Discovery — dbcli skill tasks list (when task packs exist) and dbcli doctor --format json.
• hints — human-readable, non-executable notes: the most-queried table from recent audit, the number of available task packs, and the schema-cache size with its last-refresh timestamp. In markdown output these render as a ## Hints section.

Both arrays are trimmed under --for-agent / --brief (≤ 3 hints, and a single safest suggested command).

Querying & Data Operations

# Raw Query
dbcli query "SELECT * FROM users LIMIT 10"

# Static SQL advisor (no DB connection)
dbcli lint "SELECT * FROM users" --format json

# Run Snippet
dbcli q @snippet --param id=1

# Export Data
dbcli export "SELECT * FROM logs" --format csv --output logs.csv

# Safe Write
dbcli insert users --data '{"name":"Alice"}' --dry-run

# DML --plan preflight (no DB connection; SQL, MongoDB, Redis, Elasticsearch)
dbcli insert users --data '{"name":"Alice","email":"a@b.com"}' --plan --format json
dbcli update users --where '{"_id":"abc"}' --set '{"status":"inactive"}' --plan
dbcli delete products --where '{"_id":"abc"}' --plan --format json
dbcli delete 'user:42' --where '' --plan --format json

Write confirmation gate (2.0.0)

Every write goes through a two-tier gate. Tier one is a question; tier two is a refusal you cannot flag your way past.

Tier one — ordinary writes. An INSERT, an UPDATE or DELETE that has a WHERE, a CREATE, an ALTER. At an interactive terminal dbcli prints what it understood the statement to do and asks for confirmation. --yes skips it. A non-interactive run — piped stdout, CI, an agent — never sees it, and --format json suppresses it even at a terminal.

Tier two — writes that cannot be taken back. An UPDATE or DELETE with no WHERE clause, a DROP, a TRUNCATE, a statement the SQL parser cannot read, several statements in one string, a write nested inside another statement (a data-modifying CTE such as WITH x AS (DELETE FROM t RETURNING *) INSERT INTO …), a MERGE carrying a WHEN … THEN DELETE or THEN UPDATE action, and — for dbcli update / dbcli delete — a --where that matches on no primary key and no unique index. dbcli asks you to type the target table name. No flag skips this, --yes and --force included. When nobody is watching, dbcli refuses: exit code 1, nothing sent to the database, and a message naming reason=no_where, reason=ddl_destruction, reason=unparseable, reason=multiple_statements, or reason=non_unique_where, reason=multi_table, reason=nested_write.

A write that joins another table is tier two, whatever its WHERE says. Whether such a write is limited to particular rows depends on the data, not on the statement: DELETE p FROM p JOIN o ON p.id = o.ref WHERE o.x > 0 deleted 2 of 5 rows against one dataset and all 2000 against another. A join's ON always mentions the target, so it proves nothing. This refuses the standard UPDATE … FROM, UPDATE … JOIN … SET and multi-table DELETE for unattended callers — rewrite the other table into a subquery in the WHERE, or run them where someone can confirm.

For a single-table write, the WHERE has to be about that table. A WHERE that lives only inside a subquery does not count: UPDATE p SET c = (SELECT max(x) FROM o WHERE o.id = 1) has a WHERE and rewrites every row. A correlated reference back to the target does count, so DELETE FROM t WHERE EXISTS (SELECT 1 FROM o WHERE o.tid = t.id) is an ordinary write, while the same statement with WHERE o.id = 1 inside deletes everything and is tier two. This is a lower bound rather than a guarantee — WHERE id IS NOT NULL names the target and still touches every row.

The escape route lives in the statement, not in a flag. To run a full-table write unattended, say so in the SQL — add WHERE 1=1 or a LIMIT. This is deliberate: appending WHERE 1=1 to a statement that already has a WHERE is a syntax error, so "add it everywhere just in case" breaks immediately and cannot become a habit. A flag would have the opposite property. For DROP and TRUNCATE there is no clause to add: whether they are possible at all is decided by the connection's permission level, and the typed confirmation still has to come from a person.

In dbcli shell, tier two applies and tier one does not. Every line in a shell is typed by a person, so a y/N on each write would become reflex; a full-table DELETE, a DROP or a TRUNCATE still asks for the table name. Typing anything else prints a cancellation and returns you to the prompt — the session, the connection and the history survive. Ctrl-C withdraws the question itself: nothing runs, and the next line you type is read as a statement rather than as an answer. Piped input (dbcli shell < script.sql) has nobody to answer, so a tier-two statement there is refused and the remaining lines still run. The Redis, MongoDB and Elasticsearch shells do not use the write gate; their writes are bounded by the connection's permission tier instead. For Elasticsearch that guard did not exist before 4.0.0 — the ES shell reached the cluster with no permission check at all, so a query-only connection could delete documents, drop an index or rewrite a mapping, and nothing was recorded. It now classifies every request through the same classifier dbcli query uses, refuses what the tier does not permit, and audits both executed and refused requests — one row before the request goes out and one after it returns, each naming the operation as <METHOD> <path>. Server-side scripts (script, script_fields) are refused in the shell as they already were in dbcli query: they run arbitrary code on the cluster and can read a blacklisted field back under a name of the request's choosing. A piped shell now finishes writing its audit rows before exiting, so a piped DELETE can no longer reach the cluster without leaving a record; the same fix applies to the SQL shell. The SQL shell audits every statement from 5.0.0, in the same two rows the Elasticsearch shell writes — attempt before it is sent and outcome after it returns, with a refused statement writing only outcome because it was never attempted. Before this it recorded nothing but tier-two write-gate decisions, so a SELECT typed at the prompt and an UPDATE that changed a row were equally invisible while the same statements through dbcli query were recorded. Both shells carry the phase in metadata.shell_phase. Audit writes are best-effort by default — a full disk or an unwritable .dbcli/audit lets the request through with only a warning — so set audit.strict: true in your config if an unrecorded request is worse than a refused one. It applies wherever dbcli records an operation *before* performing it: both shells' pre-flight rows and the SQL write gate's decision — which now includes a SELECT typed at the dbcli> prompt. Records written after the fact are outside it, because refusing then would report a completed operation as a failure rather than prevent one. dbcli audit health shows whether it is on. dbcli subcommands typed in the shell (query "...", \delete ...) run as separate processes with no stdin and cannot ask anything, so a tier-two statement there is refused with a message pointing you back to the dbcli> prompt.

A subcommand named after a SQL keyword needs a \ prefix. delete users --where id=1 is read as SQL — a shell is for typing SQL, and DELETE FROM users WHERE … has to work as typed — so it waits for a semicolon instead of running the subcommand. Type \delete users --where id=1 to reach the subcommand. The same applies to insert, update and explain — the four subcommands whose names are SQL keywords; the prefix works on every subcommand, so it is one rule rather than a list. While a statement is still accumulating, .quit and .clear keep working and Ctrl-C cancels — they used to be swallowed into the buffer, which made the shell look frozen.

A subcommand runs in its own process with no stdin, so its ordinary confirmation prompt reads EOF and cancels: \insert, \update and \delete need --force from inside the shell, or type the SQL at the prompt instead, where you can answer. The tier-two confirmation is not affected — it is refused in the child and points you back to the prompt, which is where a table name can actually be typed.

Every tier-two evaluation is written to the audit log — allowed, declined and refused alike — so the gate's effect is measurable rather than assumed. dbcli audit write-gate is that measurement: how often tier two was reached, under which criterion, and how it was answered. If it reports zero, the criterion is what needs revisiting, not the gate.

# Refused: no WHERE and nobody to confirm it
dbcli query "UPDATE users SET banned = 1" --format json
# → exit 1, reason=no_where, nothing was sent to the database

# Accepted: the intent is in the statement
dbcli query "UPDATE users SET banned = 1 WHERE 1=1" --format json

# An ordinary write, unattended or with the question skipped
dbcli query "UPDATE users SET banned = 1 WHERE id = 3" --yes

# Structured delete matching on a non-unique column: tier two
dbcli delete users --where "status=active"

Query input from positional text, files, or stdin

dbcli query [sql] [-f, --query-file <path>] requires exactly one query source: positional query text, a UTF-8 file named by --query-file, or stdin via --query-file -. Providing no source or combining positional text with --query-file is an error. After reading the source, dbcli removes one leading UTF-8 BOM and surrounding whitespace; an input that is then empty is rejected. --query-file - requires piped input: when stdin is an interactive terminal dbcli refuses immediately instead of waiting for input with no prompt.

Use stdin for multiline SQL without escaping it as one shell argument:

dbcli query --query-file - <<'SQL'
SELECT id, email
FROM users
WHERE status = 'active'
ORDER BY id;
SQL

MongoDB filters and aggregation pipelines use the same file/stdin sources. If pipeline.json contains [{"$match":{"message":{"$regex":"user's event"}}}], either form below avoids having to shell-escape the apostrophe:

dbcli query --collection raw_logs --query-file ./pipeline.json

dbcli query --collection raw_logs --query-file - <<'JSON'
[{"$match":{"message":{"$regex":"user's event"}}}]
JSON

Passive slow-query hints

query and q observe the execution time already returned for a completed query. At the default 1000ms threshold, a slow result receives a Performance hint in table output and a metadata.performanceAdvisory object in JSON output. Fast results remain unchanged, so normal queries do not produce repetitive advice.

# Suggest a review for a query taking 250ms or more
dbcli query "SELECT * FROM events WHERE account_id = 42" --slow-ms 250

# Disable the passive hint for one invocation
dbcli q @daily-active-users --slow-ms 0

The hint does not run EXPLAIN, inspect schema, or issue a second database request. On PostgreSQL, MySQL, MariaDB, and Redis it recommends the non-mutating next step dbcli guide slow-query --format markdown; run deeper diagnostics only after reviewing that guidance and the relevant query. MongoDB and Elasticsearch ship no diagnostic snippet for that goal, so their hint reports the timing without naming a command that would return nothing.

Field projection with --fields

--fields reduces SQL or MongoDB query results to the fields you need. Use an inclusion list to keep fields in the requested order:

dbcli query "SELECT * FROM events" --fields id,name,created_at
dbcli query '{}' --collection raw_logs --fields station_code,bet,win,created_at

Prefix every field with - to exclude it. The portable spelling uses = so the leading hyphens are unambiguously part of the option value:

dbcli query "SELECT * FROM events" --fields=-raw_response,-request_payload
dbcli query '{}' --collection raw_logs --fields=-raw_response,-request_payload

An invocation must use either inclusion or exclusion syntax; the two modes cannot be mixed. Empty lists or entries and duplicate paths are rejected. Dotted paths such as profile.name are supported, and inclusion output follows the requested order. A MongoDB inclusion excludes _id unless _id is explicitly requested. A requested field that the result does not contain is reported as null rather than raising an error, so a misspelled field name yields a column of nulls — check the spelling against dbcli schema before reading meaning into an all-null column.

SQL projection is applied to the returned rows after the query runs. MongoDB pushes the projection into find or the aggregation pipeline to reduce transferred data, then normalizes the returned rows again after blacklist masking. Redis and Elasticsearch queries do not support --fields.

The blacklist remains the final authority: --fields cannot reveal a protected field, and field projection is a result-shaping convenience, not a security boundary.

Mutation outcomes

After insert, update, or delete finishes in an interactive terminal, dbcli summarizes what happened in plain language: the affected row count and table, no-match, cancellation, dry-run, or failure outcome, plus elapsed time when work actually ran. Redirected or piped stdout remains the stable JSON result envelope for scripts. Use --format json to keep that envelope even in a terminal. The same outcome vocabulary applies across SQL, MongoDB, and Redis; dry runs report dry_run, never success.

A write that changed rows also states how to get the previous values back — which, for these commands, is a backup: dbcli keeps no automatic undo for a write that succeeded. A failure instead names --recovery, the flag that writes a recovery plan for dbcli recover to read. Outcomes that changed nothing (cancelled, dry-run, no rows matched) say nothing about reversal, because there is nothing to reverse.

A failure is prose too, and it goes to stderr with the exit code 1: a blacklist refusal, a malformed --set, or a statement the database rejected. --format accepts only text or json here; anything else is refused before dbcli connects, rather than silently meaning text.

The confirmation a non-forced mutation asks for — the generated SQL, the parameters, the destructive-delete warning, and the y/n question — is written to stderr, not stdout. A terminal shows both, so nothing looks different when you run one by hand; a script that captures stdout gets exactly one JSON document whether or not it passed --force. Neither a dry run nor a cancellation sends a write to the database, but both do connect and read the table schema, because the SQL they exist to show cannot be built without the column list; use --plan for the preflight that opens no connection at all. MongoDB and Redis writes ask the same question — they are issued straight from the command rather than through the SQL executor, and used to run unasked — so cancelled is now reachable on every engine. Their prompt shows the statement the dry run would print, with no parameter block, since those statements carry their values inline.

--recovery also changes what a failed insert / update / delete writes to stdout: on failure the recovery envelope replaces the JSON result envelope rather than following it, so a caller parsing stdout gets exactly one document either way. Without the flag, a failure prints the result envelope with status: "error" and exits 1, as before.

insert, update, and delete accept --plan to run a static risk analyzer against the planned write without connecting to the database. The planner now supports SQL (postgresql, mysql, mariadb), MongoDB, Redis, and Elasticsearch. It honors the connection's permission, blacklist, and the cached schema for the selected engine. --format text (default) prints a human verdict, --format json prints the full QueryRiskResult. Analyzer BLOCK decisions exit 0; configuration / engine / invalid-DSL errors exit 1. --plan is mutually exclusive with --dry-run.

Conservative MVP restrictions per engine:

BLOCK means the planner found an unsafe intent. Still run --dry-run on the real command before executing the write.

Snippet Management

Maintain complex queries as version-controlled snippets in your repository with visualization metadata.

• dbcli queries list: View the full inventory of available snippets.
• dbcli queries search: Search snippets using fuzzy keywords.
• dbcli queries suggest: Get suggestions based on operational intent.
• dbcli queries new @my/query --local: Scaffold a new local snippet.

Health & Diagnostics

dbcli backfill artifact --source ./backfill.json \
  --source-use staging --target-use production

dbcli skill tasks plan orm-drift-review --param orm_path=prisma/schema.prisma --format json
dbcli diff --against-orm prisma/schema.prisma --format json

drizzle-kit generate
dbcli diff --against-orm drizzle/meta/0001_snapshot.json --orm-format drizzle --format table

bunx typeorm schema:log -d <path/to/datasource> > schema.sql
dbcli diff --against-orm schema.sql --orm-format typeorm --format table

# Point the project Sequelize config at an empty scratch database first
bunx sequelize-cli db:migrate
pg_dump --schema-only <scratch-database> > schema.sql
# For MySQL instead:
mysqldump --no-data <database> > schema.sql
dbcli diff --against-orm schema.sql --orm-format sequelize --format json

dbcli diff --against-orm "migrations/*.sql" --orm-format ddl --format markdown
dbcli diff --against-orm schema.normalized.json --orm-format json --format table
dbcli diff --against-orm prisma/schema.prisma --recovery --format json

dbcli skill tasks plan migration-review \
  --param "table=${exact_table}" \
  --param "ddl=${captured_ddl}"

Data Verification

Verify data-processing correctness — capture a result fingerprint, then assert invariants against it, a second query, or inline conditions. SQL engines only (PostgreSQL / MySQL / MariaDB).

assert --write-verification-artifact

Persist a result evidence record (v1 VerificationArtifact JSON) to .dbcli/verification/ whenever you need a durable audit trail for a read-back assertion. The verification artifact is always written to <cwd>/.dbcli/verification/ (relative to the current working directory), regardless of where the --config file is located.

Flags:

assert --expect ... --evidence-receipt <path> can write a receipt without --write-verification-artifact. Only boolean check pass bits are projected into the existing receipt schema; raw SQL and result values are excluded. A failed assertion still writes a failed receipt when requested. --no-fail changes only the assertion's exit behavior: it does not turn the receipt into a success, and a receipt write error still exits 1.

evidence compose may reference that explicit workspace-contained receipt with --receipt <path>; provenance is not execution approval.

Output contract:

• --format json — adds verificationArtifactPath to the AssertVerdict envelope.
• --format table — prints an extra Verification artifact: <path> line.
• Status follows assertion truth: --no-fail failures still record not_verified / evidence exitCode: 1.

Planned vs Result evidence. dbcli skill tasks plan safe-backfill-verify produces a plan JSON with a verification block whose status is "planned" — this is the planned evidence definition describing which check will run. The final assert --write-verification-artifact step produces result evidence (status: verified or not_verified). These are two different records; "planned" does not mean verification has run.

Note: Bigint aggregates (count(*), sum()) arrive from Postgres as strings and are compared numerically anyway, so value == 0 works without a cast. Quote the expectation (value == "0") when you mean a text comparison.

# 1. plan the workflow (plan-only, planned evidence)
dbcli skill tasks plan safe-backfill-verify \
  --param table=orders \
  --param query="UPDATE orders SET status = 1 WHERE status IS NULL" \
  --param verify_query="SELECT count(*)::int FROM orders WHERE status IS NULL" \
  --param expect="value == 0"

# 2. dry-run the write manually
dbcli update orders --where "status IS NULL" --set '{"status": 1}' --dry-run

# 3. execute the write under existing write permissions
dbcli update orders --where "status IS NULL" --set '{"status": 1}'

# 4. run the final assertion and persist RESULT evidence
dbcli assert "SELECT count(*)::int FROM orders WHERE status IS NULL" \
  --expect "value == 0" \
  --write-verification-artifact \
  --verification-subject backfill:safe-backfill-verify

dbcli verify runs verification scenarios (safe-backfill, migration, rollback, constraint) and never executes writes/DDL. dbcli verification inspects and manages the local result artifacts those scenarios produce under .dbcli/verification/.

After an --after-write run, add --evidence-receipt <workspace-relative-path> to atomically write safe provenance linked to the result artifact. The receipt contains no SQL, rows, credentials, or user paths; it is provenance only, not execution approval. Preflight has no executed artifact and does not support a receipt. Receipt outcome (succeeded/failed) stays separate from scenario status (verified, not_verified, indeterminate, or blocked); task-pack planned evidence remains plan-only.

inspect, report, schema, plan, lint, explain, and impact assess also accept --evidence-receipt <workspace-relative-path>. Their receipt contains only a fixed command subject, capability, timestamp, safe context digests, and an optional correlation ID. Successful and failed command outcomes both produce a receipt when requested; a receipt write failure does not change the command result or exit code.

verify safe-backfill

Verify a safe backfill without ever executing the UPDATE. Preflight (default) runs read-only guards and prints the exact after-write command; --after-write re-runs the guards, runs the read-back assertion, and writes a verification artifact.

⚠️ verify safe-backfill never executes the backfill write. Run the approved write through your normal write command first, then run --after-write.

Preflight:

dbcli verify safe-backfill \
  --table users \
  --query "UPDATE users SET status = 1 WHERE status IS NULL" \
  --verify-query "SELECT count(*)::int AS n FROM users WHERE status IS NULL" \
  --expect "value == 0"

After-write (writes the artifact):

dbcli verify safe-backfill ... --after-write

Inspect the result: dbcli verification show <artifact-id>.

Preflight also echoes the planned update (the --query you must run yourself) so the printed output is a complete, copy-pasteable record of the operation.

Result status: verified (read-back matched --expect), not_verified (read-back contradicted --expect), blocked (a guard failed — blacklist, schema, plan, or a non-read-only verify-query), indeterminate (the assertion ran but could not yield a trustworthy verdict).

All four guards run and report their individual outcomes even if one fails. After-write runs the assertion only when all four pass; persisted failure reasons are fixed safe labels rather than driver error text. Persisted custom labels and SQL evidence also redact credentials, filesystem paths, and SQL comments.

Guard constraints (fail closed):

• --verify-query must be a plain SELECT. EXPLAIN / EXPLAIN ANALYZE, SHOW, DESCRIBE, and data-modifying CTEs (WITH … (DELETE … RETURNING) …) are rejected — on PostgreSQL EXPLAIN ANALYZE <write> actually performs the write, so the read-back is restricted to statements that can never mutate data.
• The --query UPDATE target must equal --table, compared schema-aware (public.users does not satisfy --table audit.users). An UPDATE against any other table is blocked, so the read-back you assert on always matches the table you wrote.
• The persisted artifact stores only a bounded, literal-free label of the verify-query and --expect — string, numeric, and dollar-quoted ($$…$$) literals are stripped, so raw SQL and any embedded values are never written to disk.
• The printed after-write command is shell-escaped, so it stays correct even when the SQL contains quotes; it also carries through --subject-name, --summary, and a non-default --format.

💡 Repeated backfills on the same table. Artifacts default their subject name to the table (backfill:<table>). When you run multiple distinct backfills against the same table, pass --subject-name <unique-label> so each operation is independently traceable in dbcli verification list.

verify migration

Preflight or after-write verification for a schema migration. This command never executes DDL — it analyzes the proposed ALTER TABLE, runs read-only guards, and (in after-write mode) records evidence after you apply the migration externally.

⚠️ verify migration never executes DDL. Apply the migration externally first, then run --after-write to record evidence.

Preflight:

dbcli verify migration \
  --table users \
  --ddl "ALTER TABLE users ADD COLUMN verified_at TIMESTAMPTZ" \
  --verify-query "SELECT count(*)::int AS n FROM users WHERE verified_at IS NOT NULL" \
  --expect "value == 0"

After the migration is applied externally:

dbcli verify migration ... --after-write

Preflight returns ready or blocked and prints the exact after-write command; ready is not verified — it only means the guards passed. After-write maps the read-back assertion to verified / not_verified / indeterminate, and a failed guard to blocked. CREATE TABLE, DROP TABLE, CREATE INDEX, and multi-statement DDL are blocked in the MVP.

The ALTER TABLE target may be table, schema.table, or catalog.schema.table. Each segment is a simple unquoted name ([A-Za-z_][A-Za-z0-9_]*) or a quoted identifier — double-quoted ("…"), backtick-quoted (`…`), or bracket-quoted ([…]) — so "user accounts" or "tenant-1"."orders" are accepted. Targets that cannot be fully parsed under this contract (unterminated quotes, unsupported escapes, or more than three parts) are blocked before the after-write assertion with a "could not be parsed" reason, distinct from the must match --table mismatch reason.

verify rollback

Preflight or after-write verification for a rollback you apply externally — either reverting a schema migration (--kind ddl) or reverting a data change (--kind dml). This command never executes the reverting statement — it analyzes the proposed --statement, runs read-only guards, and (in after-write mode) records evidence after you apply the rollback yourself.

⚠️ verify rollback never executes the rollback statement. Apply the rollback externally first, then run --after-write to record evidence.

Schema rollback preflight (--kind ddl, a single ALTER TABLE):

dbcli verify rollback \
  --kind ddl \
  --table users \
  --statement "ALTER TABLE users DROP COLUMN verified_at" \
  --verify-query "SELECT count(*)::int AS n FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'verified_at'" \
  --expect "value == 0"

Data rollback preflight (--kind dml, a single UPDATE):

dbcli verify rollback \
  --kind dml \
  --table users \
  --statement "UPDATE users SET status = NULL WHERE status = 9" \
  --verify-query "SELECT count(*)::int AS n FROM users WHERE status = 9" \
  --expect "value == 0"

After the rollback is applied externally:

dbcli verify rollback --kind <ddl|dml> ... --after-write

--kind selects which grammar the statement must satisfy and reuses the same guards as the sibling scenarios: ddl reuses the verify migration ALTER TABLE contract (single statement, target must match --table); dml reuses the verify safe-backfill plan contract (UPDATE only, must have a WHERE, target must match --table). Preflight returns ready or blocked; ready is not verified. After-write maps the read-back assertion to verified / not_verified / indeterminate, and a failed guard to blocked. The artifact records the rollback under the existing subject kind (migration for ddl, backfill for dml) with command: verify rollback. The MVP supports a single ALTER TABLE for ddl and a single UPDATE for dml; INSERT/DELETE reverts are not yet supported.

verify constraint

Preflight or after-write verification that a data-integrity invariant holds across your change — foreign-key consistency, NOT NULL coverage, uniqueness, or a custom violation query. This command never executes a write — it only runs read-only COUNT(*) violation queries and (in after-write mode) records evidence.

⚠️ verify constraint never executes a write or DDL statement. Run preflight before your change, then run --after-write afterward to record evidence.

Four check kinds, selected by --check:

• fk — counts orphaned rows (child column has no matching parent). Requires --column and --references <table.column>.
• not-null — counts NULL values in the column(s). --column is repeatable.
• unique — counts duplicate values across the column(s). --column is repeatable.
• custom — executes your --violation-query <sql> (a read-only SELECT returning a single integer count of violations).

FK preflight (verify no orphaned orders before a migration):

dbcli verify constraint \
  --table orders \
  --check fk \
  --column customer_id \
  --references customers.id

NOT NULL preflight (verify the column is fully populated):

dbcli verify constraint \
  --table users \
  --check not-null \
  --column email

After the change is applied externally:

dbcli verify constraint --table orders --check fk --column customer_id \
  --references customers.id --after-write

Preflight returns ready or blocked; ready is not verified. After-write maps the count to verified (violations ≤ threshold) or not_verified (violations > threshold). The default threshold is 0 (strict). With --allow-preexisting, the threshold is the --baseline count from preflight — verified as long as violations do not exceed the pre-existing level. A query error yields indeterminate; a failed guard yields blocked. The artifact uses subject.kind = 'table' and command: verify constraint. MVP: SQL engines only; single FK column; never executes writes.

verification — inspect & manage verification artifacts

dbcli verification works on artifacts written under <cwd>/.dbcli/verification/. It never connects to a database and never writes audit entries. list, show, and summary are read-only filesystem inspection; prune is a local lifecycle command that is dry-run by default and deletes files only with --execute --force. The storage root is the current working directory, independent of --config.

• dbcli verification list [--format json|table] [--limit <n>] [--status <status>] [--subject <kind[:name]>] [--include-invalid] — list artifacts latest-first.
• dbcli verification show <id-or-path> [--format json|table] — print one artifact by exact id, unique id prefix, filename, or in-bounds path.
• dbcli verification summary [--format json|table] [--status <status>] [--subject <kind[:name]>] [--latest-only] — latest status, status counts, invalid count, and per-subject breakdown. --latest-only narrows to the latest matching valid artifact plus status counts (the subjects breakdown is omitted); missing artifacts return exit 0 with latest: null.
• dbcli verification prune --older-than <Nd> [--format json|table] [--keep-latest <n>] [--status <status>] [--subject <kind[:name]>] [--include-invalid] [--execute --force] — preview (dry-run) or delete local artifact files by retention criteria.

Statuses: verified, not_verified, indeterminate, blocked.
Subject kinds: recovery, task-pack, assertion, migration, backfill, manual.

A missing .dbcli/verification/ directory returns an empty result and exits 0. Malformed files are skipped during list/summary (surfaced via --include-invalid and the summary invalid count); selecting a malformed file with show exits 1.

prune is dry-run by default. --keep-latest (default 20) always protects the newest N valid artifacts across all subjects and statuses before the --status/--subject filters are applied. prune deletes files only when both --execute and --force are given, and only regular verification-*.json files inside .dbcli/verification/; execute-mode table output lists each deleted and skipped file.

dbcli verification summary --format json
dbcli verification list --status verified --subject backfill:safe-backfill-verify
dbcli verification show ver_abcd --format json
dbcli verification prune --older-than 30d --format json
dbcli verification prune --older-than 30d --keep-latest 20 --execute --force

evidence — compose offline evidence packs

dbcli evidence combines existing verification artifacts and audit entries into a canonical, workspace-contained JSON pack for review or handoff. It never opens a database connection and does not copy SQL, targets, audit metadata, verification summaries, or credentials into the pack. Claims are external statements, explicitly labeled as such; they are not dbcli verification verdicts.

• dbcli evidence compose --claims <file> [--verification <selector...>] [--audit <selector...>] [--receipt <path...>] --output <path> [--format json|markdown] — resolves existing references and writes a new pack. A receipt is provenance only, not execution approval. The claims JSON has exactly subject and claims; claim text cannot contain SQL, credentials, error content, or blacklisted identifiers. Output stays in the current workspace and never overwrites an existing file.
• dbcli evidence validate --file <path> [--format json|markdown] — validates the SHA-256 digest and referenced evidence availability. A retained pack whose audit/artifact later rotates, is cleared, or disappears reports references: "source-expired" and exits 1, but remains renderable.
• dbcli evidence render --file <path> [--format json|markdown] — validates the active blacklist policy and renders a valid pack without rereading original references, including after source retention expires.

dbcli evidence compose --claims ./claims.json --verification ver_abcd --audit 1a2b \
  --receipt .dbcli/evidence/verify-receipt.json \
  --output .dbcli/evidence/review.json
dbcli evidence validate --file .dbcli/evidence/review.json --format json
dbcli evidence render --file .dbcli/evidence/review.json

Artifact format versions

Packs and receipts carry their own version, which is not the dbcli package version (see ADR-0013). The current format is version: 2; dbcli 3.0.0 and earlier wrote version: 1 in two mutually incompatible layouts. validate --format json reports one of three answers in status, and only the first means the pack can be relied on.

Legacy packs and receipts are readable and integrity-checkable but are never treated as current-valid, and there is no migration: a pack's identity is derived from its digest, so rewriting one would mint a new artifact wearing an old one's provenance. Compose a new pack from current evidence instead.

dbcli proxy — Local Observability Proxy

A local development observability proxy — point an existing application at the proxy port and dbcli relays every query to the real database while recording query text, latency, byte counts, row counts, and error events. This is NOT a production gateway. Use it during local development only.

Quick Start

# Explicit upstream/downstream
dbcli proxy mysql --listen 127.0.0.1:3307 --target 127.0.0.1:3306
dbcli proxy postgresql --listen 127.0.0.1:5433 --target 127.0.0.1:5432

# Infer engine + target from a named connection
dbcli --use local proxy --listen 127.0.0.1:3307

Change your application's DB host/port to the --listen address and leave credentials unchanged. The proxy is fully transparent — the application behaves identically.

Options

Event Log (JSONL)

Each completed query appends one JSON object to the event log:

{"version":1,"type":"query_completed","timestamp":"2026-06-04T12:00:00.000Z","engine":"mysql","sessionId":"pxy_1","queryId":"qry_pxy_1_1","client":"127.0.0.1:54321","target":"127.0.0.1:3306","sql":"SELECT * FROM users WHERE id = ?","statement":"SELECT","tables":["users"],"durationMs":4,"requestBytes":42,"responseBytes":318,"rowCount":1,"error":null,"tags":[]}

Privacy

SQL text is always stored in the event log. Result rows are never stored. Use --redact literals to mask string and number literals in SQL before logging (e.g. WHERE id = ? instead of WHERE id = 42).

Analyze the event log offline

dbcli proxy analyze — analyze the captured event log offline (no DB). --format json|text, --top, --slow-ms, --n-plus-one, --no-include-rotated. Produces summary, per-fingerprint stats, slowest queries, error groups, hot tables, and N+1 suspects.

Each actionable block carries machine-readable next steps so an AI agent can move from diagnosis to fix:

• SELECT hotspots / N+1 — suggestedCommands with dbcli explain "<sql>" and dbcli guide missing-index-for "<sql>".
• Errors — suggestedCommands with dbcli schema <table> (first 3 tables) plus a hints note to verify table/column names before fixing — never guess column names.
• N+1 suspects — a hints note suggesting you batch the repeated query (JOIN / IN (...)) or cache the result.

The recommended loop: run proxy analyze, then for each finding read its hints, run its suggestedCommands to gather schema/plan evidence, and apply the fix. The text format aggregates these into SUGGESTED COMMANDS and HINTS sections; the JSON format keeps them attached to each finding. Suggestions are printed as strings only — proxy analyze never executes them. When the proxy ran with --redact literals, the example SQL contains ? placeholders; fill in real values before running the commands.

Limitations (v1)

• TLS: TLS is relayed but not decrypted in v1. Encrypted sessions still produce session and byte-count events, but no SQL is parsed or visible — disable SSL for local analysis sessions when you need query visibility.
• MySQL prepared/binary protocol: Best-effort parsing; tagged prepared_statement.
• PostgreSQL extended query protocol: Best-effort parsing; tagged extended_protocol or parse_partial.

QueryLens — Proxy Query Analysis

QueryLens is the shareable Markdown report format of dbcli proxy analyze. It turns a proxy event log into a local query-analysis report; it is not a complete database-protocol analyzer.

It needs neither a database connection nor a dbcli connection configuration when analyzing: it reads the JSONL file on disk only. The QueryLens Markdown report redacts SQL literals before analysis. Capture with literal redaction as well, so the event file itself does not retain application values.

Quick Start

# 1. Capture local development traffic in a redacted JSONL log.
dbcli proxy mysql --listen 127.0.0.1:3307 --target 127.0.0.1:3306 \
  --events .dbcli/proxy/events.jsonl --redact literals

# 2. Produce the QueryLens Markdown report locally; no database is contacted.
dbcli proxy analyze \
  --events .dbcli/proxy/events.jsonl --format markdown

# The existing proxy JSON report remains available for machine consumption.
dbcli proxy analyze \
  --events .dbcli/proxy/events.jsonl --format json

Point the application at the proxy's --listen address while it runs. After stopping the proxy, run the analysis command against the resulting log from the repository root.

Analysis Options

QueryLens focuses on the proxy events it can read; do not rely on it as evidence that every database protocol or query form was captured. Treat its findings as investigation leads, then verify a query and its schema or plan before changing production code.

Advanced Tools

dbcli semantic validate --format json
dbcli semantic context --format json
dbcli semantic search purchases --kind model --format json
dbcli semantic drift --format json
dbcli semantic migrate --to 2 --format json
dbcli skill context --format json

dbcli contract validate --format json
dbcli contract context --format json
dbcli contract search customer --format json
dbcli contract drift --format json

{
  "version": 1,
  "questionHash": "<sha256-of-the-original-question>",
  "candidate": { "kind": "sql", "sql": "<reviewed-read-only-sql>" },
  "semanticReferences": ["model:<model>", "field:<model>.<field>"]
}

# Writes only to this explicit missing path; edit the starter before validation.
dbcli design init --output ./dbcli.design.json --dialect postgresql

# Both commands are offline and read-only.
dbcli design validate --format json
dbcli design render --format mermaid
dbcli design diff --against-cache --format markdown
dbcli design diff --against-orm ./prisma/schema.prisma --format markdown
dbcli design propose --against-orm ./prisma/schema.prisma --format markdown

dbcli impact assess --design ./dbcli.design.json --against-cache --events ./.dbcli/proxy/events.jsonl --output ./impact.json --format json --fail-on warn
dbcli impact assess --design ./dbcli.design.json --against-orm ./prisma/schema.prisma --output ./impact.md --format markdown --fail-on never

📊 Interactive HTML Dashboards

Generate standalone React-powered reports by appending the --ui flag.

dbcli query "SELECT day, count(*) FROM sessions" --ui

Rich visualizations (charts, KPIs) can be configured via the visual: block in snippet frontmatter. Supported chart types are line, bar, area, and pie; any other type is rejected at parse time.

When dbcli's lookahead proves that rows were truncated, the dashboard shows a warning before every KPI, chart, and table and names the applied limit. Blacklist redaction or omission notices appear there as well. This applies to query HTML/UI, saved-query HTML/UI, and HTML exports whenever the execution path produces the corresponding metadata.

Execution traceability (saved queries)

A dashboard generated from a saved query (dbcli q @name --ui or --format html) carries a standalone Execution Traceability section, shown after the truncation and blacklist notices and before the KPIs, charts, and table. It travels inside the HTML file, so a recipient sees it without dbcli, a database, or your workspace.

• Connection — logical connection name: the v2 connection key, or default for a single-connection config. Never a host or endpoint.
• Engine — postgresql, mysql, mariadb, mongodb, redis, or elasticsearch.
• Saved Query — the snippet key, such as @dau. Never its file path.
• Snippet Source — builtin, shared, or local.
• Effective Permission — the permission that actually governed the execution: query-only, read-write, data-admin, or admin.
• Applied Limit — the row cap that actually applied and whether the result was truncated, or "No limit applied", stated explicitly rather than left blank.

The applied-limit line always agrees with the truncation warning above it; a dashboard whose provenance and warning disagree is refused before the file is written.

Traceability is a closed contract. It never carries raw query bodies, parameter values or enums, credentials, endpoints, source paths, verification queries and expectations, target index or collection names, or rows beyond the ones displayed. The same allowlist governs the whole embedded payload, not just the visible section: only the displayed rows, the applied-limit and security notices, the provenance object, and the display name, description, and chart/KPI definitions that reference displayed fields are serialized. Invalid, oversized, or unknown metadata is rejected before any HTML is written, so a failed dashboard never leaves a partial file behind.

Direct-query dashboards (dbcli query --ui, dbcli export --format html) are unchanged and carry no traceability section.

🤖 AI Agent Integration

Dbcli is designed to be the "Database Driver" for AI agents:

• Machine-Readable Errors: JSON-formatted --recovery envelopes.
• Context Optimization: --for-agent flag reduces token bloat.
• Risk Gating: dbcli plan for raw SQL plus per-command --plan on insert / update / delete for DML preflight without connecting to the database.
• Expert Task Packs: Guided workflows for diagnosis and auditing.
• Audit Log: audit tail --for-agent for session handoff; every command's failure with --recovery writes matching audit.recovery_ref ↔ envelope.audit_ref UUIDs so agents can pivot from a recovery envelope to its audit entry via audit show --recovery-ref <id>.
• AI Collaboration Prompting: dbcli skill context serializes connection metadata, schema caches, and saved queries into highly-compressed XML/JSON/Markdown for AI prompt injection.
• Self-Verification Loops: Snippets can declare a verify block in frontmatter; run with --verify to auto-execute checks and assert results.
• Agent Plugin: the repo root follows the Ponytail-style plugin layout with .agents/plugins/marketplace.json, .codex-plugin/plugin.json, .claude-plugin/plugin.json, .cursor-plugin/plugin.json, .github/skills/dbcli/, and skills/dbcli/; when dbcli is not globally installed, the skill falls back to bunx @carllee1983/dbcli <command>. See plugins/dbcli-agent/INSTALL.md for Codex, Claude Code, GitHub Copilot CLI, Antigravity, and Cursor install commands, including Cursor marketplace review/indexing steps.
• Shared agent CLI interface: package consumers can import @carllee1983/dbcli/agent-core for loadEnvFile, resolveEnvRef, resolveConnectionSelector, parseConnectionNames, and trimAppliedLimit plus AppliedLimitMetadata, AppliedLimitResult, and ConnectionSelectorInputs. This small interface is framework- and database-independent and follows semver. The broader ./core product interface remains separate; CLI option factories, config-storage binding, and connection-string parsing deliberately stay outside agent-core.

Bounded cross-engine context (version 2)

Use dbcli skill context --context-version 2 --format json to hand database metadata to an external agent. Version 2 is the stable agent contract: it is offline and never opens a connection, constructs an adapter, scans Redis keys, reads documents, or reads project source. The agent may inspect project code itself under its own workspace safety rules to supply business meaning that context does not contain.

dbcli skill context --context-version 2 --format json

Give the agent this output and its own safely discovered code context; do not give dbcli source paths or ask it to interpret natural language. Treat permission and descriptive capabilities as limits, not authority to execute a command. Use only returned resources and approved semantic/contracts metadata. If metadata is absent or gaps is returned, do not infer names, types, relationships, keys, or meanings: inspect permitted project code or ask for the missing evidence.

Version 2 emits only safe fields: configured engine and permission; blacklist policy; capability command, status, and sideEffectTier; resource/field IDs, names, and types; visible SQL nullable/primary-key and visible foreign-key links; flattened Elasticsearch field paths/types; declared Redis families/fields; snippet metadata without bodies/defaults; declared data-access metadata without source paths; approved semantic/contracts metadata; truncation counts; and gaps. It never emits credentials, values/results, defaults/counts, raw Elasticsearch mappings/settings, Redis keys/values, query bodies/defaults, or project source paths/contents. Blacklisted identifiers appear only in blacklist.

Redis declarations are intentionally small: context file ≤512 KiB; ≤500 families; lowercase kebab-case family names; patterns ≤200 characters with unique valid {placeholder} values and no glob tokens, whitespace, controls, or backslashes; and types limited to string, hash, list, set, zset, or stream. Only hash and stream may declare fields (≤100); descriptions are ≤1,000 characters and aliases are ≤20 of ≤100 characters. A malformed, concrete, unsafe, blacklisted, or Redis-field-mask-overlapping declaration fails rather than exposing a partial model.

Missing optional evidence reports SQL_SCHEMA_UNAVAILABLE, ELASTICSEARCH_MAPPING_UNAVAILABLE, REDIS_KEY_FAMILIES_UNAVAILABLE, SEMANTIC_CONTEXT_UNAVAILABLE, SAVED_QUERIES_UNAVAILABLE, DATA_ACCESS_UNAVAILABLE, or ALL_RESOURCES_FILTERED; truncation also reports CONTEXT_TRUNCATED. Present but invalid evidence fails with the corresponding INVALID_SCHEMA_CACHE, INVALID_SEMANTIC_CONTEXT, INVALID_SAVED_QUERY, INVALID_DATA_ACCESS_MANIFEST, INVALID_REDIS_CONTEXT, or INVALID_RESOURCE_REFERENCE. MongoDB and unknown engines reject explicit v2 with UNSUPPORTED_CONTEXT_ENGINE.

Omitting --context-version keeps byte-compatible version 1 JSON, XML, and Markdown output. version remains configuration metadata; v2 adds integer contextVersion: 2. Consumers must ignore unknown optional v2 fields; incompatible required-field or ID-encoding changes require a new context version.

Intent confirmation for business requests

The installed skill supports three per-request conversational preferences; they are not dbcli flags or saved configuration. An agent must not first ask the meta-question “do you want questions?”

For example, “show yesterday's sales” is ambiguous when its result shape (total or detail), metric definition, timezone, status/refund treatment, grouping, or connection is unknown. The agent should summarize its candidate interpretation and ask only the result-changing questions. If the user explicitly asks it to decide without more questions, it proceeds in auto mode and discloses its material assumptions. This preference never bypasses blacklist, schema, permission, dry-run, production-selection, or write-confirmation gates.

When dbcli is a better fit than an MCP database server or a direct client

There is no universal winner. A direct database client is the shortest path when a trusted human is working in a disposable local environment. An MCP database server is useful when an agent host needs a conversational, tool-shaped integration for low-risk exploration. dbcli is the better boundary when the same database task must be safe and repeatable for an agent, a human, CI, or an incident runbook.

Even for local vibe coding

Local development changes the blast radius, not the basic failure modes. A direct client is perfectly reasonable for a throwaway database with synthetic data when you are watching every statement. It is faster because the agent can connect, inspect, and run ad-hoc SQL immediately.

The trade-off is that the client session becomes the entire safety contract. The agent can still guess a column name, query a local production dump, run an unbounded statement against fixtures worth keeping, or leave a change that nobody can replay when the task moves to CI. A database client may offer its own protections, but they are not automatically the same policy or workflow that the rest of the project uses.

dbcli adds a small, deliberate pause before the agent acts: inspect the protected surface, read the real schema, and preview a write. The commands are also the handoff artifact. A developer can paste the same sequence into a terminal, a test script, or a PR description instead of reconstructing what happened in an agent-owned client session.

dbcli blacklist list
dbcli schema <table> --format json
dbcli update <table> --where "<predicate>" --set '<json>' --dry-run

That does not mean every local SELECT deserves ceremony. Use the direct client for a disposable experiment whose result has no operational life. Use dbcli as soon as the agent touches realistic data, modifies shared fixtures or migrations, needs a repeatable answer, or is likely to carry the work into CI, staging, or production. The value is not that the CLI makes local development magically safe; it makes the transition from a quick experiment to an accountable workflow explicit.

dbcli is stronger in the last case for four practical reasons:

1. The operator can review and authorize the invocation. A command is a concrete artifact: a human can inspect the selected connection, operation, and flags before it runs. On hosts that authorize CLI arguments, such as Claude Code, this permits a useful gradient—safe discovery commands can be pre-authorized while queries or writes still require review. MCP permissions are commonly granted at the tool-name boundary; whether finer argument-level policy exists depends on the host and server.
2. The same guardrails run outside the agent. Permission tiers, blacklist checks, result limits, schema discovery, dry-run preflights, and machine-readable output live on the CLI path. A CI job and an incident responder can use the same commands and receive the same enforcement instead of relying on a particular editor or agent session.
3. The workflow leaves operational evidence. Use --recovery on supported commands to emit a structured failure envelope and link it to the audit record. Saved queries and task packs are files that a team can version, review, and reuse rather than instructions that exist only in a chat transcript.
4. The tool still works when the agent is absent. A terminal, Makefile, CI job, or runbook can execute the command with meaningful exit codes. That makes dbcli a durable operational interface, not only an AI integration.

For example, the safe baseline for an agent-assisted data change is still a sequence a person or CI can reproduce:

dbcli blacklist list
dbcli schema <table> --format json
dbcli update <table> --where "<predicate>" --set '<json>' --dry-run

Use an MCP server when its conversational interface is the main value and its authorization, logging, and execution model satisfy the environment's requirements. Use a direct client when its simplicity is appropriate for the risk. Choose dbcli when the important product is the governed, reviewable execution path—not merely the ability to send a query.

Security boundary: dbcli is defence in depth, not a substitute for database authorization. Give the agent only a least-privilege database credential. A process that can change its own dbcli configuration can raise its declared permission or choose another client; blacklist and dry-run controls cannot make that credential safe by themselves.