Metadata-Version: 2.4
Name: dbagent-cli
Version: 0.1.0
Summary: Universal Database Introspection and Script Generation AI Agent (CLI)
Author: DB-Agent Team
Requires-Python: >=3.9
Description-Content-Type: text/markdown
Requires-Dist: typer>=0.9.0
Requires-Dist: rich>=13.0.0
Requires-Dist: prompt-toolkit>=3.0.0
Requires-Dist: pydantic>=2.0.0
Requires-Dist: sqlalchemy>=2.0.0
Requires-Dist: requests>=2.28.0
Requires-Dist: httpx>=0.24.0
Requires-Dist: python-dotenv>=1.0.0
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9.0; extra == "all"
Requires-Dist: pymysql>=1.0.0; extra == "all"
Requires-Dist: pymongo>=4.0.0; extra == "all"
Requires-Dist: duckdb>=0.9.0; extra == "all"
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.20.0; extra == "dev"

# ⚡ DB-Agent

> **Universal Database Introspector & AI Script Generator CLI**  
> *100% Free, Standalone, and runs with Local (Ollama) or Free Cloud AI models.*

`db-agent` connects to any database using provided credentials or connection URLs, introspects the complete schema (tables, columns, data types, primary/foreign keys, indexes, and sample values), and generates dialect-specific SQL queries, migrations, ETL data pipelines, and backend APIs from natural language instructions.

---

## 🌟 Key Features

- **Universal Database Support**:
  - **Relational**: PostgreSQL, MySQL, MariaDB, SQLite, Microsoft SQL Server (MSSQL), Oracle, DuckDB, Snowflake.
  - **Document DBs**: MongoDB (schema inference via document sampling).
- **100% Free & Standalone AI Engine**:
  - **Ollama (Local & Offline)**: Zero cost, private, runs on your machine (`qwen2.5-coder`, `llama3.2`, `deepseek-r1`, `mistral`).
  - **Google Gemini (Free Tier)**: Direct access to `gemini-2.0-flash` & `gemini-1.5-flash`.
  - **Groq (Free Ultra-Fast Tier)**: `llama-3.3-70b-versatile`, `qwen-2.5-32b`.
  - **OpenRouter (Free Community Models)**: Free open models.
- **Deep Schema Introspection**:
  - Auto-discovers tables, views, primary keys, foreign key relations, unique/composite indexes, nullability, defaults, comments, and row counts.
  - Generates schema catalogs in Rich terminal tables, Markdown, or JSON.
- **Smart Script & Code Generation**:
  - **Dialect-Specific SQL**: Joins, subqueries, CTEs, window functions.
  - **Database Migrations**: Alembic (Python), Flyway, Prisma, raw SQL DDL.
  - **ETL & Data Pipelines**: Python (Pandas/Polars/SQLAlchemy), CSV/JSON exporters.
  - **Backend REST APIs**: FastAPI CRUD endpoints, Pydantic schemas.
- **Interactive Multi-Turn REPL & Safe Execution**:
  - Multi-turn terminal chat with live database schema loaded in memory.
  - Safe query dry-run and results preview with detection of destructive statements.

---

## 🚀 Quick Start

### 1. Installation

```bash
# Clone the repository
cd fearless-fermi

# Install dependencies and editable CLI package
pip install -e .
```

### 2. Supported AI Providers (Zero Cost)

You can run DB-Agent using any free option:

