D3Signal: npgsql and dotnet

May 31, 2022

Intro

Recently I created a small application, that contain most of technology stack, that I’m using at work and/or learned recently: dotnet, react, mobx, d3js & signalR. So I decided to make small series of posts, in which I add some tips for each of these frameworks. I’ll update this list as soon as posts would be available.

• Npgsql + dotnet + docker

Docker

Postgress is one of the famous db in the world of software development. Although my knowledge does not allow me do dig deep in differences between databases, I prefer to use postgress for my dotnet projects, because it require less resources than MsSQL and allow db migrations unlike SQLite. Simpliest way to install it would be, of course, official installer on local machine, but I prefer to use docker. This way it’s easier to support multiple versions, add plugins and throw away data, that is not needed. To run postgreSQL from docker:

docker run 
-e POSTGRES_USER=user 
-e POSTGRES_PASSWORD=pass 
-e POSTGRES_DB=d3signal_db 
-p 5432:5432 
-v db:/var/lib/postgresql/data 
-d 
postgres:14

-e flags used to set environment, especially user name, pass and db name, that would be used. -p is mapping container port to local port. In this case it’s the same, but if I run local Postgres in addition to one in docker, ports can be remapped to avoid confusion. The most interesting param here is -v, which creates docker volume db (if it’s not existed) and mounts it into container. When container would be stopped, this volume persisted and data can be saved this way between reboots.

Dotnet

There are many ways to connect dotnet to sql (as well as there are many types of dotnet application). I usually use EntityFramework and include same steps:

• add “npgsql” and “Npgsql.EntityFrameworkCore.PostgreSQL” packages.
• Use app context (inherited from EFCore DbContext) and register it by interface. In my example, I have only one table, therefore one entity type Circle. But still, I added interface

public interface ICirclesContext
{
    DbSet<Circle> Circles { get; }
    Task Save();
}

Name here is better to be pointed to application name, like MyAppContext. In this case the whole application is about circles 🙂 Implementation:

public class CirclesContext: DbContext, ICirclesContext
{
    public CirclesContext(DbContextOptions options) : base(options) { }

    public DbSet<Circle> Circles => Set<Circle>();

    public async Task Save() => await SaveChangesAsync();
}

• In initialisation (usually in Program.cs, before building WebApplication) retrieve connection string and register dependency:

var connectionString = builder.Configuration.GetConnectionString("db");
builder.Services.AddDbContext<CirclesContext>(ops => 
    ops.UseNpgsql(connectionString));
builder.Services.AddScoped<ICirclesContext>(p => p.GetService<CirclesContext>()!);

Of course, config file should contain connection string (although, in production it usually goes from environment variable where it appear by CI/CD pipeline):

{
  "ConnectionStrings": {
    "db": "Host=localhost;Username=user;Password=pass;Database=d3signal_db"
  }
}

• This is optional, but I prefer that database would be updated each time when I start application, which can be done this way:

app.Services
    .CreateScope()
    .ServiceProvider
    .GetService<CirclesContext>()?
    .Database
    .Migrate();

More about migrations and how to create them can be found in microsoft documentation

Without any other details about .net application type, these are simplest steps to add postgreSQL support to our application.