AI SQL Knex v2.0.0 🚀

A powerful AI-powered CLI tool that converts natural language to SQL queries using OpenAI, with advanced features for database exploration, query optimization, and interactive AI chat.

✨ Features

• 🤖 Natural Language to SQL: Convert plain English to SQL queries
• 🔍 Schema Exploration: Browse database tables and columns
• 📖 Query Explanation: Understand what your SQL queries do
• ⚡ Query Optimization: Get performance suggestions and best practices
• 💬 AI Chat: Interactive chat with AI for database assistance
• 📚 Query History: Track and learn from previous queries
• 🔧 Multi-Database Support: PostgreSQL, MySQL, and more
• 🛡️ Safety Features: Confirmation prompts for destructive operations

🚀 Quick Start

Installation

npm install -g ai-sql-knex

Configuration

# Set up your OpenAI API key and database URL
aisql config --openai-key "your-api-key" --database-url "postgresql://user:pass@localhost:5432/db"

Basic Usage

# Generate and execute a SQL query
aisql query "find 5 users"

# Just generate SQL without executing
aisql query "show me all products" --no-run

# Get explanation of the generated query
aisql query "find users by email" --explain

# Get optimization suggestions
aisql query "complex join query" --optimize

📋 Commands

Query Command

Convert natural language to SQL and optionally execute it.

aisql query <natural_language_query> [options]

Options:
  --model <model>    OpenAI model (default: "gpt-4")
  --no-run          Only generate SQL, do not execute
  --explain         Explain the generated SQL query
  --optimize        Suggest optimizations for the query

Examples:

# Basic query
aisql query "find all users created this month"

# With explanation
aisql query "get top 10 products by sales" --explain

# With optimization
aisql query "complex report with joins" --optimize

# Different AI model
aisql query "simple select" --model gpt-3.5-turbo

Schema Command

Explore your database schema and table information.

aisql schema [options]

Options:
  --table <table>   Show details for specific table
  --tables          List all tables

Examples:

# List all tables
aisql schema --tables

# Show specific table details
aisql schema --table users

# Show all tables (default)
aisql schema

Chat Command

Start an interactive chat with AI for database assistance.

aisql chat [options]

Options:
  --model <model>   OpenAI model (default: "gpt-4")

Examples:

# Start AI chat
aisql chat

# Use different model
aisql chat --model gpt-3.5-turbo

History Command

View your query history and learn from previous queries.

aisql history [options]

Options:
  --limit <number>  Number of entries to show (default: 10)
  --executed        Show only executed queries

Examples:

# View recent history
aisql history

# Show only executed queries
aisql history --executed

# Show more entries
aisql history --limit 20

Config Command

Manage your configuration settings.

aisql config [options]

Options:
  --openai-key <key>    OpenAI API key
  --database-url <url>  Database connection URL

Examples:

# Set configuration interactively
aisql config

# Set specific values
aisql config --openai-key "sk-..." --database-url "postgresql://..."

🗄️ Database Support

• PostgreSQL - Full support with advanced features
• MySQL - Complete compatibility
• MariaDB - MySQL-compatible
• SQLite - Basic support (coming soon)

Connection Examples

# PostgreSQL
aisql config --database-url "postgresql://user:password@localhost:5432/database"

# MySQL
aisql config --database-url "mysql://user:password@localhost:3306/database"

# With SSL
aisql config --database-url "postgresql://user:password@localhost:5432/database?sslmode=require"

🎯 Advanced Features

Query Explanation

Get detailed explanations of your SQL queries to understand what they do.

aisql query "SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id" --explain

Query Optimization

Receive performance suggestions and best practices for your queries.

aisql query "SELECT * FROM users WHERE created_at > '2024-01-01'" --optimize

Schema Exploration

Explore your database structure to understand available tables and columns.

# List all tables
aisql schema --tables

# Get detailed table information
aisql schema --table products

AI Chat

Have interactive conversations with AI about your database and queries.

aisql chat
# You: How can I optimize this slow query?
# AI: I can help you analyze and optimize your query. Please share the query...

🔧 Configuration

The tool stores configuration in ~/.ai-sql-knex.json:

{
  "OPENAI_API_KEY": "sk-your-api-key",
  "DATABASE_URL": "postgresql://user:pass@localhost:5432/db"
}

🛡️ Safety Features

• Confirmation Prompts: Non-SELECT queries require confirmation
• Query History: Track all generated and executed queries
• Error Handling: Graceful error handling with helpful messages
• Token Management: Automatic optimization to prevent API limits

📊 Examples

E-commerce Queries

# Find top customers
aisql query "show me the top 10 customers by total order value"

# Product analysis
aisql query "find products with low stock and high sales"

# Revenue report
aisql query "monthly revenue report for this year"

User Management

# User analytics
aisql query "find users who registered in the last 30 days"

# Activity analysis
aisql query "show me users who haven't logged in for 90 days"

# User segmentation
aisql query "group users by registration month and count them"

Data Analysis

# Complex reporting
aisql query "create a report showing sales by category and region"

# Trend analysis
aisql query "show me the growth trend of user registrations over time"

# Data quality
aisql query "find records with missing or invalid email addresses"

🚨 Troubleshooting

Common Issues

Token Limit Exceeded

• The tool automatically optimizes schema information to prevent this
• Use --model gpt-3.5-turbo for simpler queries if needed

Database Connection Failed

• Check your database URL format
• Ensure the database server is running
• Verify credentials and permissions

OpenAI API Errors

• Verify your API key is correct
• Check your OpenAI account has sufficient credits
• Ensure you have access to the requested model

Getting Help

# Get help for any command
aisql --help
aisql query --help
aisql schema --help
aisql chat --help
aisql history --help
aisql config --help

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

📄 License

MIT License - see LICENSE file for details.

🙏 Acknowledgments

• OpenAI for the powerful AI models
• Knex.js for excellent database abstraction
• Commander.js for CLI framework
• Chalk for beautiful terminal colors

Made with ❤️ for developers who love SQL and AI