Miudb

---
name: miudb
description: >
  Query, inspect, and manage saved database connections through the Go `miudb`
  CLI. Use when the user asks to run SQL, list schemas, add native
  connections, smoke-test connections, inspect tunnel-backed databases, or
  produce agent-readable JSON from SQLite, Postgres, MySQL, Snowflake, or
  BigQuery.
license: MIT
allowed-tools:
  - Bash
metadata:
  author: vanducng
  version: "0.2.1"
  binary: miudb
---

# miudb

Headless database CLI for agent-safe SQL work against saved database
connections. Prefer direct `miudb` commands for one-shot machine-readable
output and scripted checks. Use `miudb mcp serve` only when configuring an MCP
host, and use `miudb serve` only for Neovim/custom protocol clients.

Do not use `sqlit` for miudb tasks.

## Install/verify

```bash
brew install vanducng/tap/miudb   # first install
miudb upgrade                      # self-update to the latest release
miudb version --output json        # v0.8.0 or newer
miudb commands --output json
```

If the catalog lacks a command you need, run `miudb upgrade`, or build from the
local checkout:

```bash
cd <your-miu-db-checkout>          # your local clone of the miu-db source repo
go build -buildvcs=false -o ./.miu-db/miudb ./cmd/miudb
```

## Default local config

`miudb` uses a native Go store by default:

```text
~/.config/miu/db/connections.json
~/.config/miu/db/credentials.json
```

Sensitive values are classified before persistence. New database and SSH
passwords are stored outside `connections.json` by default using the OS
Keychain/keyring service named `miudb`.

For migrated configs, `miudb` reads `credentials-export.json` from the same
directory when `credentials.json` is absent.

Only pass `--config-dir`, `--connections-file`, `--credentials-file`,
`--credentials-export`, `--secret-source`, `--keyring-service`, or
`--gopass-prefix` when the user asks for a non-default store.

By default, secret lookup can use file credentials, the `miudb` keyring
service, and gopass paths under the `miudb` prefix. Treat `--credentials-export`
as a deprecated alias for `--credentials-file`.

## Discover commands and connections

```bash
miudb commands --output json
miudb describe connections add --output json
miudb describe connections test --output json
miudb describe query run --output json
miudb describe connections smoke --output json
miudb describe mcp serve --output json
miudb connections list --output json
miudb connections list --basic --output json   # scannable: ref/name/group/db_type/host
```

Connections are addressed by **`group/name`** (e.g. `analytics/warehouse-prod`); a bare
`name` works when it is unique across groups. The `ref` column from `--basic` is
exactly what to pass to `--connection`. The `-c` short flag only exists on
`erd` subcommands (see below) - other commands like `query run` require the
long `--connection` form and reject `-c` with "unknown shorthand flag". If the
named connection is not listed, stop and ask the user. Do not substitute a
similar connection.

## Add connections

```bash
miudb connections add \
  --name app-dev \
  --db-type postgresql \
  --host localhost \
  --port 5432 \
  --database app \
  --username app \
  --password "$APP_DB_PASSWORD" \
  --secret-store keyring \
  --output json
```

Variations: SQLite uses `--db-type sqlite --path ./app.db` (no host); bastion-reachable servers add `--tunnel --ssh-config-alias bastion`; provider settings are repeatable `--option k=v` pairs (`--option authenticator=snowflake_jwt --option warehouse=DEV_WH`) plus `--extra-option sslmode=require`. Full flag surface: `miudb describe connections add --output json`.

Secret stores for new connections:

- `keyring`: OS Keychain/keyring service named `miudb` on CGO-enabled builds.
  Release binaries (`brew install`) are CGO-disabled and fall back to an
  encrypted file keyring at `~/.config/miu/db/keyring/<connection>:<kind>`
  (JWE / PBES2-HS256+A128KW, one file per secret) - NOT the OS Keychain, so
  `security find-generic-password -s miudb ...` finds nothing there.
- `file`: local `credentials.json` with mode `0600`.
- `inline`: leave the value in `connections.json`.
- `none`: discard the supplied secret and require another resolver later.

Rules:

- Prefer `--secret-store keyring` for user-entered credentials.
- Use `--password-command` only when the command is already trusted by the
  user; never invent a credential command.
- Use `--secret-store file` only for disposable/local test configs or when the
  user explicitly wants a file-backed credential.
- Never print passwords, credential files, private keys, or service account
  JSON.

Verify where a secret landed (cheapest first, never reveals the value):

- `miudb connections list --output json` -> `has_password: true`, and the
  connection's `secrets[]` shows `provider: keyring` + `ref`.
- `ls ~/.config/miu/db/keyring/` -> `<connection>:db` file on release builds
  (contents are JWE-encrypted; the raw password is not in the file).
