In this post, I will write about documents in general and then I will show you a simple implementation of working with documents using .NET Core, PostgreSQL and Entity Framework.
Introduction
Working with documents in software is fun. It means that storage fits your code, not the other way around. This removes the object relational impedance mismatch between how you model your application and how you store those models.
Even if you do not have immediate use of documents, learning how to use documents will broaden your perspective of storage systems.
There is always more than one way to get something done. When it comes to work around my house, my favorite is not doing it at all. I take that approach with landscaping, cutting the grass, other house chores and everything else I can get away with. [somebody once said that you can always tell the house that is owned by an IT personnel – it usually has the worst looking yard in the neighborhood].
When it comes to databases, we have multiple ways to work with persistence. Two common ways are Relational databases (RDBMS) and the other one is NoSQL (document) databases. Yes, you guessed it right, this post is not about differences or comparisons between the two but rather geared towards implementing a NoSQL persistence inside a relational database system. Yes, you can have both, they should serve their purpose in your solution. We’ll see later how PostgreSQL (which is generally a RDBMS) allows you do that very easily and it will be interesting.
Databases are a fundamental part of any business application. We have worked with relational databases for a long time and enjoyed or suffered its pros and cons as well. In recent years, we have seen the growth of NoSQL (document) databases and they are very useful in various scenarios.
In this post, I will write about documents in general and then I will show you a simple implementation of working with documents using .NET Core, PostgreSQL and Entity Framework.
The post is structured in the following sections:
- Document databases (A general discussion about documents based databases)
- Solution Setup (Setting up a .NET Core solution with necessary tooling)
- API implementation (Web API Controller Implementation)
To keep the discussion focused, I’ve skipped over some of the details and mentioned the necessary resources if you want to see more information. This post shall give you a good idea about theory to implementation of documents persistence.
What is a Document
How you think about the documents you store will have a major impact on your entire project.
Remember: normalization rules do not apply to document storage. So toss your normalization thinking outside the window.
A document can be whatever you want it to be and with that freedom comes a very common paradox of choice: Too many choices. How do you chose? Well, let's narrow our case down to three ways of thinking.
- A domain model
- A process result
- A real document
A Domain Model
The first is thinking like a developer. You can easily craft a document to represent a domain model. This is a perfectly acceptable way of thinking about a document.
A Process Result (or Transaction or Event-source)
Another way to think is like an information hoarder or a DBA. You can craft your document to be results of a number of processes in your application or an event (an event-source if you will).
The example shown below represents a checkout process. So one leading up-to it is a shipping process:
A Real Document
A third way to think about document is like a business person. Purchase orders, marketing sheets, sales orders and invoices. You can represent all of that information almost directly in your database if you like. There will be overlap, but in document base system overlap is OK.
Approaches
We don’t have to limit ourselves to any of the above three, we can do all three.
Big documents containing all the results of a process. Can be thought as an Event-Source too.
Persisting Documents in PostgreSQL using .NET Core
Now as we have some idea about documents, let's see how we can implement it in our code. I have written previously about PostgreSQL and you can refer to that post if you like to. It's a relational database system but it also supports documents persistence using its data types json/jsonb.
The data types json and jsonb, as defined by the PostgreSQL documentation, are almost identical; the key difference is that json data is stored as an exact copy of the JSON input text, whereas jsonb stores data in a decomposed binary form; that is, not as an ASCII/UTF-8 string, but as binary code.
I will be using .NET Core (WebAPI) for implementing a simple API to interact with PostgreSQL. I will also be using Entity Framework for database queries however, I won't be discussing details about how the solution is setup and if you need more information about those topic, you can search on the internet or you can also check my book which goes in much more details about those concerns.
Ok, let's start.
Solution Setup
I created a typical .NET core solution with WebAPI project. I also added two .NET standard library projects to have separate layers for Core domain model and database layer. Again, for step by step details, please check my book mentioned earlier.
To work with postgreSQL, I added entity framework and postgreSQL related nuget packages. the following picture shows the solution setup with packages installed.
This setup is very basic and I described it step by step in the book mentioned earlier.
Domain Setup
Remember from the earlier discussion that a domain model (check Core Project) is one way to model the document or vice-versa. The following picture shows a very simple domain model which is self explanatory and we will persist it to postgreSQL:
EntityFramework Setup
Now, we have domain model and we will setup the entity framework (Check Data Project) mappings as follows:
Dependency Injection Setup
In Web Project, I setup the DI for postgreSQL as shown below. I also defined connection string in appsettings.json file.
Creating and Applying Migrations
Once we have all the necessary wiring setup, we can run entity-framework migration workflow to get our database/tables created.
Following is the corresponding migration created (notice the jsonb datatype):
Once reviewed, we can apply the migration as follows:
.NET Core WebAPI (CRUD Functionality)
I have created an API Controller to expose various operations on the documents. Let's see how those are implemented. I tried to keep the implementation very simple but feel free to adjust the code as needed. Same goes for client-side code, you can build an app using Angular, React or whatever technology, I will be using POSTMAN for REST Calls.
Create (Post) a Document
Here is the method implementation, which is a typical POST request and we are persisting it via dbcontext:
Read (Get) Document
Here is the code and PostMan request block for reading the documents from the database:
Delete a Document
The following code shows the implementation of Delete Request:
LINQ Query for Summary Information
The following query shows an implementation of Summary Data. You can execute other LINQ queries as well and please refer to the documentation for more details:
Summary
Working with document simplifies a lot of coding tasks. PostgreSQL is one of the options. There are many other NoSQL (document) databases available, e.g., MangoDb, RethinkDb and concepts of documents oriented databases are the same.
.NET Core and EntityFramework support and simplify getting started with PostgreSQL, but you can do the same with Node or other technologies. You can download the source code from this git repo. Till next time, happy coding.
Other Links
History
- 18th October, 2020: Initial version
