Ionic 2 – SQLite as a Service

UPDATE: In Ionic 2 RC0, there seems to be a change with using SQLite. Checkout this thread to learn more.

So, Monday, what do we have here? Throughout last week, I have been getting dirty with putting finishing touches to an app I am working on, with Ionic 2.

I am using SQLite (WebSQLite) as storage backend. That was my first time using WebSQL.

How about, SQaaS, pronounced ‘skaas’. SQL as a Service? Duh

In every Web application, including mobile applications, there are, in almost every example, a backend to go with the app. The storage backend will be responsible for persisting and retrieving data added by clients or users.

Without a storage backend to persist user interactions and information, everything will be lost should you restart your server (either the hardware or the process). Many options exist today that fills the gap, of providing a persistent storage for apps.

Depending on the tools you’re using, you may choose to go with a particular database tool or the other. It doesn’t matter. What matters is getting the job done.

Some SQL will do

If you do have a background in SQL, using the Ionic Storage class will be pretty much icing on the sugar for you. The Ionic Storage class drops down the level where you are able to run arbitrary SQL commands.

At the basic, the Storage offers you the chances to do a key value add and retrieve with the WebSQL implementation. However, using the .query function in the Storage class opens a far bigger fun for all.

We’re going to implement a simple service that handles CRUD with SQLite in Ionic 2

Create the Service

To the extent that you wish to have SQLite backend in your Ionic project and access the endpoints via a service means you’re capable of setting up an Ionic project.

So let’s cut straight to the fun part. But before we do, there are certain assumptions to make.

Forms

Let us say you have a form like this. The form below is responsible for adding a category and selecting the type of it, either the expense or income. You don’t need to wonder why this form, just focus on the examples. 🙂

<ion-navbar *navbar>
    <ion-title>New category</ion-title>
</ion-navbar>
<ion-content class="addtrans">
    <ion-list inset>
        <form #formData='ngForm' (ngSubmit)="onSubmit(formData)">
            <ion-item>
                <ion-label floating>Name of Category</ion-label>
                <ion-input type="text" ngControl="name" required></ion-input>
            </ion-item>            
            <ion-list radio-group ngControl="type" required>
                <ion-list-header>Choose type</ion-list-header>
                <ion-row>
                    <ion-col width-50>
                        <ion-item>
                            <ion-label>Income</ion-label>
                            <ion-radio value="income"></ion-radio>
                        </ion-item>
                    </ion-col>
                    <ion-col width-50>
                        <ion-item>
                            <ion-label>Expense</ion-label>
                            <ion-radio value="expense"></ion-radio>
                        </ion-item>
                    </ion-col>
                </ion-row>
            </ion-list>
            <button tappable block [disabled]="!formData.valid">
                <ion-icon name="add"></ion-icon>Add</button>
        </form>
    </ion-list>
</ion-content>

If you’ve followed my getting started with Ionic 2 Forms in my previous tutorial, the above will probably appear usual. Nothing fancy going on, just an implicit form created.

For now, we have not implemented the SQLite backend, so we will simply console log the output from the form above, which can be done as this:

import { Page, NavController } from 'ionic-angular';

@Page({
  templateUrl: 'build/pages/addcategory/addcategory.html',
})

export class AddcategoryPage {
  static get parameters() {
    return [
      [NavController],
    ];
  }

  constructor(nav) {
    this.nav = nav;
  }

  onSubmit(formData) {
    if( formData.valid ) {
       console.log("Form submission is ", formData.value)
    }
  }
}

The above should get us the formData logged into the console when the form is submitted. If the form is invalid, we aren’t gonna see anything. How does it know it is valid or not?

Remember we have the required on the form input elements we don’t want the user to miss. If a required input field isn’t satisfied, that boolean of whether valid never turns true.

If the form happens to be valid, and you submit, the submitted form object will look something like below:

Form submission is  Object {name: "Awesome", type: "income"}

It is time for Service

In your Command Line Interface, in your Ionic 2 Project folder, run ionic generate provider category.service

Open the category-service.js file in category-service folder in the providers folder in your root directory. That is where we’ll be doing our servicing.

Delete everything from it, and put in this. I leave comments inline

// Ionic Beta 7. Import from @angular/blabla instead
// No longer angular2
import {Injectable} from '@angular/core';
import { Storage, SqlStorage } from 'ionic-angular';

