Xsql

xsql

xsql is an AI-first database CLI that provides stable JSON output, explicit error codes, schema discovery, and profile-aware access to MySQL/PostgreSQL over SSH.

Default Operating Rules

• Use --format json (-f json) for all agent-driven work — it returns structured ok/data/error envelopes. YAML format (-f yaml) is also available with the same envelope structure.
• Add --attr source=codex-cli to every Codex-driven xsql invocation. Additional attributes are encouraged, but do not override the reserved source marker. Other AI clients should replace the value with their own stable client name.
• Use additional attributes to make usage searchable: agent (for example codex), env (for example prod), team, and task are recommended when known.
• stdout is data; stderr is logs. Never parse stderr as result data.
• Validate responses by checking ok, schema_version, and error.code — not by string matching.
• Assume read-only mode. Writes require both unsafe_allow_write: true in the selected profile and --unsafe-allow-write on the current CLI invocation; only use both when the user has explicitly requested writes.
• Never leak secrets, full DSNs, passwords, or private keys in output or summaries.
• --ssh-skip-known-hosts-check is a risky last resort — call out the security tradeoff if it's needed.

Working Sequence

Follow this sequence unless the user already knows the exact profile and SQL.

Step 1: Resolve the profile

xsql profile list --format json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=profile-discovery

Match the user's intent to a profile by checking each profile's description and database fields. If the user says "生产环境" or "prod", look for a profile whose description contains those keywords. If ambiguous, ask the user to choose.

Then confirm the profile details:

xsql profile show <profile> --format json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=profile-discovery

Note the db field (mysql or pg) — it determines which SQL dialect to use. Also note the database name for use in queries.

Step 2: Discover schema (adapt to database size)

Small databases (under ~20 tables): Full dump is fine.

xsql schema dump -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=schema-discovery

Medium/large databases (20+ tables): Full dump may be too large for context. Use a two-pass approach:

Pass 1 — Get table list only (lightweight, via information_schema):

# MySQL
xsql query "SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA = '<database>' ORDER BY TABLE_ROWS DESC" -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=health-check

# PostgreSQL
xsql query "SELECT relname AS table_name, reltuples::bigint AS row_estimate, pg_total_relation_size(relid) AS total_bytes FROM pg_stat_user_tables ORDER BY reltuples DESC" -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=health-check

Pass 2 — Get detailed schema only for the tables you actually need:

xsql schema dump -p <profile> -f json --table "user*" --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=schema-discovery

Additional schema dump flags: --include-system to include system tables, --schema-timeout <seconds> to override the default 60s timeout.

Step 3: Write and run targeted queries

Write minimal, narrow queries with explicit columns, predicates, and LIMIT. Match SQL syntax to the profile's engine (MySQL vs PostgreSQL).

xsql query "<SQL>" -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=targeted-query

For long-running queries, set a timeout (default: 30s):

xsql query "<SQL>" -p <profile> -f json --query-timeout 60 --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=long-query

Step 4: Validate the response

Check the JSON envelope:

• ok: true → use data.rows and data.columns
• ok: false → inspect error.code and error.message

Health Check Workflow

When the user asks for a database analysis or health check, run multiple analysis patterns together. Run independent queries in parallel for efficiency.

The SQL patterns for each engine are in the reference files — read only the one matching the profile's db field:

• MySQL: Read references/mysql-patterns.md
• PostgreSQL: Read references/postgresql-patterns.md

Recommended health check scope

For a comprehensive health check, include these areas in order of impact:

1. Database Overview — table sizes and row counts, identify the biggest tables
2. Fragmentation Analysis — detect wasted space and performance degradation
3. Missing Index Detection — find tables that need better indexing
4. Stale Table Detection — identify potentially abandoned tables

For specific investigations, also consider:

• Growth Trend Analysis — when the user asks about data growth or capacity planning
• Column Distribution Analysis — when the user asks about data distribution or skew
• Server Status — for deep performance diagnostics

Querying Guidance

• Schema discovery first, query second — never guess table or column names.
• Keep queries narrow: explicit columns, WHERE predicates, and LIMIT.
• For large tables, prefer aggregation (COUNT, GROUP BY) over SELECT *.
• Use --query-timeout for long-running queries (default: 30s).
• Use --schema-timeout for large schema dumps (default: 60s).
• Run xsql spec --format json --attr source=codex-cli --attr agent=codex --attr task=tool-discovery to discover all available commands and flags.

Output And Error Handling

JSON responses follow this envelope:

{"ok": true, "schema_version": 1, "data": {"columns": [...], "rows": [...]}}

{"ok": false, "schema_version": 1, "error": {"code": "...", "message": "...", "details": {...}}}

Handle failures by error.code and exit status:

Exit Code	Meaning
0	success
2	argument/config error
3	DB or SSH connection error
4	read-only policy blocked a write
5	database execution error
10	internal error

Table and CSV formats are for humans — they lack the ok/schema_version envelope.

Additional Commands

# MCP server for AI assistant integration
xsql mcp server --attr source=codex-cli --attr agent=codex # stdio transport (default)
xsql mcp server --transport streamable_http --attr source=codex-cli --attr agent=codex # HTTP transport
xsql mcp server --transport streamable_http \
  --http-addr 127.0.0.1:8787 --http-auth-token <token> \
  --attr source=codex-cli --attr agent=codex            # HTTP with auth

# Web UI
xsql web --attr source=codex-cli --attr agent=codex   # start web server and open browser
xsql serve --attr source=codex-cli --attr agent=codex # start web server (headless, no browser)

# Configuration
xsql config init --attr source=codex-cli --attr agent=codex                           # create template config file
xsql config set profile.dev.host localhost --attr source=codex-cli --attr agent=codex # set a config value by dot-notation key

# Tool metadata and version
xsql spec --format json --attr source=codex-cli --attr agent=codex # export tool spec for AI/agents
xsql version --attr source=codex-cli --attr agent=codex            # print version info

Config And Profile Rules

• Precedence: CLI flags > ENV vars > Config file. ENV vars use XSQL_ prefix (e.g. XSQL_PROFILE, XSQL_FORMAT).
• Config lookup: ./xsql.yaml, then ~/.config/xsql/xsql.yaml.
• Prefer keyring-backed secrets. Plaintext secrets require --allow-plaintext.
• If no profile is specified and a default profile exists, xsql uses it automatically.

SSH Rules

• The built-in SSH driver-dial path is the default and preferred method.
• Use xsql proxy only when the workflow explicitly needs a local port-forward.
• Keep host-key verification enabled by default.
• One-shot commands (query, schema dump) use fresh connections — don't expect long-lived sessions.