AngularJS, HTML5, JavaScript

WebSQL : Welcome to client side DB – Part1

I think , We,all the Devs, know about SQL . Like , we have already worked with MySQL , MS SQL Server etc .

But then what is WebSQL ? If I say in a simple statement – WebSQL is SQL which is available in Client Browser .

Inside Browser :S ? Where is it ? I never saw that :@

Then please see the below image ūüôā

WebSQL in Chrome Browser

WebSQL in Chrome Browser

Now lets begin…

With HTML5, there was also a desire to develop applications so that they’d run off-line. Where could they store data when there is no Internet?

Currently , we can store Data in browser in the below ways –

1.Local Storage
2.WebSQL
3.IndexedDB

You can get few browsers do not support all these , like previous versions.

LocalStorage can store data in Key-Value pairs but nothing more than that.To know more about LocalStorage , you can check out my another Article on LocalStorage .

WebSQL is based on a free database product called SQLite which is written in C , Go here

In a WebSQL Database , we can create DB and Tables kinda like MySQL or SQL Server and store Data in the same manner what we knew earlier ūüôā

Lets do some Coding…

I have used WebSQL in a AngularJS application . I have created an Angular Service which is separated from another Controllers and can be injected in any controller when needed where I have used WebSQL APIs .

Below is the code of my Service…

app.service('WebSqlDbService', function ($resource) {

    this.createDbAndTables = function () {
        var db = openDatabase('myClientDB', '1.0', 'Mobile Client DB', 2 * 1024 * 1024);
        db.transaction(function (tx) {
            tx.executeSql('CREATE TABLE IF NOT EXISTS User (UserInfo)');
        });
    };

    this.storeUserInfo = function (user) {
        var db = openDatabase('myClientDB', '1.0', 'Mobile Client DB', 2 * 1024 * 1024);
        db.transaction(function (tx) {
            tx.executeSql('INSERT INTO User (UserInfo) VALUES ( ?)', [JSON.stringify(user)]);
        });
    };

});

Then I have used WebSqlDbService in my Controller and called the Functions of the Service something like this…

$scope.userInfo = {
            UserId: 'as34fsdf*i#sad454',
            UserName: 'shahjada',
            IsAdmin: true
        };

        webSqlDbService.createDbAndTables();
        webSqlDbService.storeUserInfo($scope.userInfo);

I have saved the JSON Object by Stringifying , and when we will get this from WebSQL , we will convert it to JSON Object…

After saving Object to WebSQL DB , my DB and Table looks like below…

WebSQL in Chrome Browser - DB and Tables

WebSQL in Chrome Browser – DB and Tables

We can also write SQL Queries like SQL Server Management Studio…

Just follow the Image below…

WebSQL in Chrome Browser - SQL Queries

WebSQL in Chrome Browser – SQL Queries

Its nice and cool , right ?

I will write another Article on IndexedDB very soon .
Keep an eye on my Blog Site , Share/Subscribe…

Thanks and regards

Standard