A beautiful GitOps day VIII - Further deployment with DB

part-09

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:

1
version: "3"
2

3
services:
4
  db:
5
    image: postgres:15
6
    environment:
7
      POSTGRES_USER: main
8
      POSTGRES_PASSWORD: main
9
      POSTGRES_DB: main
10
    ports:
11
      - 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

Note

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

Define the entities

1
using System.ComponentModel.DataAnnotations;
2

3
namespace KubeRocks.Application.Entities;
4

5
public class Article
6
{
7
    public int Id { get; set; }
8

9
    public required User Author { get; set; }
10

11
    [MaxLength(255)]
12
    public required string Title { get; set; }
13
    [MaxLength(255)]
14
    public required string Slug { get; set; }
15
    public required string Description { get; set; }
16
    public required string Body { get; set; }
17

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

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

1
namespace KubeRocks.Application.Entities;
2

3
public class Comment
4
{
5
    public int Id { get; set; }
6

7
    public required Article Article { get; set; }
8
    public required User Author { get; set; }
9

10
    public required string Body { get; set; }
11

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

1
using System.ComponentModel.DataAnnotations;
2

3
namespace KubeRocks.Application.Entities;
4

5
public class User
6
{
7
    public int Id { get; set; }
8

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

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

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

1
namespace KubeRocks.Application.Contexts;
2

3
using KubeRocks.Application.Entities;
4
using Microsoft.EntityFrameworkCore;
5

6
public class AppDbContext : DbContext
7
{
8
    public DbSet<User> Users => Set<User>();
9
    public DbSet<Article> Articles => Set<Article>();
10
    public DbSet<Comment> Comments => Set<Comment>();
11

12
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
13
    {
14
    }
15

16
    protected override void OnModelCreating(ModelBuilder modelBuilder)
17
    {
18
        base.OnModelCreating(modelBuilder);
19

20
        modelBuilder.Entity<User>()
21
            .HasIndex(u => u.Email).IsUnique()
22
        ;
23

24
        modelBuilder.Entity<Article>()
25
            .HasIndex(u => u.Slug).IsUnique()
26
        ;
27
    }
28
}

1
using KubeRocks.Application.Contexts;
2
using Microsoft.EntityFrameworkCore;
3
using Microsoft.Extensions.Configuration;
4
using Microsoft.Extensions.DependencyInjection;
5

6
namespace KubeRocks.Application.Extensions;
7

8
public static class ServiceExtensions
9
{
10
    public static IServiceCollection AddKubeRocksServices(this IServiceCollection services, IConfiguration configuration)
11
    {
12
        return services.AddDbContext<AppDbContext>((options) =>
13
        {
14
            options.UseNpgsql(configuration.GetConnectionString("DefaultConnection"));
15
        });
16
    }
17
}

1
using KubeRocks.Application.Extensions;
2

3
//...
4

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

8
//...

1
{
2
  //...
3
  "ConnectionStrings": {
4
    "DefaultConnection": "Host=localhost;Username=main;Password=main;Database=main;"
5
  }
6
}

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

1
{
2
  "ConnectionStrings": {
3
    "DefaultConnection": "Host=localhost;Username=main;Password=main;Database=main;"
4
  }
5
}

1
<Project Sdk="Microsoft.NET.Sdk">
2

3
    <!-- ... -->
4

5
  <PropertyGroup>
6
    <!-- ... -->
7
    <RunWorkingDirectory>$(MSBuildProjectDirectory)</RunWorkingDirectory>
8
  </PropertyGroup>
9

10
  <ItemGroup>
11
    <None Update="appsettings.json">
12
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
13
    </None>
14
  </ItemGroup>
15

16
</Project>

1
using Bogus;
2
using KubeRocks.Application.Contexts;
3
using KubeRocks.Application.Entities;
4
using Microsoft.EntityFrameworkCore;
5
using Npgsql;
6
using Respawn;
7
using Respawn.Graph;
8

9
namespace KubeRocks.Console.Commands;
10

11
[Command("db")]
12
public class DbCommand : ConsoleAppBase
13
{
14
    private readonly AppDbContext _context;
15

16
    public DbCommand(AppDbContext context)
17
    {
18
        _context = context;
19
    }
20

21
    [Command("migrate", "Migrate database")]
22
    public async Task Migrate()
23
    {
24
        await _context.Database.MigrateAsync();
25
    }
26

27
    [Command("fresh", "Wipe data")]
28
    public async Task FreshData()
29
    {
30
        await Migrate();
31

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

34
        await conn.OpenAsync();
35

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

42
        await respawner.ResetAsync(conn);
43
    }
44

45
    [Command("seed", "Fake data")]
46
    public async Task SeedData()
47
    {
48
        await Migrate();
49
        await FreshData();
50

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

56
        await _context.Users.AddRangeAsync(users);
57
        await _context.SaveChangesAsync();
58

59
        var articles = new Faker<Article>()
60
            .RuleFor(a => a.Title, f => f.Lorem.Sentence().TrimEnd('.'))
61
            .RuleFor(a => a.Description, f => f.Lorem.Paragraphs(1))
62
            .RuleFor(a => a.Body, f => f.Lorem.Paragraphs(5))
63
            .RuleFor(a => a.Author, f => f.PickRandom(users))
64
            .RuleFor(a => a.CreatedAt, f => f.Date.Recent(90).ToUniversalTime())
65
            .RuleFor(a => a.Slug, (f, a) => a.Title.Replace(" ", "-").ToLowerInvariant())
66
            .Generate(500)
67
            .Select(a =>
68
            {
69
                new Faker<Comment>()
70
                    .RuleFor(a => a.Body, f => f.Lorem.Paragraphs(2))
71
                    .RuleFor(a => a.Author, f => f.PickRandom(users))
72
                    .RuleFor(a => a.CreatedAt, f => f.Date.Recent(7).ToUniversalTime())
73
                    .Generate(new Faker().Random.Number(10))
74
                    .ForEach(c => a.Comments.Add(c));
75

76
                return a;
77
            });
78

79
        await _context.Articles.AddRangeAsync(articles);
80
        await _context.SaveChangesAsync();
81
    }
82
}

1
using KubeRocks.Application.Extensions;
2
using KubeRocks.Console.Commands;
3

4
var builder = ConsoleApp.CreateBuilder(args);
5

6
builder.ConfigureServices((ctx, services) =>
7
{
8
    services.AddKubeRocksServices(ctx.Configuration);
9
});
10

11
var app = builder.Build();
12

13
app.AddSubCommands<DbCommand>();
14

15
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

1
namespace KubeRocks.WebApi.Models;
2

3
public class ArticleListDto
4
{
5
    public required string Title { get; set; }
6

7
    public required string Slug { get; set; }
8

9
    public required string Description { get; set; }
10

11
    public required string Body { get; set; }
12

13
    public DateTime CreatedAt { get; set; }
14

15
    public DateTime UpdatedAt { get; set; }
16

17
    public required AuthorDto Author { get; set; }
18
}

1
namespace KubeRocks.WebApi.Models;
2

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

1
namespace KubeRocks.WebApi.Models;
2

3
public class AuthorDto
4
{
5
    public required string Name { get; set; }
6
}

1
namespace KubeRocks.WebApi.Models;
2

3
public class CommentDto
4
{
5
    public required string Body { get; set; }
6

7
    public DateTime CreatedAt { get; set; }
8

9
    public required AuthorDto Author { get; set; }
10
}

And finally the controller:

1
using KubeRocks.Application.Contexts;
2
using KubeRocks.WebApi.Models;
3
using Mapster;
4
using Microsoft.AspNetCore.Mvc;
5
using Microsoft.EntityFrameworkCore;
6

7
namespace KubeRocks.WebApi.Controllers;
8

9
[ApiController]
10
[Route("[controller]")]
11
public class ArticlesController
12
{
13
    private readonly AppDbContext _context;
14

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

17
    public ArticlesController(AppDbContext context)
18
    {
19
        _context = context;
20
    }
21

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

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

34
        return new ArticlesResponse(articles, articlesCount);
35
    }
36

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

46
        if (article is null)
47
        {
48
            return new NotFoundResult();
49
        }
50

51
        return article.Adapt<ArticleDto>();
52
    }
53
}

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:

1
apiVersion: v1
2
kind: Secret
3
metadata:
4
  name: demo-db
5
type: Opaque
6
data:
7
  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:

1
# ...
2
spec:
3
  # ...
4
  template:
5
    # ...
6
    spec:
7
      # ...
8
      containers:
9
        - name: api
10
          # ...
11
          env:
12
            - name: DB_PASSWORD
13
              valueFrom:
14
                secretKeyRef:
15
                  name: demo-db
16
                  key: password
17
            - name: ConnectionStrings__DefaultConnection
18
              value: Host=postgresql-primary.postgres;Username=demo;Password='$(DB_PASSWORD)';Database=demo;
19
#...

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:

1
// ...
2
var app = builder.Build();
3

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

8
// ...

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:

1
{
2
  articles: []
3
  articlesCount: 0
4
}

Note

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:

1
{
2
  "ConnectionStrings": {
3
    "DefaultConnection": "Host=localhost:54321;Username=demo;Password='xxx';Database=demo;"
4
  }
5
}

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

Note

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.