🗄️ SQL Runner

A full-stack web application that allows users to execute SQL queries in real-time with a modern, intuitive interface. Built with FastAPI (Python) backend and Next.js (React) frontend.

✨ Features

🔐 Authentication & Security

• User registration and login with JWT tokens
• Secure session management
• Protected routes and API endpoints
• Read-only mode for safe query execution

💾 Query Execution

• Execute SQL queries in real-time
• View results in formatted tables
• Support for complex queries (JOINs, GROUP BY, etc.)
• Query history tracking
• Export results to CSV or JSON

📊 Table Explorer

• Browse all available database tables
• View table schemas (column names and types)
• Preview sample data from each table
• Quick query templates (SELECT, COUNT, etc.)

🕐 Query History

• Track all executed queries
• Replay previous queries with one click
• Delete individual queries or clear all history
• Relative timestamps for easy reference

🎨 Modern UI/UX

• Clean, responsive design with Tailwind CSS
• Three-panel layout (Tables, Editor, History)
• Loading states and error handling
• Keyboard shortcuts (Ctrl+Enter to run)
• SQL query formatting

---

🏗️ Architecture

┌─────────────────────────────────────────────────────────────┐
│                        Frontend (Next.js)                    │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │   Auth UI    │  │ Query Editor │  │Table Explorer│     │
│  └──────────────┘  └──────────────┘  └──────────────┘     │
└───────────────────────────┬─────────────────────────────────┘
                            │ REST API (JWT Auth)
┌───────────────────────────┴─────────────────────────────────┐
│                       Backend (FastAPI)                      │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │  Auth Routes │  │Query Executor│  │Table Manager │     │
│  └──────────────┘  └──────────────┘  └──────────────┘     │
└───────────────────────────┬─────────────────────────────────┘
                            │ SQLAlchemy ORM
┌───────────────────────────┴─────────────────────────────────┐
│                    SQLite Database                           │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │    Users     │  │QueryHistory  │  │Sample Tables │     │
│  └──────────────┘  └──────────────┘  └──────────────┘     │
└─────────────────────────────────────────────────────────────┘

---

🛠️ Tech Stack

Backend

• Framework: FastAPI 0.109
• Database: SQLite with SQLAlchemy ORM
• Authentication: JWT (python-jose) + bcrypt
• Validation: Pydantic schemas
• Server: Uvicorn ASGI server

Frontend

• Framework: Next.js 14 (Pages Directory)
• Language: JavaScript/TypeScript
• Styling: Tailwind CSS
• HTTP Client: Native Fetch API
• State Management: React Context + useState

DevOps

• Containerization: Docker & Docker Compose
• Database: SQLite (persistent volume)

---

📋 Prerequisites

Option 1: Using Docker (Recommended)

• Docker Desktop 20.10+ (Windows/Mac) or Docker Engine (Linux)
• Docker Compose 2.0+
• 4GB RAM minimum
• 2GB free disk space

Option 2: Manual Setup

• Python 3.11+
• Node.js 18+
• npm or yarn
• SQLite3

---

🚀 Quick Start with Docker

1. Clone the Repository

git clone https://github.com/DarshD22/SQL_Runner.git
cd SQL_Runner

2. Create Environment File

Create a .env file in the root directory:

# Backend Configuration
SECRET_KEY=your-super-secret-key
READ_ONLY_MODE=True
DEBUG=False

# Frontend Configuration
NEXT_PUBLIC_API_URL=http://localhost:8000

3. Start with Docker Compose

# Build and start all services
docker-compose up --build

First build takes 5-10 minutes. Subsequent starts take ~30 seconds.

4. Access the Application

• Frontend: http://localhost:3000
• Backend API: http://localhost:8000
• API Documentation: http://localhost:8000/docs
• Alternative API Docs: http://localhost:8000/redoc

5. Register and Login

1. Navigate to http://localhost:3000/register
2. Create an account (username, password)
3. Login with your credentials
4. Start running SQL queries!

6. Stop the Application

# Stop containers (keep data)
docker-compose down

# Stop and remove volumes (delete data)
docker-compose down -v

