SQL Butler

Agentic middleware that turns a sandboxed SQL database into an intelligent, self-optimizing data store.

Point your application at SQL Butler and let an LLM agent handle schema design, data ingestion, query optimization, and retrieval — all backed by plain SQL with full transparency. No vector database, no vendor lock-in.

Warning

Experimental / Use at your own risk: This project is an experimental agentic framework and has not been extensively tested for production workloads. Please ensure your database sandbox configurations are secure and use at your own risk.

---

How it works

Your app / curl
      │
      ▼
 POST /api/v1/ingest          POST /api/v1/query
      │                              │
      ▼                              ▼
 Ingest Agent                   Query Agent
 • inspects schema              • inspects schema
 • creates tables               • generates SQL
 • inserts + indexes            • retries if needed
 • builds FTS index             • formats answer
      │                              │
      └──────────────┬───────────────┘
                     ▼
          Sandboxed SQL Engine
          SQLite (default) │ PostgreSQL

A background scheduler also runs an optimization agent periodically — analyzing query patterns, creating missing indexes, and running ANALYZE.

---

Quick start

Prerequisites: Docker

git clone <repo>
cd sql-butler

# Build the image (includes React UI)
docker build -t sql-butler .

# Run with Ollama (no API key needed)
docker run -d \
  -p 8080:8080 \
  -v sql-butler-data:/data \
  -e LLM_MODEL=ollama:qwen3:4b-instruct \
  sql-butler

# Or with a cloud provider
docker run -d \
  -p 8080:8080 \
  -v sql-butler-data:/data \
  -e LLM_MODEL=anthropic:claude-sonnet-4-5 \
  -e LLM_API_KEY=sk-ant-... \
  sql-butler

URL	What
http://localhost:8080/chat	Chat UI
http://localhost:8080/admin	SQL Admin panel
http://localhost:8080/docs	Interactive API docs
http://localhost:8080/api/v1	REST API

---

Features

• Ingest anything — JSON, Markdown, plain text, CSV. The agent analyzes the content, creates tables with appropriate types, adds indexes, and builds full-text search automatically.
• Query in plain English — returns curated, context-relevant answers with the SQL used and a confidence score.
• Chat interface — web UI for conversational access: ask questions, request schema changes, inspect data.
• Self-optimizing — background scheduler analyzes query patterns, creates missing indexes, rebuilds FTS tables, and runs ANALYZE. Configurable interval.
• Transparent — every SQL query is logged to butler_audit_log. No black boxes.
• Namespace isolation — logical data domains (docs, notes, users, …) map to table prefixes.

---

Supported LLM providers

Any PydanticAI-supported provider via the provider:model-name format:

Provider	Example model string
Ollama (local, no key)	ollama:qwen3:4b-instruct
Anthropic	anthropic:claude-sonnet-4-5
OpenAI	openai:gpt-4o
Google	google-gla:gemini-2.0-flash
Groq	groq:llama-3.3-70b-versatile
Mistral	mistral:mistral-large-latest

---

API

Full interactive docs at /docs. Key endpoints:

Method	Path	Description
POST	/api/v1/ingest	Ingest data (agent structures and stores it)
POST	/api/v1/query	Natural language query → curated answer
POST	/api/v1/chat	Conversational (single response)
POST	/api/v1/chat/stream	Conversational (SSE streaming)
POST	/api/v1/optimize	Trigger optimization agent manually
GET	/api/v1/optimize/report	Slow query analysis (no agent)
GET	/api/v1/namespaces	List namespaces
GET	/api/v1/stats	Table sizes + scheduler status

Ingest example:

curl -X POST http://localhost:8080/api/v1/ingest \
  -H "Content-Type: application/json" \
  -d '{"data": {"title": "Q4 notes", "content": "..."}, "namespace": "notes"}'

Query example:

curl -X POST http://localhost:8080/api/v1/query \
  -H "Content-Type: application/json" \
  -d '{"query": "What was discussed about Q4?", "namespace": "notes"}'

---

Configuration

Environment variables

Variable	Default	Description
LLM_MODEL	openai:gpt-4o	Model string (provider:model-name)
LLM_API_KEY	—	Provider API key (not needed for Ollama)
DATABASE_ENGINE	sqlite	sqlite or postgresql
DATABASE_PATH	/data/butler.db	SQLite file path
DATABASE_URL	—	PostgreSQL connection string
SERVER_PORT	8080	Listen port
SQL_BUTLER_API_KEY	—	Require X-Api-Key header (empty = open)
SQL_BUTLER_CONFIG	sql-butler.yaml	Path to YAML config file

YAML config file

database:
  engine: sqlite            # sqlite | postgresql
  path: /data/butler.db
  url: ""                   # PostgreSQL: postgresql://user:pass@host/db

agent:
  model: ollama:qwen3:4b-instruct
  max_iterations: 5
  temperature: 0.0
  auto_optimize_interval_minutes: 60  # 0 = disable background optimizer

server:
  host: 0.0.0.0
  port: 8080

security:
  api_key: ""               # empty = no auth
  admin_enabled: true
  sandbox:
    query_timeout_ms: 30000
    max_result_rows: 10000
    max_payload_mb: 50

logging:
  level: info
  log_queries: true

Mount as a volume: docker run -v ./sql-butler.yaml:/app/sql-butler.yaml ...

---

Deployment

Docker Compose — SQLite (simplest)

docker compose up -d

Docker Compose — PostgreSQL

LLM_API_KEY=sk-... docker compose -f docker-compose.postgres.yml up -d

Local development

# Backend (Python 3.12 + uv required)
uv run python -m sql_butler.main

# Frontend dev server (separate terminal)
cd frontend && pnpm dev    # proxies /api → :8080

---

Security

• Agent SQL runs through a sandboxed executor. Blocked: ATTACH DATABASE, LOAD_EXTENSION (SQLite); COPY … FROM/TO, pg_read_file (PostgreSQL).
• Optional API key auth: set security.api_key or SQL_BUTLER_API_KEY. Pass as X-Api-Key header or ?api_key= query param.
• Rate limiting: 200 req/min per IP by default.
• SQL Admin panel can be disabled: security.admin_enabled: false.
• The agent has no shell access — it only operates through defined tool functions.

---

License

MIT