[TIP] Using DB on tests without orm

[Mark Sienkiewicz]

marrese at gmail.com wrote:

> Today we decided to look for a better approach.
> The first thing I thought about was to use sqlite3 and do all the
> testing in there, but I can't find a decent way to migrate data
> between mysql and sqlite (and I think I will have to do this on
> regular basis)
>   

DBAPI is pretty portable, but not so portable that I would count on 
testing exclusively in a different database than the one I want to 
support.  I do divide my tests into those that are more or less likely 
to be affected by database details.

mysql databases are pretty easy to create.  For your scenario, I would 
consider just making a bunch more of them for testing.  Use the 
conventional database dump/restore tools to initialize a particular 
database before using it for a test run.  It may be interesting to know 
that the default mysql configuration allows anybody to create/drop a 
database with a name that starts "test_".

I've often considered a tool that could dump/restore between a database 
table and a CSV file.  Say the first line of the CSV has the column 
names, and the rest are data.  If you had such a tool, you could dump 
your reference data to CSV and then load it into a blank test database 
immediately before your test run.  I don't know of any such tool, but it 
is so easy to write with DBAPI.  You'll want the "row factory" that 
returns the column names with the query results.  Both MySQLdb and 
sqlite3 have a suitable row factory.

As a bonus, this tool makes your test data into a plain text file that 
you can store in your configuration management system.

Testing with sqlite does have one advantage:  speed.  With a small 
enough database on a fast enough CPU, you can create a database and run 
a test faster than you can connect to a mysql server; it's the network 
latency.  But sqlite has only very coarse locking, so you might 
structure your tests so that you never run two tests against the same 
database at the same time.

If all you are doing is basic select/insert, mysql and sqlite look very 
similar through DBAPI.  I have a separate set of CREATE TABLE / CREATE 
INDEX commands for each database.  For example, sqlite doesn't know what 
"CREATE TABLE ... USING INNODB" means.  It means keeping the two copies 
in sync, but how often do you change the structure of a table?

Mark S.