xsql

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

✨ Why xsql?

🚀 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: Claude Code Plugin (Recommended)

# 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 2: 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

## Available Commands
- xsql query "SQL" -p <profile> -f json  # Execute query
- xsql schema dump -p <profile> -f json  # Export database schema
- xsql profile list -f json               # List all profiles
- xsql profile show <name> -f json        # 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 — cannot execute INSERT/UPDATE/DELETE
2. Always use -f json for structured output
3. Use profile list to see available database configurations
4. 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 3: 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"]
    }
  }
}

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

Option 4: 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`
- Export schema: `xsql schema dump -p <profile> -f json`
- List profiles: `xsql profile list -f json`

Note: Read-only mode by default. Write operations require the --unsafe-allow-write flag.

📖 Features

Command Reference

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
• Keyring Integration: password: "keyring:prod/password"
• Password Masking: profile show never exposes passwords
• SSH Security: known_hosts verification enabled by default

📚 Documentation

License

MIT