---

🔧 Manual Setup (Without Docker)

Backend Setup

1. Navigate to backend directory

   cd backend

2. Create virtual environment

   python -m venv venv
   
   # Activate on Windows
   venv\Scripts\activate
   
   # Activate on Mac/Linux
   source venv/bin/activate

3. Install dependencies

   pip install -r requirements.txt

4. Create .env file

   # Create backend/.env
   SECRET_KEY=your-secret-key
   READ_ONLY_MODE=True
   DEBUG=True

5. Run the backend

   python -m app.main
   # or
   uvicorn app.main:app --reload

   Backend will start at: http://localhost:8000

Frontend Setup

1. Navigate to frontend directory

   cd frontend

2. Install dependencies

   npm install
   # or
   yarn install

3. Create .env.local file

   # Create frontend/.env.local
   NEXT_PUBLIC_API_URL=http://localhost:8000

4. Run the frontend

   npm run dev
   # or
   yarn dev

   Frontend will start at: http://localhost:3000

---

📂 Project Structure

sql-runner/
├── backend/
│   ├── app/
│   │   ├── main.py              # FastAPI application entry point
│   │   ├── config.py            # Configuration settings
│   │   ├── db.py                # Database connection and helpers
│   │   ├── models.py            # SQLAlchemy models (User, QueryHistory)
│   │   ├── schemas.py           # Pydantic schemas for validation
│   │   ├── auth.py              # Authentication helpers (JWT, bcrypt)
│   │   ├── utils.py             # Utility functions
│   │   ├── routes/
│   │   │   ├── auth_routes.py   # Authentication endpoints
│   │   │   ├── query_routes.py  # Query execution endpoints
│   │   │   ├── tables_routes.py # Table information endpoints
│   │   │   └── history_routes.py# Query history endpoints
│   ├── requirements.txt         # Python dependencies
│   ├── Dockerfile              # Backend Docker configuration
│   └── .dockerignore
│
├── frontend/
│   ├── src/
│   │   ├── pages/
│   │   │   ├── _app.jsx         # Next.js app wrapper
│   │   │   ├── index.jsx        # Main SQL runner page
│   │   │   ├── login.jsx        # Login page
│   │   │   └── register.jsx     # Registration page
│   │   ├── components/
│   │   │   ├── Layout.jsx       # Page layout wrapper
│   │   │   ├── QueryEditor.jsx  # SQL query input component
│   │   │   ├── ResultsTable.jsx # Query results display
│   │   │   ├── TablesSidebar.jsx# Database tables list
│   │   │   ├── TablePreview.jsx # Table schema preview
│   │   │   ├── RecentQueries.jsx# Query history sidebar
│   │   │   ├── Loading.jsx      # Loading spinner
│   │   │   └── Error.jsx        # Error display
│   │   ├── services/
│   │   │   ├── api.ts           # Generic API client
│   │   │   ├── auth.ts          # Authentication service
│   │   │   └── sqlRunner.ts     # SQL execution service
│   │   ├── hooks/
│   │   │   └── useAuth.js       # Authentication hook
│   │   └── styles/
│   │       └── globals.css      # Global styles
│   ├── package.json            # Node dependencies
│   ├── next.config.js          # Next.js configuration
│   ├── tailwind.config.js      # Tailwind CSS configuration
│   ├── Dockerfile              # Frontend Docker configuration
│   └── .dockerignore
│
├── docker-compose.yml          # Docker Compose orchestration
├── .env                        # Environment variables
└── README.md                   # This file

---

🗄️ Database Schema

Sample Tables (Auto-created on startup)

Customers

CREATE TABLE Customers (
    customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    age INTEGER,
    country VARCHAR(100)
);

Orders

