AlaSQL — a query language for JavaScript

- When you need to find data fast

If you are ever need to temporarily store data for quick retrieval and want a better alternative to a full database or Redis, you should check out AlaSQL!

So what is this magical thing? AlaSQL is an open-source SQL-inspired “database” for JavaScript that stores relational data in memory. It works in your browser and Node.js, and is focused on query speed and datasource flexibility. Schemas are not required and you can import data stored in JSON, LocalStorage, and a few other sources. You can even write your own functions to combine with the built-in AlaSQL queries!

Why use it?

I used AlaSQL when I needed to restructure data storage for a scalable multi-perspective WebRTC application into a horizontal structure. I needed a way to query data quickly, and didn’t want to use a traditional database because I only needed data to be stored temporarily. AlaSQL works with nested objects, however querying nested data may be slightly more complex. AlaSQL stores records in a table format, and if you’re familiar with SQL databases, you’re at an advantage because the commands and syntax are fairly similar.

AlaSQL is a great tool, but be prepared to do some hunting on the query syntax. I’ve included some basic CRUD syntax below to get you started.

AlaSQL Quick Start Cheat Sheet

Question mark (?):

The question mark is a placeholder. The queries all start off with ‘alasql’ followed by parens containing a string and an optional array containing placeholders.

alasql(‘query ? goes here ?’, [thisDataRepresentsFirst?, thisDataRepresentsSecond?]);

Create a table:

alasql(‘CREATE TABLE tableName’);

Create one record:

alasql(‘INSERT INTO tableName VALUES ?’, [itemToInsert]);

Note: VALUES is still plural regardless if you’re only adding a single record.

Find a record:

The example below uses a boolean and subquery. The inner query says “select everything from nodeTracker where the length of the leechers array is less than 3 and the socketId is in the array named broadcasterIds, and return the result as the value of the key ‘hasCap’.” If the output returns something, the ‘select exists’ that wraps the subquery will return a number 1 (representing true) or number 0 (representing false).

alasql(‘SELECT EXISTS(SELECT * FROM ? WHERE arrlen(leechers) < 3 AND socketId in @(?)) AS hasCap’, [nodeTracker, broadcasterIds]);

Edit a record:

alasql(‘UPDATE tableName SET keyToUpdate = ? WHERE keyIdentifier = ?’, [valueThatKeyShouldBeUpdatedTo, valueOfKeyIdentifier]);

Delete a record:

alasql(‘DELETE FROM tableName WHERE keyToMatch = ?’, [“valueOfKeyToMatch”]);

Create a custom function:

Below is an example of how to create a function that calculates the length of an array.

alasql.fn.arrlen = function(arr) { return arr.length; };

An example of how to use that function in a query is below. In English, it means “Look in the table nodeTracker, find a node with a leechers array length of less than 3 and has a socketId in the array broadcasterIds, and return that socketId”.

alasql(‘VALUE OF SELECT socketId FROM nodeTracker WHERE arrlen(leechers) < 3 and socketId IN @(?)’, [broadcasterIds]);

Have fun with AlaSQL!