Re: Testing Technique when using a DB

On Mar 12, 2013, at 8:41 AM, Perry Smith <pedzsan(at)gmail(dot)com> wrote:
>
>
> One choice would be to create the database, use it, and then drop it for each test. I would create the database from a template that already has data taken from the production database (and probably trimmed down to a small subset of it). This requires some crafty dancing in the Rails set up since it likes to just attach to a database and run but it could be done. From first blush, this sounds like it would be really slow but may be not.

I do this. It's not blindingly fast, but plenty fast enough for automated testing as long as your tests aren't too fine-grained and your test database isn't too big. It takes no more than two or three seconds on my (slow, IO-starved) QA VMs.

By parameterizing the database name you can parallelize tests - each test creates it's own copy of the template database, runs whatever it needs to run, then drops the database. That lets you hide a lot of the setup/teardown latency.

>
> The other choice would be to somehow copy the data to temporary tables before the test run and then copy it back. The advantage to this is it is not very PostgreSQL specific. Indeed, if the template database is already set up, then only one copy would be needed at the start of the test.

You'd also need to undo any other state that was changed. Sequences, for instance, if they can affect the meaning of your test or expected results in any way.

>
> The other thought I had is if there is some type of "leaky" transaction. A transaction where another process can see the data but the roll back would still work and be effective. Essentially I'm asking if all the protections a database offers could be dropped... but I thought I'd see if that was possible.

That - or anything else involving rolling back transactions - would only work if you were testing an app that didn't use transactions.

> The other thought is perhaps there is a "snap shot" type concept. I don't see it in the list of SQL commands. A "snap shot" would do exactly what it sounds like. It would take a snap shot and save it somehow. Then a "restore to snap shot" would restore the DB back to that state.

That's pretty much what creating a database from a template does, other than the need to have everybody disconnect from the database before doing the drop+create.

Dump / restore will do that too - somewhat slower, but doesn't require disconnecting from the DB.

File-system level snapshots are another option, but I'm pretty sure you'd need to shut down and restart the database server, which'd cost far more than you'd save.

Cheers,
Steve