CREATE TABLE Orders (
    order_id INTEGER PRIMARY KEY AUTOINCREMENT,
    item VARCHAR(100),
    amount INTEGER,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

Shippings

CREATE TABLE Shippings (
    shipping_id INTEGER PRIMARY KEY AUTOINCREMENT,
    status VARCHAR(100),
    customer INTEGER
);

System Tables

Users

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

QueryHistory

CREATE TABLE query_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    sql_text TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

---

🔌 API Endpoints

Authentication

• POST /auth/register - Register new user
• POST /auth/login - Login user (returns JWT token)
• POST /auth/logout - Logout user
• GET /auth/me - Get current user info

Query Execution

• POST /api/query/run - Execute SQL query (Protected)

Tables

• GET /api/tables - List all tables (Protected)
• GET /api/tables/{table_name} - Get table schema and preview (Protected)

History

• GET /api/history - Get query history (Protected)
• DELETE /api/history/{id} - Delete specific query (Protected)
• DELETE /api/history - Clear all history (Protected)

System

• GET / - API health check
• GET /health - Health status

Full API Documentation: http://localhost:8000/docs

---

🎯 Usage Examples

Example 1: Simple SELECT Query

SELECT * FROM Customers LIMIT 10;

Example 2: JOIN Query

SELECT c.first_name, c.last_name, o.item, o.amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE c.country = 'USA';

Example 3: Aggregation Query

SELECT country, COUNT(*) as customer_count
FROM Customers
GROUP BY country
ORDER BY customer_count DESC;

Example 4: Complex Query

SELECT 
    c.country,
    COUNT(DISTINCT c.customer_id) as customers,
    COUNT(o.order_id) as orders,
    SUM(o.amount) as total_revenue
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.country
HAVING COUNT(o.order_id) > 0
ORDER BY total_revenue DESC;

---

🐳 Docker Commands

Using docker-compose (Recommended)

# Start services
docker-compose up

# Start in background
docker-compose up -d

# Stop services
docker-compose down

# View logs
docker-compose logs

# Follow logs in real-time
docker-compose logs -f

# Rebuild and restart
docker-compose up --build

# Reset everything
docker-compose down -v
docker-compose up --build

Manual Docker Commands

# Build backend
cd backend
docker build -t sql-runner-backend .

# Run backend
docker run -d -p 8000:8000 --name backend sql-runner-backend

# Build frontend
cd frontend
docker build -t sql-runner-frontend .

# Run frontend
docker run -d -p 3000:3000 --name frontend sql-runner-frontend

---

🔒 Security Features

Authentication

• ✅ JWT-based authentication
• ✅ Password hashing with bcrypt
• ✅ Secure session management
• ✅ Protected API endpoints

SQL Safety

• ✅ Read-only mode (blocks INSERT, UPDATE, DELETE, DROP)
• ✅ Input validation and sanitization
• ✅ Query length limits

Application Security

• ✅ CORS configuration
• ✅ HTTP security headers
• ✅ Non-root Docker containers
• ✅ Environment variable management

---

⚙️ Configuration

Backend Configuration (backend/app/config.py)

# Database
DATABASE_URL = "sqlite:///./sql_runner.db"

# Security
SECRET_KEY = "your-secret-key"  # Change in production!
ALGORITHM = "HS256"
ACCESS_TOKEN_EXPIRE_MINUTES = 1440  # 24 hours

# SQL Safety
READ_ONLY_MODE = True  # Blocks write operations
BLOCKED_SQL_KEYWORDS = ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER"]

# Limits
MAX_QUERY_LENGTH = 5000
MAX_PREVIEW_ROWS = 10
MAX_HISTORY_ITEMS = 50

Frontend Configuration (frontend/.env.local)

# API URL
NEXT_PUBLIC_API_URL=http://localhost:8000

# Environment
NODE_ENV=development

---

🧪 Testing

Test the Backend

cd backend

# Test health endpoint
curl http://localhost:8000/health

# Test registration (using curl)
curl -X POST http://localhost:8000/auth/register \
  -H "Content-Type: application/json" \
  -d '{"username":"testuser","password":"testpass123"}'

# Test login
curl -X POST http://localhost:8000/auth/login \
  -H "Content-Type: application/json" \
  -d '{"username":"testuser","password":"testpass123"}'

Test the Frontend

cd frontend

# Run build
npm run build

# Test production build
npm start

API Documentation

Visit http://localhost:8000/docs for interactive API testing with Swagger UI.

---