Using SqlStorage Instead Of SQLite In An Ionic 2 App

When it comes to Ionic 2 there are many ways that you can store your data. For example you could use HTML5 local storage, Mozilla’s localForage library, or Ionic’s SQLite extension that is part of Ionic Native. With these options available, I get a lot of requests for information on Ionic’s less advertised SqlStorage option.

We’re going to take a look at using SqlStorage in an Android and iOS application rather than the SQLite alternative.

Before getting too deep into this we should probably figure out the differences between SQLite in Ionic Native and SqlStorage which is part of the Ionic platform. Per the Ionic 2 documentation, SqlStorage will use the Apache Cordova SQLite plugin, if available, otherwise it will fall back to WebSQL. This is different from Ionic Native SQLite which will only use the Apache Cordova SQLite plugin. I previously wrote how to use SQLite with Ionic Native.

To show SqlStorage in action, we’re going to create a fresh Ionic 2 project. From the Command Prompt (Windows) or Terminal (Mac and Linux), execute the following:

ionic start SqlProject blank --v2
cd SqlProject
ionic platform add ios
ionic platform add android

There are a few things to note in the above set of commands. First you’ll note the --v2 tag. This means we are creating an Ionic 2 project which requires the Ionic 2 CLI. We are adding both the iOS and Android platforms, but if you’re not using a Mac, you won’t be able to build for iOS.

With the project created, we have an optional next step. If you wish to use the Apache Cordova SQLite plugin, which you definitely should, then you need to install it. This can be done by executing the following:

ionic plugin add cordova-sqlite-storage

Remember, if you don’t install the plugin, you’ll be using WebSQL which has a few limitations in comparison to the native alternative.

We’re going to edit two different files in our Ionic 2 project. Starting with the TypeScript logic, open the project’s app/pages/home/home.ts file and include the following code:

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

@Component({
    templateUrl: 'build/pages/home/home.html'
})
export class HomePage {

    private storage: Storage;
    public personList: Array<Object>;

    public constructor(private navCtrl: NavController) {
        this.storage = new Storage(SqlStorage);
        this.storage.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)");
        this.personList = [];
    }

    public onPageLoaded() {
        this.refresh();
    }

    public add() {
        this.storage.query("INSERT INTO people (firstname, lastname) VALUES (?, ?)", ["Nic", "Raboy"]).then((data) => {
            this.personList.push({
                "firstname": "Nic",
                "lastname": "Raboy"
            });
        }, (error) => {
            console.log(error);
        });
    }

    public refresh() {
        this.storage.query("SELECT * FROM people").then((data) => {
            if(data.res.rows.length > 0) {
                this.personList = [];
                for(let i = 0; i < data.res.rows.length; i++) {
                    this.personList.push({
                        "id": data.res.rows.item(i).id,
                        "firstname": data.res.rows.item(i).firstname,
                        "lastname": data.res.rows.item(i).lastname,
                    });
                }
            }
        }, (error) => {
            console.log(error);
        });
    }

}

There is a lot happening in the above TypeScript file, so it is probably best that we break it down.

To make this project possible we need to import both the SqlStorage and Storage components that are part of the Ionic 2 platform. Inside the HomePage class we define both a public and private variable. The personList array is public because it will be accessed via the UI.

public constructor(private navCtrl: NavController) {
    this.storage = new Storage(SqlStorage);
    this.storage.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)");
    this.personList = [];
}

In the above constructor method we initialize our storage object and create a new database table if it doesn’t already exist. We are also initializing our personList to a blank array.

public onPageLoaded() {
    this.refresh();
}

After initializing all our components we find ourself in the onPageLoaded method. This method is triggered after the constructor method fires. This method is responsible loading our data. Now it makes sense to look ahead to the refresh method that we’re trying to use.

public refresh() {
    this.storage.query("SELECT * FROM people").then((data) => {
        if(data.res.rows.length > 0) {
            this.personList = [];
            for(let i = 0; i < data.res.rows.length; i++) {
                this.personList.push({
                    "id": data.res.rows.item(i).id,
                    "firstname": data.res.rows.item(i).firstname,
                    "lastname": data.res.rows.item(i).lastname,
                });
            }
        }
    }, (error) => {
        console.log(error);
    });
}

The refresh method will make use of a SELECT query that will get all person data. Every row in the results will be pushed into our array of objects which will eventually be presented on the screen.

Finally this brings us to the add method.

public add() {
    this.storage.query("INSERT INTO people (firstname, lastname) VALUES (?, ?)", ["Nic", "Raboy"]).then((data) => {
        this.personList.push({
            "firstname": "Nic",
            "lastname": "Raboy"
        });
    }, (error) => {
        console.log(error);
    });
}

In the above method we are inserting data into the database. Of course this example is very simple so we’re not inserting dynamic data. Every insert will only add my name to the database. After the insert is complete, the data will be pushed into the array of objects for displaying on the screen.

This brings us to the UI of our simple application. Open the project’s app/pages/home/home.html file and include the following markup:

<ion-header>
    <ion-navbar>
        <ion-title>
            Ionic SqlStorage
        </ion-title>
        <ion-buttons start>
            <button (click)="refresh()">Refresh</button>
        </ion-buttons>
        <ion-buttons end>
            <button (click)="add()">Add</button>
        </ion-buttons>
    </ion-navbar>
</ion-header>

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

In our HTML file we essentially have two buttons in the header. One button will refresh the list and the other will add items to the list. The core content of this HTML file is a loop of our array of objects.

Conclusion

You just saw how to use SqlStorage instead of SQLite with Ionic Native. While I haven’t really figured out why you should use one over the other when both interface with the same Apache Cordova SQLite plugin, at least you have options.

If you want to take this to the next level, you should try to create a shared service from the SqlStorage code. This can help reduce a lot of duplicate code. If you need help getting started with this, check out a similar tutorial I wrote on the subject.

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.