ChatSQL 🗄️💬

A modern database visualizer with analytics and AI-powered query generation. Making database management cool again.

✨ Features

🔐 Secure Authentication - JWT-based auth with HTTP-only cookies
🔌 Multi-Database Support - Connect to PostgreSQL, MySQL, and more
📊 Visual Table Explorer - Browse, search, filter, and edit data
📈 Custom Dashboards - Build analytics dashboards with widgets
🤖 AI Query Generation - Write queries in plain English
🗺️ ERD Visualization - Auto-generated entity relationship diagrams
⚡ SQL Editor - Execute queries with syntax highlighting
📜 Query History - Save and reuse your queries

🚀 Quick Start

Prerequisites

Node.js >= 18.0.0
PostgreSQL >= 14
npm or yarn

Installation

Clone the repository

git clone https://github.com/ayan-mn18/ChatSQL.git
cd ChatSQL

Install dependencies
```
npm install
```

Set up environment variables

cp .env.example .env
# Edit .env with your configuration

Set up the database

# Create the database
createdb chatsql

# Run the complete schema migration
psql -U postgres -d chatsql -f database/migration-v0/schema.sql

# Optional: Add seed data for development
psql -U postgres -d chatsql -f database/seed.sql

See DATABASE_SETUP.md for detailed instructions.

Start the development server
```
npm run dev
```

The API will be available at http://localhost:3000

📁 Project Structure

ChatSQL/
├── database/
│   ├── migration-v0/
│   │   └── schema.sql      # Complete database schema (v0)
│   ├── seed.sql            # Seed data (optional)
│   └── README.md           # Database documentation
├── src/
│   ├── config/             # Configuration files
│   ├── controllers/        # Route controllers
│   ├── middleware/         # Express middleware
│   ├── routes/             # API routes
│   ├── services/           # Business logic
│   ├── types/              # TypeScript types
│   └── utils/              # Utility functions
├── server.ts               # Application entry point
└── package.json

🔌 API Endpoints

Authentication

| Method | Endpoint | Description | |--------|----------|-------------| | POST | /api/auth/register | Register new user (sends OTP) | | POST | /api/auth/verify-email | Verify email with OTP | | POST | /api/auth/resend-otp | Resend verification OTP | | POST | /api/auth/login | Login (verified users only) | | POST | /api/auth/logout | Logout user | | POST | /api/auth/forgot-password | Request password reset | | POST | /api/auth/reset-password | Reset password with token | | GET | /api/auth/me | Get current user | | PUT | /api/auth/profile | Update profile |

Health Check

| Method | Endpoint | Description | |--------|----------|-------------| | GET | /api/health | Check API health |

More endpoints coming soon...

🔐 Authentication Flow

1. User registers with email/password
2. System sends 6-digit OTP to email
3. User enters OTP to verify email
4. User can now login with verified email
5. JWT token stored in HTTP-only cookie
6. Logout clears the cookie

⚙️ Environment Variables

| Variable | Description | Required | |----------|-------------|----------| | PORT | Server port | No (default: 3000) | | NODE_ENV | Environment | No (default: development) | | DB_HOST | Database host | Yes | | DB_PORT | Database port | No (default: 5432) | | DB_NAME | Database name | Yes | | DB_USERNM | Database username | Yes | | DB_PWD | Database password | Yes | | JWT_SECRET | JWT signing secret (min 32 chars) | Yes | | SMTP_HOST | SMTP server host | Yes | | SMTP_PORT | SMTP server port | No (default: 587) | | SMTP_USER | SMTP username | Yes | | SMTP_PASS | SMTP password | Yes | | SMTP_FROM_EMAIL | Sender email address | Yes | | SMTP_FROM_NAME | Sender name | No (default: ChatSQL) | | FRONTEND_URL | Frontend URL for email links | No | | CORS_ORIGIN | Allowed CORS origin | No |

🛠️ Development

# Run in development mode with hot reload
npm run dev

# Build for production
npm run build

# Start production server
npm start

🗺️ Roadmap

[x] Project setup & authentication
[ ] Connection management
[ ] Schema & metadata APIs
[ ] Table data CRUD
[ ] SQL query execution
[ ] AI query generation
[ ] Dashboard & widgets
[ ] ERD visualization

🤝 Contributing

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

Fork the repository
Create your feature branch (git checkout -b feature/AmazingFeature)
Commit your changes (git commit -m 'Add some AmazingFeature')
Push to the branch (git push origin feature/AmazingFeature)
Open a Pull Request

📄 License

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

🙏 Acknowledgments

Built with Express.js
Database ORM by Sequelize
AI powered by OpenAI & Anthropic

Made with ❤️ by Ayan

ChatSQL

Install / Use

README