In the beginning, we looked at one use of the SchemaExport
class where we used it to generate database creation scripts based on mappings declared. In this section, we would delve deeper into this to see what different options SchemaExport
offers to generate database creation scripts. We are also going to look at another class named SchemaUpdate
that can generate database update scripts by comparing changed mappings and existing database schema.
If you are working on a legacy project with an existing database then you would have little use of the SchemaExport
class. Since you have already got a database in place, there is no need to generate a new database creation script. You may want to use SchemaUpdate
to generate scripts to manage changes to domain model but exercise caution while doing so. Since the initial version of database is not created through SchemaExport
, chances are that SchemaUpdate
would not respect the semantics and rules that had gone into building the first version of the database. Thus the scripts you end up generating may not be compatible.
Earlier we used the Execute
method on the SchemaExport
class to generate and execute database creation script. The Execute
method is quite involved and you do not have to use it for simple use cases. There is another method named Create
available on the SchemaExport
class which is simple to use. There are two overloads of the Create
method but let's look at one that is generally more useful:
new SchemaExport(configuration).Create(Console.WriteLine, true);
Configuration object we created previously is passed into the constructor of the SchemaExport
class. SchemaExport
would inspect mappings that are added to the configuration object to generate database creation script. You can choose to run these scripts against the database instance configured by passing true as the second parameter. While you are doing this, make sure that a blank database with name passed in the configured connection string is present on the database server. This is not required for SQLite. Also make sure that the user connecting to database has permission to run create/modify tables.
If you are calling the preceding code from a console application then you can pass Console.WriteLine
as the first parameter and that would print the script to console. This is useful when you just need to generate the database creation script to be deployed via other mechanisms. Make sure that you pass false
as the second parameter if that is the case.
The Create
method internally calls one of the many overloads of the Execute
method. If you are happy with the control offered by the Create
method then you do not need to go any further. But if you need more control over script generation process then feel free to explore other overloads of the Create
and Execute
method.
Software changes all the time. New requirements come in or existing features change. Inevitably, you need to make changes to your domain model and subsequently to the database schema. The SchemaUpdate
class of NHibernate makes the job of generating scripts to update database easier. All you need to do is make appropriate changes to your domain model, update the mappings, and then run the following line of code from a console application:
new SchemaUpdate(config).Execute(Console.WriteLine, true);
Make sure that the configuration object passed to the SchemaUpdate
constructor is pointing to correct instance of database. SchemaUpdate
would then look at the database schema and compare the updated mappings to find out what has changed. Based on this information, the SchemaUpdate
class would generate update scripts that would be written to the console. The second parameter tells SchemaUpdate
to also execute the scripts against the database that was specified in the configuration.
If we add a string property named Designation
on the Employee
class, add corresponding property mapping into the mapping class of Employee,
and run the preceding code from a console app, then the following SQL statement would be printed to the console. Note how SchemaUpdate
figured out that a new column needs to be added to the Employee
table.
A good thing about SchemaUpdate
is that it is not destructive. If you have removed an entity or a property from an entity then SchemaUpdate
would not drop the corresponding table or the column. This is by design.
SchemaExecute
and SchemaUpdate
are good utility classes to have to generate/execute database schema creation/update scripts. But if you want to hook into creation of session factory and execute the database schema creation/update scripts right after the session factory is built, then you could use set a configuration property named hbm2ddl.auto
. This property takes one of the following four values:
create-drop
: This drops the existing database schema and recreates the new one using the mappings defined.create
: This creates the database schema based on mappings defined using the SchemaExport
utility class.update
: This updates the database schema based on the changes made to mappings by making use of the SchemaUpdate
utility class.validate
: This validates that the database schema is compatible with the mappings defined. The utility class SchemaValidate
is used internally for validation.Loquacious configuration lets you set the hbm2ddl.auto
property, as shown next:
var config = new Configuration(); config.DataBaseIntegration(db => { db.SchemaAction = SchemaAutoAction.Create; })
SchemaAutoAction
is a helper class that exposes different hbm2ddl.auto
values, as shown in the following table:
SchemaAutoAction |
Hbm2ddl.auto |
---|---|
|
|
|
|
|
|
|
|
Since this feature internally uses the SchemaExport
, SchemaUpdate
, and SchemaValidate
utility classes, all the pros and cons of these utility classes that we discussed previously still apply. You may want to practice caution while using this feature in production. I personally do not prefer to automatically run schema modification scripts in production unless they have gone through proper scrutiny. We can use these features to generate the appropriate database scripts and then use the tools like
RoundHouse to hook into the continuous integration pipelines to run these scripts at the appropriate time during the deployment cycle. You can find more about RoundHouse from their GitHub project at https://github.com/chucknorris/roundhouse.We did not use this feature in any of our code samples. Reason for that is that hbm2ddl.auto
would use a separate database connection to execute the database schema creation scripts. In all our samples, we are using in-memory SQLite database which lives only till the connection is open. The moment the connection used for executing database scripts is closed by NHibernate, the in-memory database will be dropped. What we wanted in our tests was that the connection we used in tests should be used for executing database schema creation scripts. Hence, we explicitly used SchemaExport
to do the job.