WebSQL CRUD demo



Today was kind of a big deal.  I wrote a demo script of WebSQL CRUD operations against a custom relational database that resides in local storage in Windows.

In practical terms, this means that I can now have a free database platform for StoreBoss. It requires no install, has no runtime royalties, and is available to anyone using Chrome. Opera, Edge, Android and a variety of other browsers.

WebSQL does not, however, work on Firefox.  (Boo! Hiss!)

The simplicity of this WebSQL JS script is perhaps deceiving. What this means is that I will have all the power of native SQL at my disposal, without worrying about ORMs and Json and node and npm the rest of the Internet song and dance routine.

All the user will have to do in order to use my app is press a button.

That’s it.

Nothing to install.

It’s like your phone, car or TeeVee:  a data appliance that you just turn on and use on your desktop without worrying about sucking up bandwidth or chewing up CPU cycles on your shared WooCommerce hosted server platform.

It took a lot a work sifting through an atrociously-written W3C specification (yes, it was deprecated for political reasons 10 years ago, but check out CanIuse if you’re interested) to achieve this level of simplicity of use.

What this means is that I (or anyone else, for that matter) can now write a JS  SPA that can save a humungous amount of data locally via a performant relational engine that lives in the browser — with no, I repeat no dependencies:  just JS and a browser — and without having to tear your hair out dealing with the Pyramid of Doom.

Click to hear a really funny French dude describe callback hell, yes, in French.

If only (1) this standard wasn’t deprecated, and (2) same origin policy can be compromised (3) you have to encrypt the data in your app, and (4) no built in db login and pwd.

Aside from those minor * cough * drawbacks, here’s the code I came up with.


 WebSQL CRUD tutorial example 
 tested against Opera 63 and Chrome 76
 written by: Gounaman


// general SQL syntax when working with JS

databasehandle.executeSql(sqlStatement, optional arguments, optional callback, optional errorCallback);

// create a handle

db = openDatabase('showroom_db', '1.0', 'Furniture', 5000 );  // fine

// you have to specify exactly the db size and version number every time you want to accesss it
// no user id or password 

// dbhandle = open(dbname); would be simpler, but does NOT work

// create table

	normal SQL syntax 
    col1 int,
    col2 varchar(255));
	unfortunately SQLITE's actual syntax is a bit strange in places
	btw you have to terminate statments in sqlite3 with ; but not in WebSQL
	WebSQL syntax is even more out there, due to callbacks

db.executeSql ('CREATE TABLE chair (merchID TEXT PRIMARY KEY NOT NULL, price INTEGER);'); 

// does not work, but should -- interpreter complains it could not "prepare" the statement
// in DB2, PREPARE takes the form of 'EXEC SQL PREPARE S1 FROM :V1' 
// in WebSQL, this mean nesting the SQL call in a db handle method using a call back function

// this works ok

db.transaction(function(tx) {

// insert rows - note the double quotes

db.transaction(function(tx) {
("INSERT INTO chair (merchID, price) VALUES ('Louis XIV', 7000)"); 

// now let's grab the data

db.transaction(function(sqlTransaction) { 
sqlTransaction.executeSql('SELECT merchID, price FROM chair', [], function(sqlTransaction, sqlResultSet) {
) } );

// update col, del row, and drop table or database are left as exercises

Here are a few screen grabs off the Opera and Chrome consoles, where I ran the JS snippets.

browser local storage


opera console


chrome console