This project is a fully functional MySQL simulator built with HTML, CSS, and JavaScript. It replicates core MySQL features, including DDL, DML, transactions, indexes, and security, while maintaining a realistic database structure. The simulator supports multi-query execution, syntax highlighting, and interactive schema browsing, making it ideal for learning and practicing SQL without a live database.

• Line numbers synchronized with scroll
• Multi-query execution with semicolon splitting
• Keyboard shortcuts: Ctrl+Enter to run, Tab for indentation
• Format SQL button for basic keyword capitalization
• Clear button to reset editor
• Explain mode toggle for execution plan visualization

-- All standard SQL commands
SELECT * FROM customers WHERE country = 'US';
INSERT INTO products (product_id, name, price) VALUES (101, 'Laptop', 999.99);
UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering';
DELETE FROM orders WHERE order_date < '2020-01-01';

-- Transactions
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Indexes
CREATE INDEX idx_lastname ON employees(last_name);
DROP INDEX idx_lastname ON employees;
SHOW INDEXES FROM customers;

-- Security
CREATE USER 'analyst' IDENTIFIED BY 'password';
GRANT SELECT, INSERT ON customers TO analyst;
GRANT analyst TO 'jane_doe';

• Search tables with live filtering
• Expand/collapse to show columns
• Visual indicators for PK/FK/Indexes
• Column types displayed inline
• One-click preview button runs SELECT * FROM table LIMIT 5

• Create indexes with CREATE INDEX ... ON table(column)
• Unique indexes with CREATE UNIQUE INDEX ...
• Drop indexes with clickable "drop" links
• View all indexes per table with metadata

• Index name
• Column(s) included
• Unique flag
• Cardinality estimate
• One-click drop with confirmation

• Indexes affect EXPLAIN plan visualization
• Index scans shown in green, full table scans in orange
• Query optimizer simulation with cost estimates

• Built-in users: root (admin), analyst, viewer
• Create users: CREATE USER 'name' IDENTIFIED BY 'password'
• Drop users: DROP USER 'name'

• Built-in roles: admin (ALL), analyst (SELECT, INSERT, UPDATE), readonly (SELECT)
• Create roles: CREATE ROLE role_name
• Grant roles: GRANT role_name TO 'user'

• Grant privileges: GRANT SELECT, INSERT ON table TO user/role
• Revoke privileges: REVOKE INSERT ON table FROM user/role
• Show grants: SHOW GRANTS FOR 'user'

• Active user displayed in header
• Current roles and effective permissions
• Permission checking on every query

• Expandable cards with description
• Multiple examples per lesson
• One-click load to editor
• Run automatically after loading
• Categorized by SQL operation

• Node types: FULL SCAN, INDEX SCAN, JOIN, FILTER, SORT, LIMIT, PROJECTION
• Color coding by operation type
• Step numbers and execution order
• Cost estimates per node
• Total cost at top
• Optimization tips when full scans detected

• Run iterations: 10–500 runs
• Metrics: Min, Avg, Median, P95
• Distribution chart with histogram bars
• Query comparison A/B testing
• Percentage difference calculation

