A beautiful GitOps day VIII - Further deployment with DB

Table of Contents

Use GitOps workflow for building a production grade on-premise Kubernetes cluster on cheap VPS provider, with complete CI/CD 🎉

This is the Part VIII of more global topic tutorial. Back to guide summary for intro.

Real DB App sample #

Let’s add some DB usage to our sample app. We’ll use the classical Articles<->Authors<->Comments relationships. First create docker-compose.yml file in root of demo project:

kuberocks-demo - docker-compose.ymlversion: "3"

services:
  db:
    image: postgres:15
    environment:
      POSTGRES_USER: main
      POSTGRES_PASSWORD: main
      POSTGRES_DB: main
    ports:
      - 5432:5432

Launch it with docker compose up -d and check database running with docker ps.

Time to create basic code that list plenty of articles from an API endpoint. Go back to kuberocks-demo and create a new separate project dedicated to app logic:

dotnet new classlib -o src/KubeRocks.Application
dotnet sln add src/KubeRocks.Application
dotnet add src/KubeRocks.WebApi reference src/KubeRocks.Application

dotnet add src/KubeRocks.Application package Microsoft.EntityFrameworkCore
dotnet add src/KubeRocks.Application package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add src/KubeRocks.WebApi package Microsoft.EntityFrameworkCore.Design

This is not a DDD course ! We will keep it simple and focus on Kubernetes part.

Define the entities #

kuberocks-demo - src/KubeRocks.Application/Entities/Article.csusing System.ComponentModel.DataAnnotations;

namespace KubeRocks.Application.Entities;

public class Article
{
    public int Id { get; set; }

    public required User Author { get; set; }

    [MaxLength(255)]
    public required string Title { get; set; }
    [MaxLength(255)]
    public required string Slug { get; set; }
    public required string Description { get; set; }
    public required string Body { get; set; }

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime UpdatedAt { get; set; } = DateTime.UtcNow;

    public ICollection<Comment> Comments { get; } = new List<Comment>();
}

kuberocks-demo - src/KubeRocks.Application/Entities/Comment.csnamespace KubeRocks.Application.Entities;

public class Comment
{
    public int Id { get; set; }

    public required Article Article { get; set; }
    public required User Author { get; set; }

    public required string Body { get; set; }

    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

kuberocks-demo - src/KubeRocks.Application/Entities/User.csusing System.ComponentModel.DataAnnotations;

namespace KubeRocks.Application.Entities;

public class User
{
    public int Id { get; set; }

    [MaxLength(255)]
    public required string Name { get; set; }

    [MaxLength(255)]
    public required string Email { get; set; }

    public ICollection<Article> Articles { get; } = new List<Article>();
    public ICollection<Comment> Comments { get; } = new List<Comment>();
}

kuberocks-demo - src/KubeRocks.Application/Contexts/AppDbContext.csnamespace KubeRocks.Application.Contexts;

using KubeRocks.Application.Entities;
using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    public DbSet<Article> Articles => Set<Article>();
    public DbSet<Comment> Comments => Set<Comment>();

    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email).IsUnique()
        ;

        modelBuilder.Entity<Article>()
            .HasIndex(u => u.Slug).IsUnique()
        ;
    }
}

kuberocks-demo - src/KubeRocks.Application/Extensions/ServiceExtensions.csusing KubeRocks.Application.Contexts;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace KubeRocks.Application.Extensions;

public static class ServiceExtensions
{
    public static IServiceCollection AddKubeRocksServices(this IServiceCollection services, IConfiguration configuration)
    {
        return services.AddDbContext<AppDbContext>((options) =>
        {
            options.UseNpgsql(configuration.GetConnectionString("DefaultConnection"));
        });
    }
}

kuberocks-demo - src/KubeRocks.WebApi/Program.csusing KubeRocks.Application.Extensions;

//...

// Add services to the container.
builder.Services.AddKubeRocksServices(builder.Configuration);

//...

kuberocks-demo - src/KubeRocks.WebApi/appsettings.Development.json{
  //...
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Username=main;Password=main;Database=main;"
  }
}

Now as all models are created, we can generate migrations and update database accordingly:

dotnet new tool-manifest
dotnet tool install dotnet-ef

dotnet dotnet-ef -p src/KubeRocks.Application -s src/KubeRocks.WebApi migrations add InitialCreate
dotnet dotnet-ef -p src/KubeRocks.Application -s src/KubeRocks.WebApi database update

Inject some dummy data #

We’ll use Bogus on a separate console project:

dotnet new console -o src/KubeRocks.Console
dotnet sln add src/KubeRocks.Console
dotnet add src/KubeRocks.WebApi reference src/KubeRocks.Application
dotnet add src/KubeRocks.Console package Bogus
dotnet add src/KubeRocks.Console package ConsoleAppFramework
dotnet add src/KubeRocks.Console package Respawn

