How to use SQLite with Ionic to store data?

Hybrid Mobile apps have a challenging task of being as performant as native apps, but I always tell other developers that it depends on not the technology but how we code. The Ionic Framework is a popular hybrid app development library, which uses optimal design patterns to create awe-inspiring mobile experiences.

We cannot exactly use web design patterns to create hybrid mobile apps. The task of storing data locally on a device is one such capability, which can make or break the performance of your app. In a web app, we may use localStorage to store data but mobile apps require much more data to be stored and swift access. Localstorage is synchronous, so it acts slow in accessing the data. Also, web developers who have experience of coding in a backend language such as C#, PHP, or Java would find it more convenient to access data using SQL queries than using object-based DB.

SQLite is a lightweight embedded relational DBMS used in web browsers and web views for hybrid mobile apps. It is similar to the HTML5 WebSQL API and is asynchronous in nature, so it does not block the DOM or any other JS code. Ionic apps can leverage this tool using an open source Cordova Plugin by Chris Brody (@brodybits). We can use this plugin directly or use it with the ngCordova library by the Ionic team, which abstracts Cordova plugin calls into AngularJS-based services. We will create an Ionic app in this blog post to create Trackers to track any information by storing it at any point in time. We can use this data to analyze the information and draw it on charts. We will be using the ‘cordova-sqlite-ext’ plugin and the ngCordova library.

We will start by creating a new Ionic app with a blank starter template using the Ionic CLI command, ‘$ ionic start sqlite-sample blank’. We should also add appropriate platforms for which we want to build our app. The command to add a specific platform is ‘$ ionic platform add <platform_name>’. Since we will be using ngCordova to manage SQLite plugin from the Ionic app, we have to now install ngCordova to our app.

Run the following bower command to download ngCordova dependencies to the local bower ‘lib’ folder:

bower install ngCordova

We need to inject the JS file using a script tag in our index.html:

<script src=“lib/ngCordova/dist/ng-cordova.js"></script>

Also, we need to include the ngCordova module as a dependency in our app.js main module declaration:

