Basics of .NET Web Apps: Part 2 – PostgreSQL and Entity Framework

If you’re just jumping into this post, make sure to catch up on the current state of the RescueShare project here.

If you’ve already read up, let’s get started!

So, now we have the bare bones of a web app. But, how are we going to save and access data? We need to set up a database.

I chose to use PostgreSQL as my database based on the fact that it is powerful and is being used more frequently to handle large data. You can learn more about PostgreSQL here but the quick snapshot is that PostgreSQL is an open-sourced object-relational database that uses the SQL language. This blog series does not require experience with SQL but if you’re curious, this is a great place to get comfortable with the basics.

So, let’s download PostgreSQL!

Note: I am going to walk through the steps to download Windows x86-64 Version 11.3, but there are detailed instructions on the PostgreSQL website for other operating systems and versions.

Choose the OS and version you want to work with. I will be working with this:

Click through the prompts.

Make sure you install pgAdmin.

Okay, so now we have a pretty program and a powerful database. Cool. How do we get them to work together?

In many programs, the SQL language is how your logic will communicate with the data. However, we will be using a tool called Entity Framework to make our code cleaner and our lives a little easier.

What is Entity Framework?

TLDR: An object-relational mapper that allows you to use the what you already know and love (.NET) to communicate between your logic in Visual Studio and your data in PostgreSQL (although EF can be used with several other database providers listed here).

In-depth: Read all about Entity Framework (which I highly recommend because it’s super cool) here.

How do we get Entity Framework?

Remember the NuGet Packages we installed from Part 1? Entity Framework was one of those packages.

Why Entity Framework?

EF lets us use the Code First method which basically means we can build our database using classes rather than SQL queries. This allows us to organize our code in a more Object Oriented style and maintain a sleek and easy to-read-look.

Convinced?

Let’s get started.

First, it’s important to understand the basics of EF. There are several types in EF, most importantly being the DbContext. The DbContext communicates with Models to “write” and execute queries. Any tables you want to have in your database will be defined in the DbConext using this syntax:  

public DbSet Dogs { get; set; }
public DbSet Shelters { get; set; }
public DbSet Volunteers { get; set; }

This will look for a corresponding Model. The model is where you will define the columns for your table as properties and it looks a little like this:

namespace RescueShare.Models
{
    public class Dog
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string Breed { get; set;}
    }
}

You can find the DbContext in the Data folder in the Solution Explorer. The default name will be ApplicationDbContext.

You can add models in the Models folder by right clicking on the folder and clicking Add > Class

Remember, every time you add a new model you want to be a table, you must add it to the DbConext.

We’ll talk more later about what these Model files will contain, but for now, just add the properties you want to see as columns in your table.

Now we know how to create tables using Entity Framework. Great!

But how do we actually add them to our database?

First, let’s take a look at what our database already has.

Log in to pgAdmin if you haven’t already (this was installed with PostgreSQL and you can get to it by a quick search in your downloads folder).

Once there, navigate to localhost -> Login / Group Roles

Add a name to your user (I used the name of my application) and added a password under the Definition tab.

Now you have a user! This is next part could have been done with the default postgres user, however, it’s best to create your own.

Back in Visual Studio, navigate to your startup.cs file. You should see some lines of code that look similar to this

services.AddDbContext(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("DefaultConnection")));
            services.AddDefaultIdentity()
                .AddDefaultUI(UIFramework.Bootstrap4)
                .AddEntityFrameworkStores();

Currently, this is telling your application to use the default database (SQL Server) but we want to tell it to use pgAdmin. So, change the line that says

options.UseSqlServer

to say…

options.UseNpgsql

On the next line of code, you can see it is grabbing something called the “DefaultConnection”. This is found in our appsettings.json file. This also holds a default value we are going to change. Right now, you should see something that looks similar to this

"Server=(localdb)\\mssqllocaldb;Database=aspnet ..........

but we want to update it with our user we just created in pgAdmin.

Your updated version should look something like this (with your user information instead):

User ID=rescueshare;Password="";Host=localhost;Port=5432;Database=rescueshare_local;Pooling=true;

Congrats! You have now configured your application to use PostgreSQL!

Next step, getting your data actually into the database.

In the Data folder where you found the DbContext, you should have also seen a Migrations folder. A Migration is how Entity Framework wraps up all your database changes and converts them into SQL to send to PostgreSQL.

You can read more detail about Migrations in Entity Framework here

Remember the Package Manager Console from the first blog post that allowed you to add NuGet Packages? This is also where you will add migrations and update your database.

Here are the steps to adding a migration:

1. In the Package Manger Console, run Add-Migrations

You will be prompted to add a name. This should be something short about the changes you made. After adding a name, hit enter.

You should see something like this:

This will automatically create a class in your Migrations folder where you can see the changes that will be made.

1. Now that you have a migration, you need to update your database with the data.

Still in the Package Manager Console, run Update-Database.

Woo! You have just successfully moved all your database changes into your database which you can now view through pgAdmin!

That was a lot of heavy information so I highly recommend reading through some of the docs I linked throughout the post to get a deeper understanding for what we did here.

Up next, we’ll get more into the thick of it and start writing some world altering code.