SQL Database Manager

A comprehensive database management system with SQL and multiple database support.

🚀 Features

🗄️ Database Management

• Multiple Database Support: MySQL, PostgreSQL, SQLite
• Real-time Query Execution: Run SQL queries with live results
• Database Schema Inspection: View tables, columns, and relationships
• Connection Management: Easy database connection setup

🔧 Query Tools

• SQL Editor: Write and execute custom SQL queries
• Query Builder: Visual query construction interface
• Query History: Track and reuse previous queries
• Syntax Highlighting: Enhanced SQL code editing

📊 Data Operations

• Data Import: CSV and JSON file import with automatic table creation
• Data Export: Export query results to CSV/JSON formats
• Data Visualization: Charts and graphs with Chart.js
• Bulk Operations: Handle large datasets efficiently

👥 User Management

• Secure Authentication: JWT-based login system
• User Registration: Multi-user support with role management
• Session Management: Secure token-based sessions
• Password Security: Bcrypt password hashing

🎨 User Interface

• Modern Design: Clean, responsive interface
• Dark/Light Theme: Multiple theme options
• Mobile Responsive: Works on all devices
• Real-time Updates: Live data refresh

🛠️ Technologies

Backend

• FastAPI: Modern, fast web framework for APIs
• SQLite: Default database for user management
• PyMySQL: MySQL database connector
• psycopg2: PostgreSQL database connector
• JWT: JSON Web Tokens for authentication
• bcrypt: Password hashing
• pandas: Data manipulation and analysis

Frontend

• HTML5: Semantic markup
• CSS3: Modern styling with animations
• JavaScript (ES6+): Modern JavaScript features
• Chart.js: Data visualization library
• Font Awesome: Icon library
• Responsive Design: Mobile-first approach

Database Support

• SQLite: Lightweight, file-based database
• MySQL: Popular relational database
• PostgreSQL: Advanced open-source database

📋 Prerequisites

• Python 3.8+: Backend runtime environment
• pip: Python package manager
• Modern Web Browser: Chrome, Firefox, Safari, or Edge
• Internet Connection: For package installation

🚀 Quick Start

1. Clone the Repository

git clone https://github.com/rskworld/sql-database-manager.git
cd sql-database-manager

2. Backend Setup

# Navigate to backend directory
cd backend

# Install Python dependencies
pip install -r requirements.txt

# Start the FastAPI server
uvicorn main:app --reload

# The backend will be available at http://localhost:8000

3. Frontend Setup

# Navigate to frontend directory
cd frontend

# No build process required - just open the HTML file
# Option 1: Double-click index.html
# Option 2: Use a live server for better development

4. Access the Application

• Frontend: Open frontend/index.html in your browser
• Backend API: http://localhost:8000
• API Documentation: http://localhost:8000/docs

🔐 Default Credentials

• Username: admin
• Password: admin123

📖 Detailed Usage Guide

Database Connection

1. Select Database Type: Choose from SQLite, MySQL, or PostgreSQL
2. Configure Connection: Enter connection details
3. Test Connection: Verify database connectivity
4. Save Connection: Store connection for later use

Query Execution

1. Write SQL Query: Use the SQL editor or query builder
2. Execute Query: Click "Run Query" to execute
3. View Results: Results display in a formatted table
4. Export Results: Save results to CSV or JSON

Data Import/Export

Import Data

1. Select File: Choose CSV or JSON file
2. Configure Import: Set table name and options
3. Import Data: Automatic table creation and data insertion
4. Verify Import: Check imported data quality

Export Data

1. Select Data: Choose table or write query
2. Choose Format: CSV or JSON export
3. Export Data: Download file with results
4. Verify Export: Check exported file integrity

Data Visualization

1. Select Data Source: Table or custom query
2. Choose Chart Type: Bar, line, pie, or scatter chart
3. Configure Chart: Set labels, colors, and options
4. Generate Chart: Interactive visualization

🏗️ Project Structure

sql-database-manager/
├── backend/                 # FastAPI backend
│   ├── main.py             # Main application file
│   ├── auth.py             # Authentication logic
│   ├── database.py         # Database operations
│   ├── models.py           # Pydantic models
│   ├── query_builder.py    # SQL query builder
│   ├── data_io.py          # Data import/export
│   ├── users.py            # User management
│   ├── config.py           # Configuration
│   ├── utils.py            # Utility functions
│   └── requirements.txt    # Python dependencies
├── frontend/               # Web frontend
│   ├── index.html          # Main application page
│   ├── login.html          # User login page
│   ├── register.html       # User registration page
│   ├── style.css           # Application styles
│   ├── script.js           # Main JavaScript logic
│   ├── auth.js             # Authentication handling
│   ├── query_builder.js    # Query builder interface
│   ├── data_io.js          # Data import/export UI
│   └── visualization.js    # Chart generation
├── assets/                 # Static assets
├── docs/                   # Documentation
├── LICENSE                 # License file
└── README.md               # This file

