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

[Alan Gauld]

On 16/08/15 09:10, Ben Finney wrote:
> 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.

This is a good point.
You may recall my earlier messages talking about MVC and the
fact that only the Model should interact with the database
via SQL? So the model interface is effectively your app's
API into the database. And when unit testing the Model you
will probably use mocks for the SQL calls in most cases.
So only a very small number of tests need a real test database.

Where you do need a test database is in testing the database
itself (data structure, constraints, relationships etc).
Much of that you can(and should) do from raw SQL,
but at some point (integration testing) you need to run
the actual SQL in your Model...

Another point worth mentioning that does NOT apply with Sqlite
is that most server DBs provide a Stored Procedure concept and
many database designers prefer to make all updates to data via
stored procedures and expose all read access via read-only
views. This is especially so when exposing the API over a
network (eg as web services). SQlite does not offer stored
procedures (but does offer views) and expects to work on
a local machine rather than over a network so it doesn't
apply there. But if you ever move to Firebird, Postgres
or MySQL it would be a thing to consider.

> tests. The unit tests present mock database API functions, that
> implement only enough to satisfy the narrow actions each low-level
> function will perform.

This is also good advise but remember that unit tests only
form a small part of the overall testing (although they are
the bit normally used for TDD) and for system tests you
will need a test database.

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos