Storing and reading data in a relational database without writing SQL
In this article:
- At the beginning there was a test: Using Test-Driven Development (TDD)
- A simple domain class
- Using an object-relational mapper (ORM): NHibernate
- Mapping classes to tables with Fluent NHibernate
- Using the repository pattern to encapsulate database access
In previous articles we looked at how to set up the development environment with Docker including a separate container for the relational database. We also looked at maintaining the database schema with Fluent Migrator and we updated the dev container to .NET 5.0. Now it’s time to take the next step.
Going forward, we’ll endeavor to use tests wherever we can to drive adding new functionality. This time we’ll add a simple domain class and the code to store and read instances of that class, i.e., objects. To avoid writing raw SQL we’ll be using an Object-Relational Mapper (ORM). This also will allow us to use different database servers with only minimal code changes if we want to.
The objective of the article is to demonstrate that not a lot of code needs to be added to get started with storing and reading objects in a relational database. We’ll keep it simple and build more realistic scenarios on it in future articles.
At the end of this article, we’ll have code that configures NHibernate to access the data and we will have tests that confirm that we can in fact store and read objects to/from a relational database. And we won’t have written any SQL to do so.
In this article we’ll again be using as a running example a fictitious product named “Mahi”. The word Mahi means “task” in Te Reo Māori, the language spoken by the native people of Aotearoa, the country also known as New Zealand.
Mahi is a very simple task manager. We won’t have a commercially viable product at the end. But we will learn new concepts as we work through new features. Keep in mind, that the code base is not meant for production. You are welcome to use it as inspiration for your own work, commercial or otherwise. The responsibility is entirely yours, though, if you do.
The complete source code for this article is available at https://github.com/mahi-app/CmdLine. Make sure you switch to branch “article-2020–12–27” to get the correct commit.
For this article we’ll need the following (latest stable):
- VS Code
- VS Code Extension Pack “Remote Development” (identifier: ms-vscode-remote.vscode-remote-extensionpack)
- Git client
- Docker Desktop (Windows, MacOS) or Docker Engine (Linux)
To get the code base this article uses as starting position, clone https://github.com/mahi-app/CmdLine and then switch to branch “article-2020–12–22”.
To make things easier, you could follow these steps:
- Open a bash terminal window. On Windows, if you are using Ubuntu as your distro, open an instance of the “Ubuntu App” which is effectively a bash shell.
- Navigate to your home directory by executing command “cd ~”
- Create a directory “projects” with command “mkdir projects” unless it already exists
- Switch to it with “cd projects”
- Clone the repo with “git clone https://github.com/mahi-app/CmdLine”
- Switch to the correct branch with “git checkout article-2020–12–22”
- You are ready to start
Windows with WSL2 Only: If you clone the repo on Windows, make sure you clone it into the Linux file system, i.e. the file system of your distro. Otherwise, VS Code and its extensions will start showing weird behaviors. This issue is caused by missing inotify messages for file changes. You can find out more about this issue in the article “Docker Desktop on WSL2: The Problem with Mixing File Systems”. If you use “cd ~” in the bash terminal, in general you will be fine.
Is Starts with a Test
Adding a Solution File
We’ll begin by confirming that all existing tests in the repo pass. To do that we start VS Code by navigating to the location of the repository clone. If you followed the steps listed in section “Prerequisites”, the following command will bring you there:
From that folder we start VS Code with
Once VS Code has loaded it will prompt you to reopen the folder in the container. Wait until VS Code is finished with building the dev container (this may take a while the first time) and installing all required extensions in it. Then open a terminal window in VS Code. The prompt should look similar to this (note the word “mahi” at the beginning of the prompt):
To execute the test suite, we execute the following command:
We are greeted with an error message as follows:
One way of fixing this is to switch to directory “/app/src/CmdLine” and then to try again. However, the plan is to add a separate project with automated tests anyway. Therefore, to make our lives easier we will create a solution file in “/app” by executing this command:
dotnet new sln -n Mahi
You should see output as follows:
In the Explorer on the left-hand side, you should also see the newly created file “Mahi.sln”:
Now we could try “dotnet test” once more. However, as this is an empty solution file so it won’t add any value yet. As a next step, we’ll add the existing project by running the following command:
dotnet sln add ./src/CmdLine/CmdLine.csproj
If we now run “dotnet test” we will see output similar to the following:
Dotnet has restored the nuget packages and built the project. We still don’t have any tests yet.
Adding a Project for the Tests
Let’s add a project in which we can write automated tests. Switch to directory “/app/src” and execute the following command:
dotnet new nunit -n CmdLine.Tests
The output should look as follows:
With the tests project in place, we can now add it the solution file. Switch to directory “/app”, then run the following command:
dotnet sln add ./src/CmdLine.Tests/CmdLine.Tests.csproj
The output should look similar to the following:
Let’s try “dotnet test” once more. This time the output looks different:
As we can see the output is now showing that it found a test file and also that the test suite passed. “suite” is a bit exaggerated at this point because it’s only a dummy test that was generated as part of the boilerplate code.
The First Test
Writing the first test is often the hardest. On one hand we don’t want to cover too much ground. On the other hand we don’t want the test to do something trivial. Mahi is about a todo-list, so a reasonable choice could be that we start with a domain class name “TodoList”.
The TodoList class will be trivial at the beginning. All we have will be properties for the id and the name of a list. We’ll add TodoItems in a future article.
We might be tempted to write the class TodoList right away. However, let’s not. Instead let’s think about what test we could write. Instead of testing those two properties, let’s consider another option: What if we were able to store and to read a single instance of the class to and from a database?
While doable, testing just the write or just the read could be a bit tricky. For example, how could we read something if we cannot write it yet? Obviously, we could write some code as part of the test suite that puts some data into the database and then test if we can read that data. However, if we write code to write data, we might as well write the proper code for it right away.
Therefore, let’s write a test that writes and reads an instance of class TodoList. For better testability, we are going to use a pattern named “Repository Pattern”. With that in mind, let’s write our first test.
Rename file “UnitTest1.cs” in directory “/app/src/CmdLine.Tests/” to “TodoListRepositoryTests”. Also rename the class in that file to “TodoListRepositoryTests”. It should then look as follows:
Then rename the method “Test1()” to “SaveRead()”:
Now let’s add some more code to create the repository and an instance of the TodoList class. We also add code for saving the object to the repository and reading it back. Finally, we’ll add an assertion that the name of the list the repository returns has in fact the expected name. The test looks now as follows:
If we now run “dotnet test” it will fail with compile errors, and that’s fine. We are going to fix those next.
Making the First Test Pass
To resolve the compile errors, we need to implement two classes: TodoList and TodoListRepository. We’ll add both to the CmdLine project. The Explorer shows where those files should be located for now:
The code for class TodoList is trivial:
And class TodoListRepository looks as follows:
“dotnet test” still fails as we need to reference the project “CmdLine” from “CmdLine.Tests”. Switch to directory “/app/src/CmdLine.Tests”, then add the reference by running the following command:
dotnet add reference ../CmdLine/CmdLine.csproj
The output should be similar to the following:
Go back to “/app” and execute “dotnet test”. The output should be similar to the following:
Building both assemblies worked this time but the test still failed. Not really surprising as we didn’t implement any of the functions in class TodoListRepository.
Therefore, let’s add some code that will make the tests pass. Change the implementation of class TodoListRepository to the following:
This is probably as simplistic as we can make the implementation at this point. We’ll add more later.
Run “dotnet test” again and this time it should run successfully with the following output:
We have a first test in place and at this point it is passing. In the next section we will replace the implementation of the TodoListRepository with something more meaningful.
Adding Persistence with NHibernate
To add persistence, we need three elements:
- The first element is adding and configuring NHibernate
- The second element is changing the TodoListRepository class to use NHibernate to store and read objects.
- The third element we need a mapping between the TodoList class and the underlying relational table
NHibernate, Fluent NHibernate and Entity Framework
There are several options for object-relational mappers (ORMs) for .NET. These choices include NHibernate (“NH”) and Entity Framework (“EF”). Without going into much details beyond mentioning briefly discussing the two main reasons, we will be using NH in this article.
The first reason is that there appear more articles about EF than NH. Also, many examples and the tooling in Visual Studio give EF the preference. This is not surprising as EF is pushed heavily by Microsoft. This doesn’t mean, though, that it should be the default, let alone default choice. It is not a sacrilege to replace Microsoft’s option with an option developed and maintained by a large open-source community. This article (and more in the future) aim at making NH more accessible by showing how easily it can be introduced.
The second reason is that in my work I observe an increasing number of clients who run into significant issues with EF, many of which could have been avoided by using something other than EF. There are different types of issues but most of them appear gradually as a project grows in size. Yes, all of the problems can be solved. However, at times that is very time consuming and the problems have a tendency to reappear. Please note, though, that my opinion is that if EF works for you, then don’t change it.
In this article, we’ll be using NHibernate. To make code even simpler to write, we’ll be using a nuget package named Fluent NHibernate. This library makes configuring NHibernate and writing mapping code much easier. More about this shortly. Let’s get started.
Adding and Configuring NHibernate
Go to directory “/app/CmdLine” and run the following command to add Fluent NHibernate to the project:
dotnet add package FluentNHibernate
Although FluentNHibernate includes NHibernate as an indirect reference as well, this may not be the most recent stable version. To ensure it is, run the following command to add a direct reference:
dotnet add package NHibernate
Switch back to directory “/app” and run “dotnet test” to confirm we didn’t break anything.
Open the file containing class Database. Right after method “CreateServices()” add the following method “CreateSessionFactory()”:
In this piece of code in line 9 we run the migrations. Then in lines 11 to 18 we configure NHibernate using the fluent interface provided by Fluent NHibernate. In lines, 12 to 14 we provide the information about which SQL dialect and connection string to use. Lines 15 to 17 configure the mappings to be used (more on that later). Line 16 describes where to look for mapping classes. In line 17 we tell Fluent NHibernate to look for column names with suffix “Id” to identify foreign keys. For example, let’s assume we already had a table for the items in a TodoList. In that case that table would have a column “TodoListId” to store the information about which to-do list the item belongs to. Finally, in line 18 we build the session factory (more on this shortly).
With this in place, we can update our code in test class “TodoListRepositoryTests”. We add a private field “_sessionFactory” and then assign it a value in method “SetUp()”. The resulting file looks as follows:
Again, confirm with “dotnet test” in folder “/app” that everything still works.
Session and Session Factory in NHibernate
Both a session and session factory are key concepts in NHibernate. Conceptually a session represents a connection to a database. It is cheap to create. The session factory is an object that manages those sessions, e.g., it maintains a pool of session and decides whether a session can be reused. A session factory is fairly expensive to create in terms of runtime overhead. Generally, you will create just one instance within your application in most cases.
When we need a session, we’ll get it from the session factory. We then perform some work, e.g., we query for data, we add new objects, or we remove objects. Once we are finished with that work, we close the session to indicate that we are done with it. If we need another session, we just get a new one from the session factory.
Let’s return to the TodoListRepository. We need a new session for each the “Save()” as well as the “GetById()” method. A good option is to pass the session factory to the repository as a dependency in the constructor. Here is the repository class with that modification in place:
Accordingly, we need to change the code in the test class so we pass the session factory to the TodoListRepository constructor (line 18 in the following snippet):
And again, we confirm that our test passes by running “dotnet test” in directory “/app”.
Mapping between Objects and Table Rows
The next step is to update the implementation of methods “Save()” and “GetById()” in class TodoListRepository. As explained above this is what we need conceptually for each of the two methods:
- Get a session from session factory
- Do work, e.g., query for data, persist objects, delete objects
- Close the session to indicate we no longer need it.
The code looks to be fairly simple, given that we have the session factory in the variable “_sessionFactory”:
With this pattern we can now change the implementation of the TodoListRepository to the following:
Note, how the “Save()” method returns the id of the persistent object. NHibernate takes care of generating the id. For most scenarios it is very useful to return the object id from this method as it tends to make the client code more readable. Note also, how in line 17 we call “session.Flush()” to ensure our changes are written to the database. In a future article we make this more explicit by adding explicit transaction handling. That will allows us to remove the “Flush()”-call.
If we run “dotnet test” now, the test will fail with an error message as follows:
The error message already gives a clue: NHibernate doesn’t know how to map the object to a row in a relational table. Let’s fix this.
In project “CmdLine” create a folder “Maps” inside the existing folder “DataAccess”, then add a class name “TodoListMap” with the following code:
To be on the safe side, here is the location of that file:
Note line 9: In a previous article we created the table with Fluent Migrator and gave it the name “List”. Line 9 maps class “TodoList” to the table “List” in the database. I use this for illustrative purposes here. We’ll resolve this in a future article which will allow us to remove line 9 from the map file.
Re-run “dotnet test”. This time we’ll notice a different error:
We can resolve this by changing the implementation of our domain class “TodoList” to the following:
Note, how we made both properties “virtual” (lines 7 and 8).
If we now run “dotnet test” in directory “/app” we will see the test pass:
Awesome! We just managed to make the first test succeed. We replaced the trivial implementation of the repository with a proper one that uses NHibernate as the object-relational mapper (ORM).
As we observed, this wasn’t too hard, given the meaningful error messages along the way that guided us to resolve those problems quickly.
We now have in place a test that demonstrates that we can store and read objects to and from a relational database. Before we summarize and look at next steps, allow me a couple of additional thoughts.
If you like to can skip this section and go straight to the “Summary” section below. However, if one of the topics interests you, please keep reading here.
The Object-Relational Impedance
The Object-Relational Impedance describes challenges when mapping between the object-oriented world and the relational world. There are several such challenges and you are welcome to explore this in more details (see “References and Additional Material” at the end of this article.
Here, I’d like to cover one such challenge. Assume you have a many-to-many relationship between two classes A and B. Then in the object-oriented world this is easy to represent:
For example, in languages such as C# this is easy to implemented by just using a collection on each end. No big deal.
The picture changes in the relational world. Many-to-many relationships do not exist directly. They are typically modelled using a third table as follows:
As you can see this is quite different. A modern ORM such as NHibernate can handle this through mapping. We didn’t touch on this in this article, but we will surely encounter it in a future article.
Foreign Key Relationships
Many people use foreign key relationships in the definition of their database. They have a point in that it adds another layer of checks to the data. Good on them.
A different school of thought is to look at foreign key relationships being part of the business logic. For example, a list may have list items. A list item cannot exist without the list, so from a business logic perspective, we would enforce that a list item can only exist if it is associated with a list. Is this business logic? It is perfectly valid to think it is business logic. If it is then a logical next question would be: Where should the business logic should reside? Should it be in one place? Is it ok to have business logic in different places?
In practice I found that in many cases it is useful to distinguish the following architectural layers:
- Presentation Layer
- Business Logic Layer (aka Domain Layer)
- Data Access Layer
As an example, this would also apply to a microservice, one incarnation of which might be running in a docker container. For such a microservice, the presentation layer would translate between the format needed in the requests and responses on one hand and the domain model on the other hand. Often the format for incoming and outgoing data is JSON embedded in a HTTP request or HTTP response respectively.
Given these three architectural layers the business logic should reside in the Business Logic Layer only. Consequentially no part of it would not be in the Data Access Layer. That in turn would mean not to use and enforce foreign key relationships in the Data Access Layer if, indeed, we treat foreign key constraints as business logic.
A more important aspect is, however, that we may not even use a relational database. With a repository pattern we could store data in a variety of different storage types, e.g. JSON objects, NoSQL databases, etc. Some of these don’t even support the foreign key concept, let alone allow enforcing it.
Therefore, by keeping and enforcing business logic in the Business Logic Layer (and nowhere else), we keep your options open for the Data Access Layer, and all business logic remains in one place. And since we can mock out the Data Access Layer, our tests for the business logic will run extremely fast as well, e.g., usually in less than one millisecond per test.
Keep in mind this is just one way of looking at foreign key relationships. In your own projects, use what works best. Just keep in mind that there are consequences regardless of what you choose.
In this article we didn’t need foreign key relationships. I don’t have the intention to use them in future articles, though, either in particular because I want to keep my options open for choosing a particular storage technology, but also because I chose to look at foreign key constraints as parts of business logic.
In this article we saw how we can add NHibernate as the Object-Relational Mapper (ORM) to an existing project. We also introduced the repository pattern. The repository uses NHibernate to store and retrieve data. We only had to make a small concession in the domain class, namely adding the keyword “virtual” to all properties (there is a story to this which we will look at in a future article).
As such we have the first piece of functionality working: We can create new to-do list objects, store them in the database and read them back from the database.
In the next several articles we will look at topics such as consistency, one-to-many relationships, how to avoid duplicated code when using transactions, lazy loading, using LINQ to query for objects, and more. As always, please leave questions and suggestions in the comments below.
Thank you for reading!
References and Additional Material
The following references and suggestions for additional material might be useful if you’d like to explore the topics in this article in further detail: