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.