Android SQLite Database – How to use onUpgrade() correctly

After reading this blog post (and a few others) on how to use the onUpgrade() method for your Android SQLite Database, I thought I should share my experience about how to correctly upgrade your database. It will also be beneficial to highlight why the final solution listed in that blog post would also fail at some point for some scenarios. The post explains quite well the drawbacks of some of the solutions that I also went through, but their final solution can also leave you in trouble.

When using your own SQLite database in Android, most people take for granted future releases of the application they are working on. Looking at the Android Developer documentation can also lead you down a rickety path.

Upgrading databases in Android is difficult. I’ve been there and battled the fires in production. It is not pretty.

These are the solutions that we went through:

Solution 1: Delete the tables that have changed and recreate them

Sure, when we started development this was obviously the easiest approach. We weren’t storing user data; our database was just a cache of things from the network. When the user upgraded, the “cache” would disappear and they would have to download all the data again.

It is also the solution that the Android Developer Documentation states. It’s worth noting that this solution will not work well for changing of table names.

You might have the following in your upgrade section of your app:

   db.execSQL(drop + Books.TABLE_BOOKS);
   db.execSQL(Books.CREATE_BOOKS);

This looks okay right?  This upgrade could leave dangling tables (and might fail if you use “DROP TABLE”) if you change the name of the table in one upgrade to another and you call the same lines of code later on. It is not a good idea to reference variables in your upgrade scripts.

Solution 2: Drop tables that change and recreate them, with variables defined in code for SQL statements

The following example illustrates the second option that was taken.

1. In version 1 of the application, we defined a variable named TABLE_NAME and
   SQL_CREATE_BOOK_ENTRY_TABLE

       public static final String TABLE_NAME = "books";
       public static final String COL_BOOKNAME = "book_name";
   
       public static final String COL_NO_PAGES = "book_pages";
   
       public static final String COL_DESCRIPTION = "book_description";
   
       public static final String SQL_CREATE_BOOK_ENTRY_TABLE = "CREATE TABLE " + TABLE_NAME + " (" +
               BookEntry._ID + " INTEGER PRIMARY KEY AUTOINCREMENT , " +
               BookEntry.COL_BOOKNAME + " TEXT ," +
               BookEntry.COL_DESCRIPTION + " TEXT, " +
               BookEntry.COL_NO_PAGES + " INTEGER )";

2. In our onUpgrade, we defined the following:

      @Override
       public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
           Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
           
           //Added new column to book table - book rating 
           if (oldVersion < 2){
               db.execSQL(DROP + BookEntry.TABLE_NAME);
               db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
           }
           //Rename table to book_information - this is where things will start failing.
           if (oldVersion < 3){
               db.execSQL(DROP + BookEntry.TABLE_NAME);
               db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
           }
           // Add new column for a calculated value. By this time, if I am upgrading from version 2 to 
           // version 4, my table would already contain the new column I am trying to add below, 
           // which would result in a SQLException. These situations are sometimes difficult to spot, 
           // as you basically need to test from every different version of database to upgrade from. 
           // Some upgrades might work and some might fail with this method.
           // It is best to follow the other method that is on the master branch of this repo.
           if (oldVersion < 4){
               db.execSQL("ALTER TABLE " + BookEntry.TABLE_NAME  + " ADD COLUMN calculated_pages_times_rating INTEGER;");
           }
           //As you can probably imagine, this is a terrible way to do upgrades, Please DONT DO IT!!!!
           
           
       }

   There are a few problems with the above code sample. First of all, the change from version 2 to 3 would not drop the table, as we changed the table name to book_information from books. This would leave a dangling table in the users database.

The next issue is that the next upgrade from version 3 to 4, would fail completely if the user is upgrading from version 1 of the app to version 4 as the column calculated_pages_times_rating already would exist due to us using the same create variable defined previously.

As you can see, this method can get messy really quickly. Writing code in this way is not maintainable as it can be difficult to version our variables.

Solution 3: Final Result – Create separate .sql script files

The better solution that we found (somewhere on the internet – can’t find the link now) was to store versioned sql files in the assets/ folder in the application. This ensures that each step of the process of the upgrade is correct. If you need to drop and recreate a table, you don’t use the variables defined in the application as these might change and are not versioned.

Below is a sample of the DatabaseHelper which runs sql scripts that will run the correct upgrades for each version of the database that a user has.

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 4;

    private static final String DATABASE_NAME = "database.db";
    private static final String TAG = DatabaseHelper.class.getName();

    private static DatabaseHelper mInstance = null;
    private final Context context;

    private DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    public static synchronized DatabaseHelper getInstance(Context ctx) {
        if (mInstance == null) {
            mInstance = new DatabaseHelper(ctx.getApplicationContext());
        }
        return mInstance;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(BookEntry.SQL_CREATE_BOOK_ENTRY_TABLE);
        // The rest of your create scripts go here.

    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.e(TAG, "Updating table from " + oldVersion + " to " + newVersion);
        // You will not need to modify this unless you need to do some android specific things.
        // When upgrading the database, all you need to do is add a file to the assets folder and name it:
        // from_1_to_2.sql with the version that you are upgrading to as the last version.
        try {
            for (int i = oldVersion; i < newVersion; ++i) {
                String migrationName = String.format("from_%d_to_%d.sql", i, (i + 1));
                Log.d(TAG, "Looking for migration file: " + migrationName);
                readAndExecuteSQLScript(db, context, migrationName);
            }
        } catch (Exception exception) {
            Log.e(TAG, "Exception running upgrade script:", exception);
        }

    }

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

    }

    private void readAndExecuteSQLScript(SQLiteDatabase db, Context ctx, String fileName) {
        if (TextUtils.isEmpty(fileName)) {
            Log.d(TAG, "SQL script file name is empty");
            return;
        }

        Log.d(TAG, "Script found. Executing...");
        AssetManager assetManager = ctx.getAssets();
        BufferedReader reader = null;

        try {
            InputStream is = assetManager.open(fileName);
            InputStreamReader isr = new InputStreamReader(is);
            reader = new BufferedReader(isr);
            executeSQLScript(db, reader);
        } catch (IOException e) {
            Log.e(TAG, "IOException:", e);
        } finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    Log.e(TAG, "IOException:", e);
                }
            }
        }

    }

    private void executeSQLScript(SQLiteDatabase db, BufferedReader reader) throws IOException {
        String line;
        StringBuilder statement = new StringBuilder();
        while ((line = reader.readLine()) != null) {
            statement.append(line);
            statement.append("\n");
            if (line.endsWith(";")) {
                db.execSQL(statement.toString());
                statement = new StringBuilder();
            }
        }
    }
}

Now in order to make an upgrade to your database you can do the following:

1. Create a SQL file and name it from_2_to_3.sql according to the version of the database you are upgrading from and to.
2. In that file you can create all your SQL statements.
3. Up the DATABASE_VERSION in your code to the new version number.
4. Don’t forget to change your create script for the changes if a user is installing a fresh version of your app.
5. When a user opens your app and is upgrading, the upgrade process will run through all the scripts stored in the assets/ folder that are needed in order to upgrade them to the latest version.

The main benefit of doing it this way is that you don’t have upgrade scripts that depend on variables defined in code. It is also a really neat way to see what changed per version of your database.

This way of upgrading your database is the most future proof solution that I have found.

I have uploaded a sample project for this on Github, master branch contains the end solution. Checkout the different releases to see how the upgrade process works and the different databases associated with each release. There is also another branch that contains the broken example called – failing-upgrades-in-code.

Let me know your experiences with upgrading SQLite databases in Android in the comments below!