Using Sqlite in Ionic Framework

Posted on

I’ve been using Ionic Framework (v1) to build hybrid mobile apps for a while now, and I love it’s flexibility and power. For local storage, lately I’ve been working more and more with Sqlite, which allows me to cache data on client apps that can be manipulated easily using standards compliant SQL commands.

In this tutorial, I will build a very basic Contacts list using a service that handles the Sqlite database configuration and interactions. Special thanks to Julien Goux, whose work I adapted to build this implementation.

  1. For this example, I started with the Ionic tabs starter template. 
    ionic start myApp tabs
  2. Add the Cordova Sqlite Storage plugin. 
    ionic plugin add cordova-sqlite-storage
  3. Add a service file to your application to interface with Sqlite. 
    angular.module('app.services', ['app.config'])
.factory('DB', function($q, DB_CONFIG) {
    var self = this;
    self.db = null;
    self.init = function() {
        if (window && window.sqlitePlugin) { self.db = window.sqlitePlugin.openDatabase({name: DB_CONFIG.name}); }
        else { self.db = window.openDatabase(DB_CONFIG.name, '1.0', 'database', -1); }
        angular.forEach(DB_CONFIG.tables, function(table) {
            var columns = [];
            angular.forEach(table.columns, function(column) {
                columns.push(column.name + ' ' + column.type);
            });
            var query = 'CREATE TABLE IF NOT EXISTS ' + table.name + ' (' + columns.join(',') + ')';
            self.query(query);
        });
    };
    self.query = function(query, bindings) {
        bindings = typeof bindings !== 'undefined' ? bindings : [];
        var deferred = $q.defer();
        if (!self.db) { self.init(); }
        self.db.transaction(function(transaction) {
            transaction.executeSql(query, bindings, function(transaction, result) {
                deferred.resolve(result);
            }, function(transaction, error) {
                deferred.reject(error);
            });
        });
        return deferred.promise;
    };
    self.fetchAll = function(result) {
        var output = [];
        for (var i = 0; i < result.rows.length; i++) {
            output.push(result.rows.item(i));
        }
        return output;
    };
    self.fetch = function(result) {
        return result.rows.item(0);
    };
    return self;
})
// Methods to interact with a table
.factory('Table', function(DB) {
    var self = this;
    self.getAll = function(tableName) {
        var query = 'SELECT * FROM ' + tableName;
        return DB.query(query).then(function(result){ return DB.fetchAll(result); });
    };
    self.getAllOrderedByColumn = function(tableName, columnName, descending) {
        var query = 'SELECT * FROM ' + tableName + ' ORDER BY ' + columnName
        if (descending) { query = query + ' DESC' }
        return DB.query(query).then(function(result){ return DB.fetchAll(result); });
    };
    self.getById = function(tableName, idColumnName, id) {
        var sql = "SELECT * FROM " + tableName + " WHERE " + idColumnName + " = ?";
        return DB.query(sql, [id])
        .then(function(result){
            if (result.rows.length > 0) {
                return DB.fetchAll(result);
            }
            else {
                return null;
            }
        });
    };
    self.getByQuery = function(query) {
        return DB.query(query)
        .then(function(result){
            if (result.rows.length > 0) {
                return DB.fetchAll(result);
            }
            else {
                return null;
            }
        });
    };
    self.create = function(obj) {
        var sql = obj.getInsertStatement();
        var params = obj.getInsertParams();
        return DB.query(sql, params)
        .then(function(result) { return result; });
    }
    self.deleteById = function(tableName, idColumnName, id) {
        var sql = "DELETE FROM " + tableName + " WHERE " + idColumnName + " = ?";
        return DB.query(sql, [id])
        .then(function(result){ return result; });
    }
    self.update = function(obj) {
        var sql = obj.getUpdateStatement();
        var params = obj.getUpdateParams();
        return DB.query(sql, params)
        .then(function(result) { return result; });
    }
    self.delete = function(obj)
    {
        var sql = obj.getDeleteStatement();
        return DB.query(sql)
        .then(function(result){ });
    }
    self.executeSqlStatement = function(sql) {
        return DB.query(sql)
        .then(function(result){ return result; });
    }
    return self;
});

     

  4. Add a configuration file to your application, which will store the database structure. In this example, we’re building a simple contacts list. 
    angular.module('app.config', [])
    .constant('DB_CONFIG', {
        name: 'DB',
        tables: [
            {
                name: 'contact',
                columns: [
                    { name: 'id', type: 'integer primary key autoincrement' },
                    { name: 'firstName', type: 'text' },
                    { name: 'lastName', type: 'text' },
                    { name: 'middleName', type: 'text' },                    
                    { name: 'title', type: 'text' },
                    { name: 'company', type: 'text' },
                    { name: 'phone', type: 'text' },
                    { name: 'email', type: 'text' },
                    { name: 'address', type: 'text' },
                    { name: 'city', type: 'text' },
                    { name: 'state', type: 'text' },
                    { name: 'zip', type: 'text' }
                ]
            }
        ]
    });

     

  5. Initialize your database on app.run. Configure routes to “Contacts” controller. 
    angular.module('starter', ['ionic', 'starter.controllers', 'starter.services', 'app.services'])
