PostgreSQL AI Query Assistant

A modern, AI-powered PostgreSQL query interface with multi-connection support, intelligent query assistance, and a beautiful Apple-inspired design.

✨ Features

🗄️ Database Management

• Multi-Connection Support: Manage multiple PostgreSQL connections with secure credential storage
• Connection Switching: Easily switch between different database connections
• Connection Testing: Validate connections before activation
• Database Browser: Explore database schemas, tables, columns, and relationships

🤖 AI-Powered Assistance

• Multiple AI Providers: Support for OpenAI, Claude (Anthropic), and DeepSeek
• Query Completion: Intelligent SQL query auto-completion
• Error Fixing: Automatic error detection and suggested fixes
• Query Optimization: Performance optimization suggestions
• Query Explanation: Natural language explanations of complex queries

💻 Query Interface

• Monaco Editor: Advanced SQL editor with syntax highlighting
• Real-time Validation: Query syntax validation before execution
• Query History: Track and manage query execution history
• Results Export: Export query results in CSV, JSON, or SQL formats
• Execution Statistics: Performance metrics and timing information

🎨 Modern UI/UX

• Apple-Inspired Design: Clean, modern interface following Apple design principles
• Dark/Light Mode: Automatic or manual theme switching
• Responsive Layout: Works seamlessly on desktop and tablet devices
• Glass Morphism: Beautiful translucent UI elements with backdrop blur
• Smooth Animations: Framer Motion powered transitions and interactions

🚀 Quick Start

Prerequisites

• Node.js (v18 or higher)
• npm or yarn
• PostgreSQL database(s) to connect to

Installation

1. Clone the repository

   git clone <repository-url>
   cd PostgreSQL-AI-Query-Assistant

2. Install dependencies

   # Install root dependencies
   npm install
   
   # Install all dependencies (server + client)
   npm run install-all

3. Environment Setup

   Create a .env file in the server directory:

   # Server Configuration
   NODE_ENV=development
   PORT=3001
   CLIENT_URL=http://localhost:5173
   
   # Database Encryption Key (generate with: node -p "require('crypto').randomBytes(32).toString('hex')")
   DB_ENCRYPTION_KEY=your_32_byte_hex_encryption_key_here
   
   # AI Provider API Keys (optional - can be configured in UI)
   OPENAI_API_KEY=your_openai_api_key_here
   ANTHROPIC_API_KEY=your_anthropic_api_key_here
   DEEPSEEK_API_KEY=your_deepseek_api_key_here
   
   # Default AI Settings
   DEFAULT_AI_PROVIDER=openai
   DEFAULT_AI_MODEL=gpt-3.5-turbo

4. Start the application

   # Development mode (starts both server and client)
   npm run dev
   
   # Or start separately:
   # Terminal 1 - Server
   npm run server
   
   # Terminal 2 - Client
   npm run client

5. Access the application

   • Open your browser to http://localhost:5173
   • The server runs on http://localhost:3001

🔧 Configuration

Database Connections

1. Navigate to the Connections page

2. Click "Add Connection"

3. Fill in your PostgreSQL connection details:

   • Name: Friendly name for the connection
   • Host: Database server hostname/IP
   • Port: Database port (default: 5432)
   • Database: Database name
   • Username: Database username
   • Password: Database password
   • SSL: Enable SSL connection if required

4. Test the connection before saving

5. Activate the connection to start querying

AI Provider Setup

1. Navigate to AI Settings

2. Select your preferred AI provider:

   • OpenAI: Requires OpenAI API key
   • Claude: Requires Anthropic API key
   • DeepSeek: Requires DeepSeek API key

3. Configure model settings:

   • Model: Choose the specific model variant
   • Max Tokens: Maximum response length (100-4000)
   • Temperature: Creativity level (0.0-2.0)

4. Test your configuration

5. Save the settings

🛠️ Development

Project Structure

