sql.js: Using SQLite Databases with Firefox OS Apps

Developers coming from platforms like Android might seek for support for SQLite-like functionality in Firefox OS as well, but sadly the WebSQL specification was dropped and Firefox OS doesn’t have native SQLite support. However, you can still use SQLite through a JavaScript library, sql.js, which is SQLite compiled to JavaScript!

Warning: This approach might have performance penalties

Getting started with sql.js

Let’s first start with a blank fresh index.html with a basic template. Download sql.js from the link above (impatient ones can justclick here) and attach it as a script to your index.html. Which should look something like this.

index.html

<!doctype html>
<html>
...
<body>
<script src="js/sql.js"></script>
</body>
</html>

<!doctype html>

Now we have SQLite functionality available to our app! Now to be able to execute our own code, let’s add our very own JavaScript file to our app, I am willing to name it dbHelper.js, you can name it as you like, and again attach to index.html.

index.html

<!doctype html>
<html>
...
<body>
<script src="js/sql.js"></script>
<script src="js/dbHelper.js"></script>
</body>
</html>

We are all set to go, let’s dive in!

Creating the Database

Before we get started with populating tables with data to save the world from meteoroids, we need to create a database for those tables to be in.

dbHelper.js

var db = new SQL.Database();

And there you have it, you have an SQLite database ready to interact with!

Executing Queries

Now that we have a database, we can execute some queries for populating data, making tables or anything SQLite is capable of.db.run*(query)*is the way to execute queries on your database if you don’t need any returns from the query.

dbHelper.js

var le_query = "CREATE TABLE Meteoroids (id int, name char, distance int);";db.run(le_query);

Or, if you love one-liners,

dbHelper.js

db.run("CREATE TABLE Meteoroids (id int, name char, distance int);");

However, if you are expecting the query to return something, instead you should usedb.exec(query).

dbHelper.js

var result = db.exec("SELECT * from Meteoroids;");

Saving the database

You app might need to (and probably will need to) save that database somewhere for future use and you have a variety of choices to choose from. Before going anywhere else, exporting our database into an Uint8Array is probably a good thing.

var binaryArray = db.export();

Now we can make a Blob out of it to save it as a file on the device storage, or we can use localStorage/IndexedDB (or localForage.js, a fantastic library to handle complications for us).

var sqlFile = new Blob(binaryArray);

Opening the database from a Uint8Array

Again, after saving the database, opening it up will probably be required sometimes in the future. First, retrieve yourUint8Arrayfrom wherever and no matter how you stored it.

var binaryarray = this_is_sparta(); // somehow you get the Uint8Array

And, getting our Database ready is as simple as,

var db = SQL.Database(binaryarray);

And now that we are done with the very basics of sql.js, we can explore a bit more of what this library has to offer. Reading the documentation and example code would now make some sense to you.