Twitter: raymondcamden


Address: Lafayette, LA, USA

Creating a data backup of a WebSQL database

02-24-2014 4,508 views Mobile, JavaScript 12 Comments

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.

12 Comments

  • Jay #
    Commented on 03-16-2014 at 6:47 PM
    Nice post.
    What about restore that data to webSql?
    Can you help me? thanks.
  • Commented on 03-19-2014 at 6:39 AM
    The process would be: Download the data, iterate over it and do inserts.
  • MM #
    Commented on 04-22-2014 at 3:12 PM
    Is there a way to export websql to csv and store it locally on a device? Could you point me in the right direction?
  • Commented on 04-22-2014 at 3:16 PM
    "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.com/index.cfm/2014/4/3/Dy...

    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.
  • MM #
    Commented on 04-23-2014 at 6:19 AM
    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
  • MM #
    Commented on 04-23-2014 at 6:23 AM
    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
  • Commented on 04-23-2014 at 8:58 AM
    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.
  • MM #
    Commented on 04-23-2014 at 9:08 AM
    Sorry, I meant that I'm not using a server of any kind, the app is all local
  • Commented on 04-23-2014 at 9:09 AM
    Well then you would just use the FileSystem API to write it.
  • MM #
    Commented on 04-23-2014 at 9:12 AM
    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?
  • Commented on 04-23-2014 at 9:15 AM
    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.
  • MM #
    Commented on 04-23-2014 at 9:21 AM
    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

Post Reply

Please refrain from posting large blocks of code as a comment. Use Pastebin or Gists instead. Text wrapped in asterisks (*) will be bold and text wrapped in underscores (_) will be italicized.

Leave this field empty