IONIC WEBSQL EXAMPLE

Share the post

WebSQL Using Ionic 3 & Angular 5

Hello All, We will see today  How to implement WebSQL in the ionic 3 apps? Also, We will see Why to use WebSQL Instead of SQLite. Using WebSQL you can save up to 5MB of data which is more than enough for normal mobile applications. The main advantage of WebSQL is you do not need to install any plugin or npm dependency in your project. You just need to open DB then do the activity like create table & save, delete, update, get data. If you looking for a tutorial related to SQLite In Ionic 3 & Angular 5 then click here.

How to implement WebSQL in the ionic 3 apps?

To implement the WebSQL follow the below steps :

1.Create HTML :

       
<div>
<ion-item>
<ion-label>Username</ion-label>
<ion-input type="text" id="username" [(ngModel)]="username"></ion-input>
</ion-item>
<button ion-button full (click)="saveData()">Save</button>
<button ion-button full (click)="getData()">Get Data</button>
<button ion-button full (click)="updateData()">Edit</button>
<button ion-button full (click)="deleteData()">Delete</button>
</div>

Here, We are creating a simple HTML file that contains One input box & Four buttons.

For each button, We will call one ts function & do the Web SQL operation.

2.Create ts file :

import { Component } from '@angular/core';
import { NavController } from 'ionic-angular';
@Component({
selector: 'page-home',
templateUrl: 'home.html'
})
export class HomePage {

//web sql

db: any;
username: string = '';
constructor(public navCtrl: NavController) {

//web sql
 this.openDB();
}

//web sql
openDB() {
this.db = (<any>window).openDatabase("USERDATA", '1.0', "WebSQL Database", 2 * 1024 * 1024);
}
createTable() {
return new Promise((resolve, reject) => {
this.db.transaction((tx) => {
tx.executeSql('CREATE TABLE IF NOT EXISTS USERTABLE (SR_NO,USER_NAME,LAST_CHANGE_DATE)', [],
(tx, result) => {
 console.log('USERTABLE TABLE CREATED');
resolve(result);
},(error) => {
console.log('USERTABLE TABLE NOT CREATED');
 reject(error)
});
});
});
}

saveData() {this.createTable().then(res => {console.log("SUCCESS");this.db.transaction((tx) => {tx.executeSql('INSERT INTO USERTABLE (SR_NO,USER_NAME,LAST_CHANGE_DATE) VALUES (?,?,?)', [2, this.username, new Date().getDate() + "/" + (new Date().getMonth() + 1) + "/" + new Date().getFullYear()],
(tx, result) => {
console.log('DATA INSERTED SUCCESSFULLY');
},(error) => {
 console.log(error);
 console.log('DATA NOT INSERTED');
});
});
}, err => {console.log("FAIL"); });}

updateData() {

let query = "UPDATE USERTABLE SET USER_NAME='" + this.username + "' WHERE USER_NAME = 'test1' ";

console.log(query);

this.db.transaction((tx) => {
tx.executeSql("UPDATE USERTABLE SET USER_NAME='" + this.username + "' WHERE USER_NAME = 'test1' ", [],
(tx, result) => {
console.log('DATA UPDATED SUCCESSFULLY');
},(error) => {
console.log(error);
console.log('DATA NOT UPDATED');

});
});

}

deleteData() {

this.db.transaction((tx) => {
tx.executeSql("DELETE FROM USERTABLE WHERE USER_NAME = 'test2' ", [],
(tx, result) => {

console.log('DATA DELETED SUCCESSFULLY');

},(error) => {
console.log(error);
console.log('DATA NOT DELETED');
});
});

}

getData() {
this.db.transaction((tx) => {
tx.executeSql("SELECT * FROM USERTABLE", [],
(tx, result) => {
console.log(result);
console.log('DATA LOADED SUCCESSFULLY');
},(error) => {
console.log(error);
console.log('DATA NOT LOADED');
});
});
}
}

In the above example,

For function saveData() : We are create one table & insert the value of input box into the DB.

The Output for saveData is as follows :

Web_SQL_Ionic

For function getData(): We are getting all the data from USERTABLE.

The Output for getData is as follows :

Web_SQL_Ionic

For function updateData() : We are updating new test1 instead of test1 data from USERTABLE.

The Output for updateData is as follows :

Web_SQL_Ionic

For function deleteData() : We are deleting test2 username from USERTABLE.

The Output for deleteData is as follows :

Web_SQL_Ionic

Why use WebSQL instead of SQLite

The main advantage of WebSql is we can save up to 5MB & extend it up to 25MB which is more than sufficient for normal mobile applications.   We can able to see the full table structure in our browser. After the ionic serve, the application opens in the browser then for Chrome browser just need to right-click on the application.

=>Click on Inspect

=>Go to Application Tag,

=>On the Left side menu Storage

=> Web SQL

=>Web SQL

You can able to see our database name Please refer to the below image :  

Web_SQL_Ionic

We can write the query in the console of DB itself & execute it. Please refer to below image :  

Web_SQL_Ionic

No need to add any extra plugins & dependencies. Hence, I will always prefer to use Web SQL instead of Sqlite.

Leave a Comment