Bradley Gore Blog

Getting Started with Cordova SQLite

Bradley Gore -

I'm working on building my first hybrid mobile application of substance and am using Cordova and a nifty sqlite plugin - cordova-sqlite-storage. This allows me to use one (or more) sqlite database files as my data storage. There are a few tips and such that I've found handy and thought I'd share here.

Setting Up

First, make sure you have cordova installed: npm install -g cordova

Next, navigate to your projects root and install the cordova sqlite plugin:
cd path/to/project
cordova plugin add https://github.com/litehelpers/Cordova-sqlite-storage

Now you're ready to create a database!

First DB File

Now that we have the plugin installed, let's create a javascript file that will act as a service layer of sorts over our database file. We'll call it widgets.db.js, and in this file we'll tap into the deviceready event so that we know we can access the sqlite plugin.

//widgets.db.js
(function() {
  var widgetsDb,
      dbLoaded;

  window.widgetDbService = {};

  document.addEventListener('deviceready', function initWidgetsDb);

  function initWidgetsDb() {
    window.sqlitePlugin //cordova sqlite plugin
      .openDatabase({
        name: 'widgets.db' //db file name
      }, function success(db) {
        window.WidgetsDb = db; //If debug build, handy to have accessible to window
        widgetsDb = db;  //private reference available only within this closure
        seedDb();  //function that seeds initial data if needed
      }, function error(e) {
        //uh oh - something odd happened...
      });
  }
})();

Good deal, we've initiated our database file, and have some variables set up to help us later. One thing to point out here is that seedDb() function call. I actually prefer seeding sqlite databases inside the javascript file, as opposed to having a file named the same (i.e. 'widgets.db') that contains SQL for seeding the database. This is for instances where I have a collection of items to seed - it's much more handy to loop over those items to seed the DB than to write them all out in SQL. Here's an example of seeding the database. Note the use of promise library, Q, here. We're making our dbLoaded variable a promise - this will come in handy later to avoid any potential race conditions of querying for items before the seeding is done.

//widgets.js seedDB function
function seedDb() {
  var defer = window.Q.deferred();
  
  //dbLoaded variable will be the promise - that way we can always safely query the db
  dbLoaded = defer.promise;

  var createSql = [
    'CREATE TABLE IF NOT EXISTS Widget (',
    'id INTEGER',
    ',name TEXT NOT NULL',
    ',color TEXT NOT NULL',
    ',sku TEXT NOT NULL',
    ',quantity INTEGER NOT NULL DEFAULT(0)',
    ',PRIMARY KEY(id)',
    ',UNIQUE(name, sku)',
    ');'
  ].join(''),

    widgets = [
      {name: 'Widget1', color: 'red', sku: 'WDG1', qty: 150},
      {name: 'Widget2', color: 'black', sku: 'WDG2', qty: 137},
      {name: 'Widget3', color: 'yellow', sku: 'WDG3', qty: 23},
      {name: 'Widget4', color: 'green', sku: 'WDG4', qty: 94},
      {name: 'Widget5', color: 'brown', sku: 'WDG5'},
    ];

  widgetsDb.transaction(function(t) {
    t.executeSql(createSql, []);

    initialWidgets.forEach(function insertOrIgnore(w) {
      t.executeSql(
        'INSERT OR IGNORE INTO Widget (name, color, sku, quantity) VALUES (?,?,?,?),
        //params array is used to populate param placeholders (?) - and in the order in which they appear
        [w.name, w.color, w.sku, w.qty]
      );
    });
  }, function error(e) {
    //typically only hit this if there are issues with your SQL statements
  }, function success() {
    defer.resolve(); //now it is ready to be queried against
  });
}

Querying the data

Remember seeing that window.widgetDbService = {}; line in the first code snippet? That's how we're going to expose this simple service to the rest of our app. Typically one would prefer something like CommonJS Modules, instead of adding to global scope, but we'll keep it super simple here. Let's add a method to get all of the widgets, and another to delete a widget by its id. Note: We'll also use the Q library so we don't have to deal with icky callbacks

//widgetsDb.js

//initial stuff here

//service definition here
window.widgetDbService.getWidgets = function getAllWidgets() {
  var defer = window.Q.defer();

  dbLoaded.then(function() {
    widgetsDb.executeSql('SELECT * FROM Widget', [], function success(res) {
      //res is an object, but it's not all our serialized widgets - we'll have to pull them out 1x1
      var allWidgets = [];
      for (var i = 0, len = res.rows.length; i < len; i++) {
        allWidgets.push(res.rows.item(i);
      }

      defer.resolve(allWidgets); //resovle promise

    }, defer.reject);
  });

  return defer.promise;
};

window.widgetDbService.deleteWidget = function deleteWidget(widgetId) {
  var defer = window.Q.defer();

  dbLoaded.then(function() {
    widgetsDb.executeSql('DELETE FROM Widget WHERE id = ?', [widgetId], 
      defer.resolve, defer.reject);
  });

  return defer.promise;
};

//seed db stuff here

And it's that easy - you now have a service module that abstracts away working with sqlite data, and even promise-ifies all the interactions so you don't have to deal with callbacks.

Stuff I wish I knew up front...

This project gave me my first opportunity to use sqlite, so there are some things I've learned the hard way and really wish I'd have known up front. Here's some of those things:

  • Error codes suck   A 1 typically means you've got an issue in your SQL and a 19 means you've got a constraint violation. The 1 is the worst, as I never saw any accompanying data on what, specifically, the error was. Anyways, here is a handy list of the codes and their purposes.
  • Each DB File Connection is Mutually Exclusive   This on really annoyed me when I realized what was going on. Any time you call window.sqlitePlugin.openDatabase(..), it's creating a database that is mutually exclusive from any other(s) that you create. Again, my first time using it and I didn't see that anywhere in the parts of documentation I read, so it blew me away when I created a new file and its DB connection couldn't see any tables that were created in another connection... In hindsight it certainly makes sense - but I would've saved a few hours researching if I'd known this up front. If you're needing to separate stuff out into multiple files but use FK constraints and such, make a singular db connection that they all share.
  • FK Relationships are not enforced by default   In sqlite v3, you have to run a PRAGMA command in order to turn on FK enforcement - db.executeSql('PRAGMA foreign-keys=ON;', []);

That's it for now - I'll be posting more as I continue building this app. I'm using the ionic framework, so I'm sure I'll be writing some more about it soon :)

-Bradley