Creating a data backup of a WebSQL database

This post is more than 2 years old.

I haven't written much about WebSQL lately, mainly because it is Dead Spec Walking. However, it still works in Cordova (for now), and I get questions from time to time. This one in particular was kind of interesting. Plus, the guy asking me for help with this was super nice even though I kept delaying my answer over a few weeks. :)

His question was simple - how to create a data backup of the database. I'm stressing "data" backup because the WebSQL database is stored in a single file. You could - potentially - just grab the bits. But the assumption here is that you want the data from the various tables and not the actual bits.

With that in mind then the solution is rather simple. For each table you select * from it, convert the SQLRecordSet object into a simple array of structs, and then... well... do whatever you want. For my demo I'm converting it to a JSON string with the assumption that you want to send it over the wire.

I took an existing demo (NoteStrap) that simply supported saving notes. It has basic CRUD (create, read, update, delete):

To make things a bit more interesting, I added a second table, called log, where I store log messages for deletes and add/edits.

Ok, so how do we handle the export? In theory doing a simple SQL select operation is trivial, but remember that the call is asynchronous. Since we have two tables, we need a nice way of handling them and running only after both are done. For that, I used promises. Here is the solution I came up with. (Note, I also added a simple "Backup" button to the UI for testing.)

function backup(table) {
	var def = new $.Deferred();
	db.readTransaction(function(tx) {
		tx.executeSql("select * from "+table, [], function(tx,results) {
			var data = convertResults(results);
			console.dir(data);
			def.resolve(data);
		});
	}, dbError);

	return def;
}

$(document).on("click", "#doBackupBtn", function(e) {
	e.preventDefault();
	console.log("Begin backup process");

	$.when(
		backup("notes"), 
		backup("log")
	).then(function(notes, log) {
		console.log("All done");
		//Convert to JSON
		var data = {notes:notes, log:log};
		var serializedData = JSON.stringify(data);
		console.log(serializedData);
	});

});

Note that this does not properly handle errors, but you get the basic idea. The backup function works with jQuery promises so if I add a new table I just need to append the call in my when block and add the corresponding data to my result.

You can test this yourself here, but please remember it will only work in browsers that support WebSQL.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Jay posted on 3/17/2014 at 3:47 AM

Nice post.
What about restore that data to webSql?
Can you help me? thanks.

Comment 2 by Raymond Camden posted on 3/19/2014 at 3:39 PM

The process would be: Download the data, iterate over it and do inserts.

Comment 3 by MM posted on 4/23/2014 at 12:12 AM

Is there a way to export websql to csv and store it locally on a device? Could you point me in the right direction?

Comment 4 by Raymond Camden posted on 4/23/2014 at 12:16 AM

"Is there a way to export websql to csv and store it locally on a device? "
Yes, instead of just creating a JSON version, you would create a CSV version. If you Google for JS and CSV, you will see examples of this. It isn't difficult. Here is one example: http://www.raymondcamden.co...

As for storing it on the device... well on iOS, you can't really just download via the web browser. With PhoneGap though you could use the FileTransfer API.

Comment 5 by MM posted on 4/23/2014 at 3:19 PM

I am using PhoneGap for iOS and I'm really struggling with this. I have a multi-table websql database and I can't seem to find an efficient way to export the whole database as a CSV file and then download it to the device locally. As for accessing the file that is a different matter as iOS devices don't give you access to downloaded files

Comment 6 by MM posted on 4/23/2014 at 3:23 PM

and to add to the above, you say use the FileTransfer API, but my app is purely client side so I don't think FileTransfer would support this

Comment 7 by Raymond Camden posted on 4/23/2014 at 5:58 PM

MM, you first say you are using a PhoneGap app and then say 'purely client side' - I don't know what you mean by this. PhoneGap apps support the FileTransfer API.

Comment 8 by MM posted on 4/23/2014 at 6:08 PM

Sorry, I meant that I'm not using a server of any kind, the app is all local

Comment 9 by Raymond Camden posted on 4/23/2014 at 6:09 PM

Well then you would just use the FileSystem API to write it.

Comment 10 by MM posted on 4/23/2014 at 6:12 PM

Alright thanks, one more thing, sorry to bother you. Once the file is on the device's file system. Are there any ways to access it in iOS devices or is this impossible to do?

Comment 11 by Raymond Camden posted on 4/23/2014 at 6:15 PM

Well, your PhoneGap app can read it of course. If you store the file in a location accessible to other apps, then they would be able to as well.

Comment 12 by MM posted on 4/23/2014 at 6:21 PM

Right, I see. My initial goal was to export as CSV and then users could manually access the file and attach it to an e-mail, for example and then the CSV file could be opened on Excel on their desktop computer. Thanks for the help I'll look into the FileSystem API

Comment 13 by Rakesh Gaur posted on 9/12/2014 at 12:15 PM

Hi,

My requirement is

1.Create database using websql
2.Insertdata in tables
3.Whlie inserting data in tables at the same time I need to write the same data in Accessdatabase in Local system for backup so that if user will delete history then I can restore the same data in websql database

Please help me in 3rd ponit

Thanks
Rakesh Gaur

Comment 14 by Raymond Camden posted on 9/12/2014 at 3:13 PM

What do you mean by "local system" - you cannot run Access on the device. If you mean server, then you would use Ajax to send the data to your server.

Comment 15 by rakesh gaur posted on 9/13/2014 at 10:29 AM

Hi Raymond,

