Analyse Firebase data locally with alasql

TL;DR: Run SQL-like queries on nested JSON blobs using AlaSQL.js. This library is especially useful for easily doing advanced data analysis on exports from Firebase Realtime Database or other JSON stores.

Motivation

So, you’ve set up a sweet little web app, using Firebase Realtime Database as the backend, and successfully deployed it to the Internet. But what’s that? After playing with it for a bit, you realise some displayed data is inconsistent. Hmm. What’s going on? You open up the browser console, look around for exceptions but can’t find anything. Then you look at your data in firebase, manually drilling through the firebase console or writing little JavaScript snippets in the browser’s console, sending multiple reads and calculate results using Promise.all(...), forEach, filter, reduce and more, trying to find something that doesn’t look right...

If you’ve been there, have a look at how AlaSQL.js can prove invaluable as it allows you to query your local JSON export using a SQL-like query language. It has helped me quite a few times, on side-projects as well as while building cool new features at wondary.com.

Note: Most of the sample queries shown below could, of course, be answered by a single, simple firebase query — in case you flattened, structured and updated your data accordingly (see best practices for data structure). Those examples below are for the cases where you don’t have the data that way; they are about making ad-hoc queries for small-scale analytics or debugging purposes.

Setup

The package is available on npm. Therefore all you need to do is installing it as a dependency using yarn (or npm — if you prefer).

yarn add alasql

Querying data

We’ll start by looking at a simple example. Let’s say your export firebase-export.json looks something like this:

Simple queries

With aboves data, you can query for all users as follows. Note that we are just passing a reference to the nested userPrivate collection as an argument to alasql, basically telling it to use the collection as its first table:

The output looks a little confusing at first, as the SELECT returns an array of objects, where each object has two entries:

• '0' includes the key of the item in the firebase collection
• '1' includes the actual data as key value pairs

Let’s use that info and build a little more complex query, i.e. search users by their first name and print their id and last name:

That output looks better, doesn’t it?

Using JavaScript functions

If you need to answer some specific data analytics questions where the provided functions are not sufficient, you can call standard JavaScript functions or define your own functions. This can be useful both for selecting data as well as pre-processing outputs:

This snippet takes advantage of three noteworthy items:

• Defining a custom function startEndWith which uses a regex to match strings that start or end with a given char followed or preceded by at least one other character. Note: In the sample, the char is directly used in the regex, make sure to escape it if you want to search for something fancy.
• Using the custom function in the WHERE clause
• Calling firstname.toUpperCase() to make the result upper case.

Join tables

So how to join data from different nested collections? You know, like a relational database? Pretty much like in SQL — once you wrapped your head around how to reference columns and ids.

Count and Group

Counts and even count distincts are now easily possible:

And a very contrived example of GROUP BY which counts how many users have a nickname that starts with the same letter:

Conclusion

I’ve shown how to load and reference your firebase export and run simple queries, joins, aggregations on it while relying on custom functions for special requirements.

Would I recommend using this everywhere? Of course not — if your data set is large, you need to give other people access to the data, or you are dealing with sensitive data, then you are better off using some hosted or on-premise tools (and make sure you are aware of alasql’s limitations). If you are debugging your development setup or want to find out how your hobby project is used then alasql might be worth a try. I also recommend taking a look at the extensive documentation and especially at advanced features like graph support or traversing nested JSON objects.

Feel free to leave any comments below or message me. I’m looking forward to hearing about your projects, suggestions and ideas! ✌️

References

• AlaSQL.js
• Firebase Realtime Database
• Export data from Firebase
• Alternative tool: LokiJS (looks promising, haven’t tried it out yet)