Green Moon Software, LLC

SQLite Schema Migration in Android

In a previous post, I discussed how to version an RDBMS database schema. In Android, that concept is built into the API. The [android.database.sqlite.SQLiteOpenHelper][3] has several helper methods.

  • onCreate – called when the database is being created. This method creates the initial schema. This is analogous to the create-schema.sql
  • onUpgrade – called when the database should be upgraded. The method is passed an old version number and new version number. This is analogous to the patches directory.
  • onDowngrade – called when an prior version of the application is ran. This is an odd use-case in an enterprise application. However, it is more common in a mobile or client application.

The main concern is managing the schema version number. I did not want to manually update the version number every time I modified the schema. The following showcases how to accomplish this is a more automated fashion.

First, create a class that extends SQLiteOpenHelper.

Then create an inner class called Patch.

This class will represent a patchset to be applied.
Add the following methods in the MySqliteOpenHelper class:

Notice the PATCHES variable first reference in //1. That is the key to this whole operation. We should define it now.

And there you have it. You can have multiple db.execSQL(...) statements in each apply or revert methods. Now when you need to modify your SQLite database schema, simply add a new Patch to the PATCHES array. Your application will take care of the rest.

comments powered by Disqus