Thursday, September 8, 2011

Testing with sqlite

It can be daunting to start thinking about how you are going to test
database interaction for your app. Especially if you have never tested
database interaction. I have received several suggestions as to which
modules to use, however I have found the best success with just
creating a small sqlite db in memory,filling it with test data and
passing the db handle.

For example:

Then you can just start inserting your test case data and then set up the tests to pass or fail accordingly.

That's not to say that that I think this is the best way to go about
things. It's just that the database testing I have needed to do so far
hasn't been complex enough for me to have to go and find a more
dynamic way to perform the tests.

Now I have run into situations at thewhere sqlite doesn't fit the
bill. This usually entails when database specific functions are
used. For example, if the Oracle "TO_DATE" function is passed to a sqlite database, then it's going to fail.

So sure, sqlite testing is great in most cases, however if you have a
database team who writes your queries for you, or you tend to want to
do a lot of database specific operations I would suggest testing with
the same database you are using in production.


  1. You didn't list the suggestions you have received so my suggestion may be redundant but possibly informative for your readers.

    I use Test::Database for everything because it lets me set up my configuration details in one location (~/.test-database) and provides simple access across all my tests to the actual database I want to deploy against.

    Even better, by using Test::Database the tests I write can be fully exercised by anybody else running the tests (eg cpan-testers) who has test databases configured.

  2. Regarding TO_DATE (and other missing functions): when I was using SQLite for localised testing, I found it quite easy to work around this by generating UDFs:$dbh->sqlite_create_function(_$name,_$argc,_$code_ref_)