xsql

Let AI safely query your databases 🤖🔒

中文文档

xsql is a cross-database CLI tool designed for AI agents. Read-only by default, structured output, ready out of the box.

# AI can query your database like this
xsql query "SELECT * FROM users WHERE created_at > '2024-01-01'" -p prod -f json --attr source=codex-cli --attr agent=codex --attr env=prod --attr task=targeted-query

✨ Why xsql?

Feature	Description
🔒 Safe by Default	Dual-layer read-only protection prevents accidental writes by AI
🤖 AI-first	JSON structured output designed for machine consumption
🔑 Secure Credentials	OS Keyring integration — passwords never touch disk
🌐 SSH Tunneling	One-line config to connect to internal databases
📦 Zero Dependencies	Single binary, works out of the box

🚀 Quick Start

1. Install

# macOS
brew install zx06/tap/xsql

# Windows
scoop bucket add zx06 https://github.com/zx06/scoop-bucket && scoop install xsql

# npm / npx
npm install -g xsql-cli

# Or download directly: https://github.com/zx06/xsql/releases

2. Configure

mkdir -p ~/.config/xsql
cat > ~/.config/xsql/xsql.yaml << 'EOF'
profiles:
  dev:
    db: mysql
    host: 127.0.0.1
    port: 3306
    user: root
    password: your_password
    database: mydb
    allow_plaintext: true  # Use keyring for production
EOF

3. Use

xsql query "SELECT 1" -p dev -f json
# {"ok":true,"schema_version":1,"data":{"columns":["1"],"rows":[{"1":1}]}}

---

🤖 Let AI Use xsql

Option 1: Agent Skills Directory (Recommended)

Install via The Agent Skills Directory:

npx skills add zx06/xsql

Option 2: Claude Code Plugin

# 1. Add marketplace
/plugin marketplace add zx06/xsql

# 2. Install plugin
/plugin install xsql@xsql

After installation, Claude automatically gains xsql skills and can query databases directly.

Option 3: Copy Skill Prompt to Any AI

Send the following to your AI assistant (ChatGPT/Claude/Cursor, etc.):

You can now use the xsql tool to query databases.

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

## Available Commands
- xsql query "SQL" -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=<task> # Execute query
- xsql schema dump -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=schema-discovery # Export database schema
- xsql profile list -f json --attr source=codex-cli --attr agent=codex --attr task=profile-discovery # List all profiles
- xsql profile show <name> -f json --attr source=codex-cli --attr agent=codex --attr task=profile-discovery # Show profile details

## Output Format
Success: {"ok":true,"schema_version":1,"data":{"columns":[...],"rows":[...]}}
Failure: {"ok":false,"schema_version":1,"error":{"code":"XSQL_...","message":"..."}}

## Important Rules
1. Read-only mode by default — writes require both profile `unsafe_allow_write: true` and the current `--unsafe-allow-write` flag
2. Always use -f json for structured output
3. Add `--attr source=codex-cli` to every Codex-driven xsql invocation; add `agent`, `env`, `team`, and `task` when known
4. Use profile list to see available database configurations
5. Check the ok field to determine execution success

## Exit Codes
0=success, 2=config error, 3=connection error, 4=read-only violation, 5=SQL execution error

Option 4: MCP Server (Claude Desktop, etc.)

Add the xsql MCP server to your Claude Desktop configuration:

{
  "mcpServers": {
    "xsql": {
      "command": "xsql",
      "args": ["mcp", "server", "--config", "/path/to/xsql.yaml", "--attr", "source=codex-cli", "--attr", "agent=codex"]
    }
  }
}

Once started, Claude can query databases directly via the MCP protocol.

Option 5: AGENTS.md / Rules (Cursor/Windsurf)

Create .cursor/rules or edit AGENTS.md in your project root:

## Database Queries

Use xsql to query databases:
- Query: `xsql query "SELECT ..." -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=targeted-query`
- Export schema: `xsql schema dump -p <profile> -f json --attr source=codex-cli --attr agent=codex --attr env=<env> --attr task=schema-discovery`
- List profiles: `xsql profile list -f json --attr source=codex-cli --attr agent=codex --attr task=profile-discovery`

Note: Read-only mode by default. Writes require both profile `unsafe_allow_write: true` and `--unsafe-allow-write` on the current CLI invocation.

---

📖 Features

Command Reference

Command	Description
xsql ai [PROMPT]	Start interactive AI assistant mode (TUI)
xsql query <SQL>	Execute SQL queries (read-only by default)
xsql schema dump	Export database schema (tables, columns, indexes, foreign keys)
xsql profile list	List all profiles
xsql profile show <name>	Show profile details (passwords are masked)
xsql mcp server	Start MCP Server (AI assistant integration)
xsql config init/set	Create or update the configuration file
xsql proxy	Start an SSH local port-forwarding proxy
xsql serve / xsql web	Start the local Web UI
xsql stats	Show usage statistics and audit attributes
xsql spec	Export AI Tool Spec (supports --format yaml)
xsql version	Show version information

Output Formats

# JSON (for AI/programs)
xsql query "SELECT id, name FROM users" -p dev -f json
{"ok":true,"schema_version":1,"data":{"columns":["id","name"],"rows":[{"id":1,"name":"Alice"}]}}

# Table (for terminals)
xsql query "SELECT id, name FROM users" -p dev -f table
id  name
--  -----
1   Alice

(1 rows)

Schema Discovery (AI auto-understands your database)

# Export database schema (for AI to understand table structures)
xsql schema dump -p dev -f json

# Filter specific tables
xsql schema dump -p dev --table "user*" -f json

# Example output
{
  "ok": true,
  "data": {
    "database": "mydb",
    "tables": [
      {
        "name": "users",
        "columns": [
          {"name": "id", "type": "bigint", "primary_key": true},
          {"name": "email", "type": "varchar(255)", "nullable": false}
        ]
      }
    ]
  }
}

SSH Tunnel Connection

ssh_proxies:
  bastion:
    host: jump.example.com
    user: admin
    identity_file: ~/.ssh/id_ed25519

profiles:
  prod:
    db: pg
    host: db.internal  # Internal network address
    port: 5432
    user: readonly
    password: "keyring:prod/password"
    database: mydb
    ssh_proxy: bastion  # Reference SSH proxy

Port Forwarding Proxy (xsql proxy)

When you need traditional ssh -L behavior or want to expose a local port for GUI clients, use xsql proxy:

# Start port forwarding (auto-assign local port)
xsql proxy -p prod

# Specify local port
xsql proxy -p prod --local-port 13306

This command requires the profile to have ssh_proxy configured. It listens on a local port and forwards traffic to the target database.

Security Features

• Dual-layer Read-only Protection: SQL static analysis + database transaction-level read-only
• Dual Write Authorization: CLI writes require both profile permission and a per-invocation flag
• Keyring Integration: password: "keyring:prod/password"
• Password Masking: profile show never exposes passwords
• SSH Security: known_hosts verification enabled by default

---

📚 Documentation

Document	Description
CLI Specification	Detailed CLI interface reference
Configuration Guide	Config file format and options
SSH Proxy	SSH tunnel configuration
Error Handling	Error codes and exit codes
AI Integration	MCP Server and AI assistant integration
RFC Documents	Design change records
Development Guide	Contributing and development notes

---

License

MIT