Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I've written several unit tests for an already existing DatabaseOpenHelper class. I covered the creation scenario and migration from old schema to new one. Unfortunately, I don't feel that my tests are good. For example, for DB creation I check that SQLiteDatabase. execSQL() was called three times (we have three tables so far) and check that query string is in set of specified SQL strings.

The issue so far - if someone changes order of columns the test will fail. Probably this is OK.

But what are other ways of SQL schema unit testing? Or don't you have unit tests for that, and rely only on the integration one?

share|improve this question

Instead of verifying that

  • colum1 is name,
  • colum2 is birthday,
  • colum3 is ....

i would just test if these colums exist after upgrade.

To do this I would create an integration test that

  • starts with a databasefile which has old database version,
  • run the updater and then
  • execute a sql-select for every table with a all fields.


  • select name, birthday, telefonnumber from customer
  • select orderid, orderdate, customerid from order

Test-Success means there is no database-exception for unknown table or field.

[Update 2.2.2013] Unittesting the DatabaseOpenHelper (Unittest=Testing in isolation) would requre to test without a real database and verifying that the database update script is valid or at least contain all neccessary fields/tables. In my opinion this is to much work. Integration testing with a real database is much easier.

share|improve this answer
Agree that Db changes rarely in my case and integration tests would be enough without unit testing. But I wonder if someone is unit testing DB part – Eugen Martynov Dec 30 '12 at 15:12

A schema comparison could be useful. You have one database that is known to have the "correct" schema. This could be a copy of the development database after a code freeze. Then create your new database. Use a comparison tool to identify any differences between the 2 databases. Microsoft Visual Studio comes with a schema comparison tool. There are several 3rd party tools. I use my own custom-built comparison tool which lists all the differences in 2 databases.

Alternatively you could simply check if your schema creation scripts execute without error. The scripts ARE the schema definition, so in theory you do not need to compare or check anything other than the fact they executed without error.

But in my experience people introduce changes to a database without scripting them. A comparison tool can catch these unscripted changes.

share|improve this answer
Mike, thank you for answer. This is functional tests what about unit testing? – Eugen Martynov Jan 30 '13 at 6:06

When I faced this problem the road I walked sent me right into checking the database definition sentence with the expected one. This was broadly comparing a string with another one.

I recognized how absurd this was when I found myself getting the sentence from the code execution, checking it manually in notepad and inserting it in the test every time I made a change in the schema.

Right now I use integration test for the schema validation. In my case this involves exercising all persistance related operations (excluding performance-related ones).

I do not like the time required for this approach, but it is the best fair approach I have found.

share|improve this answer
Thank you! Sorry for long reply, so no unit testing and only integration one – Eugen Martynov Mar 11 '13 at 10:33

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.