@Injectable()
export class CategoryService {
  static get parameters(){
    return []
  }  

  constructor() {
    // give us a new Storage using SqlStorage
    this.storage = new Storage(SqlStorage);

    // we're creating a table called 'category' if it doesn't exist
    // with an 'id', 'name' and 'type' columns
    this.storage.query('CREATE TABLE IF NOT EXISTS category (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type TEXT)');
  }

  // some code here

  saveCategory(data) {
    let sql = 'INSERT INTO category (name, type) VALUES (?, ?)';
    // return the query which happens to be a promise.
    return this.storage.query(sql, [data.name, data.type]);
  }

  // more code here
}

Let us put them all together, as in, let us bring in our service to the addcategory.js file, then feed it with the data from the form when submitted.

import { Page, NavController } from 'ionic-angular';
// import the service class
import { CategoryService } from '../../providers/category-service/category-service';

@Page({
  templateUrl: 'build/pages/addcategory/addcategory.html',
})

export class AddcategoryPage {
  // what the heck is this static get parameters() ?
  // scroll to bottom of this page
  // ---> http://ionicframework.com/docs/v2/getting-started/tutorial/adding-pages/
  static get parameters() {
    return [
      [NavController],
      [CategoryService]
    ];
  }

  constructor(nav, catservice) {
    this.nav = nav;
    this.catservice = catservice;
  }

  onSubmit(formData) {
    if (formData.valid) {
      console.log('Form submission is ', formData.value);

      // the saveCategory() function from our service returns a promise,
      // so we can do some 'thening'
      this.catservice.saveCategory(formData.value)
        .then((data) => {
          console.log('Success', data.res);
          this.nav.setRoot(CategoryPage); // assuming we have a page that 
                                          // lists all category, then we switch to
        }, (error) => {
          console.log('Error', error.err);
        });
    }
  }
}

This is pretty much a basic standard use of Promises. The storage.query returns a promise, which we then do a then on. We either get a resolve (which means all went well) or a reject (bad news), and we intercept these responses in the (data) => { } block, then the error in the other.

[wp_ad_camp_1]

You could even let a toast display if successful process happens or not. See:

// Don't forget to import Toast. Toast. Toast!
import { Page, NavController, NavParams, Toast } from 'ionic-angular';
import { CategoryService } from '../../providers/category-service/category-service';

// let's say the category page lists all categories
import { CategoryPage } from '../pages/category/category';

@Page({
  templateUrl: 'build/pages/addcategory/addcategory.html',
})

export class AddcategoryPage {
  static get parameters() {
    return [
      [NavController],
      [CategoryService]
    ];
  }

  constructor(nav, catservice) {
    this.nav = nav;
    this.catservice = catservice;
  }

  // Go back to the top, see I imported Toast there?
  displayToast(message, duration) {
    // Import Toast from top
    let toast = Toast.create({ // Do, import Toast!
      message: message,
      duration: duration
    })
    this.nav.present(toast);
  }

  onSubmit(formData) {
    if (formData.valid) {
      console.log('Form submission is ', formData.value);
      this.catservice.saveCategory(formData.value)
        .then((data) => {
          console.log('Success', data.res);
          this.displayToast('Successfully added Category', 5000);
          this.nav.setRoot(CategoryPage);
        }, (error) => {
          console.log('Error', error.err);
          this.displayToast('Full of errors', 5000);
        });
    }
  }
}

You just pass in the message of your choice to the displayToast() function, and it will display after the category is added. If error, you’ll be told, if not, voila!

Edit Category

With that ground-breaking understanding you had from adding a category into the database using a service, you should pretty much be able to do the rest of the CRUD. We’ve done Create. Are you saying you can’t do the Read, Update, and Delete? You sure can, but let’s do the Update together. Because, why not?

This is the editcategory.html contents:

<ion-navbar *navbar>
    <ion-title>Update Category</ion-title>
