Adjusting NHibernate mapping for tests

As nice as SQLite is for tests it is very simple database, and it does not have all the features ‘big’ databases provide (foreign key enforcement!). I don’t think there’s much you can do about this issue, but there’s more.

SQLite does not support all the mappings you can have. I bumped into this issue when working with mapping similar to described in this post. Basically when you try to create schema from this mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" 
  <class name="Invoice">
    <id name="Id">
      <generator class="guid.comb" />
    <property name="InvoiceRtfString" type="StringClob">
      <column name ="RtfText" sql-type="nvarchar(MAX)"/>
    <property name="ScannedInvoiceJpg" type="BinaryBlob">
      <column name ="JpgData" sql-type="varbinary(max)" />

You’ll get an error:

SQLite error near "MAX": syntax error

That’s because SQLite does not know how to handle nvarchar(max) or varbinary(max). It has its own types for that – TEXT and BLOB. So you’re either stuck, or have two sets of mappings – one for tests for SQLite, one for production, for SQL Server. But wait, there’s third option!

In the previous post, you may have noticed, that in DbTestsBase Init method I call:

new Remapper().Remap(configuration);

Remapper is my remedy for that problem. It’s a simple class that traverses the mapping looking for incompatibilities and adjusts it on the fly before creating session factory and schema. This lets you have single mapping (be it XML or FluentNHibernate) while still using SQLite for tests.

The class looks like this:

public class Remapper
    private IDictionary<string, String> map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
    public Remapper()
        map.Add("varbinary(max)", "BLOB");
        map.Add("nvarchar(max)", "TEXT");
    public void Remap(Configuration configuration)
        foreach (var classMapping in configuration.ClassMappings)
    private void Remap(Table table)
        if (table.Name.Contains("."))
            // found on
            // this is a table with schema
            table.Name = table.Name.Replace(".", "_");
        foreach (var column in table.ColumnIterator)
    private void Remap(Column column)
        if (string.IsNullOrEmpty(column.SqlType))
        string sqlType;
        if (!, out sqlType))
        column.SqlType = sqlType;

It is very simple and basic, because I created it for my very specific needs, but with little effort you could extend it to handle more incompatibilities.

Technorati Tags: ,

Posted 08-17-2009 12:04 AM by Krzysztof Koźmic
Filed under:



DotNetShoutout wrote Adjusting NHibernate mapping for tests - Krzysztof Kozmic -
on 08-16-2009 8:29 PM

Thank you for submitting this cool story - Trackback from DotNetShoutout

Thomas Weller wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 5:10 AM

While this is indeed a clever solution, I'm afraid it's

far too clever. This methodology means the following:

- It 'tests' something that can never be reproduced in the

 field, and it does so by design.

- The outcome of the remapped 'tests' depends highly on

 the code of the 'Remapper' class.

- To understand the 'tests', you must understand the 'Remapper'

 code and you must have a deep knowledge of SQLite.

Sorry, but this neglects the whole point of testing...



Tuna Toksoz wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 8:39 AM

This is sort of an integration test and it can be such that it is completely transparent to the user, what's the problem with that?

Thomas Weller wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 8:56 AM

The problem is, that you create a test scenario that does not mimic the environment of the user. So, what does the outcome of such a test really say about the system under test?

While such tests can be useful in some rare cases, these kinds of tests can also lead to really bad maintenance nightmares when used in real-life projects. It feels like sort of quick-and-dirty hack...



Krzysztof Kozmic wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 9:11 AM


you're right - this is not right approach for integration tests. However, I don't use it in integration tests. I use it for DB tests, to verify that my Repositories really update the values, that they fetch the right values etc. I run this as part of my unit test suite and I don't want to set up a full-blown DB for that.

What Remapper does, is simply makes my mappings compatible with the in memory DB I use for testing.

I agree that this may introduce issues on its own, but lets be pragmatic - it has much more pros than cons.

It lets me quickly test my code interacting with DB which is all I require from it.

Steve Bohlen wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 9:23 AM

I'm afraid I have to agree with Thomas here and it's one of the reasons that I don't do unit tests via SQLite any longer (that data types and all other kinds of things aren't supported in SQLite).

IME there are really only two things worth doing in tests:

1) running integration tests against your actual vendor-specific DB that you plan to use in production

2) mocking all database interactions in isolated unit tests

The "use SQLite b/c its in-memory and thus lighting-quick to run pseudo-itegration tests against" approach has bit me time and time again where tests pass just fine for my frequent pseudo-integration tests against SQLite and then fail terribly when 'real' integration tests are run against the DB.

Fool me once, shame on you; fool me twice, shame on me.  I have observed that the false sense of security provided by targeting a different (even if realy fast!) database platform than I would use in production is just that: FALSE.

Just ask yourself this question: what exactly does a passing test against a different database engine than your app would actually ever be using in real-life actually tell you...?  I submit NOTHING and so its not worth running.  IMO Its really no different than running a test against ORACLE if your production DB was to be SQLServer (and I don't think anyone would ever argue for that approach).

Again, just my experience; YMMV of course :D

Krzysztof Kozmic wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 9:35 AM

Steve, Thomas

My mileage with NHibernate is very low so far, so this is my 'learning in the public' more than trying to be opinionated and thanks for your input on this.

Steve - so do you only test DB as part of integration tests? Do you encapsulate your data access logic in repositories or you tend to work directly with NHibernate the way Ayende does it?

I may have to re-watch your SWNH...

Thomas Weller wrote re: Adjusting NHibernate mapping for tests
on 08-17-2009 10:52 AM

This is the way I do NH/database testing normally:

(I assume there is an indepent database project to create the DB. Whether you have a legacy DB or you can build one from scratch - in either case you will have scripts to create it.)

1. I create a local DB by executing the scripts. This script execution is also automated, it's the test suite for the database project.

2. I use NDbUnit to populate it with test data.

3. I unit test the NH mappings against this database - all like you demonstrated it, but without SQLite.

The point here is that I test against the 'real' database (represented by the scripts), thus avoiding any kind of mismatch between my object model and my database model.

Most of the time, a local DB instance will already exist, so you don't have to run the 'create database' part every time.

I used this approach in two projects now and it works perfectly the TDD way.

I'm currently preparing a Codeproject article on this. When I was considering the various possibilities for that, I also came across the SQLite thing. While it attracted me in the first moment, this was no longer the case after I thought a while about it. This might be the reason for being a bit opinionated on this issue at the moment.

Sorry if anyone felt offended...



About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Google Reader or Homepage Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of
Red-Gate Tools For SQL and .NET


SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
NHibernate Profiler
Balsamiq Mockups
JetBrains - ReSharper
Web Sequence Diagrams
Ducksboard<-- NEW Friend!


Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers


Community Server (Commercial Edition)