Building a .Net Core App with PostgreSQL

Posted by Phil on February 09, 2017

While I'll always be a big promoter of Microsoft Azure and the massive breadth of services it offers, lately I've been looking at the sorts of services I actually need to use most often and what value I'm getting from the amount I'm investing.

Azure's pricing model works well for businesses of almost any scale but for the individual or smaller user, I've been less and less comfortable with the bang I get for my New Zealand buck. As a web developer, the core services I need are:

  • IIS hosting
  • SQL database
  • SSL support

Anything else is only really necessary on a case-by-case basis.

To meet these needs more economically, I've gone with Virtual Private Server hosting and while I could quite happily get by in most cases with SQL Server Community edition, I feel a more robust option is one where I don't have to sacrifice scale or features. That's where PostgreSQL comes in. Rob Conery - as ever - was well ahead of the curve on PostgreSQL and I first saw him writing about its advantages in 2011. But being quite comfortable with what I was doing within my career at the time, I saw no reason to switch from SQL Server.

I still think SQL Server has its place, but I find myself agreeing more and more with Rob (seriously - go read this article. It's worth it). I'll cover more of that in another post. For now, let's look at how we use PostgreSQL in an MVC application.

The first thing I'll do is create a new project. I'm using Visual Studio 2017, so I'll find ASP.Net Core Web Application (.Net Framework).

I'll use the "Web Application" template, which will scaffold some default views, controllers and scripts.

Also, I'll use authentication in my app. I want users to be able to create accounts and sign in, so I'll pick "Individual User Accounts".

So far, this is a pretty standard set of steps for creating a new web application. Let's get PostgreSQL added to the solution.

I'm assuming you have already downloaded and installed a PostgreSQL instance from https://www.postgresql.org. We can use Entity Framework in exactly the same familiar way as we're used to when working with SQL Server thanks to the good people at http://www.npgsql.org, who have built a superb data provider for PostgreSQL offering the same seamless development experience. We can add Npgsql using NuGet:

Search for "npgsql" in NuGet and add Entity Framework 6. I'll also need to define the connection string. A .Net core application will use appsettings.json: simply add the server and database name, port number and other credentials.

If you're opting to go with a "classic" ASP.Net MVC application, the web.config connection string would look like this:

    <add name="DefaultConnection" connectionString="Server=localhost;Port=5432;User id=pg-user;Password=b6W1Z40tJT6Ja8Eq3OuE;Database=my-pg-app" providerName="Npgsql" />

 

Obviously your database user credentials will be different than what I've shown here.

Next, we need to add services to our application container. I'll update the ConfigureServices method in Startup.cs to look for and add options for Npgsql:

        // This method gets called by the runtime. Use this method to add services to the container.
                public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddDbContext<ApplicationDbContext>(opt =>
                opt.UseNpgsql(Configuration.GetConnectionString("DefaultConnection"), b => b.MigrationsAssembly("WebApplication1")));

            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<ApplicationDbContext>()
                .AddDefaultTokenProviders();

            services.AddMvc();

            // Add application services.
            services.AddTransient<IEmailSender, AuthMessageSender>();
            services.AddTransient<ISmsSender, AuthMessageSender>();
        }

Notice the options I'm passing to the AddDbContext method of the services object. If you find that Visual Studio doesn't recognise that namespace, make sure you've installed .Net Core on your machine and that the solution is targeting that version of the framework.

The great thing about .net Core apps is the native dependency injection. I can have an ApplicationDbContext parameter in the constructor of my controllers and .net is smart enough to "know" what concrete type to pass in to that constructor (yes, I'm simplifying here. It's a deliberate choice for the purposes of this post).

If I were to build a traditional MVC application, I'd want to use a dependency injection framework. My preference these days is to use Autofac. While I'm a fan of Ninject, I've found it hasn't really kept pace with Microsoft's evolving web application frameworks and choices and Autofac offers a simple, clean syntax to DI that works across more of the project types right now.

In the App_Start\WebApiConfig.cs file, I'd include the following code in my Register method (having first added a NuGet reference to Autofac libraries such as Autofac.WebApi2, Autofac.WebApi2.Owin and Autofac.Mvc5):

            // Set the dependency resolver to be Autofac.
            var container = builder.Build();
            var resolver = new AutofacWebApiDependencyResolver(container);

            config.DependencyResolver = resolver;

 

From here, I can add my EF code first models, and start pushing the initial migration for setting up the database using Powershell. Running the EF command "dotnet ef database update" should pick up the DbSet entity types you define in the ApplicationDbContext class (including the ASP.Net Identity tables), build out the definitions of the models you've created for those entities and populate everything into your PostgreSQL database.

If I check my PostgreSQL instance, I can see my database has been created and those tables are populated correctly.

If it doesn't work straight away, check to see what permissions your DB user has in the PostgreSQL schema, and make sure the right user is defined in your config.

That's really all there is to it - stylistically quite different for people more familiar with the "traditional" MVC application, but a very simple series of steps for adopting a low-cost, high performance database alternative.

Have I glossed over anything too quickly or made a mistake here? Tweet me @Phil_Wheeler to let me know what needs updated.