PostgreSQL and Dapper in .Net Core

In my previous posts ASP.Net Core Web API Application with Dapper – Part 1 and ASP.Net Core Web API Application with Dapper – Part 2 I have covered using SQL Server and Dapper for database and data access layer.

In this post, I will be exploring PostgreSQL (aka Postgres) as a database and Dapper for the data access layer.

What is PostgreSQL

Postgres is an open-source, multi-platform RDBMS database.

The support for JSON in Postgres is fantastic. Postgres can be scaled out in a distributed fashion, and we can get all the advantages of a distributed database using Citus, an extension on Postgres.

First of all, I will download and install Postgres for Windows from https://www.postgresql.org/download/windows/. I will use the latest version 10.3 for Windows.

Once the download is complete, I will install it on my Windows 10 PC. I will use the standard setup for the installation.

What is PgAdmin

PgAdmin is a visual tool for managing Postgres. PgAdmin is installed along with Postgres. Using PgAdmin we can create and manage database. It also provides a query tool, where we can write and execute queries.

Setup Database

First I will use the PgAdmin tool to create a new database TimeManagement.

Once the database is created using the Query tool I will create a new table Employee using the query:

CREATE TABLE Employee (
    first_name varchar (100), 
    last_name varchar (100), 
    address varchar (4000) 
);

C# Code

Now its is time to finish up the C# code to connect to the Postgres database.
First of all, I will create a new Console Application using File -> Project -> New. And select .NET Core -> Console App (.NET Core), and name the project as TestPostgres.

Furthermore, I will install the nuget package Npgsql.

And than, I will add the Dapper nuget package to the project.

Finally, I will finish up writing the code.

using Dapper; 
using Npgsql; 
using System; 
using System.Linq; 
namespace TestPostges 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            using (var connection = new NpgsqlConnection("Host=localhost;Username=;Password=;Database=TimeManagement")) 
            { 
                connection.Open(); 
                connection.Execute("Insert into Employee (first_name, last_name, address) values ('John', 'Smith', '123 Duane St');"); 
                var value = connection.Query("Select first_name from Employee;"); 
                Console.WriteLine(value.First()); 
            } 
            Console.Read(); 
        } 
    } 
}

The code is self-explanatory, all I am doing is creating a connection object of type NpgsqlConnection. And using the Dapper extension method, creating the record in the Employee table first. And then selecting the same value and printing out in the console.

When I run the application, I can see “John” in the console.

JSON Support in Postgres

Postgres treats JSON as a first-class data type. When means, you can select a specific property from the JSON using a select query. Not just that, you can use it in any other SQL construct like Where. Furthermore, Postgres supports Indexes on JSON property.

Creating a JSON column

I will update my TimeManagement database that I have created in my previously. I will drop the table Employee, so that I can create it back using a JSONB column instead of multiple columns for every property.

drop table Employee;

Once the table is dropped, I will create the Employee once again. But this time, I will just have two properties. First is the employee id, an integer. And the second one is the data, which is of type JSONB. The data attribute will contain the JSON payload.

create table Employee ( 
    id int, 
    data jsonb 
);

Using JSONB in .Net Core

First of all, I will open the TestPostgres project that I have created in my previous post. After the project is opened, I will update the Main method to first insert a JSON data in the Employee table.

The JSON data will have two properties, first_name and last_name of the employee. And after the data insert is complete, I will select the first_name of from the Employee table. And for selecting the first_name JSON property from the data column, I will use the ->> syntax provided by Postgres.

static void Main(string[] args) 
{ 
    using (var connection = new NpgsqlConnection("Host=localhost;Username=;Password=;Database=TimeManagement")) 
    { 
        connection.Open(); 
        connection.Execute("Insert into Employee (id, data) values (1, '{\"first_name\":\"John\", \"last_name\":\"Smith\"}');"); 
        var value = connection.Query("Select data ->> 'first_name' from Employee;"); 
        Console.WriteLine(value.First()); 
    } 
    Console.Read(); 
}

Executing this code should print “John” on the console output.

And using a select command on the Employee will show the JSON output.

Creating an Index on JSONB

Indexes are one of the most important features of a database. It makes the reading of data fast. That is why it’s an extremely important feature to support JSON data as well. And Postgres does it very well.

The create index command for the JSON data column is also similar to other data types. The only difference is using the ->> operator for identifying the JSON property from the JSONB column.

For my application, I will just create an index on the first_name of the JSON data.

create index idx_first_name on Employee ((data ->> 'first_name'));

Using Where condition on JSON data

Finally, it’s time to use JSON data in Where condition. Since I have created an index on the first_name. Hence, I will modify the existing code inside of Main method to use Where.

First I will change the Insert query to create a different record. And then I will use a where condition n the first_name to select only the record where first_name is John. And I will be using the same ->> operator for this purpose as well.

static void Main(string[] args) 
{ 
    using (var connection = new NpgsqlConnection("Host=localhost;Username=;Password=;Database=TimeManagement")) 
    { 
        connection.Open(); 
        connection.Execute("Insert into Employee (id, data) values (1, '{\"first_name\":\"Sam\", \"last_name\":\"Mack\"}');"); 
        var value = connection.Query("Select data ->> 'first_name' from Employee where data ->> 'first_name' = 'John';"); 
        Console.WriteLine(value.First()); 
    } 
    Console.Read(); 
}

Conclusion

Installing and using the Postgres database is very simple and straight forward. And using the Npgsql and Dapper with .Net core for using Postgres is very straight forward.

JSON support in Postgres is awesome and powerful. Now we can use both the use cases of the Document database and RDBMS in the same Postgres database. And using Postgres with .Net Core is as simple as any other database. And Dapper makes it even simpler.

The other advantage of using Postgres with .Net Core is that both are open source. And both support multi-platforms.

Finally, I have created a video on YouTube with step by step implementation of Postgres with .Net Core.

https://www.youtube.com/watch?v=pfaBA8RD9SU

https://www.youtube.com/watch?v=-rxMJ5kjKOE