sqlcopilot 0.1.0

AI-powered SQL assistant for Postgres and Snowflake

sqlcopilot

AI-powered SQL assistant for Postgres and Snowflake. Convert natural language to SQL, explain queries, and optimize them — all from your terminal.

---

Features

Command	Description
convert	Natural language → SQL
explain	Plain-English explanation of any SQL query
optimize	Performance-optimized rewrite with change summary
configure	Persist defaults (dialect, schema, API key)

---

Project Structure

sqlcopilot/
├── sqlcopilot/
│   ├── __init__.py        # version
│   ├── cli.py             # Typer CLI commands
│   ├── ai.py              # OpenAI prompt logic
│   ├── config.py          # Config load/save (~/.sqlcopilot/config.yaml)
│   └── schema_loader.py   # Parse schema YAML → SQL DDL for context
├── tests/
│   ├── test_ai.py
│   ├── test_cli.py
│   └── test_schema_loader.py
├── example_schema.yaml    # Sample schema to pass as --schema
├── Dockerfile
├── pyproject.toml
├── setup.py
└── README.md

---

Installation

From PyPI (once published)

pip install sqlcopilot

From source (development)

git clone https://github.com/your-org/sqlcopilot.git
cd sqlcopilot
pip install -e ".[dev]"

---

Configuration

Option 1 — Environment variable (quickest)

export OPENAI_API_KEY="sk-..."

Option 2 — Config file

sqlcopilot configure --api-key sk-... --dialect postgres

This writes ~/.sqlcopilot/config.yaml:

openai:
  api_key: sk-...
  model: gpt-4o
database:
  dialect: postgres
  schema_file: null

---

Usage

Convert natural language to SQL

sqlcopilot convert "show me the top 10 customers by total order value"

With a schema file (gives the AI your table definitions):

sqlcopilot convert "orders placed this month" --schema example_schema.yaml

Targeting Snowflake:

sqlcopilot convert "daily revenue for last 30 days" --dialect snowflake

Explain a query

sqlcopilot explain "SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name"

Pipe from a file:

sqlcopilot explain "$(cat my_query.sql)"

Optimize a query

sqlcopilot optimize "SELECT * FROM orders WHERE status = 'shipped'"

Override model per call

sqlcopilot convert "..." --model gpt-4-turbo

---

Schema File Format

Define your tables in YAML; sqlcopilot converts them to CREATE TABLE DDL before sending to the AI:

# example_schema.yaml
tables:
  users:
    columns:
      id: BIGSERIAL PRIMARY KEY
      email: VARCHAR(255) NOT NULL UNIQUE
      created_at: TIMESTAMPTZ NOT NULL DEFAULT NOW()

  orders:
    columns:
      id: BIGSERIAL PRIMARY KEY
      user_id: BIGINT NOT NULL REFERENCES users(id)
      total_amount: NUMERIC(12, 2) NOT NULL
      status: VARCHAR(50) NOT NULL

---

Docker

Build

docker build -t sqlcopilot .

Run

# Convert
docker run --rm \
  -e OPENAI_API_KEY="$OPENAI_API_KEY" \
  sqlcopilot convert "list all active users"

# With a local schema file mounted
docker run --rm \
  -e OPENAI_API_KEY="$OPENAI_API_KEY" \
  -v "$(pwd)/example_schema.yaml:/schemas/schema.yaml" \
  sqlcopilot convert "top products by revenue" --schema /schemas/schema.yaml --dialect snowflake

# Persist config across runs
docker run --rm \
  -v "$HOME/.sqlcopilot:/root/.sqlcopilot" \
  sqlcopilot configure --api-key sk-... --dialect postgres

---

Development

# Install with dev dependencies
pip install -e ".[dev]"

# Run tests
pytest

# Lint
ruff check .

---

Supported Dialects

Dialect	Notes
postgres	CTEs preferred, ILIKE for case-insensitive search
snowflake	QUALIFY for window filtering, FLATTEN for semi-structured data

---

License

MIT