kuberocks-demo - src/KubeRocks.Console/appsettings.json{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost;Username=main;Password=main;Database=main;"
  }
}

kuberocks-demo - src/KubeRocks.Console/KubeRocks.Console.csproj<Project Sdk="Microsoft.NET.Sdk">

    <!-- ... -->

  <PropertyGroup>
    <!-- ... -->
    <RunWorkingDirectory>$(MSBuildProjectDirectory)</RunWorkingDirectory>
  </PropertyGroup>

  <ItemGroup>
    <None Update="appsettings.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

kuberocks-demo - src/KubeRocks.Console/Commands/DbCommand.csusing Bogus;
using KubeRocks.Application.Contexts;
using KubeRocks.Application.Entities;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using Respawn;
using Respawn.Graph;

namespace KubeRocks.Console.Commands;

[Command("db")]
public class DbCommand : ConsoleAppBase
{
    private readonly AppDbContext _context;

    public DbCommand(AppDbContext context)
    {
        _context = context;
    }

    [Command("migrate", "Migrate database")]
    public async Task Migrate()
    {
        await _context.Database.MigrateAsync();
    }

    [Command("fresh", "Wipe data")]
    public async Task FreshData()
    {
        await Migrate();

        using var conn = new NpgsqlConnection(_context.Database.GetConnectionString());

        await conn.OpenAsync();

        var respawner = await Respawner.CreateAsync(conn, new RespawnerOptions
        {
            TablesToIgnore = new Table[] { "__EFMigrationsHistory" },
            DbAdapter = DbAdapter.Postgres
        });

        await respawner.ResetAsync(conn);
    }

    [Command("seed", "Fake data")]
    public async Task SeedData()
    {
        await Migrate();
        await FreshData();

        var users = new Faker<User>()
            .RuleFor(m => m.Name, f => f.Person.FullName)
            .RuleFor(m => m.Email, f => f.Person.Email)
            .Generate(50);

        await _context.Users.AddRangeAsync(users);
        await _context.SaveChangesAsync();

        var articles = new Faker<Article>()
            .RuleFor(a => a.Title, f => f.Lorem.Sentence().TrimEnd('.'))
            .RuleFor(a => a.Description, f => f.Lorem.Paragraphs(1))
            .RuleFor(a => a.Body, f => f.Lorem.Paragraphs(5))
            .RuleFor(a => a.Author, f => f.PickRandom(users))
            .RuleFor(a => a.CreatedAt, f => f.Date.Recent(90).ToUniversalTime())
            .RuleFor(a => a.Slug, (f, a) => a.Title.Replace(" ", "-").ToLowerInvariant())
            .Generate(500)
            .Select(a =>
            {
                new Faker<Comment>()
                    .RuleFor(a => a.Body, f => f.Lorem.Paragraphs(2))
                    .RuleFor(a => a.Author, f => f.PickRandom(users))
                    .RuleFor(a => a.CreatedAt, f => f.Date.Recent(7).ToUniversalTime())
                    .Generate(new Faker().Random.Number(10))
                    .ForEach(c => a.Comments.Add(c));

                return a;
            });

        await _context.Articles.AddRangeAsync(articles);
        await _context.SaveChangesAsync();
    }
}

kuberocks-demo - src/KubeRocks.Console/Program.csusing KubeRocks.Application.Extensions;
using KubeRocks.Console.Commands;

var builder = ConsoleApp.CreateBuilder(args);

builder.ConfigureServices((ctx, services) =>
{
    services.AddKubeRocksServices(ctx.Configuration);
});

var app = builder.Build();

app.AddSubCommands<DbCommand>();

app.Run();

Then launch the command:

dotnet run --project src/KubeRocks.Console db seed

Ensure with your favorite DB client that data is correctly inserted.

Define endpoint access #

All that’s left is to create the endpoint. Let’s define all DTO first:

dotnet add src/KubeRocks.WebApi package Mapster

kuberocks-demo - src/KubeRocks.WebApi/Models/ArticleListDto.csnamespace KubeRocks.WebApi.Models;

public class ArticleListDto
{
    public required string Title { get; set; }

    public required string Slug { get; set; }

    public required string Description { get; set; }

    public required string Body { get; set; }

    public DateTime CreatedAt { get; set; }

    public DateTime UpdatedAt { get; set; }

    public required AuthorDto Author { get; set; }
}

kuberocks-demo - src/KubeRocks.WebApi/Models/ArticleDto.csnamespace KubeRocks.WebApi.Models;

public class ArticleDto : ArticleListDto
{
    public List<CommentDto> Comments { get; set; } = new();
}

kuberocks-demo - src/KubeRocks.WebApi/Models/AuthorDto.csnamespace KubeRocks.WebApi.Models;

