Entity Framework Core & PostgreSQL – base configuration

Hey Guys!

Today I want to present a different way of implementing the persistence layer in .Net Core & Entity Framework Core combination – Npgsql PostgreSQL.

Why PostgreSQL?

Today we have a lot kind of databases, from SQL to NoSQL. The standard way of implementing the persistence layer in the .Net world is the Microsoft SQL Server. This combination is known for all .net devs, but EF Core can handle more than 20 different engines. I want to focus on one – PostgreSQL. My experience is based on realized some projects (https://demo.e-lab.app & https://panel.cadar.it/home) So let’s go!.

Advantages

• Open Source license – You don’t need to pay for a license, like the standard way. Cost optimizations are always cool :-)!
• Stability | Support | Updates – npgsql is a very stable library with a great GitHub community.
• Easy to use – the start of using Npgsql Entity Framework isn’t complicated. You will see it later :-).
• Hybrid Mode – Postgres has very good support for NoSQL JSON (jsonB) columns I will describe it in the next articles.
• Performance – You don’t need to wait a couple of mins for fetching data ;-).
• Cloud infrastructures – If you don’t want to create Your own infrastructure, You can use exists clouds like Azure | Google | Ovh | Amazon, etc.
• External database plugins like PostGIS.
• Tools for data visualization – PgAdmin.

Disadvantages

• Differences in SQL dialect – when You use Microsoft SQL server from a child, there will be a small differences in SQL query language [but don’t worry, is very small].
• Case Sensitive – yees, PostgreSQL by the standard is Case Sensitive, not like Microsoft SQL Server. So when You typing where clause, you remember ‘Name’ != ‘name’, or You must use ILIKE | LOWER functions.
• More time to create your own infrastructure – PostgreSQL needs to be configured by CLI, here we don’t have a lot of GUI tools ;-), but this case has matter when You create your own PostgreSQL instance (like me :D).
• Pool connections – You need to have an eye on it, sometimes I had problems with that :-(.

How to start?

Implementation will be present on a simple project with two entities User & Posts, one User can have many Posts.

Firstly, You must add Npgsql.EntityFrameworkCore.PostgreSQL | Microsoft.Extensions.Configuration.FileExtensions | Microsoft.Extensions.Configuration.Json nugets package to Your infrastructure solution & Npgsql.EntityFrameworkCore.PostgreSQL.Design | Microsoft.EntityFrameworkCore.Design to startup | API project.

Then we must create Models:

    public class Post : IIdProvider,
        ICreatedAtProvider
    {
        public int Id { get; set; }
        public DateTime CreateAt { get; set; }
        public int UserId { get; set; }
        
        public User User { get; set; }
    }

    public class User : IIdProvider
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        
        public ICollection<Post> Posts { get; set; } = new HashSet<Post>();
    }

With configurations:

    public class UserEntityTypeConfiguration : IEntityTypeConfiguration<User>
    {
        public void Configure(EntityTypeBuilder<User> builder)
        {
            builder.HasKey(x => x.Id);
            builder.HasMany(x => x.Posts)
                .WithOne(x => x.User)
                .HasForeignKey(x => x.UserId);
        }
    }

    public class PostEntityTypeConfiguration : IEntityTypeConfiguration<Post>
    {
        public void Configure(EntityTypeBuilder<Post> builder)
        {
            builder.HasKey(x => x.Id);
        }
    }

And the most important part – context:

    public class BlogContext : DbContext
    {
        private readonly SqlSettings _settings;
        
        public DbSet<Post> Posts { get; set; }
        public DbSet<User> Users { get; set; }

        public BlogContext(SqlSettings settings)
        {
            _settings = settings;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql(_settings.ConnectionString);
            
            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfigurationsFromAssembly(typeof(BlogContext).Assembly);
            
            base.OnModelCreating(modelBuilder);
        }
    }

Lines:

optionsBuilder.UseNpgsql(_settings.ConnectionString) – this is very imporant line, here we set information about our decision for using PostgreSQL Entity framework with connection string.

modelBuilder.ApplyConfigurationsFromAssembly(typeof(BlogContext).Assembly) – this line is responsible for loading of all our IEntityTypeConfiguration.

All names of properties with type DbSet have matters, by default they are equal to our future table names.

Before creating & apply our migrations, we must create a way of creating DbContext to EF CLI, to do this we must create an implementation of the interface IDesignTimeDbContextFactory<BlogContext>

    public class BlogDesignTimeDbContextFactory : IDesignTimeDbContextFactory<BlogContext>
    {
        public BlogContext CreateDbContext(string[] args)
        {
            var configuration = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.Development.json")
                .Build();

            var settings = new SqlSettings();
            configuration.GetSection("Sql").Bind(settings);
            
            return new BlogContext(settings);
        }
    }

And fill appsettings.Development.json 🙂

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "Sql": {
    "connectionString": "Host=1.1.1.1;Database=the_worst_blog;Username=postgres;Password=***"
  }
}

The ConnectionString is built by:
– Host – IP or domain with the database,
– Database – the name of the database,
– Username – Login,
– Password – You knowww :-),
– Port – if You have access by special port, You can specify it by special parameter Port.

Now, we must only generate SQL migrations by EF CLI, to do this we must invoke command like this:

 dotnet ef migrations add initialize -s ../TheWorst.Blog.PostgreSQL/TheWorst.Blog.PostgreSQL.csproj -o Persistence/Migrations

where:
– s is the location of the startup | appsettings.json project (if you keep all codes in one project is no required ;-)).
– o out catalog, for clear solution good idea is always to keep it at some subfolder :-).

Finally By PgAdmin we must create our db manually:

And invoke last CLI command:

dotnet ef database update -s ../TheWorst.Blog.PostgreSQL/TheWorst.Blog.PostgreSQL.csproj 

For know it’s all, Feel free to ask questions | let knows which segment of PostgreSQL you want to know.

All codes You can find at My github:

https://github.com/Rogaliusz/TheWorst.Blog.PostgreSQL

Cheers !