A database utility class for PhoneGap

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:


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:


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";

	executeBatch:function(path,successHandler,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 = [];
			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++) {
		, errorHandler)	
	gotFile:function(doc) {
		var statements = [];
		var statementNodes=doc.getElementsByTagName("statement");
		for(var i=0; i&lt;statementNodes.length; i++) {
		if(statements.length) {
			db.transaction(function(tx) {
				//do nothing
				for(var i=0;i&lt;statements.length;i++) {
			}, failure,success);
	translateResultSet:function(res) {
		var result = [];
		for(var i=0; i&lt;res.rows.length; i++) {
		return result;

}; </code>

Raymond Camden's Picture

About Raymond Camden

Raymond is a developer advocate for Extend by Auth0. He focuses on serverless and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support.

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