| Provider | Type | Setup | Recommended Model |
|---|---|---|---|
| **Ollama** | 100% Local & Offline | Run `ollama serve` | `qwen2.5-coder` or `llama3.2` |
| **Google Gemini** | Free Cloud API | Get free key at [Google AI Studio](https://aistudio.google.com) | `gemini-2.0-flash` |
| **Groq** | Free Fast Cloud API | Get free key at [Groq Console](https://console.groq.com) | `llama-3.3-70b-versatile` |
| **OpenRouter** | Free Open Models | Get free key at [OpenRouter](https://openrouter.ai) | `meta-llama/llama-3.3-70b-instruct:free` |

Set keys via environment variables or use the interactive setup wizard:
```bash
# Interactive setup wizard
db-agent config

# Or set directly in your shell / .env file
export GEMINI_API_KEY="your-gemini-api-key"
# or
export GROQ_API_KEY="your-groq-api-key"
```

---

## 💻 CLI Commands & Usage

### 1. Connect & Save with an Alias (`connect` or `--alias`)

Save your connection credentials under an alias so you don't need to retype the URL each time:

```bash
# Connect and save with an alias name
db-agent connect postgresql://user:password@localhost:5432/mydb --alias prod_db

# Connect to a local SQLite file with an alias
db-agent connect ecommerce_demo.db --alias my_shop

# Or scan and save alias in one step
db-agent scan --db postgresql://user:pass@localhost:5432/analytics --alias analytics_db
```

After saving with an alias, you can use that alias directly anywhere in place of the connection URL:
```bash
db-agent scan --db prod_db
db-agent chat --db prod_db
db-agent generate "Show total revenue this month" --db prod_db
```

### 2. Introspect and Scan Database (`scan`)

```bash
# Scan a local SQLite database
db-agent scan --db ecommerce_demo.db

# Scan a PostgreSQL database
db-agent scan --db postgresql://user:password@localhost:5432/mydb

# Drill down into a specific table's columns and indexes
db-agent scan --db ecommerce_demo.db --table products

# Export schema catalog to JSON or Markdown
db-agent scan --db ecommerce_demo.db --export schema_catalog.md
```

### 2. Generate Scripts & Code (`generate`)

```bash
# Generate an optimized SQL analytical query
db-agent generate "Calculate customer lifetime value and total orders per country" --db ecommerce_demo.db

# Generate a Python ETL script to export sales data to CSV
db-agent generate "Write a Pandas ETL script to export all completed orders to sales.csv" --db ecommerce_demo.db --type etl --output etl_export.py

# Generate a FastAPI CRUD backend for products and categories
db-agent generate "Generate FastAPI CRUD routes for products" --db ecommerce_demo.db --type api --output api_routes.py

# Generate an Alembic migration
db-agent generate "Add loyalty_points column to users table" --db ecommerce_demo.db --type migration

# Generate and execute SQL query directly (with safety checks)
db-agent generate "Find top 5 highest priced active products" --db ecommerce_demo.db --run
```

### 3. Interactive Terminal Chat REPL (`chat`)

Start an interactive session with the database schema held in context:

```bash
db-agent chat --db ecommerce_demo.db
```

Inside the interactive chat:
- Ask questions: `> Show me total sales grouped by category`
- Execute last query: `> :run`
- Export last code: `> :export report_query.sql`
- View database tables: `> :tables`
- View single table detail: `> :table products`
- Exit session: `> :exit`

### 4. Execute Queries Safely (`run`)

```bash
db-agent run "SELECT username, country FROM users WHERE role = 'admin'" --db ecommerce_demo.db
```

### 5. Manage Database Connection Profiles (`profiles`)

Save your database connection strings so you don't need to retype them:

```bash
db-agent config
# Prompts you to add a profile (e.g. 'analytics_prod', 'local_dev')

# List saved profiles
db-agent profiles

# Use saved profile in any command
db-agent scan --db analytics_prod
db-agent chat --db analytics_prod
```

### 6. Check Available AI Models (`models`)

```bash
db-agent models
```

---

## 🧪 Running Tests

```bash
pytest -v
```

---

## 📂 Project Structure

```
.
├── pyproject.toml              # Build config & dependencies
├── README.md                   # Documentation
├── scripts/
│   └── seed_demo_db.py         # Realistic demo database generator
├── dbagent/
│   ├── cli.py                  # Typer CLI application & REPL
│   ├── config.py               # Profile & credential manager
│   ├── connectors/             # Universal database connectors
│   │   ├── base.py             # Abstract DB connector interface
│   │   ├── relational.py       # SQLAlchemy universal connector (PG, MySQL, SQLite, MSSQL, Oracle, DuckDB)
│   │   ├── mongo.py            # MongoDB introspector & sampling
│   │   └── factory.py          # Connector factory
│   ├── schema/                 # Schema catalog & context builder
│   │   ├── models.py           # Pydantic schema data models
│   │   ├── formatter.py        # Markdown/compact schema formatters
│   │   └── selector.py         # Smart table selector for large schemas
│   ├── llm/                    # 100% Free & Standalone LLM providers
│   │   ├── base.py             # Base LLM provider
│   │   ├── ollama_provider.py  # Local offline Ollama client
│   │   ├── gemini_provider.py  # Google Gemini Free API client
│   │   ├── groq_provider.py    # Groq Free API client
│   │   ├── openrouter_provider.py # OpenRouter Free models
│   │   ├── mock_provider.py    # Offline fallback provider
│   │   └── factory.py          # LLM provider factory
│   ├── agent/                  # AI Script Generation & Validation
│   │   ├── generator.py        # Prompt templates & generator engine
│   │   └── validator.py        # SQL syntax & safety validator
│   └── ui/                     # Rich terminal UI
│       ├── console.py          # Console banners, badges & code syntax
│       └── viewer.py           # Rich schema visualizer tables
└── tests/                      # Automated test suite (100% passing)
```
