DailyJS

Alasql.js: In-memory SQL

Alex R. Young

Subscribe

@dailyjs

Facebook

Google+

libraries node modules database sql

Alasql.js: In-memory SQL

Posted by Alex R. Young on .
Featured

libraries node modules database sql

Alasql.js: In-memory SQL

Posted by Alex R. Young on .

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:

  • Compiled queries are cached
  • Joined tables are pre-indexed
  • WHERE expressions are pre-filtered for joins

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.