The simple way to add a prepopulated SQLite DB in PhoneGap

A common scenario for PhoneGap applications is that you want to include a prepopulated database that contains required information for the app. The plugin often used is the SQLitePlugin for iOS and Android.

There are several ways to accomplish this:

1. Use the storage API directly and populate the database with SQL statements (ref).
2. Write the code to manually to copy the prepopulated DB (ref1, ref2).

Option 1 quickly becomes considerable work for a DB containing a lot of data. The performance of this solution when requiring many queries can also be quite poor. Option 2 might be considerably better, because it gives you the option to populate the SQLite DB and bundle it with your PhoneGap application. This method uses the SQLitePlugin for both Android and iOS. However, you will need to write a bit of code manually to create the copy functionality, where the bundled DB-file is copied to the correct location for both implementations.

Because of these issues I decided to update the SQLitePlugins for Android and iOS to support prepopulated SQLite DB files. This blog article will show how to setup and use the plugins in your own PhoneGap projects.

I will not show how to create and setup PhoneGap projects, there are Getting Started guides available for both Android and iOS.

The first step after the PhoneGap projects are created is to setup the prepopulated DB files. This can easily be achieved using several SQLite tools, for example SQLite Manager for Firefox. Next we move on to how the DB files should be included in your projects.

Android

Plugin source code: https://github.com/jarlehansen/PhoneGap-SQLitePlugin-Android

For Android the DB file should be placed in the assets-folder, remember to use the file name *.db.
For example: TestDB.db

The DB files are loaded the same way as a standard database, with the command:

 var db = window.sqlitePlugin.openDatabase({  
   name : "TestDB"  
 });  

You do not need to include the .db file-extension, this is done automatically by the plugin.

What happens next is that the SQLitePlugin will first see if the DB already exists, if it does not it will try to find the prepopulated DB-file in the assets folder and copy this to the correct location. If no prepopulated DB file is found, this step is simply skipped. For all executions after this initial setup, the plugin will use the copied database.

iOS

Plugin source code: https://github.com/jarlehansen/PhoneGap-SQLitePlugin-iOS

The iOS setup is very similar. Copy the db-file into the project, for example directly under the project root.

The files are loaded exactly the same way as in the Android example shown. The plugin also works similar to Android, in that it only copies the prepopulated DB the first time and only if it is found in the application bundle.

And that is it, no need to manually write the insert statements or write custom code. Hopefully this will make it easier to add a prepopulated SQLite DB to your project. Let me know what you think in the comments :)


References (used when I created the plugin code):
- http://gauravstomar.blogspot.ca/2011/08/prepopulate-sqlite-in-phonegap.html
- http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications