WEBSQL, SQL at the client's end

WebSQL

SQL on clients end = webSQL 😎

Now you can have Traditional Database on the client's end. Guess what? you can define size by your self, isn’t that cool ?, yes that’s the new era of IT, you can do anything with your ideas.

What you gonna see is What ?, Why ?, How ?, When ? of web SQL + perks

some small prerequisites: you should have a basic idea of RDBMS, SQL, ACID property, and a bit of JS, that's it. get ready for thrill….. 😛

WHAT is webSQL ?

a new method to store structured data in traditional style (row and column format ) on the client's end, that’s what webSQL is, also you can read and write that storage space with SQLs, isn’t that cool? few other cool stuffs are like, you don’t need to open the DB and close the DB it gets done automatically, and what about the size? it completely depends on you, you can specify the size that you want, in some browsers, if it exceeds 5MB a popup will ask you to allow the DB size or not but in some browsers, no warnings are shown.

In short, you can say webSQL is the API to manipulate client-side DB using SQL. webSQL specifications are based around the specification of SQLite(3.1.19).

Methods that will be used

• openDatabase(): this method is used to open the existing database or create a new one
• transaction(): is the method used to create a transaction of data from clients end to DB
• executeSql(): is the method that executes our SQL requests

you can directly interact with DB using SQLs, but it’s not the safe way, we should use a transaction to do so, as you will have a backup plan if you use transaction 😉, you can rollback the transaction but not the SQL (remember ACID property of RDBMS ?)

HOW ?

Let’s play with code

Check webSQL support in the browser

const supports_webSQL = ("openDatabase" in window);

if (supports_webSQL) {
   // yaay.... webSQL is supported, play with code here
}

this will check the support of webSQL in the browser

Create Database

• you can create the database using openDatabase method, this method in webSQL API will open the existing database if it is present or else it will create a new one for you :D chillax

var db = openDatabase('school_database', '1.0', 'This is database for recording scholl data', 2 * 1024 * 1024);

openDatabase() accepts 4 params

• Database Name
• Database Version
• Database Description
• Database Size

and the 5th param (not supported yet) will be a callback function for the creation of a database.

basically this will notify whether DB created successfully or we are facing some issues (it is working fine in the current version of webSQL API without 5th param, creation, and versioning of Database works fine)

openDatabase returns the instance of the database, that will be useful to work with transaction ex: db.transaction

size of the database is on you, in some browser, it will show an alert if the size exceeds 5MB, but in some browser, it will not

Starting the Transaction

you might be thinking why to worry about transactions if we can directly communicate with DB using SQLs. 🤔

so the benefit is if you use the transaction to execute SQLs to communicate with DB you would have a chance to rollback, which you won't get while using direct SQLs.

we have error success callback functions in transaction method.

db.transaction(function (tx) {
   // your code here
});

you can write your executeSql methods in the transaction();

Executing SQLs

using this method you will be able to do read/write operations on the database, this will help you to protect your DB from SQL injections, also has callback methods to process query results.

this method accepts 3 params

• 1ts: SQL Query
• 2nd: map field, if you want to map fields you can add it in 2nd param, if not you can pass ‘ [] ’ empty array
• 3rd: callback function

creation of a table, insertion of data and retrieval of data will take place in transaction() method

• Creating Table

tx.executeSql('CREATE TABLE IF NOT EXISTS students (id PRIMARY KEY AUTOINCREMENT, name, class, age)');

you have a perk here, you might have noticed here, you can get rid of data types 🕶

here tx is the variable from callback function, executeSql methods work on the transaction object

• Inserting data

you can directly add values

tx.executeSql('INSERT INTO students (name, class, age) values ("Robert Daward", "10th", 18)');

OR

you can have a map of values if you are fetching it from other source and, ya I know you are worried about your DB security 😜, it also helps to prevent your DB from SQL injection 💉, here your 2nd param comes in action [], you need to map your field with values fields

tx.executeSql('INSERT INTO students (name, class, age) values (?, ?, ?)', [studName, studClass, studAge]);

‘ ? ’ will be replaced with field map values.

• Retrieving data

return tx.executeSql('SELECT * FROM students', [], function (tx, results) {
var len = results.rows.length, i;
var displayData = "";
for (i = 0; i < len; i++) {
console.log(results.rows.item(i).id);
console.log(results.rows.item(i).name);
console.log(results.rows.item(i).class);
console.log(results.rows.item(i).age);
};
document.getElementById("tableData").innerHTML = displayData;
return len;
});

callback function comes in the picture here, while you select rows from the database and if you need to do some processing on the retrieved data here you can.

When to use webSQL ?

• if you store your data on the client's end, the speed od retrieving that data will be faster as compared to any API call and you site/app will load fast and you will have a happy client 😃.

Browser support of webSQL