This article show how an ASP.NET Core application with a PostgreSQL database can be setup together using docker as the deployment containers for both web and database parts of the application. docker-compose is used to connect the 2 containers and the application is build using Visual Studio 2017.

Code: https://github.com/damienbod/AspNetCorePostgreSQLDocker

Setting up the PostgreSQL docker container from the command line

2017.02.03: Updated to VS2017 RC3 msbuild3

The PostgreSQL docker image can be started or setup from the command line simple by defining the required environment parameters, and the port which can be used to connect with PostgreSQL. A named volume called pgdata is also defined in the following command. The container is called postgres-server.

$ docker run -d -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=damienbod 
 --name postgres-server -p 5432:5432 -v pgdata:/var/lib/postgresql/data 
 --restart=always postgres

You can check all your local volumes with the following docker command:

$ docker volume ls

The docker containers can be viewed by running the docker ps -a:

$ docker ps -a

Then you can check the docker container for the postgres-server by using the logs command and the id of the container. Only the first few characters from the container id is required for docker to find the container.

$ docker logs <docker_id>

If you would like to view the docker container configuration and its properties, the inspect command can be used:

$ docker inspect <docker_id>

When developing docker applications, you will regularly need to clean up the images, containers and volumes. Here’s some quick commands which are used regularly.

If you need to find the dangling volumes:

$ docker volume ls -qf dangling=true

A volume can be removed using the volume id:

$ docker volume rm <volume id>

Clean up container and volume (dangerous as you might not want to remove the data):

$ docker rm -fv <docker id>

Configure the database using pgAdmin

Open pgAdmin to configure a new user in PostgreSQL, which will be used for the application.

Right click your user and click properties to set the password

Now a PostgreSQL database using docker is ready to be used. This is not the only way to do this, a better way would be to use a Dockerfile and and docker-compose.

Creating the PostgreSQL docker image using a Dockerfile

Usually you do not want to create the application from hand. You can do everything described above using a Dockerfile and docker-compose. The PostgresSQL docker image for this project is created using a Dockerfile and docker-compose. The Dockerfile uses the latest offical postgres docker image and adds the required database to the docker-entrypoint-initdb.d folder inside the container. When the PostgreSQL inits, it executes these scripts.

FROM postgres:latest
EXPOSE 5432
COPY dbscripts/10-init.sql /docker-entrypoint-initdb.d/10-init.sql
COPY dbscripts/20-damienbod.sql /docker-entrypoint-initdb.d/20-database.sql

The docker-compose defines the image, ports and a named volume for this image. The POSTGRES_PASSWORD is required.

version: '2'

services:
  damienbodpostgres:
     image: damienbodpostgres
     restart: always
     build:
       context: .
       dockerfile: Dockerfile
     ports:
       - 5432:5432
     environment:
         POSTGRES_PASSWORD: damienbod
     volumes:
       - pgdata:/var/lib/postgresql/data

volumes:
  pgdata:

Now switch to the directory where the docker-compose file is and build.

$ docker-compose build

If you want to deploy, you could create a new docker tag on the postgres container. Use your docker hub name if you have.

$ docker ps -a
$ docker tag damienbodpostgres damienbod/postgres-server

You can check your images and should see it in your list.

$ docker images

Creating the ASP.NET Core application

An ASP.NET Core application was created in VS2017. The EF Core and the PostgreSQL nuget packages were added as required. The Docker support was also added using the Visual Studio tooling.

<Project ToolsVersion="15.0" Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp1.1</TargetFramework>
    <PreserveCompilationContext>true</PreserveCompilationContext>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Routing" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Server.IISIntegration" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Server.Kestrel" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="1.1.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.0.0-msbuild3-final" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="1.1.0" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL.Design" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.EnvironmentVariables" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.FileExtensions" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.0" />
    <PackageReference Include="Microsoft.Extensions.Options.ConfigurationExtensions" Version="1.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.1.0" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0-msbuild3-final" />
    <DotNetCliToolReference Include="Microsoft.Extensions.SecretManager.Tools" Version="1.0.0-msbuild3-final" />
    <DotNetCliToolReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="1.0.0-msbuild3-final" />
  </ItemGroup>
</Project>