🔧 Configuration

Backend Configuration

Edit backend/config.py to modify database connections:

DB_CONFIGS = {
    "mysql": {
        "host": "localhost",
        "port": 3306,
        "user": "root",
        "password": "",
    },
    "postgresql": {
        "host": "localhost",
        "port": 5432,
        "user": "postgres",
        "password": "",
    },
    "sqlite": {
        "db_path": "mydatabase.db"
    }
}

Frontend Configuration

Modify frontend/script.js to change API endpoints:

const API_BASE_URL = 'http://localhost:8000';

🧪 Testing

Backend Tests

# Test API endpoints
curl http://localhost:8000/test

# Test authentication
curl -X POST "http://localhost:8000/token" \
     -H "Content-Type: application/x-www-form-urlencoded" \
     -d "username=admin&password=admin123"

# Test query execution
curl -X POST "http://localhost:8000/query" \
     -H "Authorization: Bearer YOUR_TOKEN" \
     -H "Content-Type: application/json" \
     -d '{"query":"SELECT 1","db_type":"sqlite","db_config":{}}'

Frontend Tests

• Open browser developer tools
• Test all UI components
• Verify API calls in network tab
• Check console for errors

🚀 Deployment

Development Deployment

# Backend
uvicorn main:app --host 0.0.0.0 --port 8000

# Frontend (serve with any web server)
python -m http.server 3000 --directory frontend

Production Deployment

# Backend with Gunicorn
pip install gunicorn
gunicorn -w 4 -k uvicorn.workers.UvicornWorker main:app

# Frontend with Nginx (configure nginx to serve frontend files)

Docker Deployment

# Dockerfile
FROM python:3.9-slim

WORKDIR /app
COPY requirements.txt .
RUN pip install -r requirements.txt

COPY . .
CMD ["uvicorn", "main:app", "--host", "0.0.0.0", "--port", "8000"]

🤝 Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📝 API Documentation

Authentication Endpoints

• POST /token - Get access token
• POST /users/ - Register new user
• GET /users/me/ - Get current user info

Database Endpoints

• POST /connect - Test database connection
• GET /schema/{db_type} - Get database schema
• POST /query - Execute SQL query
• POST /build-query - Build SQL query

Data Endpoints

• POST /import-data - Import data from file
• POST /export-data - Export data to file
• POST /create-sample-data - Create sample database

🐛 Troubleshooting

Common Issues

Backend Issues

• Port 8000 in use: Change port with --port 8001
• Module not found: Run pip install -r requirements.txt
• Database connection failed: Check database credentials

Frontend Issues

• CORS errors: Backend CORS configuration
• API calls failing: Check backend is running
• Authentication errors: Verify login credentials

Database Issues

• SQLite file not found: Check file permissions
• MySQL connection failed: Verify MySQL service
• PostgreSQL connection failed: Check PostgreSQL service

Debug Mode

Enable debug logging:

# In main.py
import logging
logging.basicConfig(level=logging.DEBUG)

📊 Performance

Query Performance

• Use indexes for large tables
• Limit result sets with pagination
• Optimize SQL queries

Application Performance

• Enable connection pooling
• Use caching for frequent queries
• Optimize frontend JavaScript

🔒 Security

Authentication Security

• JWT tokens with expiration
• Secure password hashing
• CORS protection
• SQL injection prevention

Data Security

• Input validation
• Parameterized queries
• Secure file uploads
• Rate limiting

📈 Roadmap

Version 1.1 (Planned)

• Advanced query builder
• Database migration tools
• Query optimization suggestions
• Dark theme support

Version 1.2 (Future)

• Multi-database transactions
• Advanced data visualization
• Query scheduling
• API rate limiting

📞 Support

Contact Information

• Author: RSK World
• Website: https://rskworld.in
• Email: help@rskworld.in
• GitHub: https://github.com/rskworld/sql-database-manager

Documentation

• API Docs: http://localhost:8000/docs
• User Guide: See Usage section above
• Developer Guide: See Project Structure

🙏 Acknowledgments

• FastAPI: For the excellent web framework
• Chart.js: For data visualization capabilities
• Font Awesome: For beautiful icons
• Open Source Community: For inspiration and contributions

---

📄 License

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

---

⭐ If this project helped you, please give it a star!

🐛 Found a bug? Please report it on GitHub Issues

💡 Have a suggestion? Please open an Issue