• Dark background (#1a1f2e) — easy on the eyes for long coding sessions
• Orange accent (#f97316) for primary actions and run button
• Cyan (#38bdf8) for information and data types
• Green (#4ade80) for success and indexes
• Red (#f87171) for errors and destructive actions
• Purple (#c084fc) for security and schema builder
• Pink (#f472b6) for special features

• Bebas Neue — Bold headers, logo
• JetBrains Mono — Code editor, SQL syntax, table data
• Nunito — UI elements, buttons, labels

• Tabbed interfaces throughout (editor/results, sidebar panels)
• Expandable schema with animated arrows
• Status bar with current section, table/index counts, user
• Modal dialogs for import, schema builder, security
• Toast notifications for user feedback

┌─────────────────────────────────────┐
│      MySQL Simulator Pro            │
├─────────────────────────────────────┤
│                                     │
│  ┌─────────────────────────────┐   │
│  │   Core Database Engine      │   │
│  │   • In-memory DB object     │   │
│  │   • 15+ built-in tables    │   │
│  │   • Full DDL/DML/DQL       │   │
│  │   • Transaction state      │   │
│  │   • Index management       │   │
│  │   • RBAC permissions       │   │
│  └─────────────────────────────┘   │
│                                     │
│  ┌─────────────────────────────┐   │
│  │   SQL Parser & Executor     │   │
│  │   • Multi-statement split  │   │
│  │   • WHERE evaluator        │   │
│  │   • JOIN engine            │   │
│  │   • Aggregation functions  │   │
│  │   • Subquery support       │   │
│  └─────────────────────────────┘   │
│                                     │
│  ┌─────────────────────────────┐   │
│  │   Query Optimizer           │   │
│  │   • EXPLAIN plan builder   │   │
│  │   • Index detection        │   │
│  │   • Cost estimation        │   │
│  │   • Performance profiling  │   │
│  └─────────────────────────────┘   │
│                                     │
│  ┌─────────────────────────────┐   │
│  │   UI Components             │   │
│  │   • Editor with line nums  │   │
│  │   • Schema browser         │   │
│  │   • Results tabs           │   │
│  │   • Lesson library         │   │
│  │   • Modals & toast         │   │
│  └─────────────────────────────┘   │
└─────────────────────────────────────┘

// Core execution
executeStatement(sql)           // Parse and execute any SQL statement
execSelect(sql)                 // SELECT with WHERE, JOINs, GROUP BY
execInsert(sql)                  // INSERT INTO ... VALUES
execUpdate(sql)                  // UPDATE with WHERE
execDelete(sql)                  // DELETE with WHERE

// DDL
execCreate(sql)                   // CREATE TABLE
execDrop(sql)                      // DROP TABLE
execAlter(sql)                     // ALTER TABLE
execTruncate(sql)                  // TRUNCATE TABLE

// Indexes
execCreateIndex(sql)               // CREATE INDEX
execDropIndex(sql)                  // DROP INDEX
execShowIndexes(sql)                // SHOW INDEXES

// Transactions
execBegin()                         // BEGIN TRANSACTION
execCommit()                        // COMMIT
execRollback()                       // ROLLBACK
execSavepoint()                      // SAVEPOINT
execRollbackTo()                     // ROLLBACK TO SAVEPOINT

// Security
execCreateUser(sql)                  // CREATE USER
execCreateRole(sql)                  // CREATE ROLE
execGrant(sql)                       // GRANT privileges/role
execRevoke(sql)                      // REVOKE
execShowUsers()                       // SHOW USERS
execShowRoles()                       // SHOW ROLES
execShowGrants()                      // SHOW GRANTS

// Analysis
buildExplainPlan(sql)                 // Generate execution plan
profileQuery(sql, iterations)         // Performance profiling
runComparison()                        // A/B query comparison

// UI
renderSchema()                         // Render schema browser
renderIndexPanel()                     // Render index manager
renderSecPanel()                       // Render security panel
showResult(res)                        // Display query results

-- All customers
SELECT * FROM CUSTOMERS;

-- Filtered customers
SELECT first_name, last_name, country FROM CUSTOMERS WHERE country = 'US';

-- Aggregation
SELECT country, COUNT(*) FROM CUSTOMERS GROUP BY country ORDER BY COUNT(*) DESC;

-- Join customers with orders
SELECT c.first_name, c.last_name, o.order_id, o.total_amount
FROM CUSTOMERS c
INNER JOIN ORDERS o ON c.customer_id = o.customer_id;

-- Multi-table join with aggregation
SELECT c.country, COUNT(DISTINCT c.customer_id) AS customers,
       SUM(o.total_amount) AS total_revenue
FROM CUSTOMERS c
LEFT JOIN ORDERS o ON c.customer_id = o.customer_id
GROUP BY c.country
ORDER BY total_revenue DESC NULLS LAST;

-- Subquery
SELECT * FROM PRODUCTS 
WHERE price > (SELECT AVG(price) FROM PRODUCTS);

-- Window function (simulated via subquery)
SELECT product_name, price, 
       (SELECT COUNT(*) FROM PRODUCTS p2 WHERE p2.price > p1.price) + 1 AS rank
FROM PRODUCTS p1
ORDER BY price DESC;

BEGIN;
UPDATE PRODUCTS SET price = price * 1.10 WHERE category = 'Electronics';
SAVEPOINT after_update;
DELETE FROM PRODUCTS WHERE stock_qty = 0;
ROLLBACK TO after_update;  -- Keep price update, undo deletion
COMMIT;

-- Create index
CREATE INDEX idx_country ON CUSTOMERS(country);

-- Explain plan
EXPLAIN SELECT * FROM CUSTOMERS WHERE country = 'US';

-- Analyze table
ANALYZE TABLE CUSTOMERS;

-- Create user with password
CREATE USER 'analyst1' IDENTIFIED BY 'secure123';

-- Grant table privileges
GRANT SELECT, INSERT ON ORDERS TO analyst1;

-- Create role
CREATE ROLE finance_reader;

-- Grant role to user
GRANT finance_reader TO analyst1;

-- See effective grants
SHOW GRANTS FOR analyst1;

• Load Time: < 2 seconds (no external dependencies beyond Google Fonts)
• Memory Usage: < 60 MB
• Query Speed: Sub-millisecond for simple queries
• Transaction Handling: Full ACID simulation with savepoints

MySQL Simulator Pro is a completely safe educational tool:

• ✅ No actual database connections
• ✅ All data stored in browser memory
• ✅ No data collection or tracking
• ✅ No external dependencies
• ✅ Pure HTML/CSS/JavaScript
• ✅ Educational purposes only — learn SQL concepts safely

MIT License — see LICENSE file for details.

• MySQL — Original SQL syntax and features
• PostgreSQL — Window functions inspiration
• SQLite — In-memory database concept
• IBM Data Science Curriculum — SQL learning objectives
• JetBrains — Mono font for coding

• GitHub Issues: Create an issue
• Website: https://willie-conway.github.io/MySQL-Simulator-Pro/

• Add window functions (RANK, LAG, LEAD)
• Implement stored procedures
• Add triggers simulation
• Include views with materialization
• Add user-defined functions
• Implement query cache simulation
• Add more sample datasets
• Include SQL injection demo (safe)
• Add normalization exercises
• Include database design patterns

🗄️ MySQL Simulator Pro — The Ultimate SQL Learning Environment 🗄️

Last updated: March 2025