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.
Archived Comments
Nice post.
What about restore that data to webSql?
Can you help me? thanks.
The process would be: Download the data, iterate over it and do inserts.
Is there a way to export websql to csv and store it locally on a device? Could you point me in the right direction?
"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.
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
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
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.
Sorry, I meant that I'm not using a server of any kind, the app is all local
Well then you would just use the FileSystem API to write it.
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?
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.
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
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
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.
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
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.
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
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.
what do say about https://github.com/orbitalo... for sync ?
Haven't tried it myself. I say give it a shot and let us know here. :)
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
GG, no, I haven't. No plans to at the moment. I still encourage folks who want to try to share their experience here.
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.
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.
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
I don't mind at all - borrow at will. :)
Hi,
How much data in MB we can store on client site using WebSQL?
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.
What is this convertResults() function that you are using?
The code is here:
https://static.raymondcamde...
It basically converts a WebSQL Row object into a simple object.
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!