CipherSqlStudio

Overview

CipherSqlStudio is an interactive SQL learning platform where users can practice SQL queries, receive AI-powered hints, and track their progress. The platform executes SQL queries in a secure PostgreSQL sandbox and uses AI to guide students in solving SQL problems.

---

Features

• Practice SQL queries in a sandbox environment
• Execute SQL queries using PostgreSQL
• AI-powered hints for SQL problems
• Track user progress
• User authentication system
• Structured SQL assignments

---

Tech Stack

Frontend

• React
• Vite
• SCSS (Sass)
• Axios
• React Router

Backend

• Node.js
• Express.js
• Nodemon

Databases

• MongoDB (Users, Assignments, UserProgresses)
• PostgreSQL (SQL query execution)

AI Integration

• Gemini API

---

Project Structure

cipherSqlStudio
│
├── backend
│   ├── src
│   │   ├── config        # Database and environment configuration
│   │   ├── controller    # Request handling logic
│   │   ├── middleware    # Authentication and custom middlewares
│   │   ├── models        # MongoDB models
│   │   ├── routes        # Express routes
│   │   ├── service       # Business logic layer
│   │   ├── utils         # Helper functions
│   │   └── app.js        # Express app configuration
│   │
│   ├── index.js          # Server entry point
│   ├── seedAssignment.js # Script to seed SQL assignments
│   ├── .env.example
│   └── package.json
│
├── frontend
│   ├── public
│   ├── src
│   │   ├── components    # Reusable UI components
│   │   ├── routes        # Protected route handling and route logic
│   │   ├── screens       # Page components
│   │   ├── service       # API communication
│   │   ├── styles        # SCSS styles
│   │   ├── App.jsx
│   │   └── main.jsx
│   │
│   ├── index.html
│   ├── vite.config.js
│   └── package.json
│
├── docs
└── README.md

---

Installation

1 Clone Repository

git clone https://github.com/punit-dev/CipherSQLStudio.git
cd CipherSQLStudio

---

Backend Setup

cd backend
npm install

Create a .env file using .env.example.

Run backend server:

npm run dev

The backend uses Nodemon to automatically restart the server during development.

---

Frontend Setup

cd frontend
npm install
npm run dev

The frontend runs using Vite development server.

---

Environment Variables

Create a .env file in the backend directory.

Example:

PORT=3000

MONGO_URI=your_mongodb_connection_string

JWT_SECRET=your_jwt_secret_key

PG_USER=your_pg_username
PG_HOST=localhost
PG_DB=cipherSqlStudio
PG_PASS=your_pg_password
PG_PORT=5432

GEMINI_API_KEY=your_gemini_api_key

---

Data Flow Diagram

The system architecture is represented using Data Flow Diagrams:

• DFD Level 0 (Context Diagram) – Shows interaction between the user and the CipherSqlStudio platform.
• DFD Level 1 (Detailed Diagram) – Shows internal modules such as authentication, assignment fetching, SQL execution, hint generation, and progress tracking.

DFD Level 0 (Context Diagram)

DFD Level 1 (Detailed Diagram)

---

Future Improvements

• Difficulty levels for assignments
• Leaderboard system
• Query performance feedback
• More advanced AI guidance
• Admin dashboard for assignment management

---