PostgreSQL for .NET Developers

Jamie Bowman

July 17, 2020

PostgreSQL for .NET Developers

Venturing into microservice architecture has forced me to become more aware of other technologies that may be more performant, cheaper, and or a better match for that specific need. PostgreSQL (“Postgres”) is a relational database that is very comparable to Oracle. It’s not that different than MSSQL but has some serious quirks. The more I learn about Postgres, the bigger the fan I’m becoming.

Sample .NET Project with PostgreSQL and Dapper .NET

GitHub: .NET Repository Pattern

Why use PostgreSQL as a .NET Developer?

PostgreSQL is known as the most advanced open-source relational database in the world. It has some very notable features that make it more advanced than other relational databases. PostgreSQL is an Object-Relational Mapper Database (ORMD), which means it has ORM support right out of the box.

Features

Table Inheritance
Text Search – Natural Language Processing (NLP)
Supports Programming Languages: Ruby, Perl, Python, TCL, PL/pgSQL, SQL, .NET, JavaScript
Fully SQL Compliant
ACID Compliance
Can create custom data types
Includes data types for objects like IP Addresses
Row Level Security
NoSQL Support (JSON/XML)
JSON
Native partitioning
Fully open-source

Running in Docker

PostgreSQL can easily be ran locally in Docker.

docker pull postgres

docker run -d \
    --name some-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e PGDATA=/var/lib/postgresql/data/pgdata \
    -v /custom/mount:/var/lib/postgresql/data \
    postgres

docker run -d \

--name some-postgres \

-e POSTGRES_PASSWORD=mysecretpassword \

-e PGDATA=/var/lib/postgresql/data/pgdata \

-v /custom/mount:/var/lib/postgresql/data \

postgres

https://hub.docker.com/_/postgres

PostgreSQL Client

There are a few PostgreSQL clients worth mentioning. I prefer free and very stable reliable tools.

DBeaver Community

I personally prefer DBeaver when working with Postgres. It’s very easy to use and a very versatile tool that is capable of interfacing other databases including MSSQL, MySQL, and MongoDB.

https://dbeaver.io/download/

pgAdmin

pgAdmin runs in the browser as a web app that interfaces the Postgres database. This is a very viable option and is very well designed.

https://www.pgadmin.org/

PostgreSQL Languages

This is certainly a feature to PostgreSQL that makes it rather unique. With PostgreSQL, there are several procedural languages included with the base distribution: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python. There are also extensions that support other languages like Java, PHP, Python, R, Ruby, Scheme, and Unix shell.

PostgreSQL: G.3. Procedural Languages

This means you can create a function that runs in Python.

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ plpythonu;

CREATE FUNCTION pymax (a integer, b integer)

RETURNS integer

AS $$

if a > b:

return a

return b

$$ plpythonu;

https://www.postgresql.org/docs/9.0/plpython-funcs.html

PostgreSQL (PL/pgSQL) vs MSSQL

Switching to PostgreSQL has been a bit challenging for me. It’s really not the syntax that’s the issue. There are a lot of slightly different ways of doing things.

Declaring Variables

So, this was confusing but in a stored procedure I needed to declare the variable before the BEGIN statement.

SQL Statements Must End with a Semicolon.

All SQL statements needs to end with a semicolon, “;”.

PostgreSQL Data Types

For the most part this is very similar to MSSQL but there are some caveats and things are different.

INT

BIGINT

VARCHAR / TEXT

In PostgreSQL you have several options for a specified character length.

character(100)
char(100)
TEXT

ISNULL()?

So PostgreSQL doesn’t support an ISNULL() function however you can use NULLIF() or COALESCE().

MSSQL

SET @FirstName = COALESCE(NULLIF(@FirstName,”), NULL)

Square Brackets “[ ]”

I’ve used square brackets “[ ]” before to smooth out MSSQL keywords like state. This doesn’t work in Postgres.

Anonymous Code Blocks

In MSSQL you could create a new query and write T-SQL by declaring variables, making select statements, performing any sort of CRUD operation and everything was easy. This is not the case for pgAdmin or PostgreSQL. You can still do basic SELECT statements against tables but anything above and beyond that gets rather tricky if you aren’t familiar with PostgreSQL.

PostgreSQL: Documentation 45.5 Anonymous Blocks

DO

In order to write anonymous SQL You must wrap the block in a DO statement. This must specify the language as plpgsql because SQL does not allow anonymous code blocks.

DO language plpgsql $$
BEGIN

   -- code will go here
END
$$;

DO language plpgsql $$

BEGIN

-- code will go here

END

$$;

Variables

Variables must be declared in the DO block. They cannot be declared else where within an anonymous block.

DO language plpgsql $$
   DECLARE variable_name VARCHAR(255);
BEGIN

END
$$;

