Ionic 2 SqlStorage with SQLite and Cordova -- Words (827)

Ionic2 Ionic
In this tutorial ,you are going to learn how to use SQLite with Ionic 2 ,Cordova and Ionic Native for data storage on Android and iOS mobile apps

Ionic 2 is today one of the most popular open source and free hybrid mobile framework for build hybrid mobile apps for Android ,iOS and even Windows Universal Platform .

One of the key requirements of a mobile application is data storage .It is true that you can use local storage just like any web app (Since hybrid apps are web apps in essence ) but it has limitations such as query difficulties ,i.e you can't build complex data queries , and capacity ,you can only store a maximum of 10MB of data .

So what solution we have ?

In this tutorial ,we are going to see how to use SQLite a light version of SQL which has no capacity limitation ( Depends only your device storage capacity ) and can execute complex SQL queries on data .

Now lets see how we can use SQLite with Ionic 2 .

First lets start by creating a new Ionic 2 project .Open up your terminal on Linux / MAC or your command prompt under Windows and type the following commands to scaffold a new project .

ionic start ionic2-sqlite blank --v2

You can't use SQLite on the browser so you need to use an emulator or your real mobile device .Before you can do that lets add a target platform (In my case it is Android )

cordova platform add android 

You need to have Java and Android SDK installed on your system and ANDROID_HOME environment variable set to the location of your Android SDK .

If you want to target iOS you need to build your app under a MAC system .

Next lets add the Cordova plugin for using SQLite

ionic plugin add cordova-sqlite-storage

We are not going to build a fully fledged app but a simple example which shows how to use SQLite with Ionic 2 .

Go ahead and open your home.ts file and add the import statement to import sqlite from ionic-native

import { SQLite } from ‘ionic-native’;          

Next declare a member variable to store an instance of SQlite class

sqlstorage : any  = null ;

Then on your class constructor add

 this.sqlstorage = new SQLite();

 this.sqlstorage.openDatabase({name: ‘items.db’,location: ‘default’}).then(() => {

  this.sqlstorage.executeSql(`create table if not exists items(
    reference CHAR(10) PRIMARY KEY,
    name CHAR(30),
    qMin FLOAT,
    qReal FLOAT
  ))`, {});

});     

After running your app a database with name items.db will be created and a database table with name 'items' will be created only if it doesn't exist .

Now how can we save some data on our database tabe items .

To save data you just need to write a SQL Insert query with some values .

For example

    let q = "INSERT INTO items VALUES (?, ?, ?, ?)";

Then simply execute the query

    this.sqlstorage.executeSql(q, { "ITEM000001","ITEM 000001",1.0,100.0});

How to retrieve DATA ?


Again to get or retreive data from database table items we just need to use the right sql order ,this time its Select .

let items = [];
this.sqlstorage.executeSql("SELECT * FROM items ").then((r) => {

  if (r.res.rows.length > 0) {
    for (var i = 0; i < r.res.rows.length; i++) {
      items.push(r.res.rows.item(i));
    }
  }

Here the complete example

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 {

    sqlstorage: SQLite;
    items: Array<Object>;

    constructor(private navController: NavController, private platform: Platform) {

        this.platform.ready().then(() => {
            this.sqlstorage = new SQLite();
            this.sqlstorage.openDatabase({name: "items.db", location: "default"}).then(() => {
                this.createTables();
                this.findAll();
            }, (err) => {
                console.log("!!! ", err);
            });
        });
    }
    public createTables(){
        this.sqlstorage.executeSql(`create table if not exists items(
            reference CHAR(10) PRIMARY KEY,
            name CHAR(30),
            qMin FLOAT,
            qReal FLOAT
        ))`, {});            
    }
    public addItem() {
        this.sqlstorage.executeSql(q, { "ITEM000001","ITEM 000001",1.0,100.0}).then((data) => {
            console.log("Success");
        }, (e) => {
            console.log("Error :  " + JSON.stringify(e.err));
        });
    }

    public findAll() {
        this.database.executeSql("SELECT * FROM items", []).then((data) => {
            this.items = [];
            if(data.rows.length > 0) {
                for(var i = 0; i < data.rows.length; i++) {
                    this.items.push(data.rows.item(i));
                }
            }
        }, (e) => {

            console.log("Errot: " + JSON.stringify(e));
        });
    }

}

Conclusion


As you can see ,using SQLite with Ionic 2 is dead easy and straightforward .All you need to do is to install the Cordova plugin for SQLite then build the SQL queries and execute them .

For more advanced examples you just need to look for any tutorial about SQlite on the web .What you can do normally with SQlite ,you can do here in the case of Ionic 2 .




mrnerd is a Web Entrepreneur & Developer, Digital Marketer and a Passionate Blogger. He writes about software and website development,money making tips and SEO etc.. He is a tech addict and a computer geek with a lot of hobbies.