WebSQL CRUD demo

 

websqo

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 
	
	CREATE TABLE T (
    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) {
tx.executeSql 
('CREATE TABLE IF NOT EXISTS chair (merchID TEXT PRIMARY KEY NOT NULL, price INTEGER)'); 
});


// insert rows - note the double quotes

db.transaction(function(tx) {
tx.executeSql 
("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) {
        console.table(sqlResultSet.rows);
    }
) } );


// 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

Advertisements

Storing data in your browser using SQL + pure JS

WEB SQL

This is is the world as it might have been, before certain unenlightened beings at Mozilla and MS decided peremptorily that developers should not be allowed to run SQL and persist large amounts of data via their browsers.

Well, the domain Webquery.org is still available! Maybe a few developers who have had enough of the npm / node / SQLite3 nonsense can band together and convince Opera to start a W3C revolution.

Meanwhile, click on the image to run the pen, then read the sad history of how SQL was killed during the browser wars  here.  The link and example code (that I modified slightly, since the original console bit didn’t work properly, and I am not sure if this is because the spec has been unsupported for a decade) were written by the brilliant Nolan Lawson.  Make sure you’re using Opera before heading to my Codepen.

See how simple and intuitive this all could have been?

Imagine wrapping all the WebSQL async callback funy business in JS functions and exposing only a simple-to-use SQL API to the data analyst, who would be able to use parameterized SQL on the fly, querying data on their local machine, without worrying about getting trapped in the land of a million parentheses — the way it was before the Web mutilated SQL!

“Web Query” is exactly how I would have liked to implement StoreBoss.

No npm.

No node.js.

No dance of a zillion nested parentheses and brackets.

Just vanilla JS and pure SQL in its original elegant nobility, with disk space on my comp where I securely encrypt my data and restrict access to it via icalcs.

I’ll have to try out the Dexie wrapper and see if I can up with something workable, using the brain-damaged IndexedDB.  I have some ideas already in that regard.