Categories
.NET Core

Connect .NET Core With PostgreSQL On A Docker Container

In this post, we will learn to quickly setup a .NET core app to connect with PostgreSQL database on a docker container environment. We will setup PostgreSQL with Docker Compose to have persistent data volume. Then we will connect to that database with the help of Entity Framework on a .NET core web API.

Setup PostgreSQL With Docker Compose

We want our data to be persistent even after the PostgreSQL docker container is closed and re-started. For this we want to use use local volumes during the docker compose.

Start by creating a folder named “docker” inside your project root. Inside this folder, we will add a file called “docker-compose.yml“.
Our docker-compose.yml file will look something like this (for windows platform):

version: '3'

services:
  db:
    image: postgres:11.2
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data
      - pgconf:/etc/postgresql
      - pglog:/var/log/postgresql
    environment:
      - POSTGRES_DB=your_db
      - POSTGRES_USER=your_user
      - POSTGRES_PASSWORD=your_password

volumes:
  pgdata:
    driver: local
  pgconf:
    driver: local
  pglog:
    driver: local

Now to run or stop the container, we will have two separate script files: start_db.sh and stop_db.sh.

Create another folder and call it “scripts“.

First create start_db.sh.

docker-compose --project-name=my_project --file=docker/docker-compose.yml up -d

Then create stop_db.sh.

docker-compose --project-name=true_com --file=docker/docker-compose.yml down

Now we are ready to create our PostgreSQL docker container.

Running PostgreSQL Container

Open docker terminal.

Docker Terminal
Docker Terminal

From your project root folder, use the command below to create an image and run the container for PostgreSQL.

scripts/start_db.sh

Verify if the container was created and is running by using this command:

docker ps

Connecting To PostgreSQL Docker Container With pgAdmin

To check to see if you can access the PostgreSQL container, you can use the pgAdmin client tool. Use the correct values for host, port, username and password that you set earlier for making the connection.

For host address, you can use “localhost“. If you are on Windows and are using docker with VirtualBox you might need to use the actual IP for your docker machine.

docker-machine ip

Connecting To PostgreSQL Docker Container With .NET Core App

Now that we have a working PostgreSQL running on a docker container, we move onto the second part. We will connect to this container with .NET core app. We will use Entity Framework code first approach to create a table on the PostgreSQL server.

In your .NET app, create an entity class called Login.cs

[Table ("Logins")]
public class Login {        
        public string UserName { get; set; }
        public string Password { get; set; }
    }

This is just a basic class for now. Now we need to create a database context object.

Create a class called “MyDbContext.cs” which will look like this:

public class TrueComDbContext : DbContext {
        public TrueComDbContext (DbContextOptions<TrueComDbContext> options) : base (options) { }

        public DbSet<Login> Logins { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
            var server = "192.168.99.100";
            var port = "5432";
            var name = "my_db";
            var user = "admin";
            var password = "password";

            optionsBuilder.UseNpgsql($"Host={server};Port={port};Database={name};Username={user};Password={password}");
        }
            
    }

Here, we created a sample database context for table Logins. The database connection string is hard-coded for demo.

Running Entity Framework Migrations On PostgreSQL

We will use the Npgsql EF Core provider library to make use of our database context. Add a reference to the library in your app’s .csproj file:

<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="2.1.2" />

Finally adjust the ConfigureServices method of the Startup.cs file so that it looks like this:

public void ConfigureServices(IServiceCollection services)
        {            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
            services.AddEntityFrameworkNpgsql()
               .AddDbContext<MyDbContext>()
               .BuildServiceProvider();
        }

Now you can run migrations and update database to create new tables.

dotnet ef migrations add InitialCreate
dotnet ef database update

You can learn more about running migrations on .NET core app with Entity Framework from the links below:

Entity Framework Code First Approach With .NET Core

Adding Migrations On Entity Framework .NET Core

Conclusion

In this post we successfully created a PostgreSQL server on docker container. We set it up so that the data volumes stayed persistently. Then we connected to this container with a .NET core app and ran Entity Framework code first migrations on it.

Leave a Reply

Your email address will not be published. Required fields are marked *