Local system means any device or computer.ok if access database will not work on device then

1.I want to write data in json format on any device or computer while inserting data in websql tables.

Or if you have better suggestion please guide me

Comment 16 by Raymond Camden posted on 9/13/2014 at 5:23 PM

I'm not quite sure what you are asking. Yes, you can generate JSON in JavaScript and send it to a server, but you need *some* server to integrate with your database. If you have never used an app server like PHP, ColdFusion, etc before, then you need to do some research in that area. Or consider a solution like Parse.com.

Comment 17 by Rakesh Gaur posted on 9/13/2014 at 5:57 PM

Hi,
I am writing complete problem for which I am struggling for solution.
I am creating complete offline application in html5 for the b2b client.
Suppose one user named ‘John’ is logged in to the application using his laptop and inserts some entries into web sql db and logged out. After sometime, he logged in to the same application using his tab, then I want DB in both i.e. in Laptop as well as tab should be synchronized.

1. If user will delete browser history then data should be restore from backup (either json file ) in websql db
2. If there would be no internet connectivity then all data modification and new data would be maintained in websql as well on device as a backup so that if user will delete history then we can restore data from backup which is stored on device. When internet would be available then all modified data would be synchronized with database.

Thanks
Rakesh Gaur

Comment 18 by Raymond Camden posted on 9/13/2014 at 8:55 PM

What you want isn't possible using just WebSQL. WebSQL is client side - so the data on the laptop is ONLY on the laptop. If you synced with a central server, then both the laptop and the mobile app could share the same data.

As for *building* that, I can't tell you how to do that in a simple blog comment.

Comment 19 by Rakesh posted on 9/14/2014 at 4:37 PM

what do say about https://github.com/orbitalo... for sync ?

Comment 20 by Raymond Camden posted on 9/14/2014 at 4:49 PM

Haven't tried it myself. I say give it a shot and let us know here. :)

Comment 21 by GG posted on 10/7/2014 at 3:44 PM

Have you tried the problem statment which Rakesh asking.If possible please write a blog for this kind of problem statement which will help to lots of new beginers

Comment 22 by Raymond Camden posted on 10/7/2014 at 3:48 PM

GG, no, I haven't. No plans to at the moment. I still encourage folks who want to try to share their experience here.

Comment 23 by Rakesh posted on 10/29/2014 at 12:56 PM

have a requirement to build a web sales app that must be available offline. The app needs to allow the sales team to create a sales quote at a customer site. They may or may not have internet access. I was originally going to build a windows forms application, but then uncovered information about HTML5 Offline Mode. I'm thinking this is a very viable option, but not sure how to handle the database (or not). When online the app should use the database on the server, but when offline it should use the local copy (or xml or disconnected recordset, or ?). I could either manually keep the 2 in sync or use something like Microsoft Sync Framework.

I'm looking for any input you can give as I'm just starting this project. I think this is going to be a fun and cool project with room to learn a ton.

Comment 24 by Raymond Camden posted on 11/2/2014 at 12:34 AM

Well, it is kind of a broad question. You can store data locally using a number of methods: Local Storage, Web SQL, IndexedDB, and you can sync it to a server by using logic. By "logic" I mean code that looks at what is local that hasn't been pushed to the server and then processes it.

Comment 25 by Steven de Salas posted on 2/5/2016 at 3:01 AM

Hi Raymond,

Thanks for this simple and elegant example. I got a library on Github http://github.com/sdesalas/... but after seeing how easy you made this look - particularly around using promises - I think I'm going to pinch a couple of ideas from you if you don't mind.

Just wanted to expand a bit on your mention of WebSQL being a 'Dead Spec Walking'. In my view the situation here is that the W3G working group was bit immature pulling off the plug just because they couldn't work out a middle ground between vendors.

WebSQL is by far the most useful of client-side storage technology (IndexDB is powerful too but the complexity of the API provides a major hurdle to its use) and it IS supported in all main WebKit-based browsers (which really means most of the market - including IOS and Android apps thanks to Cordova).

It is a pity to see such a failure to compromise, but having said that I doubt that support will/can be withdrawn at such an advanced stage and really hope that the W3G guys (particularly Mozilla - who is carrying on their own agenda by pusing IndexDB instead) can start behaving like adults and agree to keep working on it. I might put a petition together sometime when I get a chance.

All the best and thanks for the post!!
Steven

Comment 26 (In reply to #25) by Raymond Camden posted on 2/5/2016 at 2:44 PM

I don't mind at all - borrow at will. :)

Comment 27 by vijay posted on 9/1/2016 at 7:03 AM

Hi,
How much data in MB we can store on client site using WebSQL?

Comment 28 (In reply to #27) by Raymond Camden posted on 9/1/2016 at 2:29 PM

It depends. Sorry - there is no firm answer to this. For desktop browsers, generally, it is a portion of the hard drive size, and some browsers will warn you after a threshold.

Comment 29 by Lewis Mutton posted on 1/11/2017 at 4:31 PM

What is this convertResults() function that you are using?

Comment 30 (In reply to #29) by Raymond Camden posted on 1/11/2017 at 4:42 PM

The code is here:

https://static.raymondcamde...

It basically converts a WebSQL Row object into a simple object.

Comment 31 (In reply to #30) by Lewis Mutton posted on 1/12/2017 at 9:52 AM

That is exactly what I have been looking for - I have attempted using lodash and ramda functions but they didn't work on an iPad for some reason.... Thanks!