⚡ DB-Agent (dbagent-cli)

Enterprise AI Database Copilot, Schema Graph Introspector & Autonomous Recovery Agent
Author: Santhosh Gupta | 100% Free, Standalone, Works 100% Offline (Ollama) or with High-Speed Cloud AI (Gemini / Groq / OpenRouter).

🌟 What is DB-Agent?

DB-Agent (dbagent-cli) is an enterprise-grade, privacy-first AI Database Copilot. Designed for developers, DBAs, data engineers, and analysts, it transforms plain English prompts into highly optimized, dialect-precise SQL, executes queries under strict safety guardrails, and renders crystal-clear data visualizations.

🚀 Key Highlights:

• Instant On-Demand Introspection: Targeted single-query metadata resolution (< 30ms) across databases with 300+ tables.
• Interactive Data Grid Suite: Native desktop GUI window (:window), browser virtual grid (:web), column paging (:page), and responsive zoom-out (:wide).
• Long-Term Post-Commit Rollback: Pluggable recovery engine that snapshots pre-write row states, detects concurrency conflicts, and allows safe revert hours or days after COMMIT.
• Multi-Hop Join Discovery: Automatically computes the shortest foreign-key join paths between disconnected tables.
• Enterprise Safety Guardrails: 5 operational modes (SAFE, READ_ONLY, CONFIRM, SANDBOX, ADMIN), PII masking, and destructive query protection.
• Zero-Cost Local or Cloud AI: Native support for Google Gemini (gemini-3.6-flash), Local Ollama (100% offline), Groq, and OpenRouter.

📦 Installation

Install dbagent-cli via pip:

# Recommended: Install with all database drivers
pip install --upgrade "dbagent-cli[all]"

Optional Driver Extras:

pip install "dbagent-cli[postgres]"   # PostgreSQL (psycopg2-binary, asyncpg)
pip install "dbagent-cli[mysql]"      # MySQL & MariaDB (pymysql, cryptography)
pip install "dbagent-cli[mongo]"      # MongoDB (pymongo)
pip install "dbagent-cli[duckdb]"     # DuckDB (duckdb, duckdb-engine)
pip install "dbagent-cli[mssql]"      # Microsoft SQL Server (pyodbc)
pip install "dbagent-cli[oracle]"     # Oracle Database (oracledb)

⚡ 2-Minute Quickstart

1. Configure AI Provider

db-agent setup

Follow the interactive prompt to choose your preferred AI backend:

• Google Gemini (Recommended Cloud): Ultra-fast inference with free tier (gemini-3.6-flash).
• Local Ollama (Recommended Offline): 100% private, runs entirely on your GPU/CPU with 0 API keys.
• Groq / OpenRouter: Fast open-weights inference (Llama 3.3, Qwen 2.5 Coder).

2. Connect Database & Save Profile Alias

db-agent connect postgresql://user:pass@localhost:5432/mydb --alias my_db

(Supports PostgreSQL, MySQL, SQLite, MSSQL, Oracle, DuckDB, and MongoDB).

3. Ask Plain English Questions or Start Interactive Chat

# Ask a one-off question:
db-agent ask "list top 10 most recent active users" --db my_db

# Launch the interactive AI shell:
db-agent chat --db my_db

🪟 Interactive Viewing & Multi-Column Scroller Suite

When working with wide enterprise tables containing 20, 40, or 60+ columns, DB-Agent provides 5 flexible viewing modes:

🛡️ Enterprise Long-Term Rollback & Recovery Engine

Unlike standard transaction rollbacks that only protect uncommitted sessions, DB-Agent includes a Post-Commit Long-Term Recovery Engine:

User: Update all inactive users to ACTIVE where last_login < '2025-01-01'
AI:   Modifying 42 rows. Capturing pre-write snapshot...
      Change committed successfully.
      Change ID: CS-20260828-001 (Recovery available for 30 days)

... 5 days later ...

User: db-agent rollback CS-20260828-001 --db my_db
AI:   Verifying live data checksums... No concurrency conflicts detected.
      Inverse UPDATE executed successfully. 42 rows restored!

Recovery CLI Commands:

# List all recorded write ChangeSets:
db-agent changes