- `miudb connections test <CONN>` -> `ok: true` proves it resolves end-to-end.

## Test one connection

```bash
miudb connections test <CONN> \
  --timeout 12s \
  --output json
```

Use `connections test` when the user names one connection or asks whether one
connection is reachable. This opens the connection and may create an SSH
tunnel, but does not run user SQL.

## OAuth login for Snowflake and BigQuery

Acquire and store an OAuth token for a connection supporting OAuth (Snowflake, BigQuery):

```bash
miudb auth login <CONN> --output json
miudb auth status <CONN> --output json
miudb auth logout <CONN> --output json
```

Tokens are stored in the keyring service. On CGO-disabled builds (release binaries), fallback to file-based credential storage.

## Smoke-test connections

```bash
miudb connections smoke \
  --timeout 12s \
  --concurrency 4 \
  --output json
```

Interpretation:

- Top-level `ok: false` can be expected when local-only databases are stopped.
- Check `.data.results[]` for per-connection pass/fail.
- Local failures like `localhost:3307 refused` usually mean a local service or
  tunnel is not running.
- Tunnel failures can mean SSH alias/key/remote network issues.

Summarize results without printing passwords or secret file contents.

## Run a query

```bash
miudb query run \
  --connection <CONN> \
  --sql '<SQL>' \
  --limit 100 \
  --output json
```

Rules:

- Keep `--limit` bounded unless the user explicitly asks for a large export.
- Prefer read-only SQL unless the user explicitly authorizes mutation.
- Use single quotes around SQL containing BigQuery/MySQL backticks.
- If SQL contains single-quoted literals and backticks, escape carefully; do
  not assume file input exists unless `miudb describe query run` says it does.
- Snowflake rejects the alias `rows` (`unexpected 'rows'`). `ROWS` is reserved.
  Use another alias, such as `row_cnt`.

## Fetch paged results

If `query run` returns a cursor or truncation marker, continue with:

```bash
miudb query fetch-page \
  --cursor <CURSOR> \
  --output json
```

## Run multi-statement scripts

For Snowflake and MySQL, execute multiple statements in one script; each statement produces one result set:

```bash
miudb query script \
  --connection <CONN> \
  --sql 'SELECT 1; SELECT 2' \
  --output json
```

Postgres rejects multi-command scripts; run statements individually with `query run`.

## Inspect schema

```bash
miudb schema tree \
  --connection <CONN> \
  --output json
```

Metadata SQL also works through `query run`: `information_schema` tables/columns on Postgres/Snowflake, `SHOW DATABASES` / `SHOW TABLES` / `DESCRIBE` on MySQL, `INFORMATION_SCHEMA` on BigQuery (quote `dataset`.`table` with backticks inside single-quoted SQL), `sqlite_master` + `PRAGMA table_info` on SQLite.

## Generate an ERD (interactive diagram)

`miudb erd` turns a connection into a self-contained, offline interactive ER diagram (Cytoscape) plus a DBML export. Tier-1: MySQL, Postgres. Snowflake/BigQuery/DuckDB return a clean "unsupported" error.

```bash
# interactive offline index.html + schema.json + schema.dbml (default --format html)
miudb erd generate -c <group/name> --out-dir .diagrams/<name>-erd --output json
# serve it; auto-opens the browser for an interactive terminal (--no-open to suppress);
# --from <dir|schema.json> renders an existing export with no DB
miudb erd serve -c <group/name> --output json
```

Short flags on every `erd` command: `-c`/`--connection`, `-s`/`--schema`,
`-m`/`--meta`, `-f`/`--format`, `-p`/`--port`. A connection with no default
database (e.g. a server-level DSN) errors with a clear "pass --schema" hint.

Two layers:
- **Deterministic (miudb):** introspect -> `schema.json` (the render source-of-truth) -> DBML + HTML. No LLM.
- **Agentic (you):** author `meta.json` to add **colored domain groups** + table **descriptions**. Without it, tables render as Framework/Other with no colors (`erd generate` emits a warning saying so).

### Agentic polish recipe (how to make a good diagram)

