.NET Core, PostgreSQL, and Document Database

Introduction

There is always more than one way to get something done. When it comes to working 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 is 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 an RDBMS) allows you to 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 the 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 with a very common paradox of choice: Too many choices. How do you choose? Well, lets 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 the results of several processes in your application or an event (an event-sourced 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 the 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 u 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 of 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 on DZone and you can refer to that post if you like to. Its a relational database system but it also supports documents persistence using its data types JSON/jsonb.

The data type 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 be also using Entity Framework for database queries however, I won't be discussing details about how the solution is set up and if you need more information about that 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 the WebAPI project. I also added two .NET standard library projects to have separate layers for the Core domain model and database layer. Again for step by step details, please check my book mentioned earlier.

To work with PostgreSQL, I added the 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 a domain model and we will set up the entity framework (Check Data Project) mappings as follows:

Dependency Injection Setup

In the Web Project, I set up the DI for PostgreSQL as shown below. I also defined the connection string in the 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 

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. The 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 DB context:

Read (Get) Document

Here is the code and PostMan request block for reading the documents from the database:

Delete a Document

Following code shows the implementation of Delete Request:

LINQ Query for Summary Information

The following query shows the implementation of Summary Data. You can execute other LINQ queries as well and please refer to the documentation for more details:

Summary

Working with a 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 simplifies 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

• https://hexquote.com/build-accounting-application-using-net-core-angular-and-entity-framework/
• https://www.amazon.com/dp/B08D8PLN6T
• https://hexquote.com/postgresql/
• https://www.npgsql.org/efcore/mapping/json.html?tabs=data-annotations%2Cpoco
• https://github.com/jawadhasan/pgjsondemo