ASP.NET Core, Identity and PostgreSQL

So, you want to use the brand new ASP.NET Core (release candidate at the time of writing) and of course you want to use the nice Identity 3.0 framework, but you want to use PostgreSQL as the database? No fear. It's extremely simple.

This assumes that you're making a new database without any existing data. If you already have a system (for example a Membership API system), you may need to make a provider for Identity, but that is also quite simple. I might write another post about that.

Start With a Template

Start with an ASP.NET MVC Core project from Visual Studio 2015. It will set up a basic system for Identity and everything and is a good base, so we'll just edit it to use PostgreSQL instead.

Install Prerequisites

First go to References, right click and select Manage NuGet Packages... Select Browse, make sure Include prereleases is on, search for Npgsql and install the newest beta (at the moment 3.1.0-alpha6). Then search for EntityFramework7.Npgsql and install it (at the moment 3.1.0-rc1-3). That's all the libraries we need for now.

Change Providers

Open up the Startup.cs file. In ConfigureServices() you will see a block of code like this:

    .AddDbContext<ApplicationDbContext>(options =>

Notice the SQL Server parts? That's all we need to change. We just need to tell the system to use Npgsql instead:

    .AddDbContext<ApplicationDbContext>(options =>

That's it. Now the code changes are done.

Change Connection String

Next open appsettings.json so we can change the connection information. You'll see there is a default Data:DefaultConnection:ConnectionString with a SQL Server definition. We'll just change it a bit:

"Data": {
"DefaultConnection": {
"ConnectionString": "host=;userid=mypgsqluser;password=verycomplicatedpwd;database=mydatabase"

Just edit the host, userid, password and database information and save.


Almost. That's all you need to do to the code. The Identity system is so untied from the database providers that this is all you need to do. Gone are the days when you had to write code yourself to handle Membership. Just tell the system where to put things and you're off!

When you run your application for the first time you will get an error page saying the tables aren't defined etc. But there is a nice little suggestion that maybe you have to run a migration script. Click on it and the framework will create the required tables in your database. After that everything just works!

Add comment