Ionic 2 and SQLite

I'm advancing in my new project and it is the moment to decide how save the data. If it's a native project in Android my decision is easy, SQLite. In iOS I have not yet used Core Data so at this time I'm not going  write over that.

At this moment seems there are two options, Local Storage and SQLite. Local Storage has a limitation of 10 MB of data and how I don't know if the users they will use more, I think not, but to ensure that storage limitation is not a problem I will use SQLite.

Here you can encounter the official documentation of ionic native.

First step is install the Apache Cordova SQLite plugin, for that from a terminal and from inside the project directory we run this command

   ionic plugin add cordova-sqlite-storage --save

Now we will create a service to manage all the functionalities of the database, fot that, we use the ionic generator command

   ionic g provider db-service

This command has created the db-service.ts file in the ../src/providers/  directory.

We open the file db-service.ts and we import the SQLite from ionic native, we declare a variable and into the constructor we create an instance to SQLite.

   
   import { Injectable } from '@angular/core';
   import { SQLite } from 'ionic-native';
   import 'rxjs/add/operator/map';


   @Injectable()
   export class DbService {

      db: SQLite = null;

      constructor() {
         this.db = new SQLite();
      }   

   }

Now we create the method openDatabase, which is the responsible of open the database

   
    //Open the database
    openDatabase() {
       return this.db.openDatabase({
         name: 'mydb.db',
         location: 'default'
       })
       .catch(error => console.error('Error opening database', error));
    }

Next, we create the method for create the tables in the database

   
    //Create all tables
    createTables() {
       let myTable = 'CREATE TABLE IF NOT EXIST mytable' + 
                            ' (id INTEGER PRIMARY KEY AUTIOINCREMENT,' +
                            ' title TEXT, data TEXT)';

       return this.db.executeSql(myTable, []).then(() => {

       }, (err) => {
          console.error('Unable to execute sql: ', err);
       }); 
    }

A method for insert data

   
   insertMyTable(title: string, data:string) {
      let sql = 'INSERT INTO mytable(title, data) VALUES(?,?)';

      return this.db.executeSql(sql, [title, data]).then(() => {

      }, (err) => {
         console.error('Unable to execute sql insertion: ', err);
      });
   }

An update method

   
   updateMyTable(list: any) {
      let sql = 'UPDATE mytable SET title=?, data=?  WHERE id=?';

      return this.db.executeSql(sql, [list.title, list.data, 
                                list.id]).then(() => {

      }, (err) => {
         console.error('Unable to execute sql update: ', err);
      });
   }

An delete method

   
   deleteMyTable(list: any) {
      let sql = 'DELETE FROM mytable WHERE id=?';

      return this.db.executeSql(sql, [list.id]).then(() => {

      }, (err) => {
         console.error('Unable to execute sql: ', err);
      });
  }

And finally a query method, in this case obtains all the titles of the table

   
   getAllTitlesFromMyTable() {
      let sql = 'SELECT title FROM mytable';
      let titles: string[] = [];

      return this.db.executeSql(sql, []).then(response = > {
         
         for (var x = 0; x < response.rows.length; x++) {
            titles.push(response.rows.item(x).title);
         }

         return Promise.resolve(titles);
      }, (err) => {

         console.error('Unable to execute sql query: ', err);   
      });
   }

Now, for use it in the file app.module.ts first import the service and  in the providers section we add it

   
   import { NgModule, ErrorHandler } from '@angular/core';
   import { IonicApp, IonicModule, IonicErrorHandler } from 'ionic-angular';
   import { HomePage } from '../pages/home/home';

   import { DbService } from '../providers/db-service';

   @NgModule({
      declarations: [
         MyApp,
         HomePage
      ],
      imports: [
         IonicModule.forRoot(MyApp),
      ],
      bootstrap: [IonicApp],
      entryComponents: [
         MyApp,
         HomePage
      ],
      providers: [
         {provide: ErrorHandler, useClass: IonicErrorHandler},
         DbService
      ]
   })
   export class AppModule {}

In app.component.ts when the app is ready we call the openDatabase method and create the tables

   
   import { Component } from '@angular/core';
   import { Platform } from 'ionic-angular';
   import { StatusBar, Splashscreen } from 'ionic-native';

   import { HomePage } from '../pages/home/home';
   import { DbService } from '../providers/db-service';


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

      constructor(platform: Platform, public dbService: DbService) {
         platform.ready().then(() => {
            StatusBar.styleDefault();
            Splashscreen.hide();
            dbService.openDatabase()
        .   then(() => this.dbService.createTables());
         });
     }
   }

And we can use it in the controller

   
   import { Component } from '@angular/core';

   import { NavController } from 'ionic-angular';
   import { DbService } from '../../providers/db-service';

   @Component({
      selector: 'page-home',
      templateUrl: 'home.html'
   })

   export class HomePage {
      titles: string[] = [];

      constructor(public navCtrl: NavController, 
                         public dbService: DbService) {
    
      }

      ionViewWillEnter () {
         this.getTitles();
      }

      getTitles() {
         this.dbService.getAllTitlesFromMyTable().then(titles => {
            this.titles = titles as string[];
         }, (err) => {
            console.error('Get title error: ', err);
         });
      }

   }

I use the query method inside the ionViewWillEnter() because it's fired when entering a page, before it's active. Here you can see more info about Navigation Lifecycle Event.

And finally in the template

   
   <ion-content>
      <ion-list inset>
         <ion-item *ngFor="let title of titles">
            {{ title }}
         </ion-item>
      </ion-list>
   </ion-content>

Well, this it's all for now.