1. Scaffold: `miudb erd meta --stub --connection <CONN> --out-dir <dir>` - auto-detects framework tables (Laravel/Rails/Django/Prisma) and seeds blank `groups`/`descriptions`. The envelope `data.next_step` reminds you what to fill.
2. Read `<dir>/schema.json` (the IR: `tables[] -> {pk, columns, fks, indexes, rows}`). Cheap, high-signal inputs: FK topology (which tables connect), table-name prefixes, FK hub degree, and row counts. Migration *filenames* (`ls database/migrations` / `db/migrate`) are a strong signal too - you do NOT need to read their contents.
3. Edit `meta.json`:
   - **`groups`**: cluster every non-framework table into 5-9 domains. Densely-FK-connected tables belong together; split by name prefix and responsibility (catalog vs apply vs analytics vs CMS vs auth). Each group = `{ "color": "#hex", "tables": [...] }`. Put core domains in saturated colors, infra/marketing in muted. Palette: `#2563eb` blue, `#16a34a` green, `#9333ea` purple, `#d97706` amber, `#dc2626` red, `#0d9488` teal, `#db2777` pink, `#475569` slate.
   - **`descriptions`**: 1 line per important table (hubs + biggest by rows), inferred from name + columns + FK role (self-ref FK -> "conditional/nested"; double-FK + unique pair -> "junction"; `*_id` hub -> "owns/links X").
   - leave **`framework_tables`** / **`audit_columns`** as detected.
4. Regenerate: `miudb erd generate --connection <CONN> --meta <dir>/meta.json` (or `erd serve`). Iterate.

Single-pass works: stub -> fill -> generate. Aim to leave 0 tables ungrouped (the renderer buckets ungrouped non-framework tables as "Other"). Use generic examples; never paste real connection/schema names into the diagram metadata you commit.

## Stdio protocol

For Neovim or custom client integration only (normal agent work uses the direct CLI above):

```bash
miudb serve --protocol jsonrpc --output json
```

## MCP server

For MCP-native hosts such as Codex, Claude Code, Cursor, and VS Code, use:

```bash
miudb mcp serve --transport stdio
```

Useful flags:

- `--connection <name>`: repeat to restrict visible/callable connections.
- `--limit <n>`: default row limit for MCP query tools.
- `--max-limit <n>`: maximum accepted MCP query limit.
- `--max-bytes <n>`: maximum serialized bytes per tool/resource response.
- `--allow-mutate`: allow mutation SQL through MCP `query_run`; unsafe.

MCP tools exposed by the server:

- `connections_list`
- `connection_describe`
- `connection_test`
- `connections_smoke`
- `schema_tree`
- `query_run`
- `query_fetch_page`

MCP `query_run` is read-only by default and rejects mutation SQL unless
`--allow-mutate` is explicitly provided. Stdout is reserved for MCP frames;
startup errors and diagnostics go to stderr.

## Query activity log

Each session captures activity events in a per-session JSONL log. Query and prune it with:

```bash
miudb activity --connection <CONN> --since 24h --output json
miudb activity --failed --since 7d --output json
miudb activity prune --older-than 30d --output json
```

Use `--since` to filter by relative duration (e.g. `24h`, `7d`); omit to read all captured events. Use `--failed` to show only failed queries.

## Output contract

- stdout is JSON.
- stderr is diagnostics only.
- `ok: false` is a structured failure, not necessarily a shell failure.
- **Query results live at `data.result`** - `columns[]` (objects with `name`/`type`) and
  `rows[]` (array-of-arrays, positional to `columns`), plus `truncated`. On failure there is
  **no `data` key** at all; read top-level `error.code` / `error.message`. Parse defensively:

  ```bash
  miudb query run --connection <CONN> --sql '<SQL>' --output json | python3 -c "
  import sys,json
  d=json.load(sys.stdin)
  if not d.get('ok'): print('ERR:', d['error']['message'][:200]); sys.exit(1)
  r=d['data']['result']
  print(' | '.join(c['name'] for c in r['columns']))
  for row in r['rows']: print(' | '.join('' if v is None else str(v) for v in row))"
  ```

  Guarding on `ok` first matters: a blocked network or expired credential returns a well-formed
  envelope with exit status 0, so an unguarded `d['data']` raises `KeyError` instead of showing
  the real error.
- Command descriptions are available via `miudb describe <command>`.
- Output is secret-hardened: credential-named values, password-bearing URLs, and
  `key=secret` assignments are redacted before stdout (`connections list` shows
  `has_password: true`, never the value). Query-result *values* are NOT masked -
  they're the user's data. Do not inspect credential stores unless asked.
- The command catalog currently includes `connections test`, `mcp serve`, and
  the native `serve` protocol; choose the narrowest command that matches the
  user's use case.

## Failure modes

- **connection not found** -> run `connections list`, then ask the user.
- **localhost refused** -> local database/tunnel is not running.
- **secret timeout** -> keyring/gopass lookup may need user session access.
- **SSH/tunnel error** -> check `~/.ssh/config`, key path, username, and network.
- **BigQuery auth error** -> verify `options.bigquery_credentials_path`.
- **Snowflake JWT error** -> verify `options.private_key_file`.
- **query too large** -> lower `--limit` or ask before exporting.