SQL Query Performance Analyzer Backend

Developed by Ahmed Medhat - Marwan Essa

---

📋 Project Overview

A developer tool that allows users to submit SQL queries, executes them on a MySQL test database, extracts performance metrics, stores them, and provides analytics insights via a REST API. Built with Node.js, Express, and MySQL.

Developed by: Ahmed Medhat - Marwan Essa. Project Type: Backend / API Server / Data Analysis. License: Proprietary – All rights reserved.

---

SQL QUERY ANALYZER

database/
├── schema.sql
└── seed.sql

server/
├── app/
│   ├── controllers/
│   │   └── queryController.js
│   ├── repositories/
│   │   ├── metrics.js
│   │   └── query.js
│   ├── routes/
│   │   └── query.routes.js
│   ├── services/
│   │   ├── explain-parser.js
│   │   ├── query-execution.js
│   │   └── sql-metrics.js
│   ├── utils/
│   │   └── sql-analyzer.js
├── config/
│   └── database.js
├── node_modules/
├── tests/
│   ├── api.test.js
│   └── test-connection.test.js
├── .env
├── .gitignore
├── app.js
├── jest.config.js
├── package-lock.json
└── package.json

---

🛠️ Technologies Used

🖥️ Backend Technologies

Technology	Purpose	Version
	JavaScript Runtime Environment	18.x+
	Web Application Framework	4.x
	Cross-Origin Resource Sharing	2.x
	HTTP Request Logger	1.x
	Development Server Auto-Restart	3.x
	Environment Variables Loader	16.x
	MySQL Database Driver	3.x

---

Features

• Submit any SQL query (SELECT, INSERT, UPDATE, DELETE) via API.
• Automatically runs EXPLAIN FORMAT=JSON to capture query plan and times execution.
• Computes metrics: execution time, rows examined, rows returned, joins count, index usage, complexity score.
• Stores queries and metrics in a separate database for historical analysis.
• Provides endpoints to retrieve query history and details.
• Designed for collaboration with data analysts (export metrics for dashboards).

---

🚀 Getting Started

Prerequisites

• Node.js v18 or higher
• MySQL v8 or higher
• npm

---

📖 API Documentation

Base URL: http://localhost:PORT/api

Query

• POST /api/queries – Submit and analyze a SQL query.
• GET /api/queries – Retrieve all analyzed queries with their metrics.
• GET /api/queries/:id – Retrieve a specific query by ID with its metrics.

---

📦 Available NPM Scripts

The project includes a set of utility scripts to simplify development, database migrations, and seeding.

Setup & Installation

1. Clone the repository

   git clone https://github.com/ahmedmedhat-se/sql-query-analyzer.git
   cd sql-query-analyzer

2. Install dependencies

   npm install

3. Configure environment variables

   PORT=

   TEST_DB_HOST=
   TEST_DB_USER=
   TEST_DB_PASSWORD=
   TEST_DB_NAME=test_db
   TEST_DB_PORT=
   
   METRICS_DB_HOST=
   METRICS_DB_USER=
   METRICS_DB_PASSWORD=
   METRICS_DB_NAME=metrics_db
   METRICS_DB_PORT=

4. Set up databases · Ensure MySQL is running. · Run the schema script to create met

---

🤝 Contributing

This is a proprietary project. External contributions are not accepted.

---

📄 License

PROPRIETARY LICENSE © 2026 - Ahmed Medhat, Marwan Essa. All Rights Reserved. This project is a personal, non-commercial work created solely for the purpose of demonstrating full-stack web development skills.

👥 Author

• Ahmed Medhat – Junior Backend Engineer
• Marwan Essa – Data Analyst