A DBA’s thoughts on ORMs

What do you think about using NHibernate with SQL Server? What would you say if we were to use Entity Framework on our next project? What is your opinion on ORM frameworks?

I am a sort of DBA who spends a lot of his time working with developers. Deep in the implementation trenches, cutting code, trying to prevent any future ‘server issues’ by influencing the design at early stages of development. I find it much more efficient than waiting for them to chuck some code over the fence to us when there is very little we can do, but complain about them and get upset that somehow indexes and statistics don’t solve the problem. And so I hear those sort of questions a lot and hardly every I have the time to answer them in any other way than just to say ‘it depends’.

So here is an attempt on answering this question.

The developer in me wants to say:
Of course, use an ORM! Go code first if you can. It saves the time, it deals with the Object-Relational Impedance Mismatch problem, it keeps your code clean as there is no need for those strange looking data queries. All the code is in one place, one solution, easy to find, read and understand. It is data engine agnostic too, so we can deploy it on MySQL, Oracle, PostgreSQL or SQL Server. On anything really.

But then the DBA in me wants to shout:
Are you mad? Of course not! Don’t use ORMs. Ever. They produce unreadable, inefficient queries that are difficult to understand or optimise. The code first approach typically leads to inefficient schema. New database engine features are ignored because cross vendor compatibility is more important than performance. And don’t you see how those leaky abstractions of generic repositories you are using? Really, passing IQueryable to the business layer? Maybe you have the ability to run it on multiple data engine, but now your business layer depends on your ORM framework and the data model.. Read the Clean Architecture by Uncle Bob, especially the part about keeping frameworks at arm’s length.

And so the developer responds:
OK. So I will be more specific with my repositories… perhaps. Fine. But I’m not going to write any SQL statements. I don’t want any magic strings in my code with no support from the IDE. And no, no stored procedures. We cannot have logic split into multiple layers. All code needs to be in the repo, all code needs to be tested. Don’t you see, stored procedures just don’t fit in the modern software development cycle. Besides, we have developers who can write LINQ and don’t need to know any SQL.

But the DBA with a smug look on his face says:
Ha! That idea of abstracting away technology, so that you don’t have to understand it has been tried before. Sometimes it works, sometimes it doesn’t. What happened to WebForms. Wasn’t it the idea to hide HTML and JavaScrip to make web development easier for existing windows developers. How did that go?

And that’s how it starts again and again, and the discussion in my head goes on and on. But eventually I come to similar sort of conclusion time after time, and here is what I actually do. (It is a compromise on which both the developer and the DBA in me agree on, allowing me to stay sane).

  • For Proof of Concept work I use ORMs and the code first approach. That saves a lot of time and effort, and the code will be a throwaway anyway. My ORM of choice is Entity Framework but it doesn’t really matter.
  • I don’t spend much time thinking about data types. In most cases string defaulting to nvarchar(255) is good enough for a PoC.
  • I prefer to use EF Core as it supports in memory storage for even faster PoC development and testing.
  • Just in case it is not thrown away (as it should), I keep my architecture clean. I make sure to use specific repositories for data access, and that the repository abstraction is not leaking any implementation details. A repository takes and returns business objects and is using ORM framework internally only.
  • On projects which will not be thrown away I start with Dapper (a micro ORM) and stored procedures. It is a bit more work but forces me to design the data structures better, and offers a lot benefits for the future (more about it later in this post).
  • While I agree that logic should be in one place, there are different types of logic, and those should be implemented independently. There is UI Logic, there is Business Logic and there is Persistence Logic which I implement in a repository or in stored procedures. A good example would be a soft delete functionality.
  • All SQL code is kept in the same solution, is tested and deployed through the normal CI/CD channels using DbUp project.

So my answer is

Use ORMs as long as they work for you, but architect your code in such a way, that you don’t depend on them, and ready to ditch them when they start to cause more problems then they solve. Consider micro ORMs. Try Dapper.

Here are a few more benefits of using Dapper with stored procedures

  • Dapper has much smaller footprint than NHibernate or Entity Framwork.
  • Dapper is faster, almost as fast as a DataReader, when compared to full ORM frameworks. According to this at the moment of writing this post it is 10 times faster than NHibernate.
  • While being small and fast Dapper still takes the ORM problem away.
  • Stored procedures add some extra code that needs to be written, but allow access to the latest database engine features. In case on SQL Server those can be Hekaton (In-Memory OLTP), JSON or XML data types, graph structures, temporal tables, windowing functions and much more.
  • Stored procedures make performance troubleshooting and reviews much easier. For DBAs it is much easier to understand which part of an application creates the load and therefore what it is trying to do with well named stored procedure rather than a lot of auto generated SQL statements.
  • The cooperation with Database Developers is much easier, as they can easily identify queries that need to be optimised, and then improve them without worrying (too much) about any non SQL code.
  • Even if you don’t have DBAs and DBDs just now, you might in the future. If the business is successful it might be that you suddenly need to get somebody with those skills to help you. Having good structure, with a separated data layer will make their life easier, your bill lower and everybody happier.

VS Code and Pester

I have recently switched to Visual Studio Code for writing PowerShell scripts. (Here is why and how). At the same time I started using Pester to do TDD with PowerShell too. It works very well out of the box, at least at the beginning of a project. As you are writing your Pester tests file to cover the function you have just written you can simply hit F5 to execute all of the tests in that file. Easy.

It’s just that it is backwards. The tests should have already been written, and as you are writing your function that will satisfy the tests you try to press F5 and that function gets executed, not the tests. Even more so, if the function is part a module it gets executed out of context. Nothing works.

Trying to solve my problem I found a very good writeup on Debugging PowerShell in VS Code but it did not answer my question of how to do test driven development with pester and Visual Studio Code.

Eventually to solve my problem I have created workinprogress.ps1 script which I typically keep just outside of the repository and define the following VS Code configuration in my launch.json file.

{
  "version": "0.2.0",
  "configurations": [

  {
    "type": "PowerShell",
    "request": "launch",
    "name": "Test workinprogress",
    "script": "${workspaceFolder}/../workinprogress.ps1",
    "args": [ "${workspaceFolder}", "${workspaceFolder}/tests", "${file}" ]
  }
  ]
}

VS Code will pass the current workspace, the path to the unit tests (${workspaceFolder}/tests in the example), and the open file with the current line number. Based on that information the following will happen when you hit F5

  • if there is a module (.psm1) file in the workspace, the module will be reloaded
  • if the open file name ends with .Tests.ps1 the script will attempt to find the closest pester -Tag and execute only that tag from the current file. Failing that the whole pester file will be executed
  • if it is not a pester file but the matching (by name) test file exists next to the open file, it will be executed.
  • if there is a matching (by name) test file somewhere inside the tests folder (${workspaceFoder}/tests in this example) it will be executed.

A matching test file has the same name as the original file but ends with .Tests.ps1 instead of .ps1. So for a command file Get-MyValue.ps1 the matching tests file is Get-MyValue.Tests.ps1

The workinprogress.ps1 file is available on github here.