Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Using WebSQL Instead of Local Storage In Your Web App

TwitterFacebookRedditLinkedInHacker News

When developing web applications a common way to store data is to use local storage. This NoSQL approach is great, but what if you’re coming from an RDBMS? With HTML5, you have access to the WebSQL API which allows us to use SQL queries for handling client side storage.

This guide will show you how to use the WebSQL API, which is based around SQLite, in your web application. This information can even be extended for use with mobile hybrid applications.

I’m going to be using a Terminal in this guide. Let’s start by creating a simple web application:

mkdir WebApp
cd WebApp
touch index.html
touch app.js

All our JavaScript code will reside in the app.js file and all our UI code will appear in our index.html file. To start things off, lets go ahead and make our super simple front-end:

<!DOCTYPE html>
<html>
    <head>
        <script src="app.js"></script>
    </head>
    <body>
        <button onclick="create()">Create</button>
        <button onclick="insert('Nic', 'Raboy')">Insert</button>
        <button onclick="select()">Select</button>
    </body>
</html>

Notice the three buttons and the onclick functions that go with them. We are going to make them in our app.js file next. The create() method will create a table if it does not already exist. In our example, the table will be called person and contain a column for firstname and lastname. The insert(firstname, lastname) method will insert a new person into our table, and the select() method will print all table records.

This brings us to our app.js file. Go ahead and open it and add the following:

var db = openDatabase("my.db", '1.0', "My WebSQL Database", 2 * 1024 * 1024);

var create = function() {
    db.transaction(function (tx) {
        tx.executeSql("CREATE TABLE IF NOT EXISTS people (id integer primary key, firstname text, lastname text)");
    });
}

var insert = function(firstname, lastname) {
    db.transaction(function (tx) {
        tx.executeSql("INSERT INTO people (firstname, lastname) VALUES (?,?)", [firstname, lastname]);
    });
}

var select = function() {
    db.transaction(function (tx) {
        tx.executeSql("SELECT firstname, lastname FROM people", [], function(tx, results) {
            if(results.rows.length > 0) {
                for(var i = 0; i < results.rows.length; i++) {
                    console.log("Result -> " + results.rows.item(i).firstname + " " + results.rows.item(i).lastname);
                }
            }
        });
    });
}

Let’s break down the above code so we can understand what is happening.

var db = openDatabase("my.db", '1.0', "My WebSQL Database", 2 * 1024 * 1024);

The above line of code will try to open a database called my.db if it exists, otherwise it will create one approximately 2MB in size.

In each of our functions we start a database transaction and query exactly like we would in any other SQL database. The queries accept prepared statements, so you can pass in your own variables without having to craft a complicated string of text.

Just like that you can start using SQL, client side, in your web applications.

If you’re building mobile hybrid applications with Apache Cordova, you can check out my previous article on the topic. It uses a library that makes use of the WebSQL API.

Nic Raboy

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.

Search

Follow Us

Support This Site