DO language plpgsql $$

DECLARE variable_name VARCHAR(255);

BEGIN

END

$$;

SELECT Doesn’t Work the Same

In the context of an anonymous block, SELECT will not work as it did in SQL Server Management Studio (SMSS). If you wanted to see specific bits of data you could use the RAISE NOTICE function.

DO language plpgsql $$
   DECLARE qty integer DEFAULT 32;
BEGIN
   RAISE NOTICE 'qty: %', qty;
END
$$;

DO language plpgsql $$

DECLARE qty integer DEFAULT 32;

BEGIN

RAISE NOTICE 'qty: %', qty;

END

$$;

Casting

Casting is done a little differently in PostgreSQL and I like it a lot. Instead of using CONVERT(VARCHAR, @VARIABLE) in PostgreSQL you can use data::string.

'08-24-1995'::date

1	'08-24-1995'::date

Date Time Objects

PostgreSQL has some really cool functions for managing date-time and timestamps.

Note: I HIGHLY recommend watching Rob Conery’s video on “Postgres for .NET Developers”. I learned some of these tricks from him. Also, I found this video long after I started this blog post… This post was originally for notes for learning Postgres. Comparing it to what I already know seemed to help.

# COMING SOON

1	# COMING SOON

Functions (Stored Procedures)

Functions in Postgres act very similar to Stored Procedures in MSSQL. Yet, Postgres also has Stored Procedures that act like functions in MSSQL.

CREATE OR REPLACE FUNCTION public.get_customers(
	firstnamefilter character,
	lastnamefilter character,
	emailfilter character,
	cityfilter character,
	statefilter character,
	countryfilter character)
    RETURNS TABLE(customerid bigint, firstname character, lastname character, email character, birthdate date) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN		
	RETURN QUERY 
		SELECT "CustomerId", "FirstName", "LastName", "Email", "BirthDate" FROM public."Customers";
END;
$BODY$;

CREATE OR REPLACE FUNCTION public.get_customers(

firstnamefilter character,

lastnamefilter character,

emailfilter character,

cityfilter character,

statefilter character,

countryfilter character)

RETURNS TABLE(customerid bigint, firstname character, lastname character, email character, birthdate date)

LANGUAGE 'plpgsql'

COST 100

VOLATILE PARALLEL UNSAFE

ROWS 1000

AS $BODY$

BEGIN

RETURN QUERY

SELECT "CustomerId", "FirstName", "LastName", "Email", "BirthDate" FROM public."Customers";

END;

$BODY$;

To query this function run this command.

SELECT * FROM get_customers('', '', '',	'',	'',	'');

1	SELECT * FROM get_customers('', '', '', '', '', '');

Text-Searching Natural Language Processing (NLP)

Microsoft SQL Server does not support text searching out of the box. Using something like Lucene would give that capability. However, PostgreSQL has text-searching capabilities built into their database engine.

# COMING SOON

1	# COMING SOON

DBeaver: Creating a Stored Procedure

Yeah, so this took me a few minutes to figure out. It’s not as intuitive as I’d like it to be. Creating a stored procedure in DBeaver is easy. You just need to right-click “Functions” and select “Create New Function”. This pops up a dialog where there is an option to change the Type to “Stored Procedure”. I also prefer setting the language to “plpgsql”.

Dapper .NET with PostgreSQL

I’ve set up a repository that includes Dapper .NET and interfaces Postgres.

GitHub: .NET Repository Pattern

PostgreSQL .NET Connection String

I’ve stored this connection string in the appsettings.json file.

  "PostgresDatabase": {
    "ConnectionString": "User ID=dbuser; Password=555gpah4jzetczpf; Host=localhost; Port=5432; Pooling=true;Min Pool Size=0;"
  }

"PostgresDatabase": {

"ConnectionString": "User ID=dbuser; Password=555gpah4jzetczpf; Host=localhost; Port=5432; Pooling=true;Min Pool Size=0;"

}

PostgreSQL for .NET Developers

Sample .NET Project with PostgreSQL and Dapper .NET

Why use PostgreSQL as a .NET Developer?

Features

Running in Docker

PostgreSQL Client

DBeaver Community

pgAdmin

PostgreSQL Languages

PostgreSQL (PL/pgSQL) vs MSSQL

Declaring Variables

SQL Statements Must End with a Semicolon.

PostgreSQL Data Types

INT

VARCHAR / TEXT

ISNULL()?

MSSQL

Square Brackets “[ ]”

Anonymous Code Blocks

DO

Variables

SELECT Doesn’t Work the Same

Casting

Date Time Objects

Functions (Stored Procedures)

Text-Searching Natural Language Processing (NLP)

DBeaver: Creating a Stored Procedure

Dapper .NET with PostgreSQL

PostgreSQL .NET Connection String

Further Reading