dbm-cli

dbm-cli

A zero-external-dependency database CLI: a single static binary queries database metadata and data. Supports Oracle (10g–21c, pure-Go go-ora), MySQL / MariaDB (5.7 / 8.0.x / MariaDB 10.x+, pure-Go go-sql-driver/mysql), ClickHouse (22.x+, pure-Go clickhouse-go/v2), PostgreSQL (9.6+, pure-Go pgx/v5), Apache Impala (3.x/4.x, pure-Go impala-go), and SQL Server (2017+, pure-Go go-mssqldb) — all without Instant Client / CGO. Extensible to more databases through a driver interface. AI-friendly: dbm-cli manifest emits a self-describing JSON contract.

中文文档

✨ Features

• 🪶 Single binary — all drivers are pure Go, so the compiled binary has no external native-library dependency. Copy it and run.
• 🗂️ Multi-version Oracle — auto-detects 10g / 11g / 12c / 18c / 19c / 21c and picks the right dictionary views per version. Restricted accounts can skip detection via force_version.
• 🐬 MySQL 5.7 / 8.0.x & MariaDB — metadata via information_schema (consistent across versions); supports caching_sha2_password (8.0 default auth) out of the box. MariaDB is protocol-compatible and usable via the mariadb type alias. Optional TLS (skip-verify) for self-signed certs.
• ⚡ ClickHouse 22.x+ — metadata via system.* tables; native TCP protocol (port 9000) for low overhead. Understands ClickHouse-specific types (Nullable(...), Array(...)) and engine-based table types.
• 🐘 PostgreSQL 9.6+ — metadata via information_schema + pg_catalog; correct schema/database distinction; array-typed index columns parsed.
• 🐝 Apache Impala 3.x/4.x — metadata via SHOW/DESCRIBE (no INFORMATION_SCHEMA); HiveServer2 protocol. Understands Impala has no traditional indexes (PARTITIONED/SORTED BY instead). Supports Kerberos auth via keytab — pure Go (exchanges a TGT from the keytab in-process, no system kinit required).
• 🟦 SQL Server 2017+ — metadata via sys.* catalog views; OFFSET/FETCH paging; no ODBC driver needed (pure-Go go-mssqldb).
• 📞 Stored procedures — dbm-cli call invokes Oracle PL/SQL procedures, reclaiming OUT scalar params (number/string) and REF CURSOR result sets, and captures DBMS_OUTPUT.PUT_LINE print output (Oracle 11g / 18c verified).
• 🧩 Extensible — Driver + MetadataProvider + a registry. Adding a database means one new package and one import _ line; core stays untouched (Open–Closed Principle).
• 🔒 Controlled writes — every datasource has an allow_write switch (read-only by default). Destructive statements (DROP / TRUNCATE / DELETE/UPDATE without WHERE) require an interactive confirmation.
• 🤖 AI-friendly — dbm-cli manifest outputs a JSON contract describing all commands, flags, drivers and examples, so an agent learns how to call the tool from a single read.
• 🔌 MCP server — dbm-cli mcp exposes the same capabilities (metadata, sample data, SQL) as a Model Context Protocol server over stdio, so AI clients like Claude Desktop / Cursor can call the database directly. Zero impact on existing CLI commands.
• 🎨 Multiple output formats — table / json / csv / yaml / vertical.
• 🧭 Machine-friendly errors — every failure is printed to stderr as [dbm-cli] error: <reason> with a retry hint, and exit codes distinguish retryable (1) from config-required (2) failures.

📦 Install

git clone https://github.com/golango-cn/dbm-cli.git
cd dbm-cli
make build          # produces ./bin/dbm-cli
# or directly
go build -o dbm-cli ./cmd/dbm-cli

Cross-compile static binaries for Linux / macOS / Windows (amd64 + arm64):

make dist           # outputs to ./dist/

🧪 Try it (live demo)

The repo ships a demo harness in examples/ that exercises every command via a Makefile. It is database-agnostic — point it at any datasource you have configured:

cd examples
# 1. build the binary into ./examples
go build -o dbm-cli ../cmd/dbm-cli
# 2. create a config (copy & edit from config.yaml.example in the same dir)
cp config.yaml.example dbm-cli.yaml   # then edit host/user/password
# 3. initialize the demo table on your default datasource
make init
# 4. run the demos
make manifest       # supported drivers
make demo           # version/tables/columns/data on default datasource
make formats        # all output formats (table/json/csv/yaml/vertical)
make demo DS=other  # run against a different datasource

The demo's dbm-cli binary and dbm-cli.yaml (which holds credentials) are gitignored. Only the Makefile, init-demo-data.sh, and the per-DB demo-*.sh scripts are tracked.

⚙️ Configuration

Copy examples/config.yaml.example to ~/.dbm-cli.yaml (the recommended default location) and edit. If you run dbm-cli without -c, it looks up the config in this order (first match wins): --config → ./dbm-cli.yaml → ~/.dbm-cli.yaml → $XDG_CONFIG_HOME/dbm-cli/config.yaml

default: prod-ro
datasources:
  # Oracle (use service_name OR sid)
  prod-ro:
    type: oracle
    host: 10.0.0.1
    port: 1521
    service_name: ORCLPDB1
    user: readonly
    password: ${DB_PWD_PROD} # env-var expansion; avoids plaintext secrets
    allow_write: false       # read-only by default

  # MySQL (use database = schema name)
  mysql-prod:
    type: mysql
    host: 10.0.0.2
    port: 3306
    database: app_db         # required for MySQL
    user: readonly
    password: ${DB_PWD_MYSQL}
    allow_write: false
    # tls: skip-verify        # optional: enable TLS for self-signed certs

  # MariaDB — protocol-compatible with MySQL; use type: mariadb (alias of mysql)
  maria-prod:
    type: mariadb             # alias -> mysql driver; works the same as type: mysql
    host: 10.0.0.10
    port: 3306
    database: app_db
    user: readonly
    password: ${DB_PWD_MARIA}
    allow_write: false

  # ClickHouse (native TCP port 9000)
  ck-prod:
    type: clickhouse
    host: 10.0.0.4
    port: 9000               # native TCP port (NOT 8123 which is HTTP)
    database: analytics
    user: default
    password: ${DB_PWD_CK}
    allow_write: false
    # tls: skip-verify        # optional: enable TLS

  # Apache Impala — no auth (NOSASL): no user/password, no auth_mech
  impala-prod:
    type: impala
    host: 10.0.0.9
    port: 21050               # HiveServer2 port (NOT 21000, which is impala-shell's)
    database: default
    allow_write: false

  # Apache Impala — LDAP auth (Cloudera AuthMech=3)
  impala-ldap:
    type: impala
    host: 10.0.0.9
    port: 21050
    database: default
    auth_mech: LDAP            # auth mechanism: LDAP (case-insensitive)
    user: bdp_admin
    password: ${DB_PWD_IMPALA_LDAP}
    allow_write: false
    timeout: 15s

  # Apache Impala with Kerberos (keytab auth, pure Go — no system kinit)
  impala-kerb:
    type: impala
    host: 10.0.0.9
    port: 21050               # HiveServer2 port of the kerberized Impala
    database: default
    allow_write: false
    timeout: 20s              # the Kerberos handshake is slower than no-auth
    kerberos:                 # optional — omit to use username/password or no-auth
      realm: EXAMPLE.COM             # KDC realm
      service: impala                # service principal's first segment (default: impala)
      krb_host: impalad-node1        # hostname in the service principal (impala/krb_host@REALM)
      keytab: /etc/security/keytabs/cdp.keytab
      principal: cdptest@EXAMPLE.COM # client principal (include realm)
      krb5_conf: /etc/krb5.conf      # krb5.conf (contains the KDC address)

  # Writable datasource (any type)
  dev-rw:
    type: mysql
    host: 10.0.0.3
    port: 3306
    database: app_db
    user: dev
    password: ${DB_PWD_DEV}
    allow_write: true

