Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Deploy Ionic Framework App With Pre-Filled SQLite DB

TwitterFacebookRedditLinkedInHacker News

Recently I did an article regarding SQLite as an alternative to local storage in an Ionic Framework application. The article was a guide for using the Apache Cordova SQLite plugin for data management. Since writing that post, a few of my readers asked me how to ship an application with a pre-populated SQLite database.

A scenario where this might be useful is if you created a lookup directory for all the employees at your company. Let’s say you have 10,000 employees, so populating the data via a web request is probably going to take a long time. You could ship each version of your app with the most recent lookup directory, and use an API to update it. Since you’re only doing small changes after the initial, it is more effective than trying to download everyone.

This task is not difficult, but it could get a little confusing. Now I’m not going to show how to do the example scenario I mentioned, but the following should put you on the right path for working with pre-populated SQLite databases.

Before we begin, I highly recommend you read the previous article I did regarding SQLite and Ionic Framework. Without having first read it, this tutorial may be confusing.

Let’s start by creating a fresh Ionic project to work with:

ionic start IonicProject blank
cd IonicProject
ionic platform add android
ionic platform add ios

Note, if you’re not using a Mac, you won’t be able to add and build for the iOS platform.

The next step is to add two Apache Cordova plugins:

These two plugins can be added by entering the following:

cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin.git
cordova plugin add https://github.com/an-rahulpandey/cordova-plugin-dbcopy.git

Because this process can get a little confusing, I’m going to re-iterate the SQLite querying portion as explained in my other article. It won’t be near the depth of my previous article, but it will remove much of the confusion.

We need to go and download the AngularJS extension set, ngCordova, because we’ll be using it as a wrapper for our queries. Download the latest version and copy the ng-cordova.min.js file into your www/js directory. Now open your project’s index.html file and make it look something like the following:

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="initial-scale=1, maximum-scale=1, user-scalable=no, width=device-width">
        <title></title>
        <link href="lib/ionic/css/ionic.css" rel="stylesheet">
        <link href="css/style.css" rel="stylesheet">
        <script src="lib/ionic/js/ionic.bundle.js"></script>
        <script src="js/ng-cordova.min.js"></script>
        <script src="cordova.js"></script>
        <script src="js/app.js"></script>
    </head>
    <body ng-app="starter">

Notice the lines in the above code block. It is very important that ng-cordova.min.js appears before the cordova.js line otherwise you’re going to end up with strange results.

Now crack open your www/js/app.js file and change your angular.module line to look like the following:

var ionicApp = angular.module('starter', ['ionic', 'ngCordova']);

ngCordova is now set up, so it is time to worry about our pre-filled SQLite database. In this example I’m going to use a file called populated.db and it is going to be created with the following SQL:

CREATE TABLE people (id integer primary key, firstname text, lastname text)

In addition to creating the table, it will be filled with the following data:

idfirstnamelastname
1NicRaboy
2MariaCampos
3MaxLynch
4BenSperry

If you’re unfamiliar with making SQLite databases you can use DB Browser for SQLite, which I used when writing this guide.

OBSOLETE AS OF 02/02/2015: To include this database file into your project you must place it in your platforms/android/assets directory for Android and your Resources directory for iOS, which in my case is platforms/ios/IonicProject/Resources. Just substitute IonicProject with whatever you called your project. For iOS only, you’ll also need to add the file from the Resources directory into your XCode project.

UPDATE 02/02/2015: To include the database file into your project, it must live in your project’s www directory.

What comes next is going to be kept basic for simplicity, but use your imagination if you want it to be better.

Open www/js/app.js in your text editor and make it look like the following:

var ionicApp = angular.module('starter', ['ionic', 'ngCordova']);
var db = null;

ionicApp.run(function($ionicPlatform, $cordovaSQLite) {
    $ionicPlatform.ready(function() {
        if(window.cordova && window.cordova.plugins.Keyboard) {
            cordova.plugins.Keyboard.hideKeyboardAccessoryBar(true);
        }
        if(window.StatusBar) {
            StatusBar.styleDefault();
        }
        window.plugins.sqlDB.copy("populated.db", function() {
            db = $cordovaSQLite.openDB("populated.db");
        }, function(error) {
            console.error("There was an error copying the database: " + error);
            db = $cordovaSQLite.openDB("populated.db");
        });
    });
});

ionicApp.controller("ExampleController", function($scope, $cordovaSQLite) {

    $scope.selectAll = function() {
        var query = "SELECT firstname, lastname FROM people";
        $cordovaSQLite.execute(db, query, []).then(function(res) {
            if(res.rows.length > 0) {
                for(var i = 0; i < res.rows.length; i++) {
                    console.log("SELECTED -> " + res.rows.item(i).firstname + " " + res.rows.item(i).lastname);
                }
            } else {
                console.log("No results found");
            }
        }, function (err) {
            console.error(err);
        });
    }

});

The database will be copied from the assets directory to the devices protected space. After the copy, the database will be opened using ngCordova. Note that if the database exists in the protected space, it will not be copied again.

In our controller, we will attempt to select all records via a button press. The button that calls our selectAll() method can be added to index.html like so:

<ion-content ng-controller="ExampleController">
    <button class="button" ng-click="selectAll()">Select All</button>
</ion-content>

With a little luck, you should get a print-out to your logs with all our records.

A video version of this article can be seen below.

Nic Raboy

Nic Raboy

Nic Raboy is an advocate of modern web and mobile development technologies. He has experience in C#, JavaScript, Golang and a variety of frameworks such as Angular, NativeScript, and Unity. Nic writes about his development experiences related to making web and mobile development easier to understand.

Search

Follow Us

Support This Site