I've done a few projects now that make use of PhoneGap's database support. Like most things in PhoneGap, it just plain works. But I've encountered a few things I thought could be done a bit easier, so I've built a simple utility class for my future projects. I thought I'd share it with folks and see if it would be useful for others.

My utility library has the following features:

executeBatch: Given a file path (via URL), you can have PhoneGap read in an XML file that contains a set of SQL commands. So for example:

myDbController.executeBatch("sql/createtables.xml",successHandler,errHandler);

Where your XML would look like so:

<sql> <statement> create table foo if not exists foo(....) </statement> <statement> create table moo if not exists foo(....) </statement> </sql>

This was done to support the fact that unlike Adobe AIR, the SQLite support in PhoneGap doesn't allow you to ship a pre-populated database. (Although it is possible via a workaround: Prepopulate SQLite DataBase in PhoneGap Application.) The syntax for this was based on work from my fellow evangelist, Christope Coenraets.

executeSql: As you can guess, this simply wraps up executing SQL. While PhoneGap doesn't make this necessarily hard, I found the API a bit awkward in terms of all the callbacks you had to use. ("All" sounds like a lot - it's more like two - but you get the idea.) So for example:

dbController.executeSql("select * from notes", gotNote, errHandler);

The other nice thing this will do is automatically take the result set and return it as a simple array of objects. Again, it's not difficult to work with the normal result set, this is just a bit simpler.

Finally, the class has a simple init() handler that sets up your connections for you. What makes it nice is that it can also automatically call your batch scripts for you. So for example:

dbController.init("main","data/seed.xml",dbReady);

The code is below and is free to use by anyone.

var DBController = function() {

var db,success,failure;

return {

	init:function(name,importscript,successHandler)	{
		//todo - allow for version
		db = window.openDatabase(name,"1.0",name,100000);			
		if(typeof importscript !== "undefined") {
			console.log("being asked to run a script");
			if(typeof successHandler === "undefined") throw "Invalid call - must pass success handler when importing data";
			this.executeBatch(importscript,successHandler);
		}
	},

	executeBatch:function(path,successHandler,errorHandler) {
		success=successHandler;
		failure=errorHandler;
		
		$.get(path, {}, this.gotFile, "xml");
	},

	//sql, successHandler, errorHandler are required
	executeSql:function(sql,args,successHandler,errorHandler) {
		console.log('going to run '+sql+ '    '+arguments.length);
		//Don't like this - but way to make args be optional and in 2nd place
		if(arguments.length == 3) {
			successHandler = arguments[1];
			errorHandler = arguments[2];
			args = [];
		}
		db.transaction(
			function(tx) { tx.executeSql(sql,args,function(tx,res) {
				//todo - figure out fraking scoping rules and why line below didnt work, nor this.X
				//res = translateResultSet(res);
				var result = [];
				for(var i=0; i&lt;res.rows.length; i++) {
					result.push(res.rows.item(i));
				}
				successHandler(result);
			})}
		, errorHandler)	
	},
	
		
	gotFile:function(doc) {
		var statements = [];
		var statementNodes=doc.getElementsByTagName("statement");
		for(var i=0; i&lt;statementNodes.length; i++) {
			statements.push(statementNodes[i].textContent);
		}
		if(statements.length) {
			db.transaction(function(tx) {
				//do nothing
				for(var i=0;i&lt;statements.length;i++) {
					tx.executeSql(statements[i]);
				}
			}, failure,success);
		}
	},
	
	translateResultSet:function(res) {
		var result = [];
		for(var i=0; i&lt;res.rows.length; i++) {
			result.push(res.rows.item(i));
		}
		return result;
		
	}
		
}

};