Interesting JavaScript - SQLite (sql.js)

February 18, 2016 by Christoff Truter JavaScript  

Recently while receiving my required dosage of ketracel-white in the kitchen at work, one of my fellow Jem'Hadar told me about a little standalone HTML5 hybrid mobile application he is busy writing.

The part I found curious was his choice in data storage, he opted to use SQLite, which in itself isn't unusual to native mobile development, but it takes a bit of "special care" when used in conjunction with hybrids like HTML5.

Since there is no web server involved (AJAX removed from the equation), my first instinct was that he used something like Apache Cordova (artist formally known as PhoneGap) and connected to a SQLite database via the app container using a plugin.

Another possibility was that he was in fact using Web SQL (apparently just a SQLite wrapper itself) instead.

Turns out it is a lot more interesting than that.

He used a port of SQLite to JavaScript named sql.js and what is more is that the port was compiled from C code to JavaScript (using Emscripten a LLVM-based project) - VERY impressive.

In the snippet below you can see a very simple example of the library in action.

var db = new SQL.Database();
db.run('CREATE TABLE friends (id INTEGER PRIMARY KEY, name TEXT)');
db.run('INSERT INTO friends VALUES (NULL, ?)', ['Gerhard Stander']);

Now the immediate concern is, where does this library physically store our data?

The answer is it doesn't, that part is your problem, they do however have a nice little example on their wiki page on how to persist the generated database using localStorage.

I used their example and created a little wrapper / adapter to simplify persistence of data.

var db = new SQL.PersistentDatabase('myDatabase', 
	function(sender) { // Initial creation of database if not found
		sender.run('CREATE TABLE friends (id INTEGER PRIMARY KEY, name TEXT)');
		sender.run('INSERT INTO friends VALUES (NULL, ?)', ['Gerhard Stander']);
		sender.save();
	}, 
	function(e) { // Initialization of existing database failed
		alert(e);
	}
);

In the snippet below you can see my little wrapper function.

SQL.PersistentDatabase = function(name, success, failure) {
	var adapter, data = window.localStorage.getItem(name), result = null;
	if (data !== null) { // Fetch existing database from localStorage
		result = [];
		for (var i = 0, size = data.length; i < size; i++)
			result.push(data.charCodeAt(i));
		result = new Uint8Array(result);
	}
	try {
		adapter = new SQL.Database(result);
		
		/* Attempt to query the database, this gives us an early 
		   warning if something is wrong with the retrieved database */
		adapter.run('SELECT name FROM sqlite_master');
		
		/*
			Add a save function to our database object, this method must
			be called whenever you are ready to commit data, preferably
			as little as possible.
		*/
		adapter.save = function () {
			var result = adapter.export(), strings = [], chunksize = 0xffff;
			for (var i = 0; i * chunksize < result.length; i++)
				strings.push(String.fromCharCode.apply(null, result.subarray(i * chunksize, (i + 1) * chunksize)));
			window.localStorage.setItem(name, strings.join(''));
		};
	} catch(e) {
		// Callback executed for when life happens
		if (failure !== undefined) failure(e);
		return null;
	}
	// Callback executed when the database is initially created
	if ((result === null) && (success !== undefined)) success(adapter);
	
	return adapter;
}

The most important thing to note (as seen in the script comments) is the use of the save function, this function must be called as little as possible as this would perform a complete database dump to localStorage every time it is called. It must only be used when you are ready to commit data.

I must be honest though...

Personally I would never willingly use this library, no offence.

Don't get me wrong, I find the whole C Code to JavaScript compilation highly impressive (orgasmic even), I am not even completely against the fact that you can't fit this library on a 1.44 MB floppy disk (except if I use ARJ), but I would personally use the alternatives if needed.

Ask yourself, how much data manipulation do I really need to do on my frontend that requires that I use SQL queries in my JavaScript (am I not trying to kill an ant with a nuclear weapon)?

If I had to write a standalone hybrid mobile app that requires the use of a database, I would consider using IndexedDB (support is a bit crappy at the moment though); I would avoid using Web SQL altogether since it will likely be dropped in the near future. My personal choice would likely be something like Apache Cordova (or a similar technology), where I get to use the real thing.

But I rarely write standalone web applications either way, I mostly connect to some kind of server (be it SOAP or REST), unless we're looking at adding some offline capabilities (viva la manifest) ?

I would maybe consider using this library in a NodeJS environment though, but then again, better alternatives exist there as well.