Use SQLite In Ionic Instead Of Local Storage

Ionic 2 is becoming all the rage right now because of it using Angular. With the introduction of Angular, comes many differences in the language and framework itself. One of the most critical parts of any mobile application is its ability to save data and have it persisted when the application is launched at a later date. I demonstrated in Ionic Framework 1 how to use SQLite as a storage solution, so I figured it would be a good idea to demonstrate the same using Ionic Framework 2.

Let’s see why it might be a good idea to use SQLite in an Ionic 2 application rather than HTML5 local storage.

In case you’re unfamiliar with local storage, it is key-value storage within your application. This makes it difficult to query for data when you have a lot of it. Not only is there query difficulties, but there are limitations.

Here is an article on Stack Overflow that lists some of the storage limits for local storage. In short, local storage has a limitation of 10MB of data.

These two reasons are what makes SQLite a better choice if only comparing against local storage.

Now let’s look at getting SQLite up and running in our Ionic 2 Android and iOS application. From a Command Prompt (Windows) or Terminal (Mac and Linux), run the following:

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

It is important to note that if you’re not using a Mac, you cannot add and build for the iOS platform. It is also important that you’re using the Ionic CLI that offers version two of the framework.

We’re not done yet. We also need to add the Apache Cordova SQLite plugin. It is the same plugin we used in the Ionic Framework 1 tutorial and it can be added via the following:

ionic plugin add cordova-sqlite-storage

With the project created and the plugin added, open your project’s app/app.ts file and change it to the following code:

import {Component} from '@angular/core';
import {Platform, ionicBootstrap} from 'ionic-angular';
import {StatusBar, SQLite} from 'ionic-native';
import {HomePage} from './pages/home/home';

@Component({
    template: '<ion-nav [root]="rootPage"></ion-nav>'
})
export class MyApp {
    rootPage: any = HomePage;

    constructor(platform: Platform) {
        platform.ready().then(() => {
            StatusBar.styleDefault();
            let db = new SQLite();
            db.openDatabase({
                name: "data.db",
                location: "default"
            }).then(() => {
                db.executeSql("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)", {}).then((data) => {
                    console.log("TABLE CREATED: ", data);
                }, (error) => {
                    console.error("Unable to execute sql", error);
                })
            }, (error) => {
                console.error("Unable to open database", error);
            });
        });
    }
}

ionicBootstrap(MyApp);

We’ve included SQLite as documented in the version two official documentation and created a new table as soon as the Apache Cordova plugin is ready. All interactions with SQLite are handled through Ionic Native. This particular table has three columns, an id, a firstname, and a lastname. Nothing fancy here.

Now we want to change the code in the actual page of our application. Open your project’s app/pages/home/home.ts file and change the code to the following:

import {Component} from '@angular/core';
import {NavController, Platform} from 'ionic-angular';
import {SQLite} from "ionic-native";

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

    public database: SQLite;
    public people: Array<Object>;

    constructor(private navController: NavController, private platform: Platform) {
        this.platform.ready().then(() => {
            this.database = new SQLite();
            this.database.openDatabase({name: "data.db", location: "default"}).then(() => {
                this.refresh();
            }, (error) => {
                console.log("ERROR: ", error);
            });
        });
    }

    public add() {
        this.database.executeSql("INSERT INTO people (firstname, lastname) VALUES ('Nic', 'Raboy')", []).then((data) => {
            console.log("INSERTED: " + JSON.stringify(data));
        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error.err));
        });
    }

    public refresh() {
        this.database.executeSql("SELECT * FROM people", []).then((data) => {
            this.people = [];
            if(data.rows.length > 0) {
                for(var i = 0; i < data.rows.length; i++) {
                    this.people.push({firstname: data.rows.item(i).firstname, lastname: data.rows.item(i).lastname});
                }
            }
        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error));
        });
    }

}

There is a lot happening in the above TypeScript code. First off we’re including the SQLite dependency from Ionic Native. Now in the constructor method we are opening the database again, but this time we call the refresh function that will do a simple SELECT query against our database and table. During this SELECT, the array we’re storing data in is emptied and repopulated with the results.

We also have an add function in this HomePage class. It is responsible for inserting static content into our database table. To keep it simple we are just inserting my name.

To make things graphically pleasing, let’s head over to the project’s app/pages/home/home.html file and change the markup to the following:

<ion-header>
    <ion-navbar>
        <ion-title>
            Ionic Blank
        </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 people">
             {{person.firstname}} {{person.lastname}}
         </ion-item>
     </ion-list>
</ion-content>

Our screen will have two buttons in the navigation bar. One button will refresh our list content and the other will add to it. Each item in our list will be from the people array created in the app/pages/home/home.ts file. We’ll be printing the firstname and lastname of every item.

Conclusion

As demonstrated in my Ionic Framework 1 SQLite tutorial, both are using the same Apache Cordova plugin. The differences between the two being one uses Angular in Ionic 2 and the other using ngCordova with Ionic Framework 1. Using SQLite is a better choice than using the key-value local storage offered by HTML5 and JavaScript.

What I showed isn’t the sexiest way to use the database. It is actually better to create a provider for your database. To see how to created a shared provider, visit this article I wrote on the subject.

A video version of this article can be seen below.

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.