Metadata-Version: 2.4
Name: sqlcopilot
Version: 0.1.0
Summary: AI-powered SQL assistant for Postgres and Snowflake
Author: sqlcopilot contributors
License-Expression: MIT
Keywords: sql,ai,openai,postgres,snowflake,cli
Classifier: Programming Language :: Python :: 3
Classifier: Environment :: Console
Classifier: Topic :: Database
Requires-Python: >=3.10
Description-Content-Type: text/markdown
Requires-Dist: typer>=0.12
Requires-Dist: rich>=13
Requires-Dist: openai>=1.30
Requires-Dist: pydantic>=2
Requires-Dist: pyyaml>=6
Provides-Extra: dev
Requires-Dist: pytest>=8; extra == "dev"
Requires-Dist: pytest-mock>=3; extra == "dev"
Requires-Dist: ruff>=0.4; extra == "dev"

# 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)

```bash
pip install sqlcopilot
```

### From source (development)

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

---

## Configuration

### Option 1 — Environment variable (quickest)

```bash
export OPENAI_API_KEY="sk-..."
```

### Option 2 — Config file

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

This writes `~/.sqlcopilot/config.yaml`:

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

---

## Usage

### Convert natural language to SQL

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

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

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

Targeting Snowflake:

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

### Explain a query

```bash
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:

```bash
sqlcopilot explain "$(cat my_query.sql)"
```

### Optimize a query

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

### Override model per call

```bash
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:

```yaml
# 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

```bash
docker build -t sqlcopilot .
```

### Run

```bash
# 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

```bash
# 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
