dbcli — Database CLI for AI Agents

A unified database CLI tool that enables AI agents (Claude Code, Gemini, Copilot, Cursor) to safely query, discover, and operate on databases.

Core Value: AI agents can safely and intelligently access project databases through a single, permission-controlled CLI tool with sensitive data protection.

Security update: dbcli init now writes only a small project binding stub into ./.dbcli/config.json. The full connection configuration is stored under ~/.config/dbcli/projects/<project-id>/config.json, so sensitive settings do not live inside the project workspace by default.

Internationalization (i18n)

dbcli supports multiple languages via the DBCLI_LANG environment variable:

# English (default)
dbcli init

# Traditional Chinese
DBCLI_LANG=zh-TW dbcli init

# Or set in .env
export DBCLI_LANG=zh-TW
dbcli init

Supported languages:

en — English (default)
zh-TW — Traditional Chinese (Taiwan)

All messages, help text, error messages, and command output respond to the language setting automatically.

Quick Start

Installation

Global Installation (Recommended)

npm install -g @carllee1983/dbcli
# or: bun install -g @carllee1983/dbcli

Zero-Install (No Installation Needed)

npx @carllee1983/dbcli init
npx @carllee1983/dbcli query "SELECT * FROM users"
# or with Bun: bunx @carllee1983/dbcli init

Update

# Self-update (recommended)
dbcli upgrade

# Or via npm
npm update -g @carllee1983/dbcli

Development Installation

git clone https://github.com/CarlLee1983/dbcli.git
cd dbcli
bun install
bun run src/cli.ts -- --help
# or: bun run dev -- --help

When dbcli is not on your PATH, use bun run src/cli.ts <subcommand> ... (same as bun run dev -- <subcommand> ...).

First Steps

# Initialize project with database connection
dbcli init

# Interactive shell (SQL + dbcli commands, tab completion)
dbcli shell

# List available tables
dbcli list

# View table structure
dbcli schema users

# Query data
dbcli query "SELECT * FROM users"

# Preview schema DDL (dry-run by default; add --execute to apply)
dbcli migrate create posts --column "id:serial:pk" --column "title:varchar(200):not-null"

# Generate AI agent skill
dbcli skill --install claude

Agent first-look

dbcli inspect --for-agent

A single read-only command that returns a bounded JSON snapshot of the current connection, permission level, blacklist size, schema cache freshness, available saved-query intents, and the safest next commands to run. No host, no port, no credentials — safe to log or pipe to an LLM.

Diagnostic report

dbcli report --format json

