Many .NET developers think of the “Microsoft Stack,” which implies the kinds of data stores we should use. That means SQL Server has been the one and only database that many of us have used. PostgreSQL is a database that has a long open-source history going back to 1997. It's about time that many .NET developers took a look at this powerful and free database.
Why PostgreSQL and Why Now?
I've been a .NET Developer since 1.0 Beta. In all that time, I've used SQL Server for most projects. It's great. As the default database (e.g., LocalDb), it has been easy to just use it everywhere. It becomes a problem later when you want to move from a prototype to determining how to deploy it. The only real issue is cost.
Not all of my customers are big enterprises with a large licensing base through Microsoft. It used to be that MySQL was the way to go for a low-cost database. Unfortunately, some concurrency and object-oriented features were lacking in MySQL. With those benefits in hand, PostgreSQL has replaced MySQL in that role for the past dozen years or so. As an open-source project, it can fit any project that has any cost pressure.
This article is too short to compare databases and help you pick the right one—that's not my goal. Instead, I'd like to get you to understand how to use PostgreSQL so you have options. As I'm apt to say a lot, it's another arrow in the quiver of your development choices.
Using PostgreSQL
Getting set up to use PostgreSQL is pretty simple. You can, of course, install PostgreSQL locally using the installer. Just visit http://postgres.org/ and run the installer for PC, Mac, or Linux. I don't do that. Instead, I just run a container locally. This has the benefit of starting with a new, empty database any time you want to chuck the database. Although the community that has maintained this Docker image stresses that the image is more suited to development and not deployment, you'll have to make up your own mind when you're ready for deployment. Because I'm showing you how to use it for development, a container image is perfect.
In my examples, I'm going to assume you're using Docker as it's the most common container runtime, but it should be similar in other runtimes.
To run the container, you first need to pull the image:
> docker pull postgres:latest
Then you can create an instance by using docker run (shown here in PowerShell with the backticks for line breaks—not necessary, but easier to show you the command-line):
> docker run --name yourpostgres `
-p 5432:5432 `
-e POSTGRES_PASSWORD={SOMESECUREPWD} `
-d `
postgres
This starts a PostgreSQL instance that you can use. You can see the different command-line arguments I'm using to start PostgreSQL in Table 1.
If you open Docker Desktop, you can see the container running as shown in Figure 1.
Let's make sure it works. Pick the container, and change the tab to the Exec tab. You can use the psql command to open a PostgreSQL console:
# psql -U postgres
Then you can issue a quick SQL query to make sure the database is working:
SELECT * FROM pg_namespace;
You can see this working in Figure 2.
Before you begin using it in .NET, let's talk about some differences.
Differences Between SQL Server and PostgreSQL
SQL Server is a relational database. In contrast, Postgres is an object-relational database. In practice, you won't really see a big difference, but you'll want to be prepared for differences in how they work under the hood. These changes can affect your applications, so you should look at the real differences. Google Cloud has a good primer on the differences that you should read (https://shawnl.ink/sql-psql-compare).
When you're working with PostgreSQL coming from SQL Server, some conventions are different.
When you're working with PostgreSQL coming from SQL Server, some conventions are different. Although SQL Server and PostgreSQL have their own flavors of SQL (T-SQL and pgSQL), the core SQL language is the same. This means that you need to change how you use SQL in PostgreSQL. I'll show you later in this article how to interact with it using Entity Framework, which hides most of these changes from the developer, but I think it's important to understand some basic differences.
First, and probably most important, PostgreSQL is case-sensitive. That means a table named People is different from a table named people. By default, PostgreSQL lowercases your table and column names. If you're moving existing schemas, you might need to specify a mixed-case table and column names. You can do this by using double quotes. For example, if you want to create a simple table in SQL:
CREATE TABLE People (
Id int primary key,
FirstName varchar(50),
LastName varchar(50),
Birthdate date
)
This results in a table:
CREATE TABLE People (
Id int primary key,
FirstName varchar(50),
LastName varchar(50),
Birthdate date
)
Notice that the table and column names are now lowercase. This isn't much of a problem, but it's important that you know this when creating any SQL. You can keep the mixed-case by surrounding them with double-quotes:
CREATE TABLE "People" (
"Id" int PRIMARY KEY,
"FirstName" varchar(50),
"LastName" varchar(50),
"Birthdate" date
)
In Figure 3, you can see both tables (both are valid; remember, the table names are case sensitive).
NOTE: I'm using the Visual Studio Code Extension called “PostgreSQL Explorer” to view the database and execute queries because Visual Studio doesn't support Postgres natively in the SQL Explorer.
The terms Postgres and PostgreSQL are used interchangeably in this article and out in the world.
Let's move onto .NET Code and see how using PostgreSQL should be pretty straightforward.
Entity Framework Provider for PostgreSQL
As a .NET developer, I've been happily using Entity Framework to access SQL Server for years now. In many .NET developers' minds, they think that Entity Framework and SQL Server are tied together firmly. Of course, this isn't true. For years now, Entity Framework has supported multiple relational databases (e.g., Oracle, MySQL, SQLite, etc.) as well as supporting non-relational data stores (e.g., CouchDB, MongoDB, CosmosDB, etc.). Let's take a look at the PostgreSQL support for Entity Framework.
One thing that may not be obvious is that Entity Framework is written so that if you're using Entity Framework to build your database, most of the code is identical between different data stores. For example, in Listing 1, you'll see the entity classes. If you review them, you'll notice that none of these classes know about what data store they're being stored in.
Listing 1: Entity Classes
public class Customer
{
public int CustomerId { get; set; }
public required string FirstName { get; set; }
public required string LastName { get; set; }
public required string Email { get; set; }
public string? PhoneNumber { get; set; }
public DateTime? DateOfBirth { get; set; }
public DateTime? MemberSince { get; set; }
public decimal LoyaltyPoints { get; set; }
// Navigation properties
public ICollection<Purchase> Purchases { get; set; }
= new List<Purchase>();
public CustomerPreferences? Preferences { get; set; }
}
public class CustomerPreferences
{
public string? FavoriteGenres { get; set; }
public string? PreferredPlatforms { get; set; }
public bool ReceivePromotions { get; set; } = false;
public bool ReceiveNewReleaseAlerts { get; set; } = false;
}
public class Purchase
{
public int PurchaseId { get; set; }
public int CustomerId { get; set; }
public DateTime PurchaseDate { get; set; }
public required string GameTitle { get; set; }
public required string Platform { get; set; }
public decimal Price { get; set; }
public bool IsDigital { get; set; }
// Navigation property
[JsonIgnore]
public Customer Customer { get; set; } = null!;
}
Similarly, in Listing 2, you'll see the DbContext class has no knowledge of PostgreSQL at all. All of the design around the data requirements are in a database-agnostic way.
Listing 2: GameContext class
public class GameContext : DbContext
{
public GameContext(DbContextOptions<GameContext> options)
: base(options)
{
}
public DbSet<Customer> Customers { get; set; }
public DbSet<Purchase> Purchases { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Configure Customer entity
modelBuilder.Entity<Customer>(entity =>
{
entity.HasKey(e => e.CustomerId);
entity.Property(e => e.Email)
.IsRequired()
.HasMaxLength(255);
entity.Property(e => e.FirstName)
.IsRequired()
.HasMaxLength(100);
entity.Property(e => e.LastName)
.IsRequired()
.HasMaxLength(100);
entity.Property(e => e.PhoneNumber)
.HasMaxLength(20);
entity.Property(e => e.LoyaltyPoints)
.HasPrecision(10, 2);
entity.HasIndex(e => e.Email).IsUnique();
entity.HasData(
new Customer
{
CustomerId = 1,
FirstName = "Alice",
LastName = "Smith",
Email = "alice.smith@example.com",
PhoneNumber = "123-456-7890",
LoyaltyPoints = 100,
},
new Customer
{
CustomerId = 2,
FirstName = "Bob",
LastName = "Johnson",
Email = "bob.johnson@example.com",
PhoneNumber = "987-654-3210",
LoyaltyPoints = 200,
});
// Complex Property: CustomerPreferences
entity.OwnsOne(e => e.Preferences)
.HasData([
new
{
CustomerId = 1,
FavoriteGenres = "RPG, Adventure",
PreferredPlatforms = "PC",
ReceiveNewReleaseAlerts = false,
ReceivePromotions = true
},
new
{
CustomerId = 2,
FavoriteGenres = "Strategy, Simulation",
PreferredPlatforms = "PC",
ReceiveNewReleaseAlerts = true,
ReceivePromotions = true
}]);
});
// Configure Purchase entity
modelBuilder.Entity<Purchase>(entity =>
{
entity.HasKey(e => e.PurchaseId);
entity.Property(e => e.GameTitle)
.IsRequired().HasMaxLength(200);
entity.Property(e => e.Platform)
.IsRequired().HasMaxLength(50);
entity.Property(e => e.Price)
.HasPrecision(10, 2);
entity.HasOne(e => e.Customer)
.WithMany(c => c.Purchases)
.HasForeignKey(e => e.CustomerId)
.OnDelete(DeleteBehavior.Cascade);
// Seed initial Purchase Data
entity.HasData(
new Purchase
{
PurchaseId = 1,
CustomerId = 1,
GameTitle = "Epic Adventure",
Platform = "PC",
Price = 59.99m,
PurchaseDate = new DateTime(2023, 1, 15)
.ToUniversalTime()
},
new Purchase
{
PurchaseId = 2,
CustomerId = 2,
GameTitle = "Strategy Master",
Platform = "PC",
Price = 39.99m,
PurchaseDate = new DateTime(2023, 4, 1)
.ToUniversalTime()
}
);
});
Only when you register your DbContext class do you need to worry about the actual provider:
builder.Services.AddDbContext<GameContext>(options =>
options.UseNpgsql(connectionString));
It might not have been obvious when you did this for SQL Server, but this is really the glue between the data store and the entity model. Connection strings are different though. You can use a name/value pair connection string like SQL Server:
Host=localhost;Database=gamestore;Username=postgres;Password=P@ssw0rd!
PostgreSQL also supports a URI format (e.g., postgres://localhost/somedb) for connecting to the database, but the PostgreSQL driver for Entity Framework requires this name/value format.
When it comes to migrations, they are tied directly to the database driver and are not database agnostic. So, if you're using PostgreSQL on an existing project, you'll have to regenerate the migrations:
dotnet ef migrations add SomeName
When creating migrations, the default behavior will be to preserve your table/column names. In many cases, you'll want to change this behavior. There's a useful package called EFCore.NamingConventions that allows you to specify the naming conventions for your tables/column names. To use it, add it to your project:
dotnet package add EFCore.NamingConventions
Then you can use extension methods on the UseNpgsql method:
builder.Services.AddDbContext<GameContext>(options =>
options.UseNpgsql(connectionString)
.UseCamelCaseNamingConvention()
// e.g. .UseSnakeCaseNamingConvention()
);
It's important to use this before you create your migrations as it will affect how the migrations are created.
In addition, you can specify the version number of the PostgreSQL instance if you want to pin your code to a specific version:
builder.Services.AddDbContext<GameContext>(
options => options.UseNpgsql(
connectionString,
n => n.SetPostgresVersion(17, 0)
)
);
You can see here that the Npgsql provider does expose most of the key features of PostgreSQL to your Entity Framework projects. The magic is in the fact that Entity Framework at runtime doesn't really care what database driver you're using. It just works.
The magic is in the fact that Entity Framework at runtime doesn't really care what database driver you're using. It just works.
Where Are We?
Hopefully, this article has given you a taste of moving out of your database comfort zone and trying a way to build your applications. Although every database has its pros and cons, having experience with PostgreSQL improves your skillset and gives you options for architecting small and large solutions.
Table 1: Docker command-line arguments
| Argument | Reason |
|---|---|
| --name | The friendly name of the container |
| -p | Specifies to expose port 5432 as the same port to the local machine. Port 5432 is the default port for PostgreSQL; you can always specify a different port if you want. |
| -e | This allows you to pass in an environment variable. In this case, we're setting the default password for the default user named postgres. |
| -d | Specifies that you want to detach from the container and leave it running. |
