Android SQLite Database Upgrade Done Right

Initially I wasn't very fond of SQLite or any other SQL databse for that matter as an enormous amount of boilerplate code is to be written to get started. I worked with realm which is noticably faster than SQLite for Android and it takes five minutes to get started. As everything in life it comes at a price, and the price to be paid here is that the size of apk gets a litte larger due to inclusion of realm code.

Recently I was working on an application where it was important for the apk size to remain minimal. I had no choice but to use SQLite. Altough I was kind of forced to use it (by myself) the experience was not that bad other than huge amount of boilerplate code which can be minimised by using some ORM library like Sugar ORM or Google's Room Persistence Library.

Now the newfound problem was of upgrading the database whenever there was a change in the schema. Upgrading databases in Android is difficult. I tried two different approaches that are appropriate for two different scenarios.

> Approach 1

If you can afford to delete the data in the database, the simplest thing to do would be to delete everything whenever you need to makes changes to the schema of database and recreate the table with the new schema.

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(Schema.CREATE_ENTRIES);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + YOUR_TABLE);
    onCreate(db);
}

> Approach 2

If you need to preserve the old data in your database you have to make alterations to your schema without deleting it. A useful approach I found is was to use switch without break. All new updates are applied after the matching case of old version.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    switch(oldVersion) {
        case 1:
            db.execSQL(CHANGES_IN_VERSION_TWO);
            // no break statement since we want all new upgrades after the matching case
        case 2:
            db.execSQL(CHANGES_IN_VERSION_THREE);
        case 3:
            db.execSQL(CHANGES_IN_VERSION_FOUR);
    }
}

This way of upgrading your database is the best solution that I have found. Let me know your experiences with upgrading SQLite databases in Android in the comments below.

Show Comments