Builds on inspect to also run curated read-only built-in @diag/* snippets grouped into health / capacity / perf sections. Bounded by per-snippet timeout and per-evidence row cap. Use --format markdown for human reading, --section health,capacity to scope, or --for-agent for compact JSON.

Guide

dbcli guide slow-query --format json

Deterministic next-command planner. Pick a goal (slow-query, capacity, health, index-usage, permissions, schema-overview) and dbcli guide emits an ordered plan that combines dbcli inspect, engine-appropriate @diag/* snippets, and dbcli queries suggest / dbcli doctor follow-ups. The planner is cache-first (no network); add --probe to refresh the underlying inspect context. Use --list to see all goals, --format markdown for human reading, or --for-agent for compact JSON.

Interactive HTML Dashboards

# Open results in browser
dbcli query "SELECT * FROM orders" --ui

# Run a saved snippet with visualization metadata
dbcli q @analytics/revenue --ui

# Export query results as a standalone HTML file
dbcli export "SELECT * FROM users" --format html --output report.html

dbcli can render query results as fully interactive, standalone HTML dashboards. These reports are powered by React + Recharts and are zero-dependency — the entire application and data are inlined into a single HTML file.

--ui flag: Automatically generates a temporary report and opens it in your default browser.
visual: block: Snippet frontmatter can define KPIs and charts (Line, Bar, Area, Pie, Scatter) to drive the dashboard.
Security: Result sets are redacted by the blacklist before injection, and data is safely escaped for HTML.

Recovery & Guided Remediation

# Lookup recovery commands for a code
dbcli recovery --code CONN_REFUSED --format json

# Execute a failing command with recovery opt-in
dbcli query "SELECT 1" --recovery

# (v1.17.0+) Inspect or apply the last saved recovery plan
dbcli recover                       # View last plan (Markdown)
dbcli recover --apply               # Execute safe steps (readonly/dry-run)
dbcli recover --apply --allow-write=readonly-cmd  # Allow local writes
dbcli recover --apply --allow-write=write-cmd     # Allow database writes

# (v1.17.0+) Multi-turn recovery (for AI agents)
dbcli recover --next --after-step 1 --result '{"status":"ok"}'

Machine-readable error envelope with guided remediation. As of v1.16.0 every first-party command accepts the --recovery flag. In v1.17.0, the recover command was added to automate the execution of these plans.

Risk Gating: --apply is safe-by-default, running only readonly and dry-run steps. Elevated tiers require --allow-write.
Verification: After a successful --apply, dbcli automatically runs a verification step to confirm the fix actually worked.
Multi-turn Protocol: The --next flag allows agents to advance recovery one step at a time, providing the result of the previous step to enable deterministic branching.

dbcli inspect --require-schema-cache throws SCHEMA_CACHE_MISSING when the active SQL connection has no usable schema cache; combine with --recovery to receive the structured envelope.

For write commands (insert / update / delete), BLACKLIST_COLUMN_WRITE and PERMISSION_DENIED envelopes lead with a risk: 'dry-run' step that suggests previewing the SQL with --dry-run before re-attempting.

MongoDB Atlas / SRV Connections

MongoDB connections are supported via both standard mongodb:// URIs and Atlas-style mongodb+srv:// URIs.

# Atlas / SRV connection
dbcli init --system mongodb --conn-name atlas --uri "mongodb+srv://user:pass@cluster.example.mongodb.net/mydb"

# List collections in the configured MongoDB database
dbcli list --use atlas

# Query a collection with JSON filter or pipeline
dbcli query '{"status":"active"}' --collection users --use atlas

For MongoDB, list and query operate on the database configured for the connection, and query requires --collection <name>.

For a command-by-command support matrix across PostgreSQL, MySQL, MariaDB, MongoDB, Redis, and Elasticsearch, see docs/feature-matrix.md.

Redis & Elasticsearch Support

dbcli extends its unified interface to Redis and Elasticsearch, providing consistent discovery and querying.

Redis

# Connect to Redis
dbcli init --system redis --host localhost --port 6379

# List keys (uses SCAN)
dbcli list

# Inspect a key (type, TTL, size, sample)
dbcli schema my-key

# Run Redis commands (whitelisted)
dbcli query "GET my-key"
dbcli query "HGETALL user:1"

Elasticsearch

# Connect to Elasticsearch
dbcli init --system elasticsearch --host localhost --port 9200

# List indices and document counts
dbcli list

# Show mapping/structure of an index
dbcli schema my-index

# Query using Lucene or DSL JSON
dbcli query "status:active" --index my-index
dbcli query '{"query": {"match_all": {}}}' --index my-index

Multi-connection Support (v2)

dbcli supports multiple named database connections within a single project. This is useful for managing different environments (development, staging, production) or multiple databases.

The project .dbcli directory now acts as a binding + cache layer. The actual connection config is stored in ~/.config/dbcli/projects/<project-id>/, which keeps sensitive settings out of the workspace by default.

Initializing Named Connections

To create a named connection, use the --conn-name option during init. You can also specify a custom .env file for that connection.

# Add a staging connection using .env.staging
dbcli init --conn-name staging --env-file .env.staging

# Add a production connection with environment variable references
dbcli init --conn-name prod --env-file .env.production --use-env-refs

Managing Connections

Use the dbcli use command to switch between connections or list them.

# List all connections (* marks the current default)
dbcli use --list

# Switch the default connection to 'staging'
dbcli use staging

# Show the current default connection
dbcli use

# Remove a connection
dbcli init --remove staging

# Rename a connection
dbcli init --rename staging:production

Using a Specific Connection Temporarily

You can use the --use <name> global flag to execute any command against a specific connection without changing the default.

# Query the production database once
dbcli query "SELECT count(*) FROM users" --use prod

# Check staging table health
dbcli check users --use staging

`dbcli init`

Initialize a new dbcli project with database connection configuration.

Usage:

dbcli init [OPTIONS]

Options (Basic):

--system <type> — Database system: postgresql, mysql, mariadb, mongodb
--host <host> — Database host
--port <port> — Database port
--user <user> — Database user
--password <pass> — Database password
--name <db> — Database name
--permission <level> — Permission level: query-only, read-write, data-admin, admin
MongoDB only: --uri <uri> — full connection URI (mongodb://… or mongodb+srv://…); --auth-source <db> — auth database (default admin when using user/password)
--use-env-refs — Store environment variable references instead of actual values in config
--skip-test — Skip connection test
--no-interactive — Non-interactive mode (requires all options)
--force — Overwrite existing config without confirmation

Options (Multi-connection v2):

--conn-name <name> — Create a named connection (e.g., staging, prod)
--env-file <path> — Load credentials from a specific .env file for this connection
--remove <name> — Remove a named connection from the config
--rename <old:new> — Rename an existing connection (format: old:new)

Behavior:

Reads .env file if present (auto-fills DATABASE_URL, DB_* variables)
Prompts for missing values (host, port, user, password, database name, permission level)
Creates a project binding stub in .dbcli/config.json and stores the full config under ~/.config/dbcli/projects/<project-id>/
Tests database connection before saving

Examples:

# Interactive initialization
dbcli init

# Multi-connection setup
dbcli init --conn-name staging --env-file .env.staging
dbcli init --conn-name prod --env-file .env.production --use-env-refs

# Store env var references (non-interactive)
dbcli init --use-env-refs --system mysql \
  --env-host DB_HOST --env-port DB_PORT \
  --env-user DB_USER --env-password DB_PASSWORD \
  --env-database DB_DATABASE \
  --no-interactive

`dbcli use` (Requires v2 config)

Manage or switch the default database connection in multi-connection projects.

Usage:

dbcli use [connection-name] [OPTIONS]

Options:

--list — List all connections and show the current default

Examples:

# Show current default connection
dbcli use

# Switch default connection to 'prod'
dbcli use prod

# List all connections
dbcli use --list

--use-env-refs: When enabled, the config stores environment variable names (e.g., {"$env": "DB_HOST"}) instead of actual values. This avoids writing sensitive credentials into the config file, making it suitable for multi-environment deployments and CI/CD pipelines. At connection time, dbcli automatically reads the actual values from the referenced environment variables.

Storage model: The project .dbcli directory is now a binding + cache layer, not the canonical home for secrets. If you inspect ./.dbcli/config.json, you should only see the binding metadata; the full config lives in the home storage path shown above.

`dbcli list`

List all tables in the connected database.

Usage:

dbcli list [OPTIONS]

Options:

--format json — Output as JSON instead of ASCII table

Examples:

# Table format (human-readable)
dbcli list

# JSON format (for AI parsing)
dbcli list --format json

# Pipe to tools
dbcli list --format json | jq '.data[].name'

`dbcli schema [table]`

Show table structure (columns, types, constraints, foreign keys).

Usage:

dbcli schema [table]
dbcli schema                 # Scan entire database and update .dbcli
dbcli schema users           # Show structure of 'users' table

Options:

--format json — Output as JSON
--refresh — Detect and update schema changes incrementally (requires --force for approval)
--reset — Clear all existing schema data and re-fetch from database (useful after switching DB connections)
--force — Skip confirmation for schema refresh/overwrite/reset

Examples:

# Show users table structure
dbcli schema users

# JSON output with full metadata
dbcli schema users --format json

# Update schema with new tables (incremental)
dbcli schema --refresh --force

# Clear and re-fetch all schema (after switching DB)
dbcli schema --reset --force

# Scan entire database
dbcli schema

`dbcli query "SQL"`

Execute SQL query and return results.

Usage:

dbcli query "SELECT * FROM users"

Options:

--format json|table|csv — Output format (default: table)
--limit <number> — Cap rows (overrides the automatic limit in query-only mode)
--no-limit — Disable the automatic 1000-row cap in query-only mode

Behavior:

Enforces permission-based restrictions (Query-only mode blocks INSERT/UPDATE/DELETE)
Auto-limits results to 1000 rows in Query-only mode (notification shown), unless --no-limit or --limit applies
Returns structured results with metadata (row count, execution time)
To write CSV/JSON to a file, use shell redirection or the export command

Examples:

# Table output (human-readable)
dbcli query "SELECT * FROM users"

# JSON (for AI/programmatic parsing)
dbcli query "SELECT * FROM users" --format json

# CSV to stdout (redirect to a file)
dbcli query "SELECT * FROM users" --format csv > users.csv

# Pipe to other tools
dbcli query "SELECT * FROM products" --format json | jq '.data[] | .name'

# Large result sets (paginate with LIMIT/OFFSET)
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 0"

`dbcli insert [table]` (Requires Read-Write or Admin permission)

Insert data into table.

Usage:

dbcli insert users --data '{"name": "Alice", "email": "alice@example.com"}'

Options:

--data JSON — Row data as JSON object (REQUIRED)
--dry-run — Show SQL without executing
--force — Skip confirmation

Behavior:

Validates JSON format
Generates parameterized SQL (prevents SQL injection)
Shows confirmation prompt before inserting (unless --force used)

Examples:

# Insert single row
dbcli insert users --data '{"name": "Bob", "email": "bob@example.com"}'

# Preview SQL without executing
dbcli insert users --data '{"name": "Charlie"}' --dry-run

# Skip confirmation
dbcli insert users --data '{"name": "Diana"}' --force

`dbcli update [table]` (Requires Read-Write or Admin permission)

Update existing rows.

Usage:

dbcli update users --where "id=1" --set '{"name": "Alice Updated"}'

Options:

--where condition — WHERE clause (REQUIRED, e.g., "id=1 AND status='active'")
--set JSON — Updated columns as JSON object (REQUIRED)
--dry-run — Show SQL without executing
--force — Skip confirmation

Examples:

# Update single row
dbcli update users --where "id=1" --set '{"name": "Alice"}'

# Update multiple rows
dbcli update users --where "status='inactive'" --set '{"status":"active"}'

# Preview SQL
dbcli update users --where "id=1" --set '{"name": "Bob"}' --dry-run

# Skip confirmation
dbcli update users --where "id=2" --set '{"email": "new@example.com"}' --force

`dbcli delete [table]` (Requires Data-Admin or Admin permission)

Delete rows (blocked for query-only and read-write; requires elevated DML permission).

Usage:

dbcli delete users --where "id=1" --force

Options:

--where condition — WHERE clause (REQUIRED)
--dry-run — Show SQL without executing
--force — Required to actually delete (safety guard)

Examples:

# Delete single row (requires --force)
dbcli delete users --where "id=1" --force

# Preview deletion
dbcli delete products --where "status='deprecated'" --dry-run

# Delete multiple rows
dbcli delete orders --where "created_at < '2020-01-01'" --force

`dbcli export "SQL"`

Export query results to file.

Usage:

dbcli export "SELECT * FROM users" --format json --output users.json

Options:

--format json|csv — Output format
--output file — Write to file (default: stdout for piping)

Behavior:

Query-only permission limited to 1000 rows per export
Generates RFC 4180 compliant CSV
Creates well-formed JSON arrays

Examples:

# Export to JSON
dbcli export "SELECT * FROM users" --format json --output users.json

# Export to CSV
dbcli export "SELECT * FROM orders" --format csv --output orders.csv

# Pipe compressed export
dbcli export "SELECT * FROM products" --format csv | gzip > products.csv.gz

# Combine with query tools
dbcli export "SELECT * FROM users WHERE active=true" --format json | jq '.data | length'

`dbcli skill`

Generate or install AI agent skill documentation.

Usage:

dbcli skill                           # Output skill to stdout
dbcli skill --output SKILL.md         # Write to file
dbcli skill --install claude          # Install to Claude Code config
dbcli skill --install gemini          # Install to Gemini CLI (being phased out)
dbcli skill --install antigravity     # Install to Antigravity CLI (Gemini CLI's successor)
dbcli skill --install copilot         # Install to GitHub Copilot
dbcli skill --install cursor          # Install to Cursor IDE

Behavior:

Ships canonical assets/SKILL.md + assets/reference.md (single source of truth: concise skill + long command reference)
Prints the skill to stdout, writes it with --output, or copies it to a platform-specific path with --install
Actual database access is still enforced by your .dbcli permission level and blacklist — the skill text describes the full CLI surface

Examples:

# Generate skill for Claude Code
dbcli skill --install claude

# Generate skill manually for documentation
dbcli skill > ./docs/SKILL.md

# View generated skill (stdout)
dbcli skill

# Install for all platforms
dbcli skill --install claude && \
dbcli skill --install gemini && \
dbcli skill --install antigravity && \
dbcli skill --install copilot && \
dbcli skill --install cursor

`dbcli blacklist`

Manage the data access blacklist to block AI agents from accessing sensitive tables or columns.

Usage:

dbcli blacklist list
dbcli blacklist table add <table>
dbcli blacklist table remove <table>
dbcli blacklist column add <table>.<column>
dbcli blacklist column remove <table>.<column>

Subcommands:

Subcommand	Description
`dbcli blacklist list`	Show current blacklist (tables and columns)
`dbcli blacklist table add <table>`	Add table to blacklist (blocks all operations)
`dbcli blacklist table remove <table>`	Remove table from blacklist
`dbcli blacklist column add <table>.<column>`	Add column to blacklist (omitted from SELECT results)
`dbcli blacklist column remove <table>.<column>`	Remove column from blacklist

Behavior:

Table blacklist blocks all operations on that table (query, insert, update, delete)
Column blacklist silently omits columns from SELECT results and shows a security notification
Blacklist rules are stored in .dbcli and apply to all permission levels
Override for admin use via DBCLI_OVERRIDE_BLACKLIST=true environment variable

Examples:

# View current blacklist
dbcli blacklist list

# Block all access to sensitive tables
dbcli blacklist table add audit_logs
dbcli blacklist table add secrets_vault

# Hide sensitive columns from query results
dbcli blacklist column add users.password_hash
dbcli blacklist column add users.ssn

# Remove a table from blacklist
dbcli blacklist table remove audit_logs

# Remove a column from blacklist
dbcli blacklist column remove users.ssn

# Override blacklist (admin use only)
DBCLI_OVERRIDE_BLACKLIST=true dbcli query "SELECT * FROM secrets_vault"

`dbcli check`

Run data-quality and health checks on tables.

Usage:

dbcli check [table] [OPTIONS]

Options:

--all — Check every table (skips huge tables unless --include-large)
--include-large — Include huge tables in --all scan
--checks <types> — Comma-separated checks: nulls, duplicates, orphans, emptyStrings, rowCount, size
--sample <number> — Sample size for large tables (default: 10000)
--format json|table — Output format (default: json)

Examples:

# Check users table
dbcli check users

# Run specific checks only
dbcli check orders --checks nulls,orphans --format table
# Scan all tables
dbcli check --all

# Table view + all-tables with selected checks
dbcli check orders --format table
dbcli check --all --checks nulls,duplicates --format json

`dbcli diff`

Save a schema snapshot or compare the live database to a previous snapshot (tables, columns, indexes).

Usage:

dbcli diff --snapshot ./schema-before.json
dbcli diff --against ./schema-before.json
dbcli diff --against ./schema-before.json --format table

Options:

--snapshot <path> — Write the current schema to a JSON file
--against <path> — Diff live schema vs. the saved snapshot
--format json|table — Output format (default: json)
--config <path> — Config path (default: .dbcli)

`dbcli snapshot`

Capture a result fingerprint of a query (distinct from diff, which snapshots schema): row count plus per-column aggregates (null/distinct counts, min/max/sum) and an order-independent checksum. Blacklisted columns are masked at the source, so the snapshot is safe to store. Use it as a baseline for dbcli assert --against. SQL engines only.

Usage:

dbcli snapshot "SELECT * FROM orders WHERE created_at >= '2026-05-01'"   # → .dbcli/snapshots/snap-<timestamp>.json
dbcli snapshot @analytics/daily-revenue --out base.json
dbcli snapshot "SELECT status, count(*) FROM orders GROUP BY status" --stdout

Options:

--out <path> — Output path (default: .dbcli/snapshots/snap-<timestamp>.json)
--rows — Also store the full (blacklist-masked) rows
--stdout — Print snapshot JSON to stdout instead of writing a file
--format json|table — Output format for --stdout (default: json)
--no-limit — Disable the automatic query-only LIMIT

`dbcli assert`

Assert an invariant on a query result. Exits 1 on failure (composes in scripts/CI) unless --no-fail. SQL engines only.

Usage:

dbcli assert "SELECT count(*) FROM orders" --expect "value > 0"
dbcli assert "SELECT * FROM orders WHERE total < 0" --expect "rows == 0"
dbcli assert "SELECT email FROM users" --expect "col:email not null"
dbcli assert "SELECT sum(amount) FROM ledger_a" --vs "SELECT sum(amount) FROM ledger_b" --compare value
dbcli assert "SELECT * FROM orders" --against base.json --tolerance 0.01

Options:

--expect <condition> — rows > 0, value == 5000, col:email not null, col:id unique, col:amount between 0 and 100, col:age >= 18
--vs <query> — Reconcile against a second query
--compare rows|value — Comparison mode for --vs (default: value)
--against <path> — Compare the current result fingerprint to a saved snapshot
--tolerance <pct> — Allowed relative drift for --against (e.g. 0.01; default: 0 = exact checksum match)
--no-fail — Always exit 0; report pass/fail in output only
--format json|table — Output format (default: json)

`dbcli status`

Show non-sensitive configuration summary (permission level, DB system, blacklist counts, config metadata version). Does not print connection credentials — intended for AI agents.

Usage:

dbcli status
dbcli status --format text
dbcli status --format json

Options:

--format text|json — Output format (default: json)

Note: This command reads the default project config path .dbcli (not the global --config flag).

`dbcli doctor`

Run diagnostic checks on environment, configuration, connection, and data.

dbcli doctor                    # Colored text output
dbcli doctor --format json      # JSON output for AI agents

Checks:

Environment: Bun version compatibility, dbcli version (compares with npm registry)
Configuration: Config file exists/valid, permission level, blacklist completeness
Connection & Data: Database connectivity, schema cache freshness (> 7 days warning), large table warnings (> 1M rows)
MongoDB SRV diagnostics: For mongodb+srv:// connections, doctor reports whether the current execution environment can resolve SRV records directly or only through the DNS-over-HTTPS fallback used by dbcli

Options: --format <text|json> Exit code: 0 = all pass or warnings only, 1 = errors found

`dbcli completion [shell]`

Generate shell completion scripts for tab auto-complete.

dbcli completion bash            # Output bash completion to stdout
dbcli completion zsh             # Output zsh completion to stdout
dbcli completion fish            # Output fish completion to stdout
dbcli completion --install       # Auto-detect shell and install to rc file
dbcli completion --install zsh   # Install for specific shell

Supported shells: bash, zsh, fish

`dbcli upgrade`

Check for updates and self-upgrade dbcli.

dbcli upgrade                   # Check and upgrade if newer version available
dbcli upgrade --check           # Only check, do not upgrade

Options: --check — check only, don't install

Background checks (stderr, skipped when --quiet or for upgrade / skill):

CLI version: dbcli checks the npm registry (cached, about once per 24 hours). If a newer package exists, a one-line hint prints after normal command output.
Installed skills: If you used dbcli skill --install <platform>, dbcli compares each installed primary skill file (SKILL.md or dbcli.mdc) to the bundled assets/SKILL.md. When they differ, a short reminder lists which platforms to re-install (dbcli skill --install <platform>). Run dbcli upgrade to see the same skill status together with version info. Re-installing also refreshes reference.md next to the skill.

`dbcli shell`

Interactive database shell with SQL execution, auto-completion, and syntax highlighting.

Usage:

dbcli shell          # Interactive mode (SQL + dbcli commands)
dbcli shell --sql    # SQL-only mode

Inside the shell:

Type SQL statements ending with ; to execute queries
Type dbcli commands without the dbcli prefix (e.g., schema users, list)
Press Tab for context-aware auto-completion (SQL keywords, table/column names)
Use .help for meta commands (.quit, .clear, .format, .history, .timing)
Multi-line SQL: input accumulates until ; is found
History persists across sessions in ~/.dbcli_history

Permission: Inherits from config. SQL and commands are fully permission/blacklist enforced.

`dbcli migrate`

Schema DDL operations. All commands default to dry-run — use --execute to actually run the SQL.

Usage:

# Create table
dbcli migrate create posts \
  --column "id:serial:pk" \
  --column "title:varchar(200):not-null" \
  --column "body:text" \
  --column "created_at:timestamp:default=now()"

# Execute (actually run the SQL)
dbcli migrate create posts --column "id:serial:pk" --execute

# Drop table (destructive — requires --execute --force)
dbcli migrate drop posts --execute --force

# Column operations
dbcli migrate add-column users bio text --nullable --execute
dbcli migrate alter-column users name --type "varchar(200)" --execute
dbcli migrate alter-column users email --rename user_email --execute
dbcli migrate drop-column users temp_field --execute --force

# Index operations
dbcli migrate add-index users --columns email --unique --execute
dbcli migrate drop-index idx_users_email --table users --execute --force

# Constraint operations
dbcli migrate add-constraint orders --fk user_id --references users.id --on-delete cascade --execute
dbcli migrate add-constraint users --unique email --execute
dbcli migrate add-constraint users --check "age >= 0" --execute
dbcli migrate drop-constraint orders fk_orders_user_id --execute --force

# Enum (PostgreSQL only)
dbcli migrate add-enum status active inactive suspended --execute
dbcli migrate alter-enum status --add-value archived --execute
dbcli migrate drop-enum status --execute --force

Column spec format: name:type[:modifier...] — Modifiers: pk, not-null, unique, auto-increment, default=<value>, references=<table>.<column>

Options (all subcommands): --execute (run SQL), --force (skip confirmation for DROP), --config <path> Permission: admin only

Query Risk Planning

Use plan to inspect SQL safety before execution. It reads local dbcli config, permissions, blacklist rules, and cached schema metadata only; it does not connect to the database.

dbcli plan "UPDATE users SET status='inactive'" --format json

Decisions are:

ALLOW — no obvious risk was detected.
WARN — inspect warnings before executing.
BLOCK — unsafe, unsupported, or violates configured safety constraints.

Text output is concise for humans:

Decision: BLOCK
Operation: UPDATE
Target tables: users

Risk factors:
- UPDATE statement has no WHERE clause.

Recommendations:
- Add a WHERE clause.
- Use --dry-run on the actual write command.

JSON output includes suggestedCommands for agents:

dbcli plan "SELECT id FROM users WHERE id = 1 LIMIT 1" --format json

Global Options

All commands support these global options:

Flag	Description
`--config <path>`	Path to .dbcli config file (default: `.dbcli`)
`--use <connection>`	Use a named v2 connection for this invocation only (does not change the default)
`-v, --verbose`	Increase verbosity (`-v` verbose, `-vv` debug)
`-q, --quiet`	Suppress non-essential output
`--no-color`	Disable colored output (respects `NO_COLOR` env var)

Internals & Strategy

Schema Update Strategy

dbcli maintains a schema snapshot in your .dbcli config file. This allows AI agents to understand the database structure without constant network overhead. Understanding when this cache updates is key:

Manual Updates:
- dbcli schema: Performs a full scan of the database.
- dbcli schema --refresh: Incremental update. Detects changes and updates only the affected tables.
- dbcli schema --reset: Clears the cache and re-fetches everything.
Automatic Updates (DDL):
- When you execute DDL through dbcli migrate (e.g., add-column), the CLI automatically re-scans the affected table and updates the .dbcli snapshot after successful execution.
Real-time Validation (Non-cached):
- Commands like insert, update, delete, and check fetch the latest schema from the database immediately before execution to ensure data integrity, but they do not update the long-term snapshot in .dbcli.

Note: If you change the database schema using external tools (like DBeaver or migration scripts), you must run dbcli schema --refresh to sync the snapshot so AI agents can see the changes.

How `dbcli migrate` Works

The migrate command follows a strict safety pipeline to prevent accidental database corruption:

Permission Check: Verifies the user has admin privileges. DDL is blocked for all other levels.
Blacklist Check: Ensures the operation isn't targeting a table restricted in the security blacklist.
Dialect-Specific Generation: The DDLGenerator translates your request into the correct SQL for your system:
- PostgreSQL: Uses SERIAL, native ENUM types, and double-quoted identifiers.
- MySQL/MariaDB: Uses AUTO_INCREMENT, inline ENUM definitions, and backticked identifiers.
Dry-run (Default): All commands output the generated SQL for review without executing it.
Execution & Confirmation:
- Requires the --execute flag to run.
- Destructive operations (like drop) require both --execute and --force.
Snapshot Sync: After successful execution, it automatically triggers a schema refresh for the modified table to keep your .dbcli file up to date.

Permission Model

dbcli implements a coarse-grained permission system with three levels. Permission level is set during dbcli init and stored in .dbcli config file. The blacklist system works alongside permissions to provide fine-grained protection for sensitive tables and columns (see Data Access Control).

Permission Levels

Level	Allowed Commands	Blocked Commands	Use Case
Query-only	`init`, `list`, `schema`, `query`, `export` (limited to 1000 rows)	`insert`, `update`, `delete`, `migrate`	Read-only AI agents, data analysts, reporting
Read-Write	+ `insert`, `update`	`delete`, `migrate`	Application developers, content managers
Data-Admin	+ `delete`	`migrate`	Full DML access, no DDL
Admin	All commands including `migrate` (DDL)	—	Database administrators, schema modifications

Configuration

Permission level is set during initialization:

dbcli init
# Prompts: permission level (query-only / read-write / data-admin / admin)
# Stored in project .dbcli/config.json as: "permission": "query-only"

Permission-Based Examples

Query-Only Mode (AI Agent)

# Allowed: Read operations
dbcli query "SELECT * FROM users"
dbcli schema users
dbcli export "SELECT * FROM orders" --format json

# Blocked: Write operations
dbcli insert users --data '{...}'  # ERROR: Permission denied
dbcli delete users --where "id=1"  # ERROR: Permission denied

Read-Write Mode (Application Developer)

# Allowed: Read + write
dbcli query "SELECT * FROM users"
dbcli insert users --data '{"name": "Alice"}'
dbcli update users --where "id=1" --set '{"name": "Bob"}'

# Blocked: Delete (requires data-admin or admin)
dbcli delete users --where "id=1"  # ERROR: Permission denied (read-write cannot DELETE)

Admin Mode (Database Administrator)

# Allowed: Everything including DDL
dbcli query "SELECT * FROM users"
dbcli insert users --data '{"name": "Eve"}'
dbcli update users --where "id=1" --set '{"status": "active"}'
dbcli delete users --where "id=1" --force  # Data-Admin+ can delete
dbcli migrate create posts --column "id:serial:pk" --execute  # Admin only

Best Practices

AI Agents: Use Query-only for read-only scenarios; prevents accidental data loss
Applications: Use Read-Write for normal CRUD operations; prevents DROP TABLE accidents
Maintenance: Use Admin only for schema changes, bulk deletes, or emergency operations
Principle of Least Privilege: Assign minimum permission level needed for each use case

Data Access Control

dbcli provides a blacklist system that works alongside the permission model to prevent AI agents from accessing sensitive tables or columns, regardless of their permission level.

Table-Level Blacklist

Blocking a table prevents all operations on it — queries, inserts, updates, and deletes are all refused with a clear error message.

# Block a table
dbcli blacklist table add secrets_vault

# Attempting access is blocked at all permission levels
dbcli query "SELECT * FROM secrets_vault"
# ERROR: Table 'secrets_vault' is blacklisted

Column-Level Blacklist

Blacklisted columns are silently omitted from SELECT results. A security notification is shown in the output so the agent is aware that the result set has been filtered.

# Blacklist sensitive columns
dbcli blacklist column add users.password_hash
dbcli blacklist column add users.ssn

# Query returns all other columns; notification shown
dbcli query "SELECT * FROM users"
# [Security] Columns omitted by blacklist: password_hash, ssn

Security Notifications

Whenever a blacklist rule filters query output, dbcli appends a notification line to the result. This ensures AI agents do not silently operate on incomplete data without awareness.

Override via Environment Variable

Administrators can bypass the blacklist for emergency or maintenance operations using the DBCLI_OVERRIDE_BLACKLIST=true environment variable:

DBCLI_OVERRIDE_BLACKLIST=true dbcli query "SELECT * FROM secrets_vault"

This override is logged and should only be used by administrators when necessary.

Blacklist Configuration

Blacklist rules are stored in the .dbcli config file and can also be set manually:

{
  "blacklist": {
    "tables": ["audit_logs", "secrets_vault"],
    "columns": {
      "users": ["password_hash", "ssn"]
    }
  }
}

Blacklist vs. Permissions

The blacklist and permission model are complementary layers of access control:

Layer	Controls	Applies To
Permission Model	Operation type (read/write/delete)	All tables
Blacklist	Specific tables and columns	Targeted sensitive data

A Query-only agent cannot write to any table, and also cannot read blacklisted tables or columns — both restrictions apply simultaneously.

Saved queries

Save parameterised SELECT snippets and re-run them by name:

dbcli queries list
dbcli queries show @dau
dbcli q @dau --param days=30 --format json

Snippets live in .dbcli-shared/queries/ (committed) or .dbcli/queries/ (gitignored, personal override). Each .sql file declares its frontmatter in a -- --- block. Read assets/reference.md for the full schema.

AI Integration Guide

dbcli ships AI-consumable skill files (assets/SKILL.md and assets/reference.md) and can copy them into your favorite AI tool directories.

Quick Start

Generate skill for your preferred platform:

# Claude Code (Anthropic's VS Code extension)
dbcli skill --install claude

# Gemini CLI (Google's command-line AI)
dbcli skill --install gemini

# GitHub Copilot CLI
dbcli skill --install copilot

# Cursor IDE (AI-native editor)
dbcli skill --install cursor

After installation, the AI agent will have access to dbcli commands and can use them to query, insert, update, or export data based on your permission level.

Platform-Specific Setup

Claude Code (Anthropic)

Install dbcli globally: npm install -g @carllee1983/dbcli
Initialize: dbcli init (choose permission level)
Install skill: dbcli skill --install claude
Restart Claude Code extension
In Claude Code chat, ask: "Show me the database schema" or "Query active users"

Skill location: ~/.claude/skills/dbcli/ (SKILL.md + reference.md)

Gemini CLI (Google)

Install dbcli globally: npm install -g @carllee1983/dbcli
Initialize: dbcli init
Install skill: dbcli skill --install gemini
Start Gemini: gemini start
In chat, request: "Query the users table" or "Show database tables"

Skill location: ~/.gemini/skills/dbcli/ (SKILL.md + reference.md)

GitHub Copilot CLI

Install dbcli globally: npm install -g @carllee1983/dbcli
Initialize: dbcli init
Install skill: dbcli skill --install copilot
Install Copilot CLI: npm install -g @github-next/github-copilot-cli
Use copilot preview: copilot --help and explore dbcli integration

Skill location: .github/skills/dbcli/ (SKILL.md + reference.md) when you run dbcli skill --install copilot in your project root.

Cursor IDE

Install dbcli globally: npm install -g @carllee1983/dbcli
Initialize: dbcli init
Install skill: dbcli skill --install cursor
Open Cursor editor
Use Cursor's Composer: "Insert a new user" or "Export user data"

Skill location: .cursor/rules/dbcli.mdc (summary + workflows) and .cursor/skills/dbcli/reference.md (full command flags and examples) under the current working directory when you run dbcli skill --install cursor.

Example: AI Agent Workflow

Scenario: You want an AI agent to analyze user engagement data.

# 1. Install and initialize
npm install -g @carllee1983/dbcli
dbcli init  # Choose "query-only" for safety

# 2. Install skill to Claude Code
dbcli skill --install claude

# 3. In Claude Code chat, ask:
# "Analyze the last 7 days of user activity and summarize insights"

# Claude Code will:
# - Use: dbcli schema users, dbcli query "SELECT ..."
# - Parse JSON output
# - Provide analysis

Updating the skill after upgrades

dbcli skill copies the bundled assets/SKILL.md (and for --install, assets/reference.md next to it). It is not regenerated from your live config. When you upgrade dbcli or the bundled skill changes, re-copy it to each platform you use:

dbcli skill --install claude
dbcli skill --install gemini
# ... etc.

If an installed primary skill file is older than the bundled assets/SKILL.md, dbcli prints a stderr reminder after most commands (see dbcli upgrade). Changing permission level or blacklist in .dbcli affects what the CLI allows at runtime — keep project context (e.g. dbcli status, dbcli blacklist list) in mind for agents even though the skill text lists the full command set.

Audit Log

Default ON since v1.20.0. Existing projects will begin creating .dbcli/audit/<connection>.jsonl on first command after upgrading. Set audit.enabled = false in .dbcli to opt out.

Every command that touches a database writes a structured JSONL entry to .dbcli/audit/<connection>.jsonl. Inspect the recent history with:

dbcli audit tail --n 10                    # last 10 entries on current connection
dbcli audit tail --all --for-agent         # cross-connection JSON envelope
dbcli audit show <uuid-prefix>             # full entry by id prefix (>=4 chars)
dbcli audit show --recovery-ref <uuid>     # find entry that emitted a recovery envelope
dbcli audit health                         # writer state, rotation %, last write status
dbcli audit clear                          # erase audit log for current connection (prompts y/N)

Entries are metadata-only — never raw SQL bodies, never --param values, never result cell contents. Redaction comes from the same source as v1.19.1's agent-facing JSON contracts (tests/helpers/sensitive-output.ts).

Recovery envelope linkage. When a --recovery failure writes .dbcli/last-recovery.json, the audit entry's recovery_ref field and the envelope's audit_ref field reference each other. Agents can pivot between audit history and recovery envelopes from either direction. The inspect / guide / recover / recover --apply JSON output embeds audit_recent: AuditEntryBrief[] (last 5 entries) so a fresh session has immediate context.

Full bi-directional coverage (v1.20.1+): Recovery ↔ audit linkage is wired on every --recovery-capable command — query, inspect, insert, update, delete, export, q, and schema. The audit entry's recovery_ref and the envelope's audit_ref carry matching UUIDs on every failure path, so agents can pivot from a saved envelope (.dbcli/last-recovery.json) to its audit entry via dbcli audit tail --recovery-ref <id> (and back via dbcli audit show --recovery-ref <id>). The v1.20.0 partial-coverage gap on the 6 DML/DDL commands was closed in v1.20.1. The coverage matrix lives in .planning/phases/25-recovery-envelope-bi-directional-linkage/25-J1-COVERAGE-MATRIX.md.

For deeper agent workflows (session handoff, forensics walk-through), see assets/SKILL.md §Audit Log usage.

Troubleshooting

Connection Issues

"ECONNREFUSED: Connection refused"

Database is not running or host/port is incorrect.

Solutions:

# Verify database is running
psql --version  # PostgreSQL installed?
mysql --version  # MySQL installed?

# Check connection string
dbcli init  # Re-run initialization to verify credentials

# Verify host/port from command line
psql -h localhost -U postgres  # PostgreSQL test
mysql -h 127.0.0.1 -u root      # MySQL test

"ENOTFOUND: getaddrinfo ENOTFOUND hostname"

Hostname resolution failed (typo or DNS issue).

Solutions:

# Verify hostname in project config (directory layout: .dbcli/config.json)
grep host .dbcli/config.json

# Test DNS resolution
ping your-hostname.com

# Use 127.0.0.1 instead of localhost if issues persist
dbcli init  # Re-initialize with correct host

Permission Errors

"Permission denied: INSERT requires Read-Write or Admin"

Trying to write with Query-only permission level.

Solution: Re-initialize with higher permission level:

rm -rf .dbcli   # Remove project config (destructive — backup if needed)
dbcli init      # Re-run, choose "read-write", "data-admin", or "admin"

"Permission denied: DELETE operation requires Data-Admin or Admin"

DELETE is not allowed in query-only or read-write mode.

Solution: Use data-admin or admin permission (re-run dbcli init, or edit .dbcli/config.json), or ask an administrator.

dbcli init  # Choose "data-admin" or "admin"
dbcli delete users --where "id=1" --force

Query Errors

"Table not found: users"

Table doesn't exist or name is misspelled.

Solution:

# Show all available tables
dbcli list

# Check spelling and retry
dbcli query "SELECT * FROM user" --format json

"Syntax error near SELECT"

SQL syntax error in query.

Solution:

# Test query in native database client first
psql  # Or mysql
# SELECT * FROM users;  <- Test here first

# Then use in dbcli
dbcli query "SELECT * FROM users"

Performance Issues

"Query returns 1000 rows instead of full result set"

Query-only permission auto-limits results for safety.

Solution: Increase permission level or fetch data in chunks:

# Re-initialize with higher permission
dbcli init  # Choose "read-write" or "admin"

# OR fetch data in chunks
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 0"
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 100"

"CLI startup takes 30+ seconds (first run)"

npx is downloading and caching package.

Solution: This is normal on first run. Subsequent runs are instant:

npx @carllee1983/dbcli init  # First run: 30s (downloads)
npx @carllee1983/dbcli init  # Second run: <1s (cached)

# Or install globally for faster startup
npm install -g @carllee1983/dbcli
dbcli init  # All future runs: <1s

Cross-Platform Issues

Windows: "Command not found: dbcli"

npm .cmd wrapper not created or PATH not updated.

Solutions:

# Restart terminal to refresh PATH
# OR reinstall globally
npm uninstall -g @carllee1983/dbcli
npm install -g @carllee1983/dbcli

# Verify installation
where dbcli  # Windows command to find executable

macOS/Linux: "Permission denied: ./dist/cli.mjs"

Executable bit not set.

Solution:

chmod +x dist/cli.mjs
./dist/cli.mjs --help

System Requirements

Database Support

PostgreSQL: 12.0+
MySQL: 8.0+
MariaDB: 10.5+
MongoDB: 4.4+ (query and collection listing via mongodb:// and mongodb+srv://; see MongoDB Atlas / SRV Connections earlier in this document)

Runtime

Node.js: 18.0.0+
Bun: 1.3.3+

Platforms

macOS: Intel and Apple Silicon
Linux: x86_64 (Ubuntu, Debian, CentOS, etc.)
Windows: 10+ (via npm .cmd wrapper)

Development

bun test                  # full test suite (Bun test runner)
bun run typecheck         # TypeScript compile-time validation
bun run test:unit         # unit + core tests only
bun run test:integration  # integration tests
bun run test:docker       # integration tests with docker-compose.test.yml (MySQL + PostgreSQL)
bun run build             # bundle CLI to dist/ (used before publish)

CI treats bun run typecheck and bun test as the required pass/fail validation gate on every push and pull request. Lint, build, smoke checks, and benchmarks run in addition to that gate.

Live database integration tests use .dbcli/config.json by default. If your live config lives elsewhere, set LIVE_DB_CONFIG_PATH=/path/to/.dbcli before running:

LIVE_DB_CONFIG_PATH=/path/to/.dbcli bun test tests/integration/live-db.test.ts

If no live config is available, tests/integration/live-db.test.ts skips instead of falling back to the default PostgreSQL configuration. Set SKIP_INTEGRATION_TESTS=true to skip all integration tests.

See CONTRIBUTING.md for full setup, testing, and release process.

License

See LICENSE file for details.

Name		Name	Last commit message	Last commit date
Latest commit History 1,087 Commits
.cursor		.cursor
.dbcli.example		.dbcli.example
.github		.github
.planning		.planning
.windsurf/skills/dbcli		.windsurf/skills/dbcli
assets		assets
benchmarks		benchmarks
docs		docs
resources/lang		resources/lang
scripts		scripts
src		src
tests		tests
.env.example		.env.example
.gitignore		.gitignore
.npmignore		.npmignore
.prettierrc		.prettierrc
.windsurfrules		.windsurfrules
AGENTS.md		AGENTS.md
CHANGELOG.md		CHANGELOG.md
CLAUDE.md		CLAUDE.md
CONTRIBUTING.md		CONTRIBUTING.md
GEMINI.md		GEMINI.md
INTEGRATION-CHECK.md		INTEGRATION-CHECK.md
LICENSE		LICENSE
README.dev.md		README.dev.md
README.md		README.md
README.zh-TW.md		README.zh-TW.md
SECURITY.md		SECURITY.md
bun.lock		bun.lock
bunfig.toml		bunfig.toml
docker-compose.test.yml		docker-compose.test.yml
eslint.config.js		eslint.config.js
package.json		package.json
tsconfig.json		tsconfig.json

Folders and files

Latest commit

History

Repository files navigation

dbcli — Database CLI for AI Agents

Internationalization (i18n)

Quick Start

Installation

Global Installation (Recommended)

Zero-Install (No Installation Needed)

Update

Development Installation

First Steps

Agent first-look

Diagnostic report

Guide

Interactive HTML Dashboards

Recovery & Guided Remediation

MongoDB Atlas / SRV Connections

Redis & Elasticsearch Support

Redis

Elasticsearch

Multi-connection Support (v2)

Initializing Named Connections

Managing Connections

Using a Specific Connection Temporarily

dbcli init

dbcli use (Requires v2 config)

dbcli list

dbcli schema [table]

dbcli query "SQL"

dbcli insert [table] (Requires Read-Write or Admin permission)

dbcli update [table] (Requires Read-Write or Admin permission)

dbcli delete [table] (Requires Data-Admin or Admin permission)

dbcli export "SQL"

dbcli skill

dbcli blacklist

dbcli check

dbcli diff

dbcli snapshot

dbcli assert

dbcli status

dbcli doctor

dbcli completion [shell]

dbcli upgrade

dbcli shell

dbcli migrate

Query Risk Planning

Global Options

Internals & Strategy

Schema Update Strategy

How dbcli migrate Works

Permission Model

Permission Levels

Configuration

Permission-Based Examples

Query-Only Mode (AI Agent)

Read-Write Mode (Application Developer)

Admin Mode (Database Administrator)

Best Practices

Data Access Control

Table-Level Blacklist

Column-Level Blacklist

Security Notifications

Override via Environment Variable

Blacklist Configuration

Blacklist vs. Permissions

Saved queries

AI Integration Guide

Quick Start

Platform-Specific Setup

Claude Code (Anthropic)

Gemini CLI (Google)

GitHub Copilot CLI

Cursor IDE

Example: AI Agent Workflow

Updating the skill after upgrades

Audit Log

Troubleshooting

Connection Issues

`dbcli init`

`dbcli use` (Requires v2 config)

`dbcli list`

`dbcli schema [table]`

`dbcli query "SQL"`

`dbcli insert [table]` (Requires Read-Write or Admin permission)

`dbcli update [table]` (Requires Read-Write or Admin permission)

`dbcli delete [table]` (Requires Data-Admin or Admin permission)

`dbcli export "SQL"`

`dbcli skill`

`dbcli blacklist`

`dbcli check`

`dbcli diff`

`dbcli snapshot`

`dbcli assert`

`dbcli status`

`dbcli doctor`

`dbcli completion [shell]`

`dbcli upgrade`

`dbcli shell`

`dbcli migrate`

How `dbcli migrate` Works

Packages