Testing SQLite on Android

Solving Chicken and Egg problem

Times, when testing Android code was a taboo are far behind us. With growth of the community, multiple members tried to educate others about importance of automated testing.

In past, Google Team have not put much attention into making Android code testable. Gladly though, they have took some steps to improve the live of professional developers, by providing better tooling, and publishing Best Practices for Testing.


While internet is full of general Tricks&Tips for testing, there is still not much knowledge, of how to solve some common problems. This is why, I decided to share my knowledge about testing storage related topics. The motivation was quite simple — show fellow developers that with some tweaks and tricks, testing can be achieved really easy.

The short, but still growing, list of articles on that topic looks like the following:

This article offers a simple way of how to test fetching and storing objects using SQLite based storage.

Storage layer is a fundamental piece of core logic in many Android applications. Providing offline access to data is a must have feature, to survive in crowded market, as well as opportunity to shine with outstanding user experience.

This is why, storage layer should be a stable piece of code, and testing increases the developer’s confidence about on that matter.

The following interface of the storage class points out the contract, which each implementation of WalletStorage should follow. It helps with finding out test cases, as well as guards implementation details from the outside world. In the most basic form, it contains two methods.

  • First, allows upper layers in architecture, to fetch all stored Wallet objects, with strong note, that returned collection will never be empty.
  • To store Wallet objects, client code, uses insert(wallet) method which persists the object, or throws exception when it is already persisted.

Finding the first case to cover is sometimes a tricky part. Even though there are only two methods to be tested, there is a problem with circular dependency between them.

In order to test fetching items, they need to be stored first, and vice versa, to store items, they need to be fetched later for assertion.

The solution presented below, leads to either duplicated tests, or single test, which covers both scenarios. It is not a comfortable scenario either, the problem is that when designing first test, implementation of all methods is needed (which breaks the purpose of Test First approach).

The solution would be finding trusted piece of code (hopefully tested as well), which gives the needed functionalities. One option could be using SQLite database, embedded into framework, to persist or fetch objects from database.

As much as we would like to trust 3rd party code, there is not much choice in this case. With few, simple SQL statements all needed cases would be covered. SQLiteDatabase class provides methods to query state of tables, as well as executing raw SQL statements.

Isolating test for storing Wallets, is possible by asserting table state with simple sql SELECT statement (SELECT name FROM tbl_wallet;). The only overhead for this solution is using the Cursor class to map results into Wallet object.

When testing storing Wallets, things get even simpler. There is no need to map Cursors into object. Insert statement on database is all what is needed (INSERT INTO tbl_wallet VALUES (‘uuid’, ‘name’);)

By covering those two fundamental cases, testing deleting or updating objects can follow the first approach. Now, there is a trusted (tested) prove that they both work as expected.

I would still advise you to use the same approach though. It makes all functionalities independent and easier to maintain in future.


The presented solution uses simple SQL statements to test CRUD operations using SQLite database. Even though, for some, it can sound like a controversial solution, I find it easy and clean to isolate test cases. As always, there is some extra work needed, but having confidence, that features work as expected is worth it!