
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
1 2 3 4 5 6 |
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.
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.
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.
1 2 3 4 5 6 7 |
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.
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 |
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.
1 2 3 4 5 6 |
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
.
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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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.
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.
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.
1 2 3 |
"PostgresDatabase": { "ConnectionString": "User ID=dbuser; Password=555gpah4jzetczpf; Host=localhost; Port=5432; Pooling=true;Min Pool Size=0;" } |
Further Reading
https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/
https://www.pluralsight.com/courses/postgres-dotnet-developers