# Inspect before/after row snapshots of a change:
db-agent changes-show CS-20260828-001

# Preview rollback SQL without executing:
db-agent rollback CS-20260828-001 --preview

# Execute rollback with conflict safety:
db-agent rollback CS-20260828-001 --conflict-policy abort

# Roll back the most recent change:
db-agent rollback --last

# Manage recovery storage retention:
db-agent recovery status
db-agent recovery cleanup --older-than-days 30

🔒 Production Safety Guardrails & Operational Modes

DB-Agent provides 5 strictly enforced operational safety modes:

• SAFE: Only read-only queries (SELECT, SHOW, DESCRIBE, EXPLAIN, WITH) allowed. All writes and DDL are blocked.
• READ_ONLY: Enforced on PRODUCTION environments with query execution timeouts and row limits.
• CONFIRM (Default): Read queries execute automatically; write (UPDATE, INSERT, DELETE) and DDL (ALTER, DROP) operations require user confirmation.
• SANDBOX: Executes writes within an uncommitted transaction to generate before/after diffs without modifying live data.
• ADMIN: Full administrative execution with audit tracking.

Sensitive Data Masking:

Confidential fields (emails, passwords, API tokens, credit cards) are automatically masked in console outputs: a***@motivitylabs.com, **********, ****-****-****-1234.

🧠 Schema Graph & Multi-Hop JOIN Discovery

DB-Agent builds an in-memory directed graph of foreign-key relationships across your database schema:

# Discover shortest foreign-key path between disconnected tables:
db-agent path ivis_user ivis_camera_mapping --db my_db

# Explain join cardinality (1:1, 1:N) and recommended syntax:
db-agent explain-join customers orders --db my_db

🏥 Database Doctor & Query Diagnostics

Run comprehensive database health checks and EXPLAIN plan optimizations:

# Full connectivity, latency, index, and table health diagnostics:
db-agent doctor --db my_db

# Analyze query execution plan and recommend missing indexes:
db-agent optimize "SELECT * FROM ivis_user WHERE email = 'test@example.com'" --db my_db

📚 Business Semantic Dictionary

Teach DB-Agent your domain-specific metrics and shorthand:

# Define a business metric:
db-agent define active_user "A user who logged in within the last 30 days" --sql "last_login >= NOW() - INTERVAL '30 days'"

# List all saved business terminology:
db-agent definitions

# Manage parameterized query templates:
db-agent template list

📖 Complete CLI Command Reference

💬 In-Chat Colon Commands (db-agent chat)

Inside the interactive chat REPL, use these colon commands:

  :window                     -> Open native desktop GUI data grid with horizontal & vertical scrollbars
  :web                        -> Open interactive browser data table with virtual scrolling
  :page <n>                   -> View column page <n> for wide tables (e.g. :page 1, :page 2)
  :card                       -> View last result in expanded vertical card format (all columns)
  :wide                       -> Render all columns across expanded / zoomed-out terminal
  :scroll                     -> Interactive terminal column scroller
  :tables                     -> List all tables in this database
  :table <name>               -> Inspect columns, PKs, types & sample data
  :fk <name>                  -> View mapped foreign keys & related tables
  :path <t1> <t2>             -> Discover shortest join path between tables
  :join <t1> <t2>             -> Explain join cardinality & syntax
  :doctor                     -> Run database health diagnostics
  :mode <name>                -> Switch mode (SAFE, READ_ONLY, CONFIRM, SANDBOX, ADMIN)
  :changes                    -> List recent write ChangeSets
  :provider <name>            -> Switch AI provider (gemini, ollama, groq, openrouter)
  :model <name>               -> Switch active model name (e.g. gemini-3.6-flash)
  :models                     -> Show active AI provider and model status
  :explain                    -> Show generated SQL & query analysis for last turn
  :history                    -> View history of queries in this session
  :auto / :noauto             -> Toggle automatic execution of read queries
  :run                        -> Re-execute the last generated SQL query
  :clear                      -> Clear conversation context
  :export <file>              -> Save the last generated SQL to a file
  :exit                       -> Exit chat session

📄 License

MIT License. Built with ❤️ by Santhosh Gupta.