/DB

What you'll build

A guided, end-to-end build of a typed Projects and Tasks HTTP API on PostgreSQL, from schema to migrations to a tested data layer, with zero hand-written SQL.

updated 5 Jun 20261 min readv0.3.2View as Markdown

This tutorial builds a small but complete HTTP API on top of Socigy.OpenSource.DB. By the end you will have modelled a schema in C#, generated and applied a migration, wired the generated unit-of-work context into dependency injection, and read and written data through it without writing a line of SQL.

What we're building

A Projects and Tasks API with two tables:

  • projects, a named project with a creation timestamp.
  • tasks, a task that belongs to a project (foreign key), with a title and a done flag.

Endpoints:

Method Route Action
POST /projects create a project
GET /projects/{id}/tasks list a project's tasks
POST /projects/{id}/tasks add a task
POST /projects (seeded) create a project and its first tasks in one transaction

Prerequisites

  • .NET 8 or later and a reachable PostgreSQL instance.
  • Basic familiarity with ASP.NET Core minimal APIs and dependency injection.

Project layout

A clean split keeps the model assembly (which the generator processes) separate from the web host:

TaskApi/
├── ← model assembly: [Table] classes + socigy.json
│ ├── Project.cs
│ ├── TaskItem.cs
│ └── socigy.json
└── ← ASP.NET host: references TaskApi.AppDb
└── Program.cs

The generator runs inside TaskApi.AppDb at build time and emits the typed context, the connection factory, and the DI extensions into that assembly. The web host just references it and calls the generated AddAppDb() / AddAppDbContext() methods.

Start with Model the schema.