[Tutor] How to test my code's interactions with SQLite db?

Ben Finney ben+python at benfinney.id.au
Sun Aug 16 10:10:49 CEST 2015

boB Stepp <robertvstepp at gmail.com> writes:

> Being committed to TDD for this project, I am not yet ready to write
> code till I figure out how to write the tests. It occurs to me that
> writing tests for code that interacts with the SQLite db may be
> non-trivial (At least for me!).

That's correct.

One of the primary benefits of Test-Driven Development is to enforce
good design on your code: you'll need to design your system so that it's
testable with clear, narrowly-defined interfaces.

SQL queries are *not* a clear, narrowly-defined interface. So, as you're
discovering, it is very difficult to write unit tests for code that
could execute some arbitrary query.

So that points to a need for better design: Don't directly issue
arbitrary SQL queries in code which implements higher-level features.
Instead, define a much more specific interface between your feature code
and the lower-level code that interacts with the database.

Put SQL queries only in very narrowly-defined database interaction
functions, where the input and output can be tested easily with unit
tests. The unit tests present mock database API functions, that
implement only enough to satisfy the narrow actions each low-level
function will perform.

If you can't set up a trivially-simple fixture to pretend to be the
database API, the function is doing too much. Break it down further,
possibly along the way finding duplication and refactoring those into
levels of abstraction.

Put feature code only in higher-level code, where the input and output
doesn't have anything to do with the details of SQL and can therefore be
tested easily with unit tests. The unit tests make trivially-simple
collections — basic types like mappings or sequences — to fake the
results from the lower-level code.

If you can't make a trivially-simple sequence or mapping to pretend to
be the result from the lower-level data code, the function is doing too
much. Break it down further, find duplications, refactor them to
abstration layers.

A database is a very heavy external dependency. You should not be
attempting to “mock the world” in order to make your tests run. Instead,
you should be making your code well designed: small and simple and
narrowly-defined functions, each one of which is easy to test with
simple fixtures.

 \         “I call him Governor Bush because that's the only political |
  `\              office he's ever held legally.” —George Carlin, 2008 |
_o__)                                                                  |
Ben Finney

More information about the Tutor mailing list