public class AuthorDto
{
    public required string Name { get; set; }
}

kuberocks-demo - src/KubeRocks.WebApi/Models/CommentDto.csnamespace KubeRocks.WebApi.Models;

public class CommentDto
{
    public required string Body { get; set; }

    public DateTime CreatedAt { get; set; }

    public required AuthorDto Author { get; set; }
}

And finally the controller:

kuberocks-demo - src/KubeRocks.WebApi/Controllers/ArticlesController.csusing KubeRocks.Application.Contexts;
using KubeRocks.WebApi.Models;
using Mapster;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace KubeRocks.WebApi.Controllers;

[ApiController]
[Route("[controller]")]
public class ArticlesController
{
    private readonly AppDbContext _context;

    public record ArticlesResponse(IEnumerable<ArticleListDto> Articles, int ArticlesCount);

    public ArticlesController(AppDbContext context)
    {
        _context = context;
    }

    [HttpGet(Name = "GetArticles")]
    public async Task<ArticlesResponse> Get([FromQuery] int page = 1, [FromQuery] int size = 10)
    {
        var articles = await _context.Articles
            .OrderByDescending(a => a.Id)
            .Skip((page - 1) * size)
            .Take(size)
            .ProjectToType<ArticleListDto>()
            .ToListAsync();

        var articlesCount = await _context.Articles.CountAsync();

        return new ArticlesResponse(articles, articlesCount);
    }

    [HttpGet("{slug}", Name = "GetArticleBySlug")]
    public async Task<ActionResult<ArticleDto>> GetBySlug(string slug)
    {
        var article = await _context.Articles
            .Include(a => a.Author)
            .Include(a => a.Comments.OrderByDescending(c => c.Id))
            .ThenInclude(c => c.Author)
            .FirstOrDefaultAsync(a => a.Slug == slug);

        if (article is null)
        {
            return new NotFoundResult();
        }

        return article.Adapt<ArticleDto>();
    }
}

Launch the app and check that /Articles and /Articles/{slug} endpoints are working as expected.

Deployment with database #

Database connection #

It’s time to connect our app to the production database. Create a demo DB & user through pgAdmin and create the appropriate secret:

demo-kube-flux - clusters/demo/kuberocks/secrets-demo-db.yamlapiVersion: v1
kind: Secret
metadata:
  name: demo-db
type: Opaque
data:
  password: ZGVtbw==

Generate the according sealed secret like previously chapters with kubeseal under sealed-secret-demo-db.yaml file and delete secret-demo-db.yaml.

cat clusters/demo/kuberocks/secret-demo.yaml | kubeseal --format=yaml --cert=pub-sealed-secrets.pem > clusters/demo/kuberocks/sealed-secret-demo.yaml
rm clusters/demo/kuberocks/secret-demo.yaml

Let’s inject the appropriate connection string as environment variable:

demo-kube-flux - clusters/demo/kuberocks/deploy-demo.yaml# ...
spec:
  # ...
  template:
    # ...
    spec:
      # ...
      containers:
        - name: api
          # ...
          env:
            - name: DB_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: demo-db
                  key: password
            - name: ConnectionStrings__DefaultConnection
              value: Host=postgresql-primary.postgres;Username=demo;Password='$(DB_PASSWORD)';Database=demo;
#...

Database migration #

The DB connection should be done, but the database isn’t migrated yet, the easiest is to add a migration step directly in startup app:

kuberocks-demo - src/KubeRocks.WebApi/Program.cs// ...
var app = builder.Build();

using var scope = app.Services.CreateScope();
await using var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
await dbContext.Database.MigrateAsync();

// ...

The database should be migrated on first app launch on next deploy. Go to https://demo.kube.rocks/Articles to confirm all is ok. It should return next empty response:

{
  articles: []
  articlesCount: 0
}

Don’t hesitate to abuse of klo -n kuberocks deploy/demo to debug any troubleshooting when pod is on error state.

Database seeding #

We’ll try to seed the database directly from local. Change temporarily the connection string in appsettings.json to point to the production database:

kuberocks-demo - src/KubeRocks.Console/appsettings.json{
  "ConnectionStrings": {
    "DefaultConnection": "Host=localhost:54321;Username=demo;Password='xxx';Database=demo;"
  }
}

Then:

# forward the production database port to local
kpf svc/postgresql -n postgres 54321:tcp-postgresql
# launch the seeding command
dotnet run --project src/KubeRocks.Console db seed

We may obviously never do this on real production database, but as it’s only for seeding, it will never concern them.

Return to https://demo.kube.rocks/Articles to confirm articles are correctly returned.

8th check ✅ #

We now have a little more realistic app. Go next part, we’ll talk about further monitoring integration and tracing with OpenTelemetry.