</ion-navbar>
<ion-content class="addtrans">
    <ion-list inset>
        <form #formData='ngForm'>
            <ion-input type="text" hidden [(ngModel)]="category.id" ngControl="id"></ion-input>
            <ion-item>
                <ion-label floating>Name of Category</ion-label>
                <ion-input type="text" [(ngModel)]="category.name" ngControl="name"></ion-input>
            </ion-item>
            <ion-list radio-group ngControl="type" [(ngModel)]="category.type">
                <ion-list-header>Choose type</ion-list-header>
                <ion-row>
                    <ion-col width-50>
                        <ion-item>
                            <ion-label>Income</ion-label>
                            <ion-radio value="income"></ion-radio>
                        </ion-item>
                    </ion-col>
                    <ion-col width-50>
                        <ion-item>
                            <ion-label>Expense</ion-label>
                            <ion-radio value="expense"></ion-radio>
                        </ion-item>
                    </ion-col>
                </ion-row>
            </ion-list>
            <button block (click)="onSubmit(formData)">
                <ion-icon name="update"></ion-icon>Add</button>
        </form>
    </ion-list>
</ion-content>

Some slight differences here and there. Let’s talk about it.

<ion-input type="text" hidden [(ngModel)]="category.id" ngControl="id"></ion-input>

We could add the id value to the form after submitted, before pushing to the service to save, OR we could simply hide the id in the form, then it’ll get submitted along the rest. This approach is probably lazy and not recommended, at least if you’re in the web application environment. This is a mobile app, which will be built into an APK, where someone cannot right click and inspect the element to change the id value. So, we can get away with that, but in a web app, please don’t say I told you that trick when your boss asks.

<ion-input type="text" [(ngModel)]="category.name" ngControl="name"></ion-input>

The [(ngModel)]="category.name" should be familiar to you if you followed my previous Ionic 2 Forms getting started on the right foot. It is similar to the ng-model found in Angular 1, which ensures a two-way binding. Awesome!

But what are we binding to? Our editcategory.js should make things clearer:

import { Page, NavController, NavParams } from 'ionic-angular';
// Services
import { CategoryService } from '../../providers/category-service/category-service';

@Page({
  templateUrl: 'build/pages/editcategory/editcategory.html',
})

export class EditcategoryPage {
  static get parameters() {
    return [
      [NavController],
      [CategoryService]
    ];
  }

  constructor(nav, navparams, catservice) {
    this.nav = nav;
    this.params = navparams;
    this.service = catservice;
    // the [(ngModel)]= "category.name"
    // we're passing in an 'obj' as parameter from previous controller
    // we use the params.get to grab the param
    // then access the individual fields on them, then attach
    // to our fields in the category object.
    this.category = {
      'id': this.params.get('obj').id,
      'name': this.params.get('obj').name,
      'type': this.params.get('obj').type
    }
  }

  onSubmit(formData) {
    if (formData.value) {
      console.log(formData);
      this.service.updateCategory(formData);
      this.nav.pop();
    }
  }
}

This is getting fun. You see how the whole game is now? If you’re wondering how the params were sent in, it might be something like this:

  gotoEditCategory(obj) {
    this.nav.push(EditcategoryPage, {
      'obj': obj // where obj is an object like { id: 1, name: 'something', type: 'something' }
    });
  }

Conclusion:

In all, this is how your service might look like with all the other CRUD parts:

import {Injectable} from '@angular/core';
import { Storage, SqlStorage } from 'ionic-angular';

@Injectable()
export class CategoryService {
  static get parameters(){
    return []
  }  

  constructor() {
    this.storage = new Storage(SqlStorage);
    this.storage.query('CREATE TABLE IF NOT EXISTS category (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, type TEXT)');
  }

  // get all categories
  getCategory() {
    return this.storage.query('SELECT id, name, type FROM category');
  }

  // save category
  saveCategory(data) {
    let sql = 'INSERT INTO category (name, type) VALUES (?, ?)';
    return this.storage.query(sql, [data.name, data.type]);
  }

  // update category
  updateCategory(obj) {
    let sql = 'UPDATE category SET id = ?, name = ?, type = ? WHERE id = ?';
    return this.storage.query(sql, [obj.id, obj.name, obj.type, obj.id]);
  }

  // delete category
  deleteCategory(id) {
    let sql = 'DELETE FROM category WHERE id = (?)';
    return this.storage.query(sql, [id]);
  }

  // call this to reset your category table
  resetTable() {
    let sql = 'DELETE FROM category';
    return this.storage.query(sql);
  }
}

That is it from me today. I hope you go out there, and as usual, turn on the Hadron Collider, and get things Ionized!