How To: Android SQLite onUpgrade()

Android SQLite OnUpgrade(): The Problem

If you have written an Android app of even moderate size, you have likely implemented and utilized a SQLite Database. The internet is full of "helpful" examples on how to write the code needed to do this. Although many of these guides will get developers up and running, they can leave them stranded when it comes time to upgrade the app and by extension the app's database.

Sample App

For this guide, we will imagine we have an app that needs a single SQLite table. This table will be called Team. It will have the following columns: Id (int), Name (string), City (string), Mascot (string).

Standard OnUpgrade() Guide

When I first started making Android apps, I found a guide that looked something like this (Warning: this is not the code you want to copy!):

public class SQLiteHelper extends SQLiteOpenHelper {

    public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //database values
    private static final String DATABASE_NAME      = "demoApp.db";
    private static final int DATABASE_VERSION      = 1;
    public static final String COLUMN_ID           = "_id";

    //team table
    public static final String TABLE_TEAM       = "team";
    public static final String COLUMN_MASCOT    = "mascot";
    public static final String COLUMN_CITY      = "city";

    public static final String DATABASE_CREATE_TEAM = "create table "
            + TABLE_TEAM + "(" + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_NAME + " string, "
            + COLUMN_MASCOT + " string, "
            + COLUMN_CITY + " string);";

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

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

This works great when you are in early development. You can change tables by simply altering the create statement and incrementing the version number and you are good to go! The problem is that this technique will wipe out the table every time it is updated. That is hardly practical for apps in production. You don't want to be calling:

db.execSQL("DROP TABLE IF EXISTS " + TABLE_TEAM);

for each table every time a new version of the app is released.

Marginally Better Examples

A developer who realizes this is going to be a problem might Google something like "android onupgrade add column." Unfortunately on the day this article was written, 5 of the top 6 results have bad solutions! To view some of these bad solutions just look here, here, here, here, or here!

These guides all suggest altering the OnUpdate() function to better leverage the oldVersion parameter. But, they all have a problem. Here are some of the suggested "improvements". See if you can spot the problem with each:

Bad Example 1

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
{
   db.execSQL(ALTER_USER_TABLE_ADD_USER_SOCIETY);
   db.execSQL(ALTER_USER_TABLE_ADD_USER_STREET1);
}

The Problem: These alter statements will be run every time you update the app! If you go from version 1 to 2, they will be run and add the columns. Then, if you update from 2 to 3 (and do not change this code), you will have an error when attempting to add these columns again! You might be tempted to just alter this section every time you change the app, but this is error prone and will expose you to the same bug as example 3.

Bad Example 2

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    // If you need to add a column
    if (newVersion > oldVersion) {
        db.execSQL("ALTER TABLE foo ADD COLUMN new_column INTEGER DEFAULT 0");
    }
}

The Problem: I might give this very common answer the benefit of the doubt. But, this is a dangerous example, because many developers will simply copy this code. The problem is that every time an upgrade occurs, the if statement is true and we are left with the exact same problem as the previous code snippet.

Bad Example 3

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    String upgradeQuery = "ALTER TABLE mytable ADD COLUMN mycolumn TEXT";
    if (oldVersion == 1 && newVersion == 2)
         db.execSQL(upgradeQuery);
}

The Problem: This one is a bit tougher to spot. Consider what happens if a user goes from version 1 to 3. They will miss the upgrade query completely! These users will be in an awkward limbo where they are missing a few of the intermediate updates and do not have the expected sql schema. This would likely be a major issue for your app.

The Right Way

As we have seen, we do not want drop the database onUpgrade. We also do not want to assume users will get each upgrade sequentially, but do want to do some version checking before running update statements. Imagine we want to update our app to include coach's name in version 2. Then in version 3, we also want to add each team's stadium name. This leaves us with the following solution (This is the code you are safe to copy. Or better yet read, understand, and then copy):

public SQLiteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //database values
    private static final String DATABASE_NAME      = "demoApp.db";
    private static final int DATABASE_VERSION      = 3;
    public static final String COLUMN_ID           = "_id";

    //team table
    public static final String TABLE_TEAM       = "team";
    public static final String COLUMN_MASCOT    = "mascot";
    public static final String COLUMN_CITY      = "city";
    public static final String COLUMN_COACH     = "coach";
    public static final String COLUMN_STADIUM   = "stadium";

    private static final String DATABASE_CREATE_TEAM = "create table "
            + TABLE_TEAM + "(" + COLUMN_ID + " integer primary key autoincrement, "
            + COLUMN_NAME + " string, "
            + COLUMN_MASCOT + " string, "
            + COLUMN_COACH + " string, "
            + COLUMN_STADIUM + " string, "
            + COLUMN_CITY + " string);";

    private static final String DATABASE_ALTER_TEAM_1 = "ALTER TABLE "
        + TABLE_TEAM + " ADD COLUMN " + COLUMN_COACH + " string;";

    private static final String DATABASE_ALTER_TEAM_2 = "ALTER TABLE "
        + TABLE_TEAM + " ADD COLUMN " + COLUMN_STADIUM + " string;";

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) {
             db.execSQL(DATABASE_ALTER_TEAM_1);
        }
        if (oldVersion < 3) {
             db.execSQL(DATABASE_ALTER_TEAM_2);
        }
    }
}

Now the app will iterate over the update statements and run any that are needed. No matter what previous version was and regardless of what more recent version they upgrade to, the app will run the proper statements to take the app from the older schema to the properly upgraded one. One other thing to keep in mind is that you will also want to alter your create statements that are run inside the onCreate function. So, if you add a column as part of an update, add it in the create statement inside the onCreate function (for new users) and as an alter statement inside the onUpgrade function (for existing users).

Do you know of an even better way to handle Android SQLite database migrations? Send us your technique and if we agree, we will add it to this guide. Also, if you are looking for help on your upcoming Android app project, make sure you check out our Android development services!