The Official Ionic Blog

Build amazing native and progressive web apps with HTML5

Ahh, SQLite, the plugin that everyone wants. The SQLite plugin for Cordova is a very popular plugin that allows devs to bypass browser-based storage by creating a full SQLite DB. But the plugin can be intimidating, especially for those who’ve never dealt with database queries before. Thankfully, we can use PouchDB as an abstraction. Check it out!

Here’s some of the code I used to set up PouchDB:

.service('DBService', function($q) {
  var items;
  var db;
  var self = this;

  this.initDB = function() {
    return db = new PouchDB('simpleDB', {
      adapter: 'websql'
    });
  };

  this.getDB = function() {

    if (!items) {
      return $q.when(
          db.allDocs({
            include_docs: true
          }))
        .then(function(docs) {
          items = docs.rows.map(function(row) {
            row.doc.Date = new Date(row.doc.Date);
            return row.doc;
          });

          // Listen for changes on the database.
          db.changes({
              live: true,
              since: 'now',
              include_docs: true
            })
            .on('change', function(change) {
              self.onDatabaseChange(change)
            });
          return items;
        });
    } else {
      return $q.when();
    };

  };

  this.onDatabaseChange = function(change) {
    var index = self.findIndex(items, change.id);
    var item = items[index];

    items.splice(index, 0, change.doc) // insert
  }

  this.findIndex = function(array, id) {
    var low = 0,
      high = array.length,
      mid;
    while (low < high) {
      mid = (low + high) >>> 1;
      array[mid]._id < id ? low = mid + 1 : high = mid
    }
    return low;
  }

  this.storeData = function(data) {
    return $q.when(db.post({
      'title': data
    }))
  };

  return this
})

Conclusion

While you could still use SQLite without PouchDB, the built-in adapter makes the process much easier. You can write to your data store like you would an regular JavaScript object, knowing that it will translate over to a SQL data store. Hopefully, this screencast will simplify the process of using the SQLite plugin in your app! Thanks for watching.

  • Mladen Petrovic

    The biggest problem no one talks about is the performance issue. Simplest queries are taking minimum 300ms and if you add 10000 rows and do a SELECT * it takes more then 15 seconds to return.
    Also WebSQL is not supported on Samsung based devices.

    • Mike Hartington

      All good points. Something I do plan on addressing in a following post/video

    • Jusuf Darmawan

      I am having the same problem. The more rows the longer to SELECT. What do you recommend the best database for handling big data. Thanks.

      • Mladen Petrovic

        Yeah, its crazy that even on simple queries it would be faster to remotely from some web server.
        You may find LokiJS useful (lokijs.org) its in memory NoSQL DB with adapters for persistence in localStorage or JSON on File system. It has some really cool features like Dynamic view and changes API for syncing. And its not only useful for Cordova, can be used with NodeJS or in browser.

      • http://nicholls.azurewebsites.net/ Juan David Nicholls

        Currently I’m working with 25000 records, see my example: https://github.com/jdnichollsc/Ionic-Starter-Template

        • Uptown Jorge

          Hello Juan, Might you be able to assist? I saw you made a commit recently. I am still confused how to implement this.

    • http://nicholls.azurewebsites.net/ Juan David Nicholls
  • jon

    if you’re a developer who’s never dealt with database queries before or are intimidated by that, you’re not a developer.

  • http://nicholls.azurewebsites.net/ Juan David Nicholls

    Any example with PouchDB and SQLite cordova plugin with pre-populated databases? =)

  • alby111

    I would like to know for sure if WebSQL is not supported on Samsung based devices, because if this is so , i cant use this code

    • Young Park

      I think there’s some misunderstanding here. adapter: ‘websql’ means to use the local sqlite database, but you need “isDatabaseLocation” param as mentioned here in the document.

      https://pouchdb.com/adapters.html

  • Periyasamy M

    How to i find my database location and how to view my database name,table name and values? sqlite file storage location where it’s Stored?i search the location but i can’t find the location!

  • veljkoz

    What I don’t like with PouchDB is that you can’t really delete anything – it just appends the flag ‘deleted’, and this can be an issue on mobile because the size would be only increasing (and without sqlite we’d hit the storage limits pretty soon)