The EF Core context is setup to access the 2 tables defined in PostgreSQL.

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace AspNetCorePostgreSQLDocker
{
    // >dotnet ef migration add testMigration in AspNet5MultipleProject
    public class DomainModelPostgreSqlContext : DbContext
    {
        public DomainModelPostgreSqlContext(DbContextOptions<DomainModelPostgreSqlContext> options) :base(options)
        {
        }
        
        public DbSet<DataEventRecord> DataEventRecords { get; set; }

        public DbSet<SourceInfo> SourceInfos { get; set; }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<DataEventRecord>().HasKey(m => m.DataEventRecordId);
            builder.Entity<SourceInfo>().HasKey(m => m.SourceInfoId);

            // shadow properties
            builder.Entity<DataEventRecord>().Property<DateTime>("UpdatedTimestamp");
            builder.Entity<SourceInfo>().Property<DateTime>("UpdatedTimestamp");

            base.OnModelCreating(builder);
        }

        public override int SaveChanges()
        {
            ChangeTracker.DetectChanges();

            updateUpdatedProperty<SourceInfo>();
            updateUpdatedProperty<DataEventRecord>();

            return base.SaveChanges();
        }

        private void updateUpdatedProperty<T>() where T : class
        {
            var modifiedSourceInfo =
                ChangeTracker.Entries<T>()
                    .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified);

            foreach (var entry in modifiedSourceInfo)
            {
                entry.Property("UpdatedTimestamp").CurrentValue = DateTime.UtcNow;
            }
        }
    }
}

The used database was created using the dockerfile scripts executed in the docker container init. This could also be done with EF Core migrations.

$ dotnet ef migrations add postgres-scripts

$ dotnet ef database update

The connection string used in the application must use the network name defined for the database in the docker-compose file. When debugging locally using IIS without docker, you would have so supply a way of switching the connection string hosts. The host postgresserver is defined in this demo, and so used in the connection string.

 "DataAccessPostgreSqlProvider": "User ID=damienbod;Password=damienbod;Host=postgresserver;Port=5432;Database=damienbod;Pooling=true;"

Now the application can be built. You need to check that it can be published to the release bin folder, which is used by the docker-compose.

Setup the docker-compose

The docker-compose for the application defines the web tier, database server and the network settings for docker. The postgresserver service is built using the damienbodpostgres image. It exposes the PostgreSQL standard post like we have defined before. The aspnetcorepostgresqldocker web application runs on post 5001 and depends on postgresserver. This is the ASP.NET Core application in Visual studio 2017.

version: '2'

services:
  postgresserver:
     image: damienbodpostgres
     restart: always
     ports:
       - 5432:5432
     environment:
         POSTGRES_PASSWORD: damienbod
     volumes:
       - pgdata:/var/lib/postgresql/data
     networks:
       - mynetwork

  aspnetcorepostgresqldocker:
     image: aspnetcorepostgresqldocker
     ports:
       - 5001:80
     build:
       context: ./src/AspNetCorePostgreSQLDocker
       dockerfile: Dockerfile
     links:
       - postgresserver
     depends_on:
       - "postgresserver"
     networks:
       - mynetwork

volumes:
  pgdata:

networks:
  mynetwork:
     driver: bridge

Now the application can be started, deployed or tested. The following command will start the application in detached mode.

$ docker-compose -d up

Once the application is started, you can test it using:

http://localhost:5001/index.html

You can add some data using Postman

POST http://localhost:5001/api/dataeventrecords
{
  "DataEventRecordId":3,
  "Name":"Funny data",
  "Description":"yes",
  "Timestamp":"2015-12-27T08:31:35Z",
   "SourceInfo":
  { 
    "SourceInfoId":0,
    "Name":"Beauty",
    "Description":"second Source",
    "Timestamp":"2015-12-23T08:31:35+01:00",
    "DataEventRecords":[]
  },
 "SourceInfoId":0 
}

And the data can be viewed using

http://localhost:5001/api/dataeventrecords

Or you can view the data using pgAdmin

Links

https://hub.docker.com/_/postgres/

https://www.andreagrandi.it/2015/02/21/how-to-create-a-docker-image-for-postgresql-and-persist-data/

https://docs.docker.com/engine/examples/postgresql_service/

http://stackoverflow.com/questions/25540711/docker-postgres-pgadmin-local-connection

http://www.postgresql.org

http://www.pgadmin.org/

https://github.com/npgsql/npgsql

https://docs.docker.com/engine/tutorials/dockervolumes/