.run(function($ionicPlatform, DB) {
  $ionicPlatform.ready(function() {
    // Initialize SQLite Database
    DB.init();
    ....
  });
})

.config(function ($stateProvider, $urlRouterProvider) {
    $stateProvider
      .state('tab', {
        url: '/tab',
        abstract: true,
        templateUrl: 'templates/tabs.html'
      })
      ....
      .state('tab.contacts', {
        url: '/contacts',
        views: {
          'tab-contacts': {
            templateUrl: 'templates/tab-contacts.html',
            controller: 'ContactsCtrl'
          }
        }
      })
      .state('tab.contact-detail', {
        url: '/contacts/:Id',
        views: {
          'tab-contacts': {
            templateUrl: 'templates/contact-detail.html',
            controller: 'ContactDetailCtrl'
          }
        }
      });

     

  6. Add the Contacts tab to your Tabs menu. This will replace the Chat tab. 
    <ion-tabs class="tabs-icon-top tabs-color-active-positive">
  <!-- Dashboard Tab -->
  <ion-tab title="Status" icon-off="ion-ios-pulse" icon-on="ion-ios-pulse-strong" href="#/tab/dash">
    <ion-nav-view name="tab-dash"></ion-nav-view>
  </ion-tab>

  <!-- Contacts Tab -->
  <ion-tab title="Contacts" icon-off="ion-ios-people-outline" icon-on="ion-ios-people" href="#/tab/contacts">
    <ion-nav-view name="tab-contacts"></ion-nav-view>
  </ion-tab>

  <!-- Account Tab -->
  <ion-tab title="Account" icon-off="ion-ios-gear-outline" icon-on="ion-ios-gear" href="#/tab/account">
    <ion-nav-view name="tab-account"></ion-nav-view>
  </ion-tab>
</ion-tabs>

     

  7. Add a model for your object. This allows us to encapsulate the table-specific sql in  our models, preventing duplication of the code in the Table service. 
    function Contact (o) {
    this.tableName = "contact";
    this.keyFieldName = "id";
    
    this.id = 0;
    this.firstName = "";
    this.lastName = "";
    this.middleName = "";
    this.title = "";
    this.company = "";
    this.phone = "";
    this.email = "";
    this.address = "";
    this.city = "";
    this.state = "";
    this.zip = "";

    this.parseObject = function(o) {
        if (!!o.id) { this.id = o.id; }
        if (!!o.firstName) { this.firstName = o.firstName; }
        if (!!o.lastName) { this.lastName = o.lastName; }
        if (!!o.middleName) { this.middleName = o.middleName; }
        if (!!o.title) { this.title = o.title; }
        if (!!o.company) { this.company = o.company; }
        if (!!o.phone) { this.phone = o.phone; }
        if (!!o.email) { this.email = o.email; }
        if (!!o.address) { this.address = o.address; }
        if (!!o.city) { this.city = o.city; }
        if (!!o.state) { this.state = o.state; }
        if (!!o.zip) { this.zip = o.zip; }
    }
     this.getInsertStatement = function() {
        return "INSERT INTO " + this.tableName +  
        "(firstName, lastName, middleName, title, company, phone, email, address, city, state, zip)" +
        " Values (? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)" ;
    }
	this.getInsertParams = function() {
        return [
        this.firstName,
        this.lastName, 
        this.middleName,
        this.title,
        this.company,
        this.phone,
        this.email,
        this.address,
        this.city,
        this.state,
        this.zip
		];
    }
    this.getUpdateStatement = function() {
        return "UPDATE " + this.tableName + 
        " SET firstName = ? " + 
        ", lastName = ? " + 
        ", middleName = ? " + 
        ", title = ? " +
        ", company = ? " +
        ", phone = ? " + 
        ", email = ? " + 
        ", address = ? " + 
        ", city = ? " + 
        ", state = ? " + 
        ", zip = ? " + 
        " WHERE " + this.keyFieldName + " = ? ";
    }
    this.getUpdateParams = function () {
        return [
            this.firstName,
            this.lastName, 
            this.middleName,
            this.title,
            this.company,
            this.phone,
            this.email,
            this.address,
            this.city,
            this.state,
            this.zip,
            this.id
		]; 
    }
    this.getDeleteStatement = function() {
        return "DELETE FROM " + this.tableName + " WHERE " + this.keyFieldName + " = '" + this.id + "' ";
    }
    this.GetDirtyStatement = function() {
        return 'SELECT * FROM ' + this.tableName + ' WHERE IsDirty = 1;'; 
    }
    
    if (!!o) {
        this.parseObject(o);
    }
}

     

  8. In your controller, consume tthe Table service to interact with Sqlite. 
    angular.module('starter.controllers', [])
  .controller('ContactsCtrl', function ($scope, $ionicModal, Table) {
    $scope.Init = function () {
      // Retrieve from Sqlite
      Table.getAll(new Contact().tableName).then(function (contacts) {
        var o = [];
        angular.forEach(contacts, function (contact) {
          o.push(new Contact(contact));
        })
        $scope.contacts = o;
      })
      $ionicModal.fromTemplateUrl('templates/modals/contact-add.html', { 
        scope: $scope, animation: 'slide-in-up'
      }).then(function (modal) {
        $scope.AddModal = modal;
      });
    }
    $scope.remove = function (contact) {
      // Delete from Sqlite
      Table.deleteById(new Contact().tableName, new Contact().keyFieldName, contact.id).then(function (contacts) {
        $scope.contacts.splice($scope.contacts.indexOf(contact), 1);
      });
    };
    $scope.add = function () {
      $scope.newContact = new Contact();
      $scope.AddModal.show();
    };
    $scope.save = function () {
      // Save to Sqlite database:
      Table.create($scope.newContact).then(function (result) {
        if (result) {
          $scope.newContact.id = result.insertId;
          $scope.contacts.push($scope.newContact);
        }
        $scope.AddModal.hide();
      });
    }
    $scope.Init();
  });

     

  9. Add your HTML templates 
    <ion-view view-title="Contacts">
  <ion-content>
    <ion-list>
      <ion-item class="item-remove-animate item-avatar item-icon-right" ng-repeat="contact in contacts" type="item-text-wrap" href="#/tab/contacts/{{contact.id}}">
        <h2>{{contact.firstName}} {{contact.lastName}}</h2>
        <p>{{contact.title}} <span ng-if="contact.company"> - {{contact.company}}</span></p>
        <i class="icon ion-chevron-right icon-accessory"></i>

        <ion-option-button class="button-assertive" ng-click="remove(contact)">
          Delete
        </ion-option-button>
      </ion-item>
    </ion-list>
    <button class="button button-full button-stable" ng-click="add()">Add</button>
  </ion-content>
</ion-view>
    <ion-modal-view>
    <ion-content class="padding">
    <div class='row'>
        <div class='col'>
            <span class='editor-label'>First Name</span>
            <label class="item item-input">
                <input type='text' ng-model="newContact.firstName"></input>
            </label>
        </div>
    </div>
    <div class='row'>
        <div class='col'>
            <span class='editor-label'>Last Name</span>
            <label class="item item-input">
                <input type='text' ng-model="newContact.lastName"></input>
            </label>
        </div>
    </div>
    <div class='row'>
        <div class='col'>
            <span class='editor-label'>Title</span>
            <label class="item item-input">
                <input type='text' ng-model="newContact.title"></input>
            </label>
        </div>
    </div>
    <div class='row'>
        <div class='col'>
            <span class='editor-label'>Company</span>
            <label class="item item-input">
                <input type='text' ng-model="newContact.company"></input>
            </label>
        </div>
    </div>
    <div class="row">
        <div class="col" style="text-align: center">
            <button class="button button-full button-stable" ng-click="save()">Save</button>
        </div>
    </div>
  </ion-content>
</ion-modal-view>

     

  10. Run the app, and click the Contacts tab. You will see an empty list, click the button to add a contact.
    contacts-empty contacts-add contacts-one

 

While this app is very basic, it shows you how to interact with the Sqlite database with minimal effort.  By putting the Insert and Update SQL in your classes, you can use an abstract interface to Sqlite.

In a future post, I will show a method for rolling back changes that have been made to data in Sqlite.

One thought on “Using Sqlite in Ionic Framework”

  1. Pingback: Adding Undo Functionality to Ionic Using Sqlite

Leave a Reply

Your email address will not be published. Required fields are marked *

↑
©2024 eimagine. All rights reserved.