SQLSim

SQL Simulator in Javascript. Performance and storage space be damned! This is an in-memory simulator meant to show how SQL works, allowing inspection of database state at any (and every!) time during execution.

Install

Clone the repository then run the following commands:

$ bun install
$ bun test

Don't have bun? You can get it here. If you don't want to use bun, you can also easily use npm or yarn instead.

Supported SQL Flavors

• [x] MySQL

More to come!

Keywords Supported

General

• [x] CREATE TABLE (see below)
• [x] INSERT INTO Table VALUES ... (all-column insert)
• [x] INSERT INTO Table (col1, col2, ...) VALUES ... (column-specific inserts)
• [x] UPDATE Table SET _col1 = ...; (update all records)
• [x] UPDATE Table SET _col1 = ..., col2 = ... WHERE ... (single and multi column updates with filtering)
• [x] SELECT * FROM Table (basic selection)
• [x] SELECT col1, col2, ... FROM Table (column filtering via projection)
• [x] SELECT col3, col3, col3 FROM Table (column expansion via projection)
• [x] SELECT (col2 + 5) FROM Table (expressions via projection)
• [x] SELECT ... FROM Table WHERE expression (row filtering via expressions, see below)
• [x] SELECT ... FROM Table JOIN AnotherTable ON expression (inner joins)
• [x] SELECT ... FROM Table LEFT JOIN AnotherTable ON expression (left joins)
• [x] SELECT ... FROM Table RIGHT JOIN AnotherTable ON expression (right joins)
• [x] SELECT ... FROM Table FULL JOIN AnotherTable ON expression (full joins)
• [x] SELECT ... FROM Table CROSS JOIN AnotherTable (cross join)
• [x] SELECT Table1.col1, Table2.col2 FROM Table1 JOIN Table2 ON ... (table name prefixing)
• [x] SELECT ... FROM Table AS NewName (AS for table names)
• [x] SELECT col1 AS newname FROM ... (AS for column names)
• [ ] SELECT 5 AS newname (selecting literals with AS for dymanic table creation)
• [x] SELECT ... FROM Table WHERE (SELECT ...) (subqueries in the WHERE clause)
• [x] SELECT ... FROM Table WHERE col1 > (SELECT AVG(col1) FROM ... WHERE col2 = Table.col2) (correlated subquery)
• [x] SELECT ... FROM (SELECT ...) (subqueries in the FROM clause)
• [ ] SELECT _(SELECT ...) FROM ... (subqueries in the projection list)
• [x] SELECT ... FROM ... ORDER BY col1 ASC, col2 DESC, ... (single and multi-column row ordering, with direction)
• [x] SELECT ... FROM ... ORDER BY col1 > 5, ... (single and multi-column row ordering with expressions)
• [x] SELECT FUNC(col1) FROM Table (simple aggregation)
• [x] SELECT FUNC(col1 + 100) FROM Table (aggregation with expressions)
• [x] SELECT FUNC(col1) + 100 FROM Table (aggregation as expressions)
• [x] SELECT groupCol, FUNC(col1) FROM Table GROUP BY (aggregation with grouping)
• [x] SELECT groupCol, FUNC(col1) FROM Table GROUP BY ... HAVING ... (with grouping and filtering)

CREATE TABLE

• [ ] Data type constraints (e.g., INTEGER, VARCHAR(20), etc.)
• [ ] AUTO_INCREMENT constraint
• [ ] UNIQUE constraint
• [ ] NOT NULL constraint
• [ ] PRIMARY KEY (col1) constraint (single primary key)
• [ ] PRIMARY KEY (col1, col2, ...) constraint (composite primary key)
• [ ] FOREIGN KEY ... REFERENCES ... constraint
• [ ] ON UPDATE constraint
• [ ] ON DELETE constraint
• [ ] CHECK constraints

Expressions (e.g., in WHERE clause)

• [x] Column references (e.g., age > ...)
• [x] + operator
• [x] - operator
• [x] / operator
• [x] * operator
• [x] = operator
• [x] != operator
• [x] < operator
• [x] <= operator
• [x] > operator
• [x] >= operator
• [x] <> operator
• [x] AND operator
• [x] OR operator
• [x] IS operator (Note: only TRUE/FALSE supported; UNKNOWN not yet supported)
• [x] IS NOT operator (Note: only TRUE/FALSE supported; UNKNOWN not yet supported)
• [x] IN (...expression list...) operator
• [x] IN (...subquery...) operator
• [x] NOT IN (...expression list...) operator
• [x] NOT IN (...subquery...) operator
• [x] LIKE operator
• [ ] ANY operator
• [ ] SOME operator

Aggregation Functions

• [x] AVG()
• [ ] AVG(DISTINCT)
• [ ] BIT_AND()
• [ ] BIT_OR()
• [ ] BIT_XOR()
• [x] COUNT()
• [ ] COUNT(DISTINCT)
• [ ] GROUP_CONCAT()
• [ ] GROUP_CONCAT(DISTINCT)
• [ ] JSON_ARRAYAGG()
• [ ] JSON_OBJECTAGG()
• [x] MAX()
• [ ] MAX(DISTINCT)
• [x] MIN()
• [ ] MIN(DISTINCT)
• [ ] STD()
• [ ] STDDEV()
• [ ] STDDEV_POP()
• [ ] STDDEV_SAMP()
• [x] SUM()
• [ ] SUM(DISTINCT)
• [ ] VAR_POP()
• [ ] VAR_SAMP()
• [ ] VARIANCE()