Local Storage with AlaSQL in Cordova/Ionic Framework

You are building a mobile application with Ionic Framework using ionic serve to test it in a browser, most likely, you will need to store some data.

We can store in a SQLite database, however, your application will not work in a browser, because they dont support it.

So you think in making use of local storage to pull it off. But we all know that it's just a key/value storage, and we need to store relational data.

I came across this situation last week and the best approach I found was to use the great AlaSQL library to reach my goal.

This JavaScript library allows us to perform queries like that:

alasql("SELECT * FROM products WHERE category_id = ?", [1]);

Yeah! It's SQL in browser my friend. They support many kinds of storage like localStorage, indexedDB, text files (JSON, CSV) etc.

In my www/js/app.js file I placed the database bootstrap like below:

alasql('CREATE localStorage DATABASE IF NOT EXISTS db');
alasql('ATTACH localStorage DATABASE db');
alasql('USE db');

alasql("CREATE TABLE IF NOT EXISTS products (id INT, category_id INT, name string, created_at DATE)");
alasql("CREATE TABLE IF NOT EXISTS categories (id INT, name string)");

Then we can manipulate some data in controllers:

var id         = $scope.id;
var categoryId = $scope.categoryId;

alasql("INSERT INTO products (id, category_id, name, created_at) VALUES (?,?,?,?)", [parseInt(id), parseInt(categoryId), 'XYZ', new Date()]);

alasql("SELECT * FROM products WHERE id = ?", [parseInt($state.params.id)]);

alasql("SELECT * FROM products ORDER BY created_at DESC");

See that we are using parseInt() to ensure that int fields will receive a proper value. You can have problems like I had if dont care about it.

That is all, see you.

Written on March 30, 2015