angular.module('starter', [‘ionic’,’ngCordova'])

Now, we need to add the Cordova plugin for SQLite using the CLI command:

cordova plugin add https://github.com/litehelpers/Cordova-sqlite-storage.git

Since we will be using the $cordovaSQLite service of ngCordova only to access this plugin from our Ionic app, we need not inject any other plugin. We will have the following two views in our Ionic app:

• Trackers list: This list shows all the trackers we add to DB
• Tracker details: This is a view to show list of data entries we make for a specific tracker

We would need to create the routes by registering the states for the two views we want to create. We need to add the following config block code for our ‘starter’ module in the app.js file only:

.config(function($stateProvider,$urlRouterProvider){
  $urlRouterProvider.otherwise('/')

  $stateProvider.state('home', {
    url: '/',
    controller:'TrackersListCtrl',
    templateUrl: 'js/trackers-list/template.html'
  });

  $stateProvider.state('tracker', {
    url: '/tracker/:id',
    controller:'TrackerDetailsCtrl',
    templateUrl: 'js/tracker-details/template.html'
  })
});

Both views would have similar functionality, but will display different entities. Our view will display a list of trackers from the SQLite DB table and also provide a feature to add a new tracker or delete an existing one. Create a new folder named trackers-list where we can store our controller and template for the view. We will also abstract our code to access the SQLite DB into an Ionic factory. We will implement the following methods:

• initDB: This will initialize or create a table for this entity if it does not exist
• getAllTrackers: This will get all trackers list rows from the created table
• addNewTracker - This is a method to insert a new row for a new tracker into the table
• deleteTracker - This is a method to delete a specific tracker using its ID
• getTracker - This will get a specific Tracker from the cached list using an ID to display anywhere

We will be injecting the $cordovaSQLite service into our factory to interact with our SQLite DB. We can open an existing DB or create a new DB using the command $cordovaSQLite.openDB(“myApp.db”). We have to store the object reference returned from this method call, so we will store it in a module-level variable called db. We have to pass this object reference to our future $cordovaSQLite service calls. $cordovaSQLite has a handful of methods to provide varying features:

• openDB: This is a method to establish a connection to the existing DB or create a new DB
• execute: This is a method to execute a single SQL command query
• insertCollection: This is a method to insert bulk values
• nestedExecute: This is a method to run nested queries
• deleted: This is a method to delete a particular DB

We see the usage of openDB and execute the command in this post. In our factory, we will create a standard method runQuery to adhere to DRY(Don’t Repeat Yourself) principles. The code for the runQuery function is as follows:

function runQuery(query,dataParams,successCb,errorCb)
		{
		  $ionicPlatform.ready(function() {		  
			    $cordovaSQLite.execute(db, query,dataParams).then(function(res) {
			      successCb(res);
			    }, function (err) {
			      errorCb(err);
			    });
		  }.bind(this));
		}

In the preceding code, we pass the query as a string, dataParams (dynamic query parameters) as an array, and successCB/errorCB as callback functions. We should always ensure that any Cordova plugin code should be called when the Cordova ready event is already fired, which is ensured by the $ionicPlatform.ready() method. We will then call the execute method of the $cordovaSQLite service passing the ‘db’ object reference, query, and dataParams as arguments. The method returns a promise to which we register callbacks using the ‘.then’ method. We pass the results or error using the success callback or error callback.

Now, we will write code for each of the methods to initialize DB, insert a new row, fetch all rows, and then delete a row.

initDB Method:

function initDB() {
		 
			  db = $cordovaSQLite.openDB("myapp.db");
			  
			   var query = "CREATE TABLE IF NOT EXISTS trackers_list (id in-teger autoincrement primary key, name string)";
			    runQuery(query,[],function(res) {
			      console.log("table created ");
			    }, function (err) {
			      console.log(err);
			    });
	
		}

In the preceding code, the openDB method is used to establish a connection with an existing DB or create a new DB. Then, we run the query to create a new table called ‘trackers_list’ if it does not exist. We define the columns ID with integer autoincrement primary key properties with the name string.

addNewTracker Method:

function addNewTracker(name) {

		var deferred = $q.defer();
		var query = "INSERT INTO trackers_list (name) VALUES (?)";
		runQuery(query,[name],function(response){
			//Success Callback
			console.log(response);
			deferred.resolve(response);
		},function(error){
			//Error Callback
			console.log(error);
			deferred.reject(error);
		});

		return deferred.promise;
	}

In the preceding code, we take ‘name’ as an argument, which will be passed into the insert query. We write the insert query and add a new row to the trackers_list table where ID will be auto-generated. We pass dynamic query parameters using the ‘?’ character in our query string, which will be replaced by elements in the dataParams array passed as the second argument to the runQuery method. We also use a $q library to return a promise to our factory methods so that controllers can manage asynchronous calls.

getAllTrackers Method:

This method is the same as the addNewTracker method, only without the name parameter, and it has the following query:

var query = "SELECT * from trackers_list”;

This method will return a promise, which when resolved will give the response from the $cordovaSQLite method. The response object will have the following structure:

{
insertId: <specific_id>,
rows: {item: function, length: <total_no_of_rows>}
rowsAffected: 0
}

The response object has properties insertId representing the new ID generated for the row, rowsAffected giving the number of rows affected by the query and rows object with item method property, to which we can pass the index of the row to retrieve it.

We will write the following code in the controller to convert the response.rows object into an utterable array of rows to be displayed using the ng-repeat directive:

for(var i=0;i<response.rows.length;i++)
{
	    $scope.trackersList.push({
        id:response.rows.item(i).id,
        name:response.rows.item(i).name
    });
	}

The code in the template to display the list of Trackers would be as follows:

<ion-item ui-sref="tracker({id:tracker.id})" 
   class="item-icon-right" 
    ng-repeat="tracker in trackersList track by $index">
    	{{tracker.name}}
    	<ion-delete-button class="ion-minus-circled" ng-click=“deleteTracker($index,tracker.id)">
	            </ion-delete-button>
    	<i class="icon ion-chevron-right”></i>
</ion-item>

deleteTracker Method:

function deleteTracker(id) {
		var deferred = $q.defer();
		var query = "DELETE FROM trackers_list WHERE id = ?";
		runQuery(query,[id],function(response){
		… [Same Code as addNewTrackerMethod]
}

The delete tracker method has the same code as the addNewTracker method, where the only change is in the query and the argument passed. We pass ‘id’ as the argument to be used in the WHERE clause of delete query to delete the row with that specific ID.

Rest of the Ionic App Code:

The rest of the app code has not been discussed in this post because we have already discussed the code that is intended for integration with SQLite. You can implement your own version of this app or even use this sample code for any other use case. The trackers details view will be implemented in the same way to store data into the tracker_entries table with a foreign key, tracker_id, used for this table. It will also use this ID in the SELECT query to fetch entries for a specific tracker on its detail view.

The GitHub link for the exact functioning code for complete app developed during this tutorial.

About the author

Rahat Khanna is a techno nerd experienced in developing web and mobile apps for many international MNCs and start-ups. He has completed his bachelors in technology with computer science and engineering as specialization. During the last 7 years, he has worked for a multinational IT service company and ran his own entrepreneurial venture in his early twenties. He has worked on projects ranging from static HTML websites to scalable web applications and engaging mobile apps. Along with his current job as a senior UI developer at Flipkart, a billion dollar e-commerce firm, he now blogs on the latest technology frameworks on sites such as www.airpair.com, appsonmob.com, and so on, and delivers talks at community events. He has been helping individual developers and start-ups in their Ionic projects to deliver amazing mobile apps.