Simple trick to make your SQLite Database tests faster on Android

One parameter can make a difference

It is a known fact, that developers complain about duration of test execution. Many times, this is the main argument against writing tests at all. Sometimes, we cannot do much about this, but in most cases, there is a way to make it work fast.

Another known fact, is that testing Android applications is especially tricky, or at least most of us try to make a noise like that around other fellow developers. When in few cases it could be true, with some small tricks, and browsing documentation, it is not always the case.

Let’s start with some theory about SQLite on Android.

SQLiteOpenHelper is an abstract class, providing an interface for managing database creation and version management. It provides two mandatory methods, which have to be implemented.

• onCreate(SQLiteDatabase db) is the place, where initial schema goes in. Here, we see sql commands to be feed into SQLiteDatabase::execSQL(String sql) methods.
• onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) handles all updates to our schema. Depending on oldVersion, and newVersion, developers should run sql to alter schema and migrate data.

SQLite database is a file backed, persistent storage. The file is located in application folder. To be more specific, inside databases folder, like /data/data/your.app.package/databases/database_name

Going back to our topic, each time test is executed, database has to be opened, requested operation has to be invoked and finally database get closed. This means touching file storage.

Disk operations are slow, and makes developers unhappy.

— Me, developer, mostly happy!

The good news is, that there is a way to skip disk operations at all. If you take a look at the documentation, by passing null as a database name, we get in memory database.

Sounds cool, let’s see some code.

Below is the bare bone implementation of SQLiteOpenHelper class. Mind the VisibleForTesting annotated constructor, which will be used to create in memory database for tests. For readability, implementations of onCreate and onUpgrade are left blank.

public class Database extends SQLiteOpenHelper {
    public Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @VisibleForTesting
    Database(Context context, String name, int version) {
        super(context, name, null, version);
    }

    @Override public void onCreate(SQLiteDatabase db)
    @Override public void onUpgrade(SQLiteDatabase db, int a, int b)
}

The only overhead here is the need to have extra constructor. This could be avoided, by having single constructor, and providing name and version from outside of this class (consider Dependency injection). Encapsulating those values inside SQLiteOpenHelper implementation is common practice though, so developer should decide what fits him better.

Going to the test class, now we need to inject our database object (with null-name parameter) in any possible way (still Dependency Injection…), and concentrate on testing the logic.

A simple test could look like that..

@RunWith(AndroidJUnit4.class)
public class WalletModelTest {
    Database db = DI.storage().testDatabase();

    @Test
    public void storeWallet() {
        db.insertWallets(Wallet.create("Test-Wallet"));
        
        assertThat(db.listWallets())
                .containsExactly(wallet);
    }

    @Test(expected = DatabaseConstraintException.class)
    public void blockStoringDuplicates() {
        String walletName = "Test-Wallet";

        db.insertWallets(Wallet.create(walletName),
                         Wallet.create(walletName));
    }
}

But why do we even want to test Storage layer?

These days, most of local caches are implemented using SQLite provided by framework. This core component of most of the applications has to be stable and understandable by each team member.

Additionally, there is much business logic around SQLite database, schema creation and migration, just to mention few. It is a fully valuable piece of code to be covered with tests.

Other point is, that mostly developers write abstraction on top of SQLite database, with custom storage logic. This definitely sounds like a good candidate for test coverage. By having in memory database, there is no need to mock it.

Like it or not, there are no more excuses to test your storage. When done correctly, there is no need to blame time spent waiting for Jenkins reports.