Know How To Use SQLite In Ionic 2

Know How To Use SQLite In Ionic 2

Ionic 2

Ionic 2 is the upgraded version of ionic 1 and it has a lot of differences in it than the earlier one. Typescripts are introduced in this as well. Some new components are also included in ionic 2.

Let’s talk about the backend database with ionic 2. Nowadays requirements for offline application is becoming more popular. People want to have a quick and very much accurate application with or without internet connectivity. SQLite is a phenomenon, which can be useful in providing such feature.

SQLite

SQLite is a software library that implements SQL operations using SQL database engine. SQLite is the widely deployed SQL database engine in the world.

SQLite is a local storage for the applications, SQLite is used for storing data similarly we store data in server side websites using MySQL. SQLite provides the similar functions and queries as we have in MySQL

SQLite provides faster access to the data in an application. One can fetch the data from the server and can save in SQLite database so that later on we can use it from SQLite only because of which application’s performance will good rather than normal application.
 
Note: SQLite stores its database on the local device so that you can’t find your data in another device also due to this the size of an application is also got increased.

Why we use SQLite 

  • 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 their query difficulties, but there are limitations. And local storage has a limitation of 10MB of data.

These reasons are what makes SQLite a better choice if we compare it against local storage.

Connection with SQLite 

For using SQLite in our application we need to add the SQLite plugin given by Cordova in our ionic v2 application. It can be added via the following:


$ ionic plugin add cordova-sqlite-storage
$ npm install --save @ionic-native/sqlite

    
pages/home/home.ts

import {Component} from '@angular/core';
import {NavController, Platform} from 'ionic-angular';
import {SQLite} from "ionic-native";
import { AddNote } from '../addnote/addnote';
@Component({
  selector: 'page-home',
  templateUrl: 'home.html'
})
export class HomePage {
  public database: SQLite;
  public people: Array<Object>;
  addnote = AddNote;
  public username:any;
  public password:any;
  constructor(private navController: NavController, private platform: Platform) {
      this.platform.ready().then(() => {
          this.database = new SQLite();
          this.database.openDatabase({
            name: "data.db",
             location: "default"
           }).then((data) => {
              this.database.executeSql("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, firstname TEXT, lastname TEXT)", {})
                .then((data) => {
                    console.log("TABLE CREATED: ", data);
                   alert('db and table created ');
                }, (error) => {
                    console.error("Unable to execute sql", error);
                })
            }, (error) => {
                console.error("Unable to open database", error);
            });
      });
  }
  public add() {
    this.database.executeSql("INSERT INTO userdata (firstname, lastname) VALUES ('"+this.firstname+"', '"+this.lastname+"')", []).then((data) => {
            console.log("INSERTED: " + JSON.stringify(data));
            alert(JSON.stringify(data));

        }, (error) => {
            console.log("ERROR: " + JSON.stringify(error.err));
        });
  }
  Public select() {
       this.database.executeSql("SELECT * FROM userdata", []).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));
       });
   }

Here, in the platform.ready() function, we will create a new database and it will store in device, openDatabase() is a function for opening database, it has two cases if the Database does not exist then, it will create the DB and open it and if it already exists then, it will open that one. deleteDatabase() delete the database.  add() function will add the firstname and lastname in userdata table. executeSql() Execute SQL on the opened database. 

Note, you must open/create DB first, and ensure it resolved and successfully opened.

pages/home/home.html

<ion-content padding>
  <ion-item>
      <ion-input type="text" name="title"  placeholder="Firstname" [(ngModel)]="firstname"  required></ion-input>
  </ion-item>
  <ion-item>
     <ion-input type="text" placeholder="Lastname" [(ngModel)]="lastname" required></ion-input>
   </ion-item>
<button ion-button (click)="add()">Add</button>  <button ion-button (click)="select()">Select</button>

  <ion-card *ngFor="let peopl of people ">
      <div>{{peopl.firstname}}</div>
      <div>{{peopl.lastname}}</div>
  </ion-card>
</ion-content>

Note: Access SQLite databases on the device.

Feel free to review this document for more details. Leave your vital feedback and we are readily available for any query resolving. Hope you had a quick solution for your purpose, feel free to Hire our Hybrid App Development service, provider.