The Journey Continues: Containerized.NET5 Web App on Docker Connects to database-container
--
In a previous article, I was showing you how to create a .net5 webapp with the ultimate goal to containerize it using docker.
In this second part of the journey, I will show you how to make your webapp consume data from your own database and how to turn this into a docker scenario where the .net5 web app container talks securely to a database container.
Prerequisites:
- Debian or Ubuntu Linux to follow the guide here, but works with other OS too.
- A local docker installation (https://docs.docker.com/engine/install)
- .net 5 web app from part 1 (Or get the v1 from the related github repo https://github.com/martinhorvath/com.primebird.net5webapp/releases)
- Visual Studio Code (or other IDE of choice) with the C# extension installed (https://marketplace.visualstudio.com/items?itemName=ms-dotnettools.csharp)
If you need help or encounter unexpected errors, check the short troubleshooting section at the end of this article or leave a response. I’ll keep an eye on this and I’m happy to help.
First of all, let me create an objective so that this exercise/guide makes a little sense. One thing I love to keep track of is how I use my time. We import airports in the US then have the .net web app render them for us. This data is publicly available as part of a Kaggle challenge (https://www.kaggle.com/usdot/flight-delays)
PostgreSQL, my favorite database
This is not the place to start a discussion about the best sql database or if one should better choose nosql. I’m an Oracle certified expert, and also worked with MS SQL Server, MySQL, SAP HANA and PostgreSQL in productive environments. As they all have pro’s and con’s, I’ll choose PostgreSQL for you. Why? I like the advanced data types, spatial capabilities and is a great choice if you’re “not so sure where this project is going to end up”.
To avoid any platform dependent installation confusion and steps, we will create that db container first and then adjust the web application. Use docker pull to download a postgres db image from docker hub. Open a terminal shell (or windows command line) and execute the following command:
docker pull postgresYou will see some console downloads and a final status message. This indicates that the image is available locally and that a container can be started like like I showed you in part one of this article series. The trailing postgres is the name of the image to be used. Ensure that this is entered in a terminal as one single line.
docker run --name webappdb -p 5432:5432 -e POSTGRES_PASSWORD=newton243 -d postgresEnsure that the container was started:
docker ps -a
As you can see in the screenshot above, port 5432 (standard port of postgres) on localhost is mapped to the port 5432 inside the container.
The most convenient way to work with databases is using a database administration tool and I’d recommend this, even if you’re an experienced shell-user. My favorite tool is DBeaver (Available here and comes with an installer https://dbeaver.io/) which is platform independent and allows me to work with several different databases in parallel. But you can also use pgadmin or else.
Connecting to PostgresSQL and creating data
Open DBeaver and click on the plug symbol below “File” to open the connection wizard (shown below) and search for “postgres”, select the resulting PostgreSQL and hit next…
Leave all settings as recommended and enter the password you have chosen when the docker container was started. This is newton243 in my case. Hit “Test Connection” to check if everything works as expected before saving the connection.
That looks good! Hit “finish” now and then expand the connection named “postgres” in the Database Navigator.
It is quite likely that DBeaver asks you to confirm the download of additional drivers. This is ok and required to technically connect to the database “behind the scenes”.
We won’t make things complicated now with the creation of a separate database schema etc. and just use the available database postgres and the existing schema public.
It’s time to download a copy of flightnet_airports.csv from the github repo of one of my other articles (https://github.com/martinhorvath/com.primebird.graphplayground, click on the file, then hit “raw” in the top bar and then right-click-save-as on the page with the csv content)
DBeaver comes with a handy csv import functionality that creates the table for us. Right-click on “public” from the image above and choose “Import Data”. CSV as source is pre-selected and you can hit next. Choose the just downloaded file and proceed with the default importer settings.
Click on the “Target” column to remove the “csv” from the to be used table-name. We leave the rest as-is as we don’t care of data-type-mappings or other tweaks. Confirm all other dialogs with default settings and hit “Start” finally. If everything went well, you’ll be presented a view similar to mine:
The advanced SQL user will spot that latitude and longitude shouldn’t be treated as varchar and that iata_code will ever consume that much space. Please execuse that dirtiness as SQL is really just hurdle to take in this walk-through.
You can preview the created content using the “Data-Tab”. Before we continue on the .net side, let’s prepare a SQL query to get all airports from this database as we need this later. Select the flightnet_airports entry in the database navigator and then choose SQL Editor -> New SQL Editor from the menu bar. In the opening window, enter the following SQL query, execute it by hitting ctrl+return or the little play button on the vertical icon-bar and ensure that a list of airports is returned like below:
You end up with the same result? Great! Then you’re ready for the .net part
Le’s recap what we achieved so far:
- Pulled a PostgreSQL image from Docker hub
- Created new PostgreSQL container and exposed port 5432
- Connected to the container instance and imported CSV data
Connecting .net5 web app with PostgreSQL
The overall goal is to read the airports just created from within the web app so that they can be rendered in a web page. Open your sources (download from my github repo if you missed part one of this journey: https://github.com/martinhorvath/com.primebird.net5webapp/releases/tag/v1)
Class for airport
First of all, we need a class for an entity called airport. I prefer to put classes for entities in a dedicated folder/package. Right-click somewhere below Startup.cs and create a folder named entities.Then right-click the new folder and select New File. Name it Airport.cs and open the file.
To pave the path for new developers a short explanation what happens now: Classes can be seen as blueprints of real objects and multiple objects can be created from the same class. Classes have properties with unique names and a pre-defined datatype. These properties are then populated with real values for each object.
The table flightnet_airports will become a class in our application. Every column will become a property in the class (Airport.cs). Every row of the table will become an object of type Airport in the .net application.
Add the following lines to the file to define the namespace (used to group classes together) and create a class in it. Public is used to not restrict access to this class.
Inside the class, several properties are created. They are marked as public to allow any other class to make use of it. The second argument is the data type and we use string (i.e. text) and double (i.e. floating point) for the latitude and longitude.
In a real application, we wouldn’t declare everything public and also make use of getters and setters. But hey, simplicity first to make beginner’s (please search the web for c# getters and setters) life easier.
namespace aspnetcore_webapp.Entities
{
public class Airport
{
public string iata_code;
public string airport;
public string city;
public string state;
public string country;
public double latitude;
public double longitude;
}
}Now we’ll do a little drift off the usual how-to guides. We’ll put all the database connection stuff aside and try to achieve something that makes already sense to the less experiences developers out there. Hey… everyone was at this stage in the past and I was asking myself many times: “What the heck am I doing here”. To avoid that, we’ll create a shortcut and use that class on the already existing welcome page of our web app. Open the Index.cshtml.cs:
The OnGet method is called before that page is loaded and we will create a first airport ourselves for testing purpose. To do that, we need to import the namespace with our model. This is done my pasting the following right after the last using … statement. The value must match the namespace declared in Airport.cs
using aspnetcore_webapp.Entities;Next, we want to make an instance of Airport available in the page model. This is done by pasting the following right above the declaration of the _logger variable:
public Airport myAirport;The last thing we want to do is assigning a real airport object to that variable. We do that inside the OnGet method so that it looks as follows:
public void OnGet() {
myAirport = new Airport();
myAirport.airport = "New York JFK";
myAirport.city = "New York";
myAirport.country = "USA";
myAirport.iata_code = "JFK";
myAirport.latitude = 0;
myAirport.longitude = 0;
myAirport.state = "New York";
}We can directly set the values of these variable because we declared them public in the Airport class. If we would have used private, we wouldn’t see or being able to access this variable. We’re done here and your file should look as follows:
The last missing piece before we can inspect the result is the usage of our new model variable in the view/rendering. The view that relates to that Index.cshtml.cs is named similar, just without the .cs extension. So open now Index.cshtml in the editor and add the following code at the end of the file:
<div class="text-center">
<h1 class="display-4">@Model.myAirport.airport</h1>
<p>This airport is in @Model.myAirport.country and identified by iata code @Model.myAirport.iata_code</p>
</div>As you can see, the variable myAirport that we defined in the IndexModel class within Index.cshtml.cs is available as a property of the Model. Variables are always accessed by using the @ symbol, that introduces c# code. The variable Model is created automatically. Your final file should look as follows:
Now give it a try and start debugging the webapp (Run -> start debugging) and navigate to https://localhost:5001 and cherish the result :-)
Connecting to the database and reading airports
That was easy, right? All we need to to now is to remove our hard-coded airport single airport and get a list of airports using the database table instead.
But step by step… let’s summarize what we will do first:
- Establish a connection to the database when the page is prepared
- Read all airports using a sql-query
- Pass a list of airports into the IndexPageModel
- Render such a paragraph like above for every returned airport
The first thing we need to add is an appropriate driver for connecting to Postgres. This is called Npgsql and available as a nuget package. Nuget is a tool that allows us to download and use additional .net packages in our application. There are other ways as well to get additional packages, but this is the most convenient way I’d say.
Open a terminal inside VisualStudioCode (Menu bar Terminal -> New Terminal) and ensure you’re in the correct folder (root of your webapp where Startup.cs is located. You can do ls on linux/Mac OS or dir on Windows systems.
Now enter the following command so that the Npgsql package is downloaded and registered in our application (like documented here: https://www.nuget.org/packages/Npgsql/).
dotnet add package Npgsql
Behind the scenes, this package is cached on your local system and a package reference is added to the csproj file of your solution. No black magic at all.
Switch back to the file Index.cshtml.cs to remove the myAirport variable and to create a new variable of type IList where we will put all our airports in:
public Airport myAirport; // <--- remove this
public IList<Airport> airports; // <-- add this insteadWe did not use collections in that file so far and this is why a warning will be shown. Either use the yellow bulb to import the recommended namespace or add this below the other using… at the top of the file:
using System.Collections.Generic;The next thing we want to do is writing a method to capsule all the database code to read the airports. I don’t want to confuse here with different patterns, methodologies etc. like singletons, DataAccessObjects, ObjectRelationalMappers. What we are doing here is ideal for learning and to understand the concept, without navigating through many files, debugging different application layers and tracing annoying little errors.
Right below the closing curly bracket of OnGet, we add a new private class which we name GetAirports and which returns a list of Airport. Let me paste the code first (for the anxious ones) and then I’ll explain it to you:
private IList<Airport> GetAirports()
{
IList<Airport> airports = new List<Airport>();
var connString = "Host=localhost;Username=postgres;Password=newton243;Database=postgres";using (var conn = new NpgsqlConnection(connString))
{
conn.Open();using (var cmd = new NpgsqlCommand("SELECT * FROM flightnet_airports", conn))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Airport myAirport = new Airport();
myAirport.airport = (string)reader["airport"];
myAirport.city = (string)reader["city"];
myAirport.country = (string)reader["country"];
myAirport.iata_code = (string)reader["iata_code"];
myAirport.latitude = 0;
myAirport.longitude = 0;
myAirport.state = (string)reader["state"];
airports.Add(myAirport);
}
}
}
}
return airports;
}
From top down, the following happens: Line 1 declares a new method with the return type IList<Airport> which is a list that can only contain objects of type Airport. It has the name GetAirports and is called without arguments. This is why the parentheses are empty. It’s also declared as private which makes it accessible only to functions inside of the class IndexModel declared in the very same file.
Then we declare a new variable inside this method named airports as an empty list of Airports.
To connect to a database, a connection string is required that holds information like database host, username etc. It is declared using var, which tells the compiler to detect the type of the variable itself. We could also write string connString=….
var connString = "Host=localhost;Username=postgres;Password=newton243;Database=postgres";Then comes a using-block. This is handy when instances of objects are used that we should recycle after usage. I.e. we don’t want to close the database connection once the job is done. This can only be used if the object used within the parentheses supports this.
using (var conn = new NpgsqlConnection(connString))Here we are also using the recently added nuget package Npgsql which makes the NpgsqlConnection available and handles the database connection technicals. It just needs a connectionstring with correct information. For the usage of this connection, we also need to explicitly open a connection which is done right after. This can be compared to what we previously did with DBeaver when connecting to our db inside docker.
With the connection opened, we are able to create commands with SQL-queries like we did in DBeaver:
using (var cmd = new NpgsqlCommand("SELECT * FROM flightnet_airports", conn))It’s again capsuled into a using statement to recycle stuff once done. The NpgsqlCommand requires the SQL-query to be executed and a NpgsqlConnection to execute on. We’re using the conn we created before.
We’re nearly there… when executing this command, we can utilize the NpgsqlDataReader class which allows us to process the results row by row. Therefore, we create it (again using a using-statement) as follows:
using (var reader = cmd.ExecuteReader())That reader is now open and we are good to grab row-by-row as long as there are rows left in the result set. With run in this iteration, the pointer is moved forward until the very last row is reached. It will return false in this last case and cause the while-loop to exit.
while (reader.Read())Inside this loop, we create one instance of Airport for every row. The code looks similar what what we did before by hand and I marked the important parts for you. We want to have a fresh Airport for every row. This is done using the first line below.
Instead of setting values by hand, we make use of the reader variable which let’s us specify the column-name, quoted inside the brackets, to retrieve the column value for the current row. In our case, we know from our previous SQL-work, that all columns are of type varchar. The reader actually returns generic object-values and this is why we need to cast them to string. This means that we’re explicitly telling the compiler: “Even if you retrieve a value of type object, I’m sure this is a string”.
We were super dirty and did also store the latitude and longitude as strings in the database. A conversion to a number using Double.Parse would be possible, but then we’d have to deal with empty values, blanks, decimal separators etc. As we are not using this information right now, we are again working dirty and set the value to 0 by hand.
The last thing inside the loop is to add this newly created airport to the list we defined at the beginning of this method. And of course, we need to return the list at the very end of the method.
Airport myAirport = new Airport();
myAirport.airport = (string)reader["airport"];
myAirport.city = (string)reader["city"];
myAirport.country = (string)reader["country"];
myAirport.iata_code = (string)reader["iata_code"];
myAirport.latitude = 0;
myAirport.longitude = 0;
myAirport.state = (string)reader["state"];
airports.Add(myAirport);I know you’re eager to test this, but hold on for another minute or two. A few things are left and then we’re done. We need to remove all the hard-coded stuff from the OnGet method and fill the airports variable using our shiny new function instead:
public void OnGet()
{
this.airports = GetAirports();
}Your file should now look as below. A complete version is also found on my github repo (https://github.com/martinhorvath/com.primebird.net5webapp/blob/feature/read-from-db/Pages/Index.cshtml.cs)
Now move over to the Index.cshtml one last time. There we need to tweak the code so that we create one block for every airport in the airports variable and we do this as follows:
@foreach (Airport airport in Model.airports)
{
<hr />
<div class="text-center">
<h1 class="display-5">@airport.airport</h1>
<p>This airport is in @airport.country
and identified by iata code @airport.iata_code
</p>
</div>
}Capsule the whole div block we created initially in a foreach loop which is preceeded by the @-symbol that introduces C# code inside the cshtml. In a foreach-loop, we can define the type of object as the first argument inside the parentheses. The second argument is the name of that variable. And finally, this airport can be found in Model.airports. You remember that all variables inside the IndexModel are available in the cshtml, right?
For visual reasons, I added a <hr /> before the text block to create a horizontal ruler. The variables we used before, @Model.airport are modified: Model is not required anymore as we reference the airport-variable defined inside the foreach.
Now do also add this line to the top of the file (NOT above “page”) so that the Airport type is also found in this file:
@using EntitiesThat’s it, we’re ready for a test if your file looks as below. A complete version is also found on my github repo (https://github.com/martinhorvath/com.primebird.net5webapp/blob/feature/read-from-db/Pages/Index.cshtml)
Run the debugger and enjoy the beauty of your airport list, read live from your database.
Make this app-changes compatible with containerized DB
Even if this app now runs locally, that doesn’t mean that this is the case for a containerized setup. Did you already spot the critical parts?
var connString = "Host=localhost;Username=postgres;Password=newton243;Database=postgres";When this is executed in the container, it will run into a connection exception as the localhost (i.e. “this container”) won’t host a database. Instead we need to connect to the correct container and the exposed port.
Let’s use an environment variable for that which we can define when later starting the container. This can be achieved by replacing the connectionstring with the following:
var connString = Environment.GetEnvironmentVariable("primebird_connectionstring");To test this also locally, where you do not have this variable set, you can edit the launch.json file which is the configuration of your debug/run mode. You should be able to spot there a property called env which already includes a variable called ASPNETCORE_ENVIRONMENT. We’ll add a custom one with the connectionstring like shown in the screenshot below:
Docker networking
Another thing I need to introduce you to is docker networking. Docker manages it’s own networks and assigns IP-addresses to the connected containers. The same is happening when you plug an Ethernet cable into your computer or when you connect to a WiFi. The networks created by docker can be checked with the following:
docker network ls
Containers can be added to one or more networks during startup and even when they are up. To check the details of a network, use the following command (where bridge stands for the name of the network):
docker network inspect bridge
There you can see the IP-range, attached containers (none in this case) etc. Another important fact to know is that docker containers can reach each other by container-name if they are attached to the same network. This means that we can use the db-container name in the connection-string of our web-app!
To keep things separated, we’ll do the following:
- Create a dedicated network in docker for our web application and it’s database
docker network create -d bridge webapp-network- Attach db-container
docker network connect webapp-network webappdbThe last parameter of the docker network connect command is the name of the container. webappdb was created in this article. Check the names of your containers using docker ps -a!
If you now repeat the previous docker network ls and docker network inspect commands, you should see a new network and the at least one container (webappdb) attached (if the containers are running!). If they are stopped, start them first with docker start <containerName>! In my case, the web-container is attached as well, but you don’t need that right now. But it also doesn’t hurt if you want to practice.
Everything is in place and we are ready to update the aspnet-webapp container with another build like we did in part one. CD into the root directory of your app (where the Dockerfile is located) or use the terminal inside VisualStudioCode to execute the publish command so that the latest changes are compiled:
dotnet publish --configuration DebugThen build a new docker container:
docker build -t aspnetcore-webapp .And verify the image was updated. My one says 4 minutes ago in the picture below.
docker image ls
Now we create another container aspnetwebapp-ct2 (or any name you like) with a slightly edited command to provide the db connectionstring as environment variable. To have both web containers running in parallel, I decided to map the local port 5009 to the container port. I renamed the container to aspnetwebapp-ct2 and as host, I used the name of the db-container. The whole connectionstring is capsuled in double-quotes to avoid command-errors.
docker run -d -p 5009:5001 --name aspnetwebapp-ct2 -e ASPNETCORE_Kestrel__Certificates__Default__Password=<passwordForTheCertificate> -e ASPNETCORE_Kestrel__Certificates__Default__Path=/opt/webapp/devcert.pfx -e primebird_connectionstring="Host=webappdb;Username=postgres;Password=newton243;Database=postgres" --mount type=bind,source=<dockersharepath>,target=/opt/webapp aspnetcore-webappEnsure the container started successfully with docker ps -a and if it is so, you just need to connect it to the previously connected network. Want to see an error? Browse your website before connecting it to the network ;) It’s accessible at https://localhost:5009
docker network connect webapp-network aspnetwebapp-ct2Now browse the website at https://localhost:5009 and enjoy the beauty of your airport list again. Which is now pulled from the other container.
What you have achieved…
You read that far! Congratulations, you did great and learned
- how to deploy a postgres database in a docker container
- how to import data into a sql database
- how to read data from sql inside a .net app
- how to create docker networks and connect containers
- how to use environment variables provided by docker in a .net web app
But you should also be aware that this is just the very basic implementation and now open your mind as there is so much to explore. Its worth looking at EntityFramework or NHibernate to make use of a database abstraction layer. You can look into security aspects such as SQL-injection, container isolation,… just to provide a few ideas.
Last but not least… keep an eye on my profile if you liked this article. I’ll continue this journey :-)
Troubleshooting
- Looking for the final code? It’s here: https://github.com/martinhorvath/com.primebird.net5webapp/tree/feature/read-from-db-dockerized
- Always double check the container names and ensure they are running.
- Ensure that there’s no line break in most commands, even if it looks like in the view here
- Permission denied when running docker … commands? You probably need to use sudo if you’re on linux
- Connection issues with the database? Try to turn off the firewall to avoid blocked ports.