PostgreSQL-AI-Query-Assistant/
├── server/                 # Backend Node.js/Express server
│   ├── src/
│   │   ├── routes/        # API route handlers
│   │   ├── services/      # Business logic services
│   │   ├── middleware/    # Express middleware
│   │   └── utils/         # Utility functions
│   └── package.json
├── client/                # Frontend React application
│   ├── src/
│   │   ├── components/    # Reusable UI components
│   │   ├── pages/         # Page components
│   │   ├── hooks/         # Custom React hooks
│   │   ├── stores/        # Zustand state management
│   │   └── styles/        # CSS and styling
│   └── package.json
└── package.json          # Root package.json

Backend Architecture

• Express.js: Web framework with TypeScript
• PostgreSQL Client: pg library for database connections
• AI Integration: OpenAI, Anthropic, and DeepSeek SDKs
• Security: Helmet, CORS, rate limiting, credential encryption
• Validation: Zod schemas for request validation

Frontend Architecture

• React: UI framework with TypeScript
• Vite: Build tool and development server
• Tailwind CSS: Utility-first CSS framework
• Zustand: Lightweight state management
• Framer Motion: Animation library
• Monaco Editor: Advanced code editor
• React Query: Server state management

Available Scripts

# Development
npm run dev          # Start both server and client in development mode
npm run server       # Start only the backend server
npm run client       # Start only the frontend client

# Production
npm run build        # Build the frontend for production
npm start           # Start the production server

# Utilities
npm run install-all  # Install dependencies for all packages

🔒 Security Features

• Credential Encryption: Database passwords are encrypted at rest
• API Key Protection: AI provider keys are securely stored
• CORS Configuration: Proper cross-origin request handling
• Rate Limiting: API endpoint protection
• Input Validation: Comprehensive request validation
• SQL Injection Prevention: Parameterized queries

📊 Performance Features

• Query Optimization: AI-powered performance suggestions
• Connection Pooling: Efficient database connection management
• Result Pagination: Large result set handling
• Query Timeout: Configurable execution timeouts
• Execution Metrics: Detailed performance statistics

🎯 Usage Tips

Query Writing

• Use Ctrl/Cmd + Enter to execute queries
• Enable AI Assistant for real-time suggestions
• Use the Database Browser to explore table structures
• Validate queries before execution to catch syntax errors

AI Assistance

• Ask natural language questions about your data
• Request query optimizations for better performance
• Get explanations for complex SQL queries
• Use error fixing suggestions when queries fail

Connection Management

• Test connections before activating
• Use descriptive names for easy identification
• Keep multiple connections for different environments
• Monitor connection status in the sidebar

🔗 API Documentation

Connection Endpoints

• GET /api/connections - List all connections
• POST /api/connections - Create new connection
• PUT /api/connections/:id - Update connection
• DELETE /api/connections/:id - Delete connection
• POST /api/connections/:id/connect - Activate connection
• POST /api/connections/test - Test connection

Query Endpoints

• POST /api/query/execute - Execute SQL query
• POST /api/query/validate - Validate query syntax
• POST /api/query/explain - Get query execution plan
• GET /api/query/history - Get query history

AI Endpoints

• GET /api/ai/providers - List AI providers
• POST /api/ai/providers - Configure AI provider
• POST /api/ai/assist - Get AI query assistance
• POST /api/ai/chat - Chat with AI about queries

Database Endpoints

• GET /api/database/schemas - List database schemas
• GET /api/database/tables - List tables
• GET /api/database/tables/:name/columns - Get table columns
• GET /api/database/structure - Get complete database structure

🤝 Contributing

We welcome contributions! Please see our contributing guidelines for details on:

• Code style and standards
• Pull request process
• Issue reporting
• Feature requests

📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

🆘 Support

If you encounter any issues or have questions:

1. Check the Issues section for existing solutions
2. Create a new issue with detailed information
3. Include error messages, screenshots, and reproduction steps

🚧 Roadmap

Upcoming Features

• Database schema visualization
• Advanced query builder GUI
• Collaborative query sharing
• Query performance analytics
• Custom AI prompt templates
• Database backup/restore tools
• Multi-user authentication
• Query scheduling and automation

Known Limitations

• Currently supports PostgreSQL only
• AI features require internet connection
• Some advanced PostgreSQL features may not be fully supported

---

Built with ❤️ using modern web technologies and AI-powered assistance.