ASP.NET Core PostgreSQL CRUD API Starter

An ASP.NET Core 10 minimal API CRUD starter in C# that stores one resource, items, in PostgreSQL through Npgsql 10 and one pooled NpgsqlDataSource. It is for .NET developers who want an endpoint group, a repository with plain SQL, validation, JSON errors, a database health check, an OpenAPI document and xUnit tests.

by Light CloudCRUD APIs

Features

  • GET /items?limit=20&offset=0 lists items oldest first; a limit outside 1 to 100 or a negative offset answers 400
  • POST /items answers 201 with a Location header; /items/{id:int} routes make a non-numeric id a 404
  • ItemInput reads the raw JSON, trims name to 1 to 200 characters and rejects a number sent as name or description
  • UseExceptionHandler and UseStatusCodePages answer exceptions, unknown routes and wrong methods as { "error": "..." }
  • GET /health runs SELECT 1 and answers 503 with { "error": "Database unavailable" } when it fails
  • OpenAPI document at /openapi/v1.json from Microsoft.AspNetCore.OpenApi, and snake_case JSON such as created_at
  • The items table is created on start with CREATE TABLE IF NOT EXISTS, and the app exits if the database is unreachable
  • xUnit tests for DatabaseOptions, ItemInput and PageRequest that run with no database

Tech stack

What you need

  • A paid plan: deploying creates a PostgreSQL database, which the Free plan does not include
  • To run it locally: the .NET 10 SDK and Docker for a local PostgreSQL
  • No API keys: it starts with nothing to configure
  • Outside Light Cloud: a PostgreSQL database set through DB_HOST, DB_NAME, DB_USER and DB_PASSWORD, with DB_SSLMODE=disable if it has no TLS

About

Program.cs does the wiring on one screen: Kestrel listens on the port in PORT, JSON uses a snake_case naming policy, AddDatabase registers DatabaseOptions and a pool capped at 5 connections, and two Map...Endpoints calls add the routes. Settings come through ASP.NET Core configuration, so the DB_* values can be environment variables or entries in appsettings.json, and an empty value counts as unset. It serves plain HTTP with no HTTPS redirect, since TLS ends at the proxy in front of it. The current tests cover the models rather than the HTTP routes, so a WebApplicationFactory integration test is a sensible early addition. On Light Cloud CrudApi.csproj is built and run as a container on PORT with the database variables set, and every push to your copy redeploys.

FAQ

How do I add Swagger UI to an ASP.NET Core 10 minimal API?

This template serves the OpenAPI document at /openapi/v1.json but no UI. Add a package such as Scalar.AspNetCore and map it in Program.cs next to app.MapOpenApi().

How do I add a new resource to an ASP.NET Core minimal API?

Add a model in Models/, a repository in Data/ modelled on ItemRepository.cs with its own CREATE TABLE, and an Endpoints/ file with a Map...Endpoints method. Register the repository and call the map method in Program.cs.

Why Npgsql and not Entity Framework Core?

Each query is a few lines of SQL, which keeps the starter small and shows exactly what runs against the database. You can add EF Core with the Npgsql provider later; the endpoints only talk to ItemRepository.

How do I connect an ASP.NET Core API to a local PostgreSQL?

Start PostgreSQL in Docker with docker run -d --name items-db -p 5432:5432 -e POSTGRES_PASSWORD=postgres postgres:16, copy .env.example to .env, which sets DB_SSLMODE=disable, and load it with set -a && . ./.env && set +a before dotnet run.

Can I host this .NET API without Light Cloud?

Yes. Publish with dotnet publish CrudApi.csproj -c Release -o out and run dotnet out/CrudApi.dll with PORT and the DB_* variables set, on any host with the ASP.NET Core 10 runtime and a PostgreSQL database. The repo has no Dockerfile, so add one if your host needs an image.

Price
Free
Framework
ASP.NET · .NET
Updated