How good is NHibernate's BuildSchema?

Question

I've been looking at NHibernate recently and trying to learn the ins and outs. One thing I did notice in a few articles was the use of BuildSchema to create a testable database.

The particular example used SqlLite as the database. Can BuildSchema be trusted to create your database or is it still best to create your own Database Schema in the standard way? In my case using SQL Server Management Studio.

Update: Both answers seem to give valid advice, I wouldn't say this question has an "answer" so I won't mark one in particular answer as the definitive. However I have voted you both up for your advice.

The overall idea seems to be to use BuildSchema in early development before the database design settles then move onto create / alter SQL scripts while using version control.

Answer 1

Toward the beginning of a project is may be practical to have the schema generated by BuildSchema but once you are up and running with some critical mass (or if you have released anything to others) you need to coordinate changes in the code with changes in the schema. That doesn't work with the buildschema method.

In our projects we create upgrade and downgrade scripts for each changeset that requires a database change. We haven't automated that process but the practice allows developers to get to a current version by running the incremental change scripts and get back to a previous version by running the downgrade script.

Each script is its own file and may contain more than one change to DDL and or DML to migrate data etc.

We only really use downgrades when there is a change that would be incompatible with previous versions (these are rare on our projects).

Answer 2

In trying to keep as close to a domain driven design methodology as possible, I use NHibernate to build my schema during development. Once the app has reached some level of stability (the domain model churn has slowed considerably) or a milestone release has been met, I switch to the method K. Scott Allen discusses on his blog.

K. Scott Allen's blog articles on database schema versioning

Ash Tewari has implemented a change script mgmt system that uses NHibernate that is based on Allen's blog articles about this.

Ash Tewari's DbUpdater