.NET Core, PostgreSQL and Document Database

Introduction

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 is post is not about differences or comparisons between the two but rater 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 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 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 with a very common paradox of choice: Too many choices. How do you chose? 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 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 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 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, lets 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. Its 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 be also using Entity Framework for database queries however, I wont be discussing details about how the solution is setup and if you need more information about those topic, you can search on internet or you can also check my book which goes in much more details about those concerns.

Ok, lets 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 book mentioned earlier.

Domain Setup

Remember from 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. Lets 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

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 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 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.

.NET Core, PostgreSQL and Document Database

Introduction