AI Database Helper (aidb)

AI-optimized database schema utility for intelligent database interaction

AI Database Helper (aidb) is a command-line utility that creates AI-optimized representations of database schemas, enabling AI assistants to interact with databases intelligently and efficiently.

Features

• Multi-Database Support: MySQL, PostgreSQL, MSSQL, SQLite, MongoDB, DB2, Oracle, Azure SQL
• Multiple Simultaneous Databases: Connect to multiple databases at once - AI has visibility across your entire data ecosystem
• Relationship Intelligence: Automatic discovery of explicit and inferred relationships with multiplicity detection (1:1, 1:N, N:N)
• Query Execution: Execute SQL queries directly via CLI with safety confirmations
• Index Advisory: Comprehensive index recommendations with impact analysis and redundancy detection
• Schema Caching: Lightning-fast schema retrieval (<100ms) from local cache
• Connection String Support: Easy connection using standard database connection strings
• AI-Optimized Formats: Export schemas in JSON, Markdown, DDL, Mermaid ER diagrams, and TypeScript interfaces
• Enhanced Help System: Comprehensive examples and AI assistant guidance built into CLI
• CLI-First Design: Built for automation and integration with AI workflows

Quick Start

Installation

npm install -g @rvegajr/aidb

Connect to a Database

# Connect using connection string (easiest)
aidb connect mydb --connection-string "mysql://user:pass@localhost:3306/myapp"
aidb connect analytics --connection-string "postgresql://user:pass@host:5432/analytics"
aidb connect logs --connection-string "mssql://sa:Password@host:1433/logs"

# Or connect with individual parameters
aidb connect mydb --type postgres --host localhost --database myapp

# Connect to Azure SQL with managed identity
aidb connect azure-prod --type azure-sql --auth managed-identity

Multi-Database Usage

AI Database Helper supports multiple databases simultaneously with complete visibility:

# Connect to all your databases
aidb connect users-db --connection-string "mysql://..."
aidb connect orders-db --connection-string "postgresql://..."
aidb connect analytics --connection-string "mssql://..."

# List all connected databases
aidb list

# View schemas across all databases
aidb schema users-db
aidb schema orders-db
aidb schema analytics

# AI now has complete visibility across your entire data ecosystem!

View Schema

# View schema as compact JSON (AI-optimized)
aidb schema mydb --format json

# View as human-readable Markdown
aidb schema mydb --format markdown

# View as Mermaid ER diagram
aidb schema mydb --format mermaid > schema.mmd

# View specific table
aidb schema mydb --table users

Discover Relationships

# Show all relationships
aidb relationships mydb

# Find join path between tables
aidb join-path mydb --from users --to comments
# Output: users → posts → comments (cost: 25, indexes: posts.user_id, comments.post_id)

# Detect junction tables (many-to-many)
aidb junctions mydb
# Output: post_tags (connects posts ↔ tags, confidence: 95%)

Query Planning & Index Recommendations

# Analyze a query
aidb explain mydb "SELECT * FROM posts WHERE user_id = 123"

# Get index recommendations
aidb indexes mydb --recommend
# Output:
#   HIGH PRIORITY:
#   - CREATE INDEX idx_comments_user_id ON comments(user_id);
#     Reason: Frequent WHERE/JOIN usage (15 queries), est. 70% speedup

# Apply recommended indexes (interactive)
aidb indexes mydb --apply

Safe Schema Changes

# Create snapshot before changes
aidb snapshot create mydb --message "Before adding new column"

# Apply schema change (dry-run first)
aidb apply mydb --sql "ALTER TABLE posts ADD COLUMN view_count INT DEFAULT 0" --dry-run

# Apply for real
aidb apply mydb --sql "ALTER TABLE posts ADD COLUMN view_count INT DEFAULT 0"

# Rollback if needed
aidb rollback snapshot_20251003_103000 --execute

Architecture

AI Database Helper follows a strict Interface Segregation Principle (ISP) with clear separation of concerns:

ai-db-helper/
├── packages/
│   ├── contracts/      # Interface definitions (ZERO dependencies)
│   ├── core/          # Business logic implementations
│   ├── adapters/      # Database-specific implementations
│   ├── cli/           # Command-line interface
│   └── test-harnesses/ # Integration tests with real databases
└── .aidb/             # Runtime cache directory (gitignored)
    ├── schemas/       # Cached schema JSON files
    ├── credentials.enc # Encrypted credentials
    ├── rollbacks/     # Rollback snapshots
    └── config.json    # Configuration

Key Design Principles

1. Interface-First Development: All business logic implements well-defined contracts
2. Real Implementations in Tests: NO MOCKS - all tests use Docker-based real databases
3. AI-Optimized Output: Token-efficient schema representations for AI context windows
4. Relationship Intelligence: First-class support for FK discovery and inference
5. Query Planning Integration: Built-in EXPLAIN analysis and index recommendations
6. Security by Default: AES-256-GCM encrypted credentials, proper file permissions

