I'm trying to setup a "safe" testing database environment for some
unit testing of a web application. I would like to have the unit
tests restore the database to a known state before every test. The
simplest way I thought would be to have the testing tools drop/create
the testing database on every test case, and then populate the
database from a specified file. However I don't want to give the
test user superuser privileges. Thus I don't think I can restrict it
to only drop/create a single named DB.
My next thought was to code up a "DELETE ALL" script that would
delete all entities in the database. However it seems easy to miss
something and its not robust against schema changes, even though it
can be looked down to the test_db_owner.
A third thought would be to run a second cluster containing only the
test database(s). Then the users wouldn't be shared, so even if it
someone connected to the wrong DB it would lack any permissions. I
don't have much experience running multiple clusters, however. So I
don't know if thats opening up another whole can of worms.
Any suggestions?
Thanks
Eric