DailyJS

Alasql.js: In-memory SQL

2014-11-06 00:00:00 +0000 by Alex R. Young

I recently wrote about an in-memory database called LokiJS. Most in-memory databases are NoSQL, but there are also similar projects that use SQL. Andrey Gershun sent in Alasql.js (GitHub: agershun/alasql, License: MIT, npm: alasql), a relational database that runs in browsers and Node.

It's designed to be fast, and was created partly by using a Jison file that produces a parser for SQL statements. The author has made some benchmarks so you can compare it to other projects:

This project is different to WebSQL because it doesn't use the Storage API. It has an impressive set of features, including joins, group by, union, and subqueries. It even has partial support for transactions, so it's not a trivial implementation.

The API allows JavaScript objects to be mixed with SQL statements, so it's easy to create test data:

var alasql = require('alasql');

var db = new alasql.Database();

db.exec('CREATE TABLE test (one INT, two INT)');
db.tables.test.data = [
  { one: 3, two: 4 },
  { one: 5, two: 6 }
];

var res = db.exec('SELECT * FROM test ORDER BY two DESC');
console.log(res[0].one);

To make it fast, the authors have used various optimisation strategies:

So in a way it has a query planner like more established databases.

The readme includes a list of similar projects, details on the tests (they use Mocha), and there's a to-do list where you can see what Andrey is planning.