Re: Testing Technique when using a DB

On Mar 12, 2013, at 8:09 PM, Joe Van Dyk <joe(at)tanga(dot)com> wrote:

> On Mar 12, 2013, at 8:42 AM, Perry Smith <pedzsan(at)gmail(dot)com> wrote:
>
>>
>> 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.
>
> This would be super super super awesome, but it doesn't exist as far as I know. This would be a "permanent snapshot" that could be easily and quickly restored.
>
> I wonder if it would be possible to make an extension that made this easy to do.

For what it's worth, I have something very similar to that to write JUnit tests in Java.
While that might not be much help to the original poster's Ruby environment, the code is
available as open source for anyone who thinks this is an interesting idea.

https://github.com/NessComputing/components-ness-pg

We got tired of maintaining a Postgres instance on every development box and trying to make sure they had reasonably close versions, so I hacked this together.

It's fairly specific to the infrastructure we use but if there is any interest I could spruce it up a bit, write some documentation, and make it more stand-alone.

Short description:

* Drop your SQL files in the class path somewhere (src/test/resources for Maven folk)
* Provides an embedded PostgreSQL instance that you can spin up and down on demand -- builds via shell script, auto detects platform
* The SQL files are loaded into a Postgres template database, and then cloning the databases can be done relatively cheaply and quickly.
* A fresh database is prepared in a thread in the background so one is always immediately available to test cases
* Provides a handy JUnit @Rule so you can mix it in to test cases easily