Configuration

Configuration is stored in .aidb/config.json:

{
  "version": "1.0.0",
  "databases": {
    "mydb": {
      "type": "postgres",
      "lastRefresh": "2025-10-03T10:30:00Z",
      "schemaHash": "abc123...",
      "schemaSubset": {
        "enabled": true,
        "includeSchemas": ["public", "analytics"],
        "excludeTables": ["audit_log", "sessions"]
      },
      "relationships": {
        "includeInferred": true,
        "inferenceConfidenceThreshold": 0.7
      }
    }
  }
}

Schema Subset Selection

For large databases (10,000+ tables), use schema subset filtering:

# Include only specific schemas
aidb connect largedb --type postgres --schemas public,analytics

# Include only specific tables
aidb connect largedb --tables users,posts,comments

# Exclude tables by pattern
aidb schema largedb --exclude-tables *_audit,sessions

Command Reference

Connection Management

• aidb connect <db-name> --type <type> - Connect to database
• aidb disconnect <db-name> - Disconnect from database
• aidb list - List all configured databases

Schema Operations

• aidb schema <db-name> [--format json|markdown|ddl|mermaid|typescript] - View schema
• aidb refresh <db-name> - Refresh schema from database
• aidb diff <db-name> - Show schema changes since last cache

Relationships

• aidb relationships <db-name> - Show all relationships
• aidb join-path <db-name> --from <table> --to <table> - Find join path
• aidb junctions <db-name> - Detect junction tables

Query Planning

• aidb explain <db-name> "<sql>" - Analyze query execution plan
• aidb indexes <db-name> --recommend - Get index recommendations
• aidb indexes <db-name> --apply - Apply index suggestions interactively

Rollback & Snapshots

• aidb snapshot create <db-name> [--message <msg>] - Create snapshot
• aidb rollback <snapshot-id> [--dry-run] - Rollback to snapshot
• aidb apply <db-name> --sql "<ddl>" [--dry-run] - Apply schema change

Credentials

• aidb credentials list - List stored credentials
• aidb credentials delete <db-name> - Delete credentials
• aidb credentials rotate <db-name> - Rotate credentials

Utilities

• aidb export <db-name> --output <file> - Export schema to file
• aidb import <db-name> --input <file> - Import schema from file
• aidb doctor - Validate .aidb directory integrity
• aidb ai-info - Show complete guide for AI assistants
• aidb exec <db-name> "<sql>" - Execute SQL query directly

Supported Databases

Database	Version	Schema Extraction	Relationships	Query Planning
MySQL	5.7+	✓	✓	✓
PostgreSQL	12+	✓	✓	✓
MSSQL	2017+	✓	✓	✓
SQLite	3.x	✓	✓	Partial
MongoDB	4.0+	✓ (inferred)	✓ (embedded)	N/A
IBM DB2	11.5+	✓	✓	✓
Oracle	12c+	✓	✓	✓
Azure SQL	Latest	✓	✓	✓

Development

Prerequisites

• Node.js 20+
• Docker (for integration tests)
• TypeScript 5.3+

Setup

# Clone repository
git clone https://github.com/yourusername/ai-db-helper.git
cd ai-db-helper

# Install dependencies
npm install

# Build all packages
npm run build

# Run tests
npm test

# Run integration tests (requires Docker)
npm run test:integration

Project Structure

See architecture-checklist.md for complete implementation roadmap.

Contributing

Contributions are welcome! Please read our Contributing Guidelines and Code of Conduct.

Development Workflow

1. Create feature branch from main
2. Write tests first (TDD approach)
3. Implement feature following interface contracts
4. Ensure all tests pass with real database instances (NO MOCKS)
5. Run linter and formatter
6. Submit pull request

License

MIT License - see LICENSE file for details.

Roadmap

v1.0.1 (Current)

• ✓ Multi-database support (8+ databases)
• ✓ Relationship intelligence with multiplicity detection
• ✓ Index advisory with comprehensive analysis
• ✓ Connection string support for easy setup
• ✓ Direct SQL query execution via CLI
• ✓ Enhanced help system with AI guidance
• ✓ Multiple simultaneous database connections
• ✓ AI-optimized output formats

v1.1 (Planned)

• Interactive schema browser (TUI)
• Query history tracking
• Schema comparison between databases
• Performance monitoring integration

v1.2 (Future)

• Schema migration generator
• Natural language query builder
• Real-time schema change detection
• Cloud sync (.aidb to S3/Azure Blob)

v2.0 (Vision)

• Web UI dashboard
• API server mode
• GraphQL schema export
• Team collaboration features

Support

• Documentation: docs/
• Issues: GitHub Issues
• Discussions: GitHub Discussions

Acknowledgments

Built with:

• Commander.js - CLI framework
• Inquirer.js - Interactive prompts
• mysql2 - MySQL driver
• pg - PostgreSQL driver
• graphlib - Graph algorithms

---

Built for AI, by humans 🤖