Automated Testing of SQLite Database Upgrades – Android

As a follow up to my previous post [How to use onUpgrade() correctly in Android], I decided that I should probably add some tests to avoid database upgrade failures in the future. Manually testing database upgrades in Android can be quite a pain and you are bound to miss something, especially if you have had loads of versions of your application released.

How to test SQLite database upgrades manually

1. Install the old APK.
2. Test your app to get the database filled with data that you need to test the upgrade on.
3. Install the new version of your app over the old version. Test that nothing is broken and that the database upgrades as expected.
4. Repeat. This process would need to be repeated for every database version that you have released. (In my case its something like 30 versions of the database 😑)

Testing 30+ different versions of your application is a tedious process and you are most likely to miss something in one of the tests.

How to test SQLite Database Upgrades automatically:

I decided to try add at least a basic test to the Github Project that demos Android Database Upgrades .

The test is simple: Instead of uninstalling one version of the app and installing the new one on top of it, it copies a version of the database from the /androidTest/assets/ folder and then performs an upgrade on that version to the latest version of your application.

If there is any issue with the upgrade, typically a SQLException will be thrown and the test will fail.

The following class is the test for upgrading your SQLite database.

@RunWith(AndroidJUnit4.class)
@LargeTest
public class DatabaseUpgradesTest {


    private static final String TAG = DatabaseUpgradesTest.class.getCanonicalName();

    /**
     * This test runs through all the database versions from the /androidTest/assets/ folder. It copies the old database to the file path of the application.
     * It tests that the database upgrades to the correct version.
     * If there is an issue with the upgrade, generally a SQLiteException will be thrown and the test will fail.
     * for example:
     * android.database.sqlite.SQLiteException: duplicate column name: calculated_pages_times_rating (code 1): , while compiling: ALTER TABLE book_information ADD COLUMN calculated_pages_times_rating INTEGER;
     *
     * @throws IOException if the database cannot be copied.
     */
    @Test
    public void testDatabaseUpgrades() throws IOException {
        DatabaseHelper.getInstance(InstrumentationRegistry.getTargetContext());

        for (int i = 1; i < DatabaseHelper.DATABASE_VERSION; i++) {
            DatabaseHelper.clearInstance();
            Log.d(TAG, "Testing upgrade from version:" + i);
            copyDatabase(i);

            DatabaseHelper databaseHelperNew = DatabaseHelper.getInstance(InstrumentationRegistry.getTargetContext());
            Log.d(TAG, " New Database Version:" + databaseHelperNew.getWritableDatabase().getVersion());
            Assert.assertEquals(DatabaseHelper.DATABASE_VERSION, databaseHelperNew.getWritableDatabase().getVersion());
        }

    }


    private void copyDatabase(int version) throws IOException {
        String dbPath = InstrumentationRegistry.getTargetContext().getDatabasePath(DatabaseHelper.DATABASE_NAME).getAbsolutePath();

        String dbName = String.format("database_v%d.db", version);
        InputStream mInput = InstrumentationRegistry.getContext().getAssets().open(dbName);

        File db = new File(dbPath);
        if (!db.exists()){
            db.getParentFile().mkdirs();
            db.createNewFile();
        }
        OutputStream mOutput = new FileOutputStream(dbPath);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = mInput.read(mBuffer)) > 0) {
            mOutput.write(mBuffer, 0, mLength);
        }
        mOutput.flush();
        mOutput.close();
        mInput.close();
    }
}

The testDatabaseUpgrades()  method requires that every time you release a new database version, you archive it in your androidTest/asset/ folder and name it database_v1.db etc.

It is advisable to do it with a database filled with data that needs to be migrated and not an empty one. You can obviously extend this kind of testing to do specific upgrade tests if there is a bigger complexity to your upgrade.

You can checkout the Github Project here for the full source code.

How do you test SQLite database upgrades? Leave a comment below!