0

I have written a .Net application which has many components, some of those components are database access layers which abstract from the rest of the components where the data comes from.

I have unit tested the rest of the components by mocking the database access layer. One way I have of testing the database access layers is to use create new empty databases on the test servers. This can be slow, and most would argue that it is not a unit tests as I depend on the database server.

What I think I want, is a mocked database which I can use to test my database access layer. The mocked database can be given a schema and process SQL commands as if it were a remote database, but in fact it is all in-memory. Does this exists? Or how else can I test my SQL and database <-> data model code.

To solve my problem you may want to know I am using SQL Server, versions 2008 and later, and my code is written in C#, running with .Net 4.5 and using Visual Studio 2013

Note: I do not want to use Linq2SQL/EntityFramework to replace my database access layer, as in my experience it results difficult to debug issues and performance problems.

I tried to phrase my question carefully to avoid people lecturing me on their beliefs in what should be tested and how, but perhaps to be a little more blunt: I want to unit test my SQL, small changes to that have a big impact on the outcome of the program. I do have integration tests, but it takes much longer to create a release for the test environment than it does to tweak code and run the unit tests. I appreciate people taking the time to read my question and respond anyhow.

JoshSub
  • 499
  • 1
  • 4
  • 11
  • I see no point in a mocked database. I would unit test the interface-based data access objects, make sure they worked perfectly, then create mocked versions for clients to use in subsequent unit tests. You know that the database will do its job, but you need it to verify that your SQL is indeed correct. – duffymo Sep 02 '15 at 09:06
  • 1
    You dont *Unit test* this layer, you *integration test* the whole thing when its running in an appropriate environment. – Jamiec Sep 02 '15 at 09:08
  • 1
    I want to unit test my SQL, small changes to that have a big impact on the outcome of the program. I do have integration tests, but it takes much longer to create a release for the test environment than it does to tweak code and run the unit tests. – JoshSub Sep 02 '15 at 09:29
  • What version of Visual Studio (2008,2010,2012,2013,2015) are you using and what type (pro, premium,ultimate, enterprise)? – Igor Sep 02 '15 at 11:07
  • Isn't straight approach not working for you? I mean, on test class initialize - restore database from back up, then do test. The only thing you need is to group your tests in classes so it requires less database restoring. Assiuming that changing database engine makes no sense(you are testing sql), the easiest way to speed up restore process will be placing database on a RAM drive. – Timur Mannapov Sep 02 '15 at 11:12
  • 1
    *If* I feel the need to test the database layer (which, I admit, can occasionally be an appropriate thing to do), I automate the creation and teardown of the database. My [Outside-In TDD Plurasight course](http://bit.ly/13m5eeJ) contains a full example of how to do that. – Mark Seemann Sep 02 '15 at 11:29
  • What form do the queries take? SPs or in-line SQL? – Robbie Dee Sep 02 '15 at 13:27
  • @Igor - Visual Studio 2013 – JoshSub Sep 02 '15 at 15:30
  • @Mark - Thanks for this, I already have code which automagically creates an empty clone of the test server database for running tests against and cleans it up after. I'm hoping for something which doesn't depend on the server and can run quickly in memory, but exercises the SQL. – JoshSub Sep 02 '15 at 15:30
  • @JoshSub - thanks but the type is important. If you have premium or ultimate you can take advantage of shims but in other versions you can't so then options might be limited. So is it the free version, pro, premium, or ultimate? – Igor Sep 02 '15 at 15:32
  • @Igor - ah sorry. We are currently using 2013 Pro, Update 4. – JoshSub Sep 02 '15 at 15:44
  • @Robbie Mostly in-line SQL, prepared to migrate away from SPs to aid test-ability though. – JoshSub Sep 02 '15 at 15:46
  • @JoshSub If such a thing exists, I'm not aware of it. It *is* slow testing against the database in this way, which is one of the driving factors behind the [Test Pyramid](http://martinfowler.com/bliki/TestPyramid.html). – Mark Seemann Sep 02 '15 at 15:52
  • @JoshSub your question should be: How to run SQL Server in a unit test? Now the truth. You don't test an ORM but when you roll your own DAL you test it against an unabstracted database. That's because your code is exposed to connection, query and mapping bugs. – maxbeaudoin Sep 02 '15 at 16:01
  • @JoshSub - with pro your options are very limited as you can't use Shims to catch the incoming calls to objects like a SqlCommand. If you want more ideas on how to test your custom DAL layer then it would be better to provide a fragment of code that is interesting for testing so we could discuss how to test that piece. That should give you some insight as to how to test the other pieces as well. – Igor Sep 02 '15 at 16:14
  • @JoshSub If you *can* move to SPs, you can take advantage of unit tests within SQL Server database projects – Robbie Dee Sep 02 '15 at 16:45
  • So, the logic you want to test is written in SQL? In that case why do you want to test it using MSTest? If so, create PS instead of inline commands and write the test in SQL as well. Here is a QA on tetsing SQL logic: http://stackoverflow.com/questions/754527/best-way-to-test-sql-queries/754570#754570 – Jocke Sep 02 '15 at 19:33

1 Answers1

1

I don't know if it's going to be the best answer, but. The way we're doing is that we're using SQLite, which is an in-memory database. There are a number of different ways to set it up, we use NHibernate as an ORM, and for that it is fairly easy to set up using FluentNHibernate, but I don't think it's much harder using any other framework either:

Fluently.Configure()
            .Database(SQLiteConfiguration.Standard.InMemory())
            .Mappings(m => ... )
            .BuildConfiguration()
            .BuildSessionFactory();

I think you can run queries against a SQLite database without any ORMs as well, e.g. using the base SqlConnection class.

This database can accept migrations, schemas, etc. It behaves very close to a proper MsSql database, we can run any DDL an DML statements against it, and it works fine. Since it's all in-memory, it's also pretty fast.

blas3nik
  • 1,381
  • 11
  • 21
  • It looks like the OP is not using an ORM of any sort but executing queries directly using ADO.NET in his own access layer. – Igor Sep 02 '15 at 11:12
  • Amended my answer. It should work without any ORMs actually... I just never used it without, so I can not give an example for that. – blas3nik Sep 02 '15 at 11:21
  • As per Igor's comment, he is correct in the fact I am using a library for database access which wraps the ADO.NET classes such as SqlCommand and SqlTransaction. I will look into this suggestion though as it sounds fairly promising. – JoshSub Sep 02 '15 at 15:36