Clear NHibernate database fast

Question

Hi,
I am using NHibernate for ORM, and everything works fine.

Now I started to write some unit-tests (using the DB, I do not want to put tooo much effort in abstracting this away, I know its not perfect, but it works..).

I need to be sure that the DB is completly empty for some tests. I can, of course, create the whole DB. But that seems to be overkill and I think it takes longer...

Is there a DELETE_ALL command which clears all tables, I can use in NHibernate?

Chris

EDIT: A short update, I decided to go the SLQ lite way, no problem to change this with NHibernate. There are some pitfalls, I am using this config, and it works. Otherwise you might get "table not found" errors, due to nHibernate closing the connection while in session, resulting in a "lost" database...

For your convinience: Copy and paste...

.Database(SQLiteConfiguration.Standard.ConnectionString("Data Source=:memory:;Version=3;New=True;Pooling=True;Max Pool Size=1;")  
                       .Raw("connection.release_mode", "on_close"))
                        .Mappings(obj => obj.AutoMappings.Add(_config.APModel));

Answer 1

You're not writing unit tests (i.e. tests that test one unit), you're writing integration tests where units interact (i.e. with your database).

Part of your test infrastructure could run a sql script that does one of the following:

1. Drop db and recreate.
2. Truncate all tables.

Ideally, you do want to put a bit of work in abstracting the db away, especially since you have NH which makes it much easier than some other frameworks.

Answer 2

Use an in memory database like SQLite, and setup the necessary data in it before each test. The initial setup takes a bit of time, but each test runs very fast afterwards and you can make sure that you start off with a clean slate. Ayende has a few blog posts about how to set it up.

Answer 3

Drop and recreate the database. You can use schemaexport:

var export = new SchemaExport(config);
export.Drop(false, true);
export.Create(true, true);

Sql lite in memory runs faster for tests than a "normal" database, but the disadvantage is that the sql-lite dialect can be different than the sql dialect in production.

Answer 4

I would recommend you check out ndbunit. It's not a NHibernate-specific, but I've used it for testing NHibernate projects in the past and it works well. Basically, it provides functions to clear the database, prefill it with test data, or restore it to known states after each test. You just have to provide an XSD of the database schema, and optionally some XML data for pre-filling.

I believe I first saw this in the Summer of NHibernate screen-cast series, so check those out to see it in use.

Answer 5

Just in case the Drop/Create DB does not suit your needs (like if the db contains object that NHibernate is not aware of, like SPs, functions etc) you could always make a backup point with the DB empty and after you're done testing just restore to that point