In memory SQLite for testing NHibernate + SQL Server
In .Net applications with a DDD architecture, when it is needed to create a component test, the database is a problem. A real database should not be used (it would be a system test) so ideally it should be an in-memory database. An additional problem appears when NHibernate ORM is used to handle SQL Server databases. This article will show a proposal of how to use an in-memory SQLite database to test it.
First of all, can you use EntityFramework? If then answer is yes then move to it (it supports in-memory database natively), otherwise this article is for you.
Required NuGet Dependencies
Some NuGet libraries are needed to perform these kind of tests.
System.Data.SQLite.Core has to be added to the testing projects. This library contains all the necessary classes to use SQLite in a .Net application.
NHibernate and FluentNHibernate should be already added to the projects to be tested. The first one is the library of the ORM and the second one is a configuration system of NHibernate.
Incompatibilities SQL Server vs SQLite
It is not compatible by default so the faced incompatibilities are described below and how to sort them out. Mainly they are:
- Tables with version/timestamp.
- Usage of the type Guid (uniqueidentifier).
- Views.
- Schemas.
- Custom SQL Scripts.
Tables with dedicated version/timestamp columns. Those columns are used to configure the concurrency policy. This feature is deprecated in SQL Server 2019, however there are legacy codes that still use this feature. When an entity is defined using FluentNHibernate.Mapping, its mapping (inheriting from ClassMap<T>) should be something like the following code.
SQLite doesn’t support this feature but a value should be assigned, otherwise some queries could fail. The field Version is a byte array so it will be treated as an empty blob (using the method zeroblob).
Guid as keys. Whatever the reason, Sometimes a table could use a guid instead of an integer. SQL Server represents them as uniqueidentifier. Moreover, the method NEWID() should be defined as the default value in the CREATE TABLE script.
SQLite doesn’t support guid, though the column can be a TEXT with the guid format (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx). Every section in guid is generated randomly and flacky tests should not appear due to this because the database should be destroyed after the test (or a rollback sould be called).
GuidAsText class is used by NHibernate to serialize/deserialize all guid columns. It specifies the source and target types and the way to read them.
FluenNHibernate has a way to connect to a SQLite database by default, however the default implementation is a dialect for binary guid, so it is necessary to activate the text mode with BinaryGUID=False. The connection string and the proper way to generate a Guid (with MySQLiteDialect class) should be specified. If a binary guid is used then in the database it will be represented as a blob, and a blob cannot be easily readed.
Debugging and testing go hand-in-hand, and it isn’t possible connecting to a in-memory database. Therefore for debugging the connection string should be changed to use an in-file SQLite database. It is slower nevertheless it can be queried with DB Browser for SQLite.
Views are supported by SQLite but NHibernate treats them like a read only table. A first approach could be use SchemaExport to create the database, but it doesn’t generate the code for the views and in some cases the mapping is not exactly the same to the real structure.
On the one hand a SQL script has to be added to create all the database structure (it has to be maintained), but on the other hand we can use the file ddl.log to prepare it (most of this one is done in the file).
Schemas are not supported by SQLite but it applies a workaround putting the name of the schema as a prefix. E.g. dbo_MyTable
Dots (My.Schema.Name) aren’t allowed in the schema name in SQLite (they are in SQL Server) so a class for the schema names should be used to hide the names with constants or properties. In general, magic strings are a bad practice.
Maybe a static property should be used to distinguish a normal execution from a test (don’t forget to restore the status after running the test).
Open a session
To open a session using FluenNHibernate the previous configuration is used, either SQL Server or SQLite.
Transactions
Fortunately they are compatible so no changes are needed.
When the approach of testing is one database per test-class then a transaction should be opened before running the test and an rollback should be called after running the test, otherwise flacky tests could appear.
Drawbacks
The creation of the database takes some time (few milliseconds). It depends on the decision of the developer/tester using one for the entire test-class or one per test.
The database is detroyed when the session is closed/disposed, so don’t dispose it before the test finises.
SQL script for the creation has to be maintained. Depending on the size of the database and how often it is modified, it could add an overhead in the development.
SQL queries has to be maintained. An approach could be create a wrapper for ISession and another for ISQLQuery to replace the specific methods.
Acknowledgements
I wish to acknowledge the help provided by Juan Alfredo and Manuel Mazuecos.
Conclusions
There are a lot of incompatibilities that can be avoided with the previous changes.
How to open a session has been explained in the component tests too. It is agnostic of the testing framework.
The speed is a little bit faster. E.g. a test class using NUnit with 30 test, with a SQL Server Express database it takes 6.6–7 seconds and with an in-memory SQLite 3 it takes 5–5.2 seconds, so it could be around 40% less.
Another approach could be use a SQL Server, create a template database an attach a copy into a SGBD. It can be a local/remote instance, or a SQL Server Express or in a docker container. I would recommend this options for integration test.
