So your android app is out in the wild with thousands of Happy Users and the Happy Users are begging and pleading for a new feature that requires a new field in your database. However, the aforementioned Happy Users, would quickly turn to Unhappy Users if your next upgrade wiped their data when it restructured the app’s database. What to do? Here’s how I upgraded my app’s Database schema, while preserving all of the user’s data. First of all, I use a DatabaseHelper class (that extends SQLiteOpenHelper) that performs some of the basic database needs (create, upgrade). The function we will concentrate on in this post is onUpgrade(). This function is called when the database version, represent with the constant DATABASE_VERSION is changed. Most barebones implementation of this function call will simply drop the app’s tables and recreate them with a call to onCreate():

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

However, this approach will destroy all of your user’s data, (ie the entire 2500+ CD collection they painstakingly entered with the soft keybard–if this were the case perhaps you should add a barcode scanner intent, alas, back to the story.) Which results in a Very Unhappy User (hereafter VUU). The solution, is to check for the app’s Database Version and make a call to our friend ALTER TABLE, like so.

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
   if(oldVersion == 2) {
          db.execSQL("ALTER TABLE albums ADD COLUMN hip_again integer");
   } else {
         ...
        db.execSQL("DROP TABLE IF EXISTS albums");
        onCreate(db);
  }

}

There! Now your user’s can flag their old CD’s with the shifting tides of what’s hip. Also note that should add the column in the onCreate method. So that Happy Users of the Future (hereafter HUF), will also have the field available to them:

private static final String DATABASE_CREATE =
          "create table albums (_id integer primary key autoincrement, "
          ...
          + "hip_again integer);";

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

One final thing is to increment the constant DATABASE_VERSION, so that android will call onUpgrade() the next time the app is started:

private static final int DATABASE_VERSION = 3;

That’s it.