How to do database schema migrations in Android?-ThrowExceptions

Exception or error:

Is there a standard way to do database schema migrations on Android? For example, user installs newer version of my Android app but the new version needs to make updates to the database schema (and wiping the user’s database and starting over is not an option!). So I need to run some ALTER statements and/or copy tables the first time my new version runs.

How to solve:

Yes SQLiteOpenHelper has support for migrating between different versions of DB schemas.

Upgrading is done by implementing

public abstract void onUpgrade (SQLiteDatabase db, int oldVersion, int newVersion)

And Rolling back to a previous version is also supported :

public abstract void onDowngrade (SQLiteDatabase db, int oldVersion, int newVersion)

###

With a little bit of thought, you can nearly automate a lot of the SQLiteOpenHelper methods. Have a look at this blog post http://www.greenmoonsoftware.com/2012/02/sqlite-schema-migration-in-android/

Update: I ran into some issues if the database updates take a while to complete. Have a look at this blog entry for an implementation that works for me. http://www.greenmoonsoftware.com/2012/05/android-developer-how-to-safely-update-the-application-database/

###

As of version 3.0 Flyway now supports database migrations on Android. It works with SQLite, but also with any other database you wish to use (local in-memory one, or a DB on the server for two-tier apps).

Migrations can be written in both SQL and Java.

###

All the above answers concerning SQLiteOpenHelper are correct, but they all contain a kind of antipattern – creating/modifying DB structure with Strings. It makes both development and maintenance more expensive. Sometimes a migration consists of many statements, some of them may be quite big. Writing them as Strings, without any syntax higlighting… well, for small structures it might work, but for some bigger ones it’d be a nightmare.

A better way to manage schema changes would be to keep each migration script in external file and make SQLiteOpenHelper’s onUpgrade method execute them automatically, in the proper order. Here’s an article covering this topic: http://www.vertabelo.com/blog/sqlite-on-android-handling-database-structure-changes. I hope this helps.

###

You can enhance default SQLiteOpenHelper with library Android-Migrator, that allow you to apply migrations from sql files in android assets folder.

###

I know this is an old one but I have developed an open source eclipse plugin that is a domain specific language (DSL written with XText) that allows you to specify a database as a list of migrations.

It generates your ContentProvider, SqliteOpenHelper and Contract for you, into an API that resembles an API like the Contacts API or Media API.

I have written a basic guide that helps you get started here http://robotoworks.com/mechanoid-plugin/mechanoid-db/

So far it supports create table, create view and alter table statements, I hope to fully implement Sqlite syntax where appropriate for this DSL.

Hope that helps and sorry for the spam! 🙂

###

yanchenko’s answer is correct, however, you might want to take a look at Droid Migrate, which is a framework I wrote that makes extensive use of SQLiteOpenHelper to facilitate automatic SQLite migrations (much like how Rails does it).

Leave a Reply

Your email address will not be published. Required fields are marked *