android database github

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.

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.
    public void testDatabaseUpgrades() throws IOException {

        for (int i = 1; i < DatabaseHelper.DATABASE_VERSION; i++) {
            Log.d(TAG, "Testing upgrade from version:" + 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()){
        OutputStream mOutput = new FileOutputStream(dbPath);
        byte[] mBuffer = new byte[1024];
        int mLength;
        while ((mLength = > 0) {
            mOutput.write(mBuffer, 0, mLength);

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.

SQLite Automated Database Upgrades in Android

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!

3 replies on “Automated Testing of SQLite Database Upgrades – Android”

Hey Rebecca – thanks for the tip! We were delighted to implement your solution with our latest database upgrade.

However, with our automated testing infrastructure, my team member ahunt discovered an issue: Android databases created on newer devices cannot always be opened on older devices. Specifically, I created the database for testing on a Galaxy S4, Android 4.4, and our 2.3 emulators in the test infrastructure failed to open the database.

The error our 2.3 devices saw was:

database disk image is malformed

Surprisingly, this did not throw the Exception into our harness and it was followed by:

Deleting and re-creating corrupt database 

And then, since the database was recreated, the DB was at the latest version and the tests unexpectedly passed! Uh oh!

ahunt’s research into the SQLite docs backed up our problem:

Since 2004, there have been enhancements to SQLite such that newer database files are unreadable by older versions of the SQLite library. But the most recent versions of the SQLite library should be able to read and write any older SQLite database file without any problems.

We’re currently fixing the issue by creating all of our databases on our lowest API levels and it seems to be working.

To address your question about how we do database upgrades, we previously tested upgrades by copy-pasting the database creation code from the old revision into a mock SQLiteOpenHelper which overrides onCreate to prevent the SQLiteOpenHelper from running it’s upgrade algorithm. Instead, we run the code to create the old table.

In the test, we create the mock database, insert some values, re-open the helper with the version we want to upgrade to (by calling super.onCreate()), and asserting our data is consistent post-upgrade.

Unfortunately, I didn’t deal with this code closely enough to know whether or not this approach was worthwhile.

We ended up using your method this time around because it is simpler and provides a more realistic test (since we can have real data in the database).

Thanks again for your tips!

Hi Michael,
Thanks for this feedback, good to know that it wont work with older APIs. We only support 4.1 upwards which is probably why we haven’t experienced it.
Thanks for taking the time to write this up, I am sure some one else will come across this too!

Comments are closed.