Docker: Adding PostgreSQL to .Net Core – Part 2

In our last blog, we dug deep into all things Docker and learned how to scaffold our app. In this installment, we’ll break down what you need to know about Postgres and build out our API code.

Add Postgres To .NET Core

So far, we’ve written some basic startup scripts for our Gadget Depot project. You can get the final project template here.

We had scaffolded all the Docker features of the app, but now we need to hack on the application source code to get things up and running. Out of the box, .NET Core is not expecting to work with PostgreSQL – this is the first issue we’re going to fix.

Adding PostgreSQL to .NET is a relatively simple procedure:

  • Include a reference to the .NET PostgreSQL package
  • Add a connection string in our app’s configuration
  • Register the PostgreSQL service in the ASP.NET startup.

Add the Npgsql dependency

To teach .NET how to interface with PostgreSQL, we’re going to add the Npgsql library by adding the reference to Npgsql to your .Backend/GadgetDepot/GadgetDepot.csproj file:

That’s all for added dependencies!

Next, we need to give our app a connection string for the dockerized PostgreSQL database. This connection string specifies the username, password, host address and database name for our connection.

Configure .NET database connection

Update your appsettings.json to resemble the following:

Notice the key=value: Server=db. Where does the hostname db come from? This is just the name we gave our database service in the docker-compose.yml. Internally, Docker sets up a kind of DNS for addressing services from within the networked containers, where each service’s name functions as its hostname. So, directing the .NET program to the hostname db will send it straight toward the PostgreSQL instance.

Add the Npgsql Entity Framework service

The last bit of code needed to set up our PostgreSQL connection in the .NET app is an Entity Framework adapter.

We’ll add this adapter service to the Startup class in the Backend/GadgetDepot/Startup.cs file. Update the ConfigureServices method in your Startup class so that it includes the call to the IServiceCollection.AddEntityFrameworkNpgsql method, making use of the connection string we created above.

That’ll do it! Now you have an ASP.NET Core app communicating with PostgreSQL.

Congrats!

All that remains is to load up some gadgets and display them in the Angular app. Gadget Depot is going to have its deadline met.

Build the API

Time to build out the REST API for our gadgets. This section isn’t really about PostgreSQL or Docker in particular, so we will stay at a high level and move quickly without getting bogged down in details.  We are going to declare a model, alter our database to support this model and write a basic REST API controller. For a deeper look into how to develop REST APIs with ASP.NET Core, take a gander at the official Microsoft docs.

Model a Gadget

For our requirements, a gadget is just a name and nothing else. To model it in our app, go to the GadgetDepot/Models directory and add a new class Gadget in Gadget.cs:

That’ll do just fine. Next, we need to integrate this model declaration with our database. To do this, we need to implement the DbContext interface with our new Gadget class taking a leading role.

Include Gadgets in the Database Context

Create the file Backend/GadgetDepot/ApiDbContext.cs, and write our custom Gadget Depot DbContext to include the Gadget model:

This class declares that our app’s persistence layer has a set of gadgets that we will implement in PostgreSQL as a single table.

To use this context, swap out the DbContext that we declared in the Startup class. Be sure to add a using GadgetDepot.Models at the head of the file, then make the change to the ConfigureServices method in the GadgetDepot.Startup class:

As a result of this change, Entity Framework Core now expects a table called “Gadgets” in the PostgreSQL database. At this stage, we’re primed to make a migration file and update the schema of the database.

Navigate your shell to the Backend/GadgetDepot project and run the following commands to create and apply a migration.

Initialize the Database

As a final flourish, we can insert some test gadgets into the database.  Create the file Backend/GadgetDepot/DbInitializer.cs with the following class:

The static Initialize method will programmatically make sure that the database gadget is existent, and if the “Gadgets” table is empty, it will add a few test rows.

We can make this method run at startup by inserting a call to Initialize in the Main method of the Program class. The method has a dependency on a database context, so we need to get that context from within Main.

Alter your Backend/GadgetDepot/Program.cs file to mimic the following:

The trick here is to create a scope wherein we can access the services — most importantly, an ApiDbContext instance. Passing this context into the DbInitializer.Initialize method allows it to make a connection with the database, and execute its routine.

Note that this Initialize call will run every time the app boots. In the future, we might want a more sophisticated way to condition whether or not we want this code to run, but for the sake of immediate development, this is good enough for Gadget Depot.

Write an API Controller for Gadgets

To round out our API, we need to add a controller class for exposing the gadget data. As with the steps it took to provision the database, you can find an in-depth guide to this facet of ASP.NET programming in other tutorials.

We’ll just copy in the code so we can get our Gadget Depot app delivered on schedule!

Create the file Backend/GadgetDepot/Controllers/GadgetController.cs:

Now localhost:5000/api/gadgets returns our list of gadget inventory in nice, JSONified form. All that’s left to do is make our Angular app consume this API.

Build an Angular Web Client

So long to the .NET code.

Move over to the frontend directory and get ready to write an Angular app.

Truth be told, it’s not going to be much of an app at all, and Angular is certainly overkill for what we’re setting out to accomplish. But, it’s easy to set up using Docker and the ng tool, so we may as well lay a good foundation for future iteration on Gadget Depot’s web app.

Change the src/app/app.component.ts file so that the main component will connect to the REST API backend:

Be sure to import the HTTPClientModule in your AppModule, and your Gadget Depot client is officially minimally viable. Which is to say: it works!

Ship it to Gadget Depot – we’re done here!

Wrap-up

We used Docker to build a whole full-stack web app. ASP.NET Core, PostgreSQL and Angular all working together right out of the box.

Kudos!

To run your app, all that needs doing is a call to docker-compose in the root directory:

Watch as all your services network together and spring to life in a cozy, containerized world unto themselves.

Share this story

Leave a comment