Buckle in because this will be a long one!

You can see parts 2 and 3 here and here.

Introduction

While I've been happy with my file based approach to blogging, as an excercise I decided to move to using Postgres for storing blog posts and comments. In this post I'll show how that's done along with a few tips and tricks I've picked up along the way.

Setting up the Database

Postgres is a free database with some great features. I'm a long time SQL Server user (I even ran performance workshops at Microsoft a few years back) but Postgres is a great alternative. It's free, open source, and has a great community; and PGAdmin, to tool for administering it is head and shoulders above SQL Server Management Studio.

To get started, you'll need to install Postgres and PGAdmin. You can set it up either as a windows service or using Docker as I presented in a previous post on Docker.

EF Core

In this post I'll be using Code First in EF Core, in this way you can manage your database entirely through code. You can of course set up the database manually and use EF Core to scaffold the models. Or of course use Dapper or another tool and write your SQL by hand (or with a MicroORM approach).

The first thing you'll need to do is install the EF Core NuGet packages. Here I use:

• Microsoft.EntityFrameworkCore - The core EF package
• Microsoft.EntityFrameworkCore.Design - This is needed for the EF Core tools to work
• Npgsql.EntityFrameworkCore.PostgreSQL - The Postgres provider for EF Core

You can install these packages using the NuGet package manager or the dotnet CLI.

Next we need to think about the models for the Database objects; these are distinct from ViewModels which are used to pass data to the views. I'll be using a simple model for the blog posts and comments.

public class BlogPost
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    
    public string Title { get; set; }
    public string Slug { get; set; }
    public string HtmlContent { get; set; }
    public string PlainTextContent { get; set; }
    public string ContentHash { get; set; }

    
    public int WordCount { get; set; }
    
    public int LanguageId { get; set; }
    public Language Language { get; set; }
    public ICollection<Comments> Comments { get; set; }
    public ICollection<Category> Categories { get; set; }
    
    public DateTimeOffset PublishedDate { get; set; }
    
}

Note that I've decorated these with a couple of attributes

 [Key]
 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

These let EF Core know that the Id field is the primary key and that it should be generated by the database.

I also have Category

public class Category
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<BlogPost> BlogPosts { get; set; }
}

Languages

public class Language
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<BlogPost> BlogPosts { get; set; }
}

And comments

public class Comments
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Comment { get; set; }
    public string Slug { get; set; }
    public int BlogPostId { get; set; }
    public BlogPost BlogPost { get; set; } 
}

You'll see I refer to the BlogPost in Comments, and ICollections of Comments and Categories in B;ogPost. These are navigation properties and is how EF Core knows how to join the tables together.

Setting up the DbContext

In the DbContext class you'll need to define the tables and relationships. Here's mine:

public class MostlylucidDbContext : DbContext
{
    public MostlylucidDbContext(DbContextOptions<MostlylucidDbContext> contextOptions) : base(contextOptions)
    {
    }

    public DbSet<Comments> Comments { get; set; }
    public DbSet<BlogPost> BlogPosts { get; set; }
    public DbSet<Category> Categories { get; set; }

    public DbSet<Language> Languages { get; set; }


    protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
    {
        configurationBuilder
            .Properties<DateTimeOffset>()
            .HaveConversion<DateTimeOffsetConverter>();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BlogPost>(entity =>
        {
            entity.HasIndex(x => new { x.Slug, x.LanguageId });
            entity.HasIndex(x => x.ContentHash).IsUnique();
            entity.HasIndex(x => x.PublishedDate);

            entity.HasMany(b => b.Comments)
                .WithOne(c => c.BlogPost)
                .HasForeignKey(c => c.BlogPostId);

            entity.HasOne(b => b.Language)
                .WithMany(l => l.BlogPosts).HasForeignKey(x => x.LanguageId);

            entity.HasMany(b => b.Categories)
                .WithMany(c => c.BlogPosts)
                .UsingEntity<Dictionary<string, object>>(
                    "BlogPostCategory",
                    c => c.HasOne<Category>().WithMany().HasForeignKey("CategoryId"),
                    b => b.HasOne<BlogPost>().WithMany().HasForeignKey("BlogPostId")
                );
        });

        modelBuilder.Entity<Language>(entity =>
        {
            entity.HasMany(l => l.BlogPosts)
                .WithOne(b => b.Language);
        });

        modelBuilder.Entity<Category>(entity =>
        {
            entity.HasKey(c => c.Id); // Assuming Category has a primary key named Id

            entity.HasMany(c => c.BlogPosts)
                .WithMany(b => b.Categories)
                .UsingEntity<Dictionary<string, object>>(
                    "BlogPostCategory",
                    b => b.HasOne<BlogPost>().WithMany().HasForeignKey("BlogPostId"),
                    c => c.HasOne<Category>().WithMany().HasForeignKey("CategoryId")
                );
        });
    }
}

In the OnModelCreating method I define the relationships between the tables. I've used the Fluent API to define the relationships between the tables. This is a bit more verbose than using Data Annotations but I find it more readable.

You can see that I set up a couple of Indexes on the BlogPost table. This is to help with performance when querying the database; you should select the Indices based on how you'll be querying the data. In this case hash, slug, published date and language are all fields I'll be querying on.

Setup

Now we have our models and DbContext set up we need to hook it into the DB. My usual practice is to add extension methods, this helps keep everything more organised:

public static class Setup
{
    public static void SetupEntityFramework(this IServiceCollection services, string connectionString)
    {
        services.AddDbContext<MostlylucidDbContext>(options =>
            options.UseNpgsql(connectionString));
    }

    public static async Task InitializeDatabase(this WebApplication app)
    {
        try
        {
            await using var scope = 
                app.Services.CreateAsyncScope();
            
            await using var context = scope.ServiceProvider.GetRequiredService<MostlylucidDbContext>();
            await context.Database.MigrateAsync();
            
            var blogService = scope.ServiceProvider.GetRequiredService<IBlogService>();
            await blogService.Populate();
        }
        catch (Exception e)
        {
            Log.Fatal(e, "Failed to migrate database");
        }        
    }
}

Here I set up the database connection and then run the migrations. I also call a method to populate the database (in my case I'm still using the file based approach so I need to populate the database with the existing posts).

Your connection string will look something like this:

 "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Database=Mostlylucid;port=5432;Username=postgres;Password=<PASSWORD>;"
  },

Using the extension approach means that my Program.cs file is nice and clean:

services.SetupEntityFramework(config.GetConnectionString("DefaultConnection") ??
                              throw new Exception("No Connection String"));

//Then later in the app section

await app.InitializeDatabase();

The section below is responsible for running the migration and actually setting up the database. The MigrateAsync method will create the database if it doesn't exist and run any migrations that are needed. This is a great way to keep your database in sync with your models.

     await using var scope = 
                app.Services.CreateAsyncScope();
            
            await using var context = scope.ServiceProvider.GetRequiredService<MostlylucidDbContext>();
            await context.Database.MigrateAsync();

Migrations

Once you have all this set up you need to create your initial migration. This is a snapshot of the current state of your models and will be used to create the database. You can do this using the dotnet CLI (see here for details on installing the dotnet ef tool if needed):

dotnet ef migrations add InitialCreate

This will create a folder in your project with the migration files. You can then apply the migration to the database using:

dotnet ef database update

This will create the database and tables for you.