Is it possible to create a database using NHibernate?

Question

I am using NHibernate with FluentNHibernate for my DAL. I am also using SchemaExport and/or SchemaUpdate to create and update my database schema.
My problem is that the schema operations all require the database to exist before they will work. I want to programmatically create my database and update the schema as there may be multiple databases and the creating of the database is not only a once-off operation.
I know I can do this manually by executing a create database command on a connection to the master database, but this feels wrong considering that I otherwise use NHibernate for all my database interaction. (as an aside, I use a SQLite inmemory database for my unit tests so any sql I write will then have to know which database I'm using).
Is there any way to get HNibernate to create my database for me?
Thanks
Andrew

Answer 1

Hmm, you can indeed use SchemaExport as you mention, but I see NHibernate primarely as a way to bridge the gap between OO domain model and a relational persistence storage. That is, for me NHibernate's purpose is to persist and retrieve entities in my domain model.

When you want to do schema updates, imho, you should not use NHibernate (if possible at all), since that's not the purpose of NHibernate; there is a 'domain specific language' called SQL :) which is good at doing those things.

In order to create my database, and update my database-schema, I use Migrator.NET, which is a framework which lets you write C# classes in where you specify what needs to be done (create a table, add a column, etc...).
The advantage of this, is that Migrator.NET also supports multiple DBMS'es, so you do not end up writing DB-specific SQL code.

Answer 2

I think that creating db with NHibernate is nice, if you start to develop application with domain model. I mean - it gives an insight how your db should look. But in general - sql scripts should be used.

I heard that some developers uses NHibernate db generation for integration tests, they create lightweight sqlite db on fly.

How to generate db with NHibernate? This is how i do that:

 public static void InitSessionFactory()
    {
        var connectionString =
            ConfigurationManager
                .ConnectionStrings["MyConnectionString"]
                .ConnectionString;

        var cfgFromXml = new Configuration();
        cfgFromXml.Configure();

        var cfg = Fluently.Configure(cfgFromXml)
            .Database(MsSqlConfiguration.MsSql2005
                          .ConnectionString(x => x.Is(connectionString))
                          .UseReflectionOptimizer())
            .Mappings(x => x.FluentMappings
                               .AddFromAssemblyOf<NHibernateBootstrapper>())
            .ExposeConfiguration(BuildSchema);


        ObjectFactory.Inject(cfg.BuildSessionFactory());
    }

    private static void BuildSchema(Configuration config)
    {
        //Creates database structure
        //new SchemaExport(config)
        //   .Create(false, true);
    }

Answer 3

Have a look at RhinoCommons. Under Rhino.Commons.ForTesting, in UnitOfWorkTestContextDbStrategy.cs, Ayende has done some routine there that creates databases.

Answer 4

No, NHibernate will not create the database for you. Creating databases is something you usually don't want do be done on the fly. It needs many configuration parameters and this is very database specific, even database version specific.

For our product, I wrote a class that sets up the database. It is used for the installer, the integration tests and the database maintenance tool.

• NHibernate creates the connection using its configuration.
• my own implementation creates the database. There are different implementations for different dialects.
• NH creates the tables and other database objects
• Installer classes create initial data

There is also a special case, where a customer doesn't want to grant the right to create databases. Then we expect him to do it and skip this part.

Answer 5

We use SchemaExport to provide a schema and a Nant task to drop and recreate the database. Another Nant task runs the schema export and dataloading code which exists in the form of an integration test run by the nunit task. There is a sample here.

Answer 6

I wouldn't exactly call sql a "Domain Specific Language," more like database assembly...