Twitter: raymondcamden


Address: Lafayette, LA, USA

Creating a data backup of a WebSQL database

02-24-2014 5,514 views Mobile, JavaScript 22 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.

22 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
  • Rakesh Gaur #
    Commented on 09-12-2014 at 3:15 AM
    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
  • Commented on 09-12-2014 at 6:13 AM
    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.
  • rakesh gaur #
    Commented on 09-13-2014 at 1: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
  • Commented on 09-13-2014 at 8:23 AM
    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.
  • Rakesh Gaur #
    Commented on 09-13-2014 at 8:57 AM
    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
  • Commented on 09-13-2014 at 11:55 AM
    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.
  • Rakesh #
    Commented on 09-14-2014 at 7:37 AM
    what do say about https://github.com/orbitaloop/WebSqlSync for sync ?
  • Commented on 09-14-2014 at 7:49 AM
    Haven't tried it myself. I say give it a shot and let us know here. :)
  • GG #
    Commented on 10-07-2014 at 6:44 AM
    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
  • Commented on 10-07-2014 at 6:48 AM
    GG, no, I haven't. No plans to at the moment. I still encourage folks who want to try to share their experience here.

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