MySQL note: database is required (MySQL's notion of schema == database). 8.0's default auth plugin caching_sha2_password works without extra config.

Connection timeout: add timeout: 10s to any datasource. dbm-cli verifies the connection on first use; if it can't connect within the timeout it fails fast with a clear message (host/port/credentials/network hints). Default 30s.

Datasource description: add an optional description: "..." to any datasource — a human/AI-readable note of its role (e.g. "prod orders DB, read-replica"). Shown by dbm-cli config so an agent can pick the right datasource by purpose, not just by name.

🚀 Usage

dbm-cli config                                          # show config overview (datasources, default; passwords hidden)
dbm-cli config    -o json                               # ...as JSON (for AI/scripts to pick a datasource)
dbm-cli datasources                                     # list configured datasources (passwords hidden)
dbm-cli version   -d prod-ro                            # database version
dbm-cli databases -d prod-ro                            # databases / PDBs
dbm-cli schemas   -d prod-ro --like HR%                 # schemas
dbm-cli tables    -d prod-ro --schema HR                # tables
dbm-cli columns   -d prod-ro --table EMPLOYEES --schema HR
dbm-cli indexes   -d prod-ro --table EMPLOYEES
dbm-cli views     -d prod-ro --schema HR
dbm-cli table     -d prod-ro --name EMPLOYEES --schema HR --limit 20 -o json
dbm-cli query     -d prod-ro "SELECT * FROM HR.EMPLOYEES WHERE ROWNUM<=10"
dbm-cli call      -d prod-ro add_numbers --in 3 --in 5 --out sum:number   # Oracle stored procedure
dbm-cli manifest                                        # self-describing JSON for AI agents

Global flags: -c/--config, -d/--datasource, -o/--output {table|json|csv|yaml|vertical}, --no-header

Custom SQL (query)

query runs any SQL. Read-only statements execute directly; writes go through the allow_write guard (off by default), with a second confirmation for destructive statements (DROP/TRUNCATE, DELETE/UPDATE without WHERE).

Three SQL sources (priority: --file > stdin > argument):

dbm-cli query -d prod-ro "SELECT * FROM HR.EMPLOYEES WHERE ROWNUM<=10"   # argument
dbm-cli query -d prod-ro -f report.sql                                    # from file
echo "SELECT COUNT(*) FROM orders" | dbm-cli query -d prod-ro             # stdin / pipe / heredoc

Parameterized queries (SQL-injection-safe — preferred over string concatenation). Write ? as the placeholder; it is auto-translated to each engine's native style (? for MySQL/ClickHouse, $1 for PostgreSQL, :1 for Oracle):

dbm-cli query -d prod-ro "SELECT * FROM users WHERE id=? AND status=?" --param 100 --param active

Result-set guard — --limit caps rows returned by read-only queries (default 1000, <=0 disables) to prevent an accidental SELECT * from pulling a huge table:

dbm-cli query -d prod-ro -f big-report.sql --limit 500

Flags: --file/-f, --param (repeatable, positional binding), --limit (default 1000), --yes (skip destructive confirmation).

Stored procedures (call) — Oracle

call invokes an Oracle PL/SQL procedure and reclaims its outputs. It supports three kinds of OUT result:

• Scalar OUT params — declared via --out name:type, where type ∈ number / string; printed to stdout as name = value.
• REF CURSOR — declared via --out name:cursor; the returned result set is printed using the global -o format (table/json/...).
• DBMS_OUTPUT print — DBMS_OUTPUT.PUT_LINE text is captured (session-pinned connection) and printed to stderr by default; --no-print disables capture.

Parameters bind in order: all --in first (placeholders :1..:N), then all --out (:N+1..) — matching the procedure's signature.

dbm-cli call add_numbers --in 3 --in 5 --out sum:number
dbm-cli call get_users   --in 2 --out users:cursor
dbm-cli call get_user_info --in 42 --out status:string --out count:number --out rows:cursor

Flags: --in (repeatable, positional), --out name:type (repeatable), --no-print.

Only the Oracle driver implements stored-procedure calls. The capability is exposed through an optional driver.StoredProcCaller interface, so other drivers are unaffected (the call command reports "not supported" for them rather than failing the build).

Output formats

dbm-cli tables -d prod-ro --schema HR                      # bordered table
dbm-cli tables -d prod-ro --schema HR -o json              # JSON
dbm-cli table  -d prod-ro --name EMP --limit 5 -o csv      # CSV

Exit codes & errors (for automation / AI)

All errors go to stderr as [dbm-cli] error: <message>, often followed by a [dbm-cli] hint: line telling you exactly what to change before retrying.

🔌 MCP server (for AI clients)

dbm-cli mcp runs the tool as a Model Context Protocol server over stdio. AI clients (Claude Desktop, Cursor, etc.) can connect to it and call the database directly — no need to shell out to CLI commands.

It exposes 12 tools, mirroring the CLI one-to-one:

Safety is inherited, not re-implemented: every connection goes through the same allow_write guard as the CLI, so a read-only datasource rejects writes identically. Since MCP has no interactive terminal, the execute tool is stricter than the CLI — destructive statements (DROP / TRUNCATE / DELETE/UPDATE without WHERE) require an explicit confirm_destructive: true argument.

Configure in Claude Desktop

{
  "mcpServers": {
    "dbm-cli": {
      "command": "/path/to/dbm-cli",
      "args": ["mcp"]
      // optional: "args": ["mcp", "-c", "/path/to/dbm-cli.yaml"]
    }
  }
}

The server reads the same YAML config as the CLI (see Configuration). Each tool takes an optional datasource argument (falls back to the configured default).

The mcp subcommand is additive — it does not change any existing CLI command. Users who never call dbm-cli mcp are completely unaffected.

🏗️ Architecture

dbm-cli is built around a driver abstraction so that adding a database touches almost no existing code. Core patterns:

Adding a datasource (e.g. MySQL): create internal/mysql/, implement Driver/Conn/MetadataProvider, call driver.Register in init(), and add one line to cmd/dbm-cli/main.go: import _ "github.com/golango-cn/dbm-cli/internal/mysql". The CLI, output layer and manifest pick it up automatically.

📚 Programmatic API

A stable Facade lives in pkg/dbmcli for embedding in other Go programs:

import "github.com/golango-cn/dbm-cli/pkg/dbmcli"

conn, cleanup, err := dbmcli.Open(ctx, &dbmcli.Datasource{
    Type: "oracle", Host: "10.0.0.1", Port: 1521,
    ServiceName: "ORCLPDB1", User: "ro", Password: os.Getenv("PWD"),
})
defer cleanup()
tables, _ := conn.Metadata().Tables(ctx, "HR")

📌 Roadmap

• M0 Skeleton
• M1 Oracle connectivity (driver, version detection, ping)
• M2 Metadata queries (multi-version dictionary SQL)
• M3 Data queries (table paging, query read path)
• M4 Writes & guard (allow_write, classification, destructive confirmation)
• M5 Output formats
• M6 Manifest (driver self-description injection)
• MySQL driver (5.6 / 5.7 / 8.0.x, verified end-to-end against 8.0.37)
• Oracle driver verified end-to-end against 18c XE
• ClickHouse driver (22.x+, verified end-to-end against 25.8)
• PostgreSQL driver (9.6+, verified end-to-end against 12 / 17)
• Apache Impala driver (3.x/4.x, verified end-to-end against 4.5.0)
• Impala Kerberos auth (keytab, pure Go, verified against TESTBOE.COM realm)
• Impala LDAP auth (auth_mech: LDAP, Cloudera AuthMech=3, verified against 4.5.0 with LDAP+SASL)
• SQL Server driver (2017+, verified end-to-end against 2017 / 2022)
• Oracle stored-procedure calls (call, verified against 11g / 18c)
• M7 Polish: unit tests, cross-platform release

All drivers (Oracle, MySQL, ClickHouse, PostgreSQL, Apache Impala, SQL Server) are complete, pass go vet + go build, and have been verified end-to-end against live instances: Oracle 11g/18c XE, MySQL 5.7/8.0.12/8.0.37, ClickHouse 25.8, PostgreSQL 12/17, Apache Impala 4.5.0 (incl. Kerberos), SQL Server 2017/2022. Oracle stored-procedure calls (call) are verified against 11g / 18c.

🤝 Contributing

Issues and PRs welcome. Please run go vet ./... and go test ./... before submitting.

📄 License

MIT © golango