Part 1: Designing an API with DotNet and Postgres

For your next project, why not try one of the best open source database servers?

For much of its first decade, the C# and other DotNet programming languages were expected to be paired with a Microsoft SQL Server database. Much has changed since then, and the biggest change is one I’d like to suggest to everyone: Postgres is a better database than SQL Server.

There are a variety of reasons to prefer Postgres over SQL Server:

• Postgres, unlike SQL Server, is natively based on UTF-8. This means no more text collation warnings, and text is no longer stored in two-bytes-per-character. This is a huge advantage for any software that deals with text. UTF-8 is unquestionably better than UTF-16.
• Postgres is extremely well integrated with containers. This means you can do tons of really neat things like automatically spawn a new test database for every run of your integration test suite.
• Postgres is generally considered to be faster than SQL Server. The TechEmpower benchmarks show DotNet and Postgres to be close to the top of performance numbers; SQL Server isn’t even measured.
• Postgres’ data types are simpler and easier to use than other database servers. Even better, Postgres allows you to bypass the silliness that is XML and move directly to native JSON objects.
• Oh yeah — it’s also cheaper.

With all this said, let’s write up a basic tutorial project demonstrating how to implement a DotNet API on top of Postgres. Along the way, we’ll demonstrate a variety of useful techniques for API design and how they fit together to create a useful and maintainable solution.

An elephant is the icon of Postgres (rawpixel.com)

Using Docker to set up a Postgres database

The first step to get started is, ironically, the most counterintuitive to people like me who began with C# 1.0 and SQL Server: We don’t install the database server.

Instead, Postgres works best with containers. Unlike SQL Server’s grotesque monstrosity of a setup process, a postgres container with a fresh database can be launched in just a few seconds using Docker. I am a huge fan of docker instances that I can turn off or delete anytime I’m done with them!

Here’s how to get started:

• If you’re on a Mac, you can install Docker Desktop or use homebrew to install just the docker service.
• On Windows, you can download Docker Desktop for Windows — it will require you to reboot, but it will only ask you one question: whether you want to use Windows Subsystem for Linux 2.0. Say yes.

What comes next? Chances are, like me, you’ll start to notice some problems. On reboot, my Docker Desktop would essentially lock up in “Starting…” mode. Why is this?

Well, as it turns out, there are some configuration instructions you need to execute to make sure you are on WSL 2.0. These may not be obvious, but if you don’t execute these instructions, your Docker Desktop will simply never start. You may also want to turn off Windows Telemetry at this point; mine went nuts around this time and started eating up 30% CPU.

If you succeed in installing Docker Desktop, it will look something like this:

A fresh Docker Desktop installation with nothing running

Launch your Postgres docker instance

Once you have a working version of Docker Desktop, the next step is to create a fresh Postgres instance. This requires two commands:

> docker pull postgres
... it will do some work here ...

> docker run -p 5432:5432 --name some-postgres 
  -e POSTGRES_PASSWORD=mysecret 
  -e POSTGRES_USER=myuser -d postgres
... it will give you an instance ID ...

These two commands aren’t rocket science, but let’s break them down.

• With the command docker pull postgres you tell Docker to connect to its online catalog of images and find the most recent Postgres image.
• The command docker run will launch a new instance.

But here’s where it gets tricky: for some reason, the order of parameters in the call to docker run are significant! If you type the parameters in a different order, it might work or it might not. I was very confused by this when I tried to be clever and reorder them in a way that seemed appropriate for me. Don’t do it.

Let’s explain what they mean:

• The -p 5432:5432 parameter tells Docker which TCP/IP ports to use. The first number is the port that will be visible on your PC; the second port is the internal port within the container, which should be 5432.
• The --name some-postgres is the name of the container to Docker. This can be anything you want.
• The -e NAME=VALUE parameters set environment variables for the Postgres instance. The standard Postgres container consumes the environment variables POSTGRES_USER and POSTGRES_PASSWORD to set up the default account.
• The -dparameter tells docker to run in a detached/daemon mode. If you forget this parameter, Postgres will run directly in your shell.
• The final parameter postgres tells Docker what image to launch.

If, like me, you’re used to the glacially painful process of setting up SQL Server, you may be shocked when you run this command and the Postgres instance is available before you blink.

A fully running Postgres instance in less than sixty seconds.

Connect to your Postgres database

The next thing that may not be obvious to users of DotNet and SQL Server is this: Postgres doesn’t come with a management studio.

Frankly, I’m glad: The installation challenges of SSMS are just as bad as the installation issues with the main SQL Server product. Let it die; this program has just lived far too long.

For Postgres, I like to inspect it by using DBeaver, an open source database management tool. DBeaver can not only manage Postgres but most other databases as well. It doesn’t have licensing costs unless you decide to purchase the DBeaver Pro version.

To connect to your database server, there’s a few basic steps:

• In the top menu, select Database | New Database Connection.
• Select Postgres under the popular connection types screen.

DBeaver connection process for Postgres

• For host, choose localhost; for port, choose whatever you specified when you created the docker instance.
• For username and password, choose the values you decided upon when you launched the database.

DBeaver connection screen for my docker instance

When complete, you can navigate through your database and find your table schema and work with them just like you had Management Studio in place.

Creating a connection string for your Docker instance

The final step to connecting to your database is to create and preserve the connection string for your application. Connection strings can be annoying since they often look right but don’t work on first attempt.

Here’s my connection string — it’s pretty simple:

Host=localhost;Port=5432;Username=myuser;Password=mysecret;

Yet when I first started this project, I got an “invalid credentials” message. No matter how many times I retyped the username, password, server, and port, I would always get invalid credentials.

The trick was that I forgot I had installed Postgres locally. I had, on my computer, an instance of Postgres for Windows that was laying claim to port 5432 and rejecting all connection requests. Once I uninstalled it, and relaunched my Docker, everything worked as expected.

What’s next for your DotNet API project?

This article will be the first of a series on basic API design. I’ll publish my ongoing work on this tutorial to my GitHub account and on my Example API repository.

In our next article, we’ll talk through Dapper and EntityFramework Core. In 2019 Dapper was significantly faster than EF Core; but will the same still be true today?

We’ll also look at strategies for creating a healthy separation between your data layer and your business logic layer; these are all things that can slow down your code but will result in a more manageable product in the long run.

Ted Spence heads engineering at ProjectManager.com and teaches at Bellevue College. If you’re interested in software engineering and business analysis, I’d love to hear from you on Mastodon or LinkedIn.