Testing SQLite database in Robolectric

Question

I'm trying to test a simple SQLite database using Robolectric in my Android application. I'm putting in some values, but when reading them back 0 rows are returned.

I'm using the SQLiteOpenHelper class to access the database.

// RequestCache extends SQLiteOpenHelper
RequestCache cache = new RequestCache(activity); 
SQLiteDatabase db = cache.getWritableDatabase();

// Write to DB
ContentValues values = new ContentValues();
values.put(REQUEST_TIMESTAMP, TEST_TIME); 
values.put(REQUEST_URL, TEST_URL);
db.insertOrThrow(TABLE_NAME, null, values);

// Read from DB and compare values      
Vector<Request> matchingRequests = new Vector<Request>();
db = cache.getReadableDatabase();
Cursor cursor = db.query(TABLE_NAME, SEARCH_URL_RETURN_COLUMNS, SEARCH_URL_WHERE, new String[] {url}, null, null, ORDER_BY, null);
int id = 0;

while(cursor.moveToNext()) {
    long timestamp = cursor.getLong(0);
    Request request = new Request(id++);
    request.setUrl(url);
    request.setCreationTimestamp(new Date(timestamp));
    matchingRequests.add(request);
}


// Assert that one row is returned
assertThat(matchingRequests.size(), equalTo(1));  // fails, size() returns 0

When debugging the code outside robolectric this works as expected. Am I doing anything wrong or is it not possible to test SQlite databases using Robolectric?

Answer 1

Robolectric 2.3 uses a real implementation of SQLite instead of a collection of shadows and fakes. Tests can now be written to verify real database behavior.

Answer 2

NOTE: This answer is outdated. If you are using Roboletric 2.X, please see https://stackoverflow.com/a/24578332/850787

The problem is that Robolectric's SQLiteDatabase is stored only in memory, so when you call getReadableDatabase or getWritableDatabase, the existing database will be overridden with new empty database.

I was running to the same problem and only solution I found was that I needed to fork the Robolectric project and added ShadowSQLiteOpenHelper to save database if same context is given two times. However the problem with my fork is that I had to 'disable' close()-function when contex is given because otherwise Connection.close() will destroy the database in memory. I have made pull request for it but it isn't merged to project yet.

But feel free to clone my version and it should fix your problem (If I understood it correctly :P ). It can be found on GitHub: https://github.com/waltsu/robolectric

Here is a example how to use the modification:

Context c = new Activity(); 
SQLiteOpenHelper helper = new SQLiteOpenHelper(c, "path", null, 1); 
SQLiteDatabase db = helper.getWritableDatabase(); 
// With the db write something to the database 
db.query(...); 
SQLiteOpenHelper helper2 = new SQLiteOpenHelper(c, "path", null, 1); 
SQLitedatabase db2 = helper2.getWritableDatabase(); 
// Now db and db2 is actually the same instance 
Cursor c = db2.query(...) ; // Fetch the data which was saved before 

Ofcourse you don't need to create new SQLiteOpenHelper, but that is just example that passing same context to two different SQLiteOpenHelper will give same database.