⚡ 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

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

Set keys via environment variables or use the interactive setup wizard:

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

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

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)

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

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

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)

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:

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)

db-agent models

🧪 Running Tests

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)