NHibernate and Multiple Database backends - Architecture question

Question

Hello,

I have a project that is supposed to run on different (at least 2) databse backends, so users can choose (currently between MSSQL and SQLite). I've just started learning NHibernate, following the tutorial on NHForge.

Now, my current Architecture looks like this:

MyProject.DAL

Contains NewsItem and INewsItemRepository. NewsItem is a POCO, essentially just an int Id and string Title.

INewsItemRepository is the Interface that includes CRUD Functionality.

MyProject.Plugins.DAL.MSSQL

This class implements the backend. It includes hibernate.cfg.xml, NewsItem.hbm.xml and NewsItemRepository.

The cfg holds the Driver and ConnectionString, the hbm is the SQL-Server specific Mapping, and NewsItemRepository implements the INewsItemRepository.

If I want to add the MyProject.Plugins.DAL.SQLite, I will have to duplicate all this, which is logical. However, I wonder if that is the correct approach? I feel like I'm unnecessarily duplicating things. I mean: I need to make sure the mapping is Database-specific, and that I update all my Plugins if the database changes. But this architecture still seems somehow... bloated.

Am I doing the right thing here? Or are there common patterns about what goes into the general business assembly and what needs to be kept separate for each database?

Edit: One of the challenges here is the handling of sql-types. For example, strings are mapped as nvarchar(255) by default, and if I need ntext in MSSQL, i'll have to use the <column sql-type="NTEXT" syntax, do I? I guess that specifying sql-type is database-dependent, so I'll need my mapping separated per database? Or is there a built-in mechanism to have mappings "inherit" each other? (I.e. have one .hbm.xml with all the <property> Tags in the general DAL Project and then .hbm's in each Backend-Assembly containing the columns?

Answer 1

Well, I haven't seen your project or data model so you'll have to interpret this as best suits your situation. However, yes, my reaction is that this is more code than is necessary.

One of the major benefits of using an ORM like NHibernate is that they separate your code from your persistence architecture. It seems to me that mapping NewsItem (for example) multiple times is unnecessary, for one. Isn't the data model likely to be the same regardless of backend? Perhaps you could give an example of where they might differ.

Secondly, I would probably not re-implement NewsItemRepository. Regardless of database backend, the repository is using NHibernate which has a primary purpose of abstracting the database. All CRUD functionality handled by the repository will be the same on MSSQL as SQLite (unless there actually is a difference in data model).

If your database model is the same on both backends, in theory you should only need to change the connection string to change the database being used. The way I've implemented a DAL before is to provide the interfaces as you've done and then have a sub namespace for implementations, one (well, the only one at the moment ;) being NHibernate. Repositories et al. are acquired through dependency injection. The DI kernel handles session factories as well so it's easy to target a different database for different sections of code.

Of course all this assumes that your data model is the same on both database engines. If it's not, my answer might not be very helpful. If that's the case, knowing what is different might lead me or someone else to a better answer.

EDIT:

I see what you mean about the sql-type. However, that's only a problem if you are generating the schema for your database from NHibernate. When mapping the properties, you don't need to use <column> and the type you specify on <property> is an NHibernate type. The StringClob type will handle NTEXT fields properly.

If you are generating your schema from the NHibernate mappings and NHibernate is not smart enough to strip out the sql-type="NTEXT" for SQLite (I'm hoping it is, but I've never used the schema generation tool), I think I would add a build step to cleanup the SQLite schema. Of course, now you're adding complexity to your process so it's up to you. However, I'd rather keep my code and mappings as simple and clean as possible and keep the complexity only where it is needed (the nit-picky details of the database engine). It seems to me that keeping two separate mapping files is a recipe for a missing property mapping and users left scratching their heads as to why they can't put in their DOB when using SQLite ;)

EDIT2 (regarding DI use with NH):

This is a bit subjective since I'm sure there are many ways to use DI with NHibernate. I've had great success using DI with Fluent NHibernate and Ninject, a DI framework that also uses a "fluent" DSL. Fluent NHibernate (FNH) provides an interface IPersistenceConfigurer that is used to create your database connection string (it also provides a number of Dialects to choose from). Thus, it's easy to bind that IPersistenceConfigurer service to a provider via the DI framework. You can do the same for ISessionFactory to have a fully D-injected factory. Since Ninject works off attributes my repository constructor might look like:

[Inject, CRM]
public MyRepository(ISessionFactory sessionFactory)

Which would inject a factory for the CRM database. Me likey. :)

Answer 2

The data types NHibernate uses depends on the Dialect used in your configuration file. When it processes the xml it looks up the proper data type dependent on this dialect for each field. You would use a different dialect for each database so can use the same hbm.xml for the whole project.

Answer 3

I handle dynamically switching databases by configuring the session factory in code from a connection string in the config file. I decide which connection string to use by either asking the user or reading an app setting. I pass the ISessionFactory or ISession to your repositories using dependency injection, typically by passing it into the constructor.

I don't have any experience doing this against multiple database providers so I don't know what problems you'll run into due to that.