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).
• 🟦 SQL Server 2017+ — metadata via sys.* catalog views; OFFSET/FETCH paging; no ODBC driver needed (pure-Go go-mssqldb).
• 🧩 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.
• 🎨 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 demo/ that exercises every command via a Makefile. It is database-agnostic — point it at any datasource you have configured:

cd demo
# 1. build the binary into ./demo
go build -o dbm-cli ../cmd/dbm-cli
# 2. create a config (copy & edit from ../examples/config.yaml.example)
cp ../examples/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 Makefile and init-demo-data.sh 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

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

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.

🏗️ 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)
• SQL Server driver (2017+, verified end-to-end against 2017 / 2022)
• M7 Polish: unit tests, cross-platform release

All four drivers (Oracle, MySQL, ClickHouse, PostgreSQL) 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, SQL Server 2017/2022.

🤝 Contributing

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

📄 License

MIT © golango