sqlsim v0.0.1

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

• MySQL

More to come!

Keywords Supported

General

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

• 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)

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

Aggregation Functions

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