SQLite storage in Ionic 2

In my last blog I promised to provide step by step guide to implement on phone storage. The main purpose of storing data on phone (offline support) is to reduce load time and optmize the downloads.

In this blog I am sharing my experience on how to use storage with Ionic 2.

There are different ways of integrating storage with Ionic 2 app. I was considering two options, 1) use local-storage which is collection of key-value pairs within application or 2) use SQLite database. I chose SQLite database. The reason being,  local storage has key value structure, its hard to query large amount of data, plus it has limitation of 10 MB data.

There are few other blogs, but I found them little convoluted. So, I tried to simplify steps. Let’s see, step by step procedure.

Step 1: Basic Installation

Install latest version of Ionic and Cordova by –

       npm install -g ionic
       npm install -g cordova
     

Step 2: Start Project

Lets start new project with

     ionic start SampleProject blank --v2
   

Here by specifying v2 in command, I am referring it as Ionic 2 project. Also SampleProject will be created with blank theme.

Now add platform to project. Basically here I am just focusing on android platform. If you want it for IOS too, then you can add it with same command but replace android with IOS.

     ionic platform add android
   

Step 3: Add SQLite plugin

Next step is to add SQLite plugin. This plugin is provided by Cordova SQLite Storage. After installing add package name in package.json file. I am adding it with following command :

    ionic plugin add cordova-sqlite-plugin
  

Step 4: Add database provider

There are many ways, In which you can manage various operation over a SQLite database. Here is my plan, I am creating one provider which will be called as ‘Database Provider’. In that I will be specifying all CRUD operation including database open and destroy function. After that I will access that provider wherever I want. So, lets do it.

Add provider with,

     ionic g provider DatabaseProvider
   

Add following content in database-provider.ts file :

     import { Injectable } from '@angular/core';
     import { Http } from '@angular/http';
     import { SQLite } from 'ionic-native';

     @Injectable()
     export class DatabaseProvider {
       db = new SQLite();

       constructor(public http: Http) {
       }

       public openDatabase() {
         return this.db.openDatabase({name: 'user_data.db', location: 'default'});
       }

       public createUserTable(){
         let query = "CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)";
         this.db.executeSql(query, {}).then(() => {
           console.log("user table created");
         });
       }

       public insertIntoUser(user_details){
         let insert_query = "INSERT INTO user VALUES (?, ?)";
         this.db.executeSql(insert_query, [user_details.first_name, user_details.last_name]).then((data) => {
           console.log("user inserted");
         });
       }

       public fetchFromUser(){
         return this.db.executeSql('SELECT * FROM user', []);
       }

      public dropDatabase(){
        console.log("dropping database");
        SQLite.deleteDatabase({name: 'user_data.db', location: 'default'});
      }
    }
   

Here I am first importing SQLite native class provided by Ionic CLI and then making its instance. While opening database we should provide database name and location to store it. Same parameters will be passed while dropping database.

Step 5: Open database in app.component.ts file

Now its time to open database, I want to open database when my app is started, so I will do it in app.component file as,

    import { Component } from '@angular/core';
    import { Platform } from 'ionic-angular';

    import { HomePage } from '../pages/home/home';
    import { DatabaseProvider } from '../providers/database-provider';

    @Component({
      templateUrl: 'app.html'
    })
    export class MyApp {
      rootPage = HomePage;

      constructor(platform: Platform, public dbService: DatabaseProvider) {
        platform.ready().then(() => {
          Promise.all([this.dbService.openDatabase()]).then((data) => {
            console.log("database is open now");
          });
        });
      }
    }
  

Here I am just importing database provider, which we have already created. Once platform gets ready I am opening it by just calling openDatabase() method.

Step 6: Apply operations on database

Now I am going to do all insert and fetch operation on database in home page. So now, I am again importing database-provider in home page. After that I will create table USER, add data to it and fetch user from it. In this example I am adding static data. In real-time we could store data coming from server side in SQLite DB. So here is the magic,

     import { Component } from '@angular/core';
     import { NavController } from 'ionic-angular';

     import { DatabaseProvider } from '../../providers/database-provider';

     @Component({
       selector: 'page-home',
       templateUrl: 'home.html'
     })
     export class HomePage {
       first_name:any = 'Kevin';
       last_name:any = 'Watson';
       user_list:any;
       constructor(public navCtrl: NavController, public dbService: DatabaseProvider) {
       }

       insert(){
         this.dbService.insertIntoUser({first_name: this.first_name, last_name: this.last_name});
       }

       refresh(){
         Promise.all([this.dbService.fetchFromUser()]).then((data) => {
           this.user_list = data;
         });
       }
    }
   

Now update the code in home.html file. Here I am showing two buttons and list of users. Add button will add record ( first name and last name ) in User table. Refresh button will take updated data from User table and store it to user_list. We can do any opration on DB. Just write that method in provider and access it in component.

     <ion-header>
       <ion-navbar>
         <ion-title>
           User List
         </ion-title>
         <ion-buttons start>
           <button (click)="refresh()">Refresh</button>
         </ion-buttons>
         <ion-buttons end>
           <button (click)="insert()">Add</button>
         </ion-buttons>
       </ion-navbar>
     </ion-header>

     <ion-content padding>
       <ion-list>
          <ion-item *ngFor="let user of user_list">
             {{user.firstname}} {{user.lastname}}
          </ion-item>
       </ion-list>
     </ion-content>
  

In this example, the data we are storing is static. In real time, we fetch data from server and store it in database. Periodically we need to have watch on server db to get updated data. There are two ways, either we could use tools available for auto sync of a database or on some action we will manually check for update.

Tools like PouchDB can be used with SQLite. It needs URL of remote database server to sync with. Second way is to check for the updated data on some action/ event. If data is updated then we will update our local data with same.

In this way, We can store data in application and also retrieve it whenever needed. For example we could load data from local DB when we have no internet connection. This will serves our purpose of offline support.

Advertisements

One thought on “SQLite storage in Ionic 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s