SQL inMemory.js

SQL inMemory.js is a full SQL engine written in JavaScript.

You can test it live on http://launix.de/sqlmemjs/

You can also look at the documentation at http://launix.de/sqlmemjs/doc

Download:

• http://launix.de/sqlmemjs/sqlinmem.js
• http://launix.de/sqlmemjs/sqlinmem.min.js

Donating

Please help this project by using the donate button under http://launix.de/donate.html

Planned features

• provide a full SQL engine
• prepared statements or queries
• full support for SELECT FROM INNER OUTER JOIN WHERE GROUP BY HAVING SORT BY and subqueries
• index joins
• runs in browser and on node.js
• safe way to provide SQL shell to web users

Build instructions

1. Install dependencies (make deps)
2. Compile the parser and the minified library (make)
3. Run the test cases (make run)

How to use

var db = new SQLinMemory();
db.query("CREATE TABLE x(id integer PRIMARY KEY AUTO_INCREMENT, name string DEFAULT 'New Item')");
var id = db.query("INSERT INTO x(name) VALUES ('Carl')").insert_id;
db.query("INSERT INTO x(name) VALUES (?), (?)", "Peter", "Paul");

var rows = db.query("SELECT * FROM x");
var row;
console.log(JSON.stringify(rows.getSchema()));
while(row = rows.fetch()) {
  console.log(JSON.stringify(row));
}

var getX = db.prepare("SELECT * FROM x where id=?");
var carl = db.query(getX, id);
console.log(JSON.stringify(carl.fetch()));
carl.close();

// interactive shell that takes semicolon separated queries
var shell = db.openShell(function(cursor) {
	cursor.printTable();
});
shell.write("SEL");
shell.write("ECT 1;");
shell.write("SELECT 2;SELECT 3");
shell.close();

The API allows the following operations:

• var db = new SQLinMemory(); - creates the database
• db.prepare(sql) - compiles the sql string into a easier to process representation
• var table = db.query(sql_or_prepared, param1, param2) - executes the query and returns a table iterator
• table.getSchema() - returns an array of all columns where a column has the form ['IDENTIFIER', 'TYPE']
• table.fetch() - returns the next tuple or undefined when the table end is reached
• table.reset() - start reading again (you can do that at any point of time)
• table.close() - release all cursors (do not fetch after close; but you can reset; close is necessary when not fetching all data and unnecessary when fetch() once returned undefined)
• table.toArray() - fetches all tuples and returns them as a JavaScript Array
• table.printTable(printLine) - prints the table in human readable form (leave printLine blank to use console.log)
• db.exportJSON() - returns a compressed JSON object of all data stored
• db.importJSON(json) - imports the previously exported tables and overrides existing tables

For more look at the JSdoc.

Supported Commands

• SHOW TABLES
• CREATE TABLE table(col1 type1 PRIMARY KEY AUTO_INCREMENT, col2 type2 DEFAULT value)
• DESCRIBE|EXPLAIN table
• DROP TABLE [IF EXISTS] table
• INSERT INTO table(col1, col2) VALUES (val11, val12), (val21, val22)
• INSERT INTO table(col1, col2) SELECT ....
• UPDATE table SET col1=val1, col2=val2
• UPDATE table SET col1=val1, col2=val2 WHERE condition
• DELETE FROM table WHERE condition
• SELECT * FROM table
• SELECT * FROM table1, table2
• SELECT table1.*, table2.col FROM table1, table2
• SELECT col1, col2+col3 FROM table
• SELECT * FROM table WHERE ID=?
• SELECT 1/2 UNION SELECT 1+2
• SELECT a+b FROM (SELECT 1 as a, 2 as b)
• SELECT 1+(SELECT 2+3)
• SELECT * FROM table WHERE value BETWEEN 1 AND 6
• SELECT * FROM table LIMIT 5
• SELECT * FROM table LIMIT 5, 15
• SELECT * FROM table ORDER BY value [DESC|ASC]
• SELECT SUM(value), COUNT(value) as cnt FROM table HAVING cnt > 3

Supported Data Types

• INTEGER
• FLOAT
• DOUBLE
• NUMBER
• TEXT
• STRING