Minimal web API with CRUD on PostgreSQL: A RESTful Microservice implementation in ASP.NET Core for .NET 6

In this tutorial we will see how to implement a Minimal web API with CRUD on PostgreSQL using ASP.NET Core for .NET 6. Our RESTful microservice performs basic CRUD operations on PostgreSQL using ASP.NET Core for .NET 6. This tutorial is intended for both absolute beginners and advanced developers who want to quickly implement a RESTful microservice with Minimal web API with CRUD on PostgreSQL using open source ASP.NET Core for .NET 6.0 (LTS).

Table of Contents

• Implementing RESTful Microservice using ASP.NET Core Minimal web API with CRUD on PostgreSQL
• Prerequisites
• Install PostgreSQL
• Clone the repo
• Add connection string
• Model database using Entity Framework Core
  • Create a record
  • Create DbContext
• Npgsql Entity Framework Core Provider for PostgreSQL
• Connecting to PostgreSQL Database using Npgsql
• Performing EF Core Database Migrations
  • Add migrations
  • List out pending migrations
  • Update database with pending migrations
• Implementing RESTful endpoints of a Minimal web API
• Create Note
  • Curl request to create note
• Read Note
  • Request to read note by id
• Read all Notes
  • Request to read all notes
• Update Note
  • Request to update note
• Delete Note
  • Request to delete note
• Source Code
• Summary

Implementing RESTful Microservice using ASP.NET Core Minimal web API with CRUD on PostgreSQL

In the previous tutorial, we have covered Getting started with Minimal WebAPI using ASP.NET Core for .NET 6. To re-iterate, we also learnt the motivation for choosing ASP.NET Core for .NET 6 for our next web application. It is in the second place after pico.v web framework.

The pico.v is a web server written using V lang. The pico.v is based on picoev and picohttpparser. Interested in learning V? Order my book Getting Started with V Programming.

With the aforementioned pre-text, we will proceed with implementing CRUD based on RESTful microservice using Minimal web API backed by ASP.NET Core for .NET 6. At the end of this tutorial, you will learn how to implement Create, Read, Update and Delete REST endpoints. To brief, our Minimal web API will have the following features.

• Microservice uses PostgreSQL as a database server
• Entity Framework Core for .NET 6 as an ORM (Object Relational Mapper) to build DbContext
• Database model using record, a new reference type introduced in C# 9. A record type can be used instead of classes or structs.
• Npgsql Entity Framework Core Provider to connect to the PostgreSQL database
• Perform database migrations using Entity Framework Core
• Build Create, Read, Update and Delete endpoints
• Query the endpoints using curl request

Prerequisites

In order to follow along with this tutorial, it is recommended having the following list of tools and software installed on your PC.

• Admin privileges to the PC to install software
• Access to a command-line terminal
• You need at least .NET SDK 6.0.100 LTS or the latest installed on your PC
• Either of the following IDEs which are free to download and install
  • Visual Studio Code or
  • Visual Studio 2022 or latest.
• Optional but good to have Postman or any REST Client
• Access to local or remote PostgreSQL server
• pgAdmin4 or DBeaver Community Edition to connect and view database
• Basic knowledge of computer programming

This tutorial will use VS Code, a light-weight IDE compared to Visual Studio 2022. With the aforementioned prerequisites in place, we will proceed and start creating a RESTful Minimal web API with CRUD on PostgreSQL using ASP.NET Core for .NET 6.

Install PostgreSQL

If you already have PostgreSQL installed, you can move to the next section where you will learn how to add PostgreSQL database connection string to Minimal web API.

In case you haven’t installed PostgreSQL, here are the resources to help you install PostgreSQL:

Install PostgreSQL on Windows 10 OS:

• 📃 How to Install PostgreSQL Without Admin Rights on Windows 10 OS
• 📺 Installing Postgres SQL on Windows PC

Install PostgreSQL on Ubuntu OS:

• 📃 Install PostgreSQL 12 on Ubuntu
• 📺 Install PostgreSQL 13.1 on Ubuntu 18
• 📺 Configure PostgreSQL 13.1 on Ubuntu 18

Clone the repo

We will work on a project available in repo that we have created as a part of tutorial Getting started with Minimal web API using ASP.NET Core for .NET 6.0 (LTS). So clone this repo using the following command.

git clone https://github.com/windson/minimal-web-api-tutorials.git
cd minimal-web-api-tutorials
cd NotesMinimalAPI
code .

Running the preceding commands will clone and launch the repo in VS Code.

Add connection string

We will now proceed and see how to add the PostgreSQL database connection string in the appsettings.json file.

Notice the ConnectionStrings has a key DefaultConnection that holds the value of the PostgreSQL database. You can change the connection string to point to the local or any remote PostgreSQL database.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=5432;Database=notesdb;User Id=postgres;Password=secret"
  },
  "Kestrel": {
    "Endpoints": {
      "Https": {
        "Url": "https://localhost:5001"
      },
      "Http": {
        "Url": "http://localhost:5000"
      }
    }
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

The connection string to connect to the PostgreSQL database from Minimal web API implementing using ASP.NET Core has the following form.

Server=localhost;Port=5432;Database=notesdb;User Id=postgres;Password=secret

• Server: is localhost or the server url in case of a remote PostgreSQL server.
• Port: 5432 is the default port from which you can establish a connection to the PostgreSQL server instance.
• Database: the name of the database for your application. After a successful connection, dotnet core database migration commands will create the database if it doesn’t already exist. In this case, the database name will be notesdb.
• User Id: for localhost it is generally postgres. For remote connections it might vary based on the configuration.
• Password: secret in my case. Replace it with your password.

Model database using Entity Framework Core

In order to communicate to the database from the object-oriented programs such as C#, we can use ORMs. The ORM stands for Object Relation Mapper. ORMs allow you to define Objects that map to the tables in the world of a relational database. In our case, these objects will be created using C#, which is the language backend for our Minimal web API that we are implementing using ASP.NET Core for .NET 6.

We will be using Entity Framework Core as an ORM for our Minimal web API. Entity Framework Core or EF Core is an open source, cross-platform object-relational mapper. The EF Core is an extensible and light weight version of the most popular Entity Framework data access technology.

Let us install Microsoft.EntityFrameworkCore using the command shown as follows.

dotnet add package Microsoft.EntityFrameworkCore --version 6.0.0

Create a record

Now using EF Core, we will build a record which replicates a single row of the table in a database. This record will serve as ORM for our Note table.

Add the following at the very end of the Program.cs which represents a Note record.

record Note(int id){
    public string text {get;set;} = default!;
    public bool done {get;set;} = default!;
}

The preceding code represents a Note record with fields text to hold the content of a Note and a boolean field done to represent the status of Note.

Create DbContext

Now we will create database context for our application. For this, add the following code at the very end of the Program.cs.

using Microsoft.EntityFrameworkCore; // place this line at the beginning of file.

class NoteDb: DbContext {
    public NoteDb(DbContextOptions<NoteDb> options): base(options) {

    }
    public DbSet<Note> Notes => Set<Note>();
}

The class NoteDb serves as the database context for our microservice. As we see, it inherits DbContext which is available from the package Microsoft.EntityFrameworkCore. So is the statement using Microsoft.EntityFrameworkCore;. All using statements must be placed at the top of the code file in .NET.

We will now proceed to learn about Npgsql Entity Framework Core Provider for PostgreSQL and see how to install it.

Npgsql Entity Framework Core Provider for PostgreSQL

So far we have done the following:

• added ConnectionString in the appsettings.json for the PostgreSQL database
• created a Note record as an object to a relation mapper
• created database context for our database

We will now proceed to learn how to establish the connection between our Minimal web API and the database using a third-party open-source Entity Framework Core package Npgsql.EntityFrameworkCore.PostgreSQL. This is an Entity Framework Core Provider for the PostgreSQL database.

Let’s install the Npgsql.EntityFrameworkCore.PostgreSQL package from the command line is shown as follows.

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 6.0.0

We will now proceed to learn how to connect to the PostgreSQL database using Npgsql programmatically.

Connecting to PostgreSQL Database using Npgsql

Using the database context NoteDb that we created earlier, we will now see how to establish a connection to the database using the EF Core Provider for PostgreSQL.

In the Program.cs file, before the line var app = builder.Build();, add the following to establish connection.

// Connect to PostgreSQL Database
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<NoteDb>(options =>
    options.UseNpgsql(connectionString));

var app = builder.Build();
//... rest of the code omitted for brevity

In the preceding code, we are telling the Web application builder to add a service which establishes the database context. Also, we are specifying that database context NoteDb to use Npgsql Entity Framework Core Provider.

As we are set with the code base, it’s time to perform database migrations such that they reflect in the PostgreSQL server.

Performing EF Core Database Migrations

To work with EF Core database migrations, we will install Microsoft.EntityFrameworkCore.Design package shown as follows.

dotnet add package Microsoft.EntityFrameworkCore.Design --version 6.0.0

Finally install the following package.

dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore --version 6.0.0

In the Program.cs, add the following before the line var app = builder.Build(); as shown.

//... rest of the code omitted for brevity
builder.Services.AddDatabaseDeveloperPageExceptionFilter();
//... rest of the code omitted for brevity

Here we are invoking another service AddDatabaseDeveloperPageExceptionFilter. The AddDatabaseDeveloperPageExceptionFilter method helps capture database-related exceptions along with possible actions to resolve those in the HTML response format. The exceptions can be resolved by using Entity Framework migrations.

Add migrations

Run the following code to add migrations.

dotnet ef migrations add firstmigration --project NotesMinimalAPI.csproj

The output of the preceding command will be as follows.

Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.0 initialized 'NoteDb' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: None
Done. To undo this action, use 'ef migrations remove'

Now you should see a Migrations directory added to the root directory of your project. The Migrations directory will have all the information related to the migration tagged with the name firstmigration. You can replace firstmigration with any meaningful name every time you add migrations.

The --projects flag takes the name of the .csproj file as an argument. In this case it will be NotesMinimalAPI.csproj. If you use Visual Studio 2022 instead of VS Code IDE, then you can remove .csproj from the file name.

List out pending migrations

The migration is just added. To list the pending migrations to be applied on the database, run the following command.

dotnet ef migrations list --project NotesMinimalAPI.csproj

The output of the preceding command will be as follows.

Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.0 initialized 'NoteDb' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: None
20211130132934_firstmigration (Pending)

Update database with pending migrations

As we haven’t updated the database with the migrations yet, you should see a pending migration. So, run the following command to apply the migration named firstmigration on your database server.

dotnet ef database update firstmigration --project NotesMinimalAPI.csproj

After running this command, you will get a database named notesdb as defined in the ConnectionString of appsettings.json will be created. Also, you will find a table named Notes with three columns id, name and done. This is based on the Note record with three fields we defined earlier in C#.

The output of the preceding command will be as follows.

Build started...
Build succeeded.
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 6.0.0 initialized 'NoteDb' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL:6.0.0+025fc55189ae41e3f2b98bcbe4eb44c505653700' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (15,256ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE notesdb;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (206ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "__EFMigrationsHistory" (
          "MigrationId" character varying(150) NOT NULL,
          "ProductVersion" character varying(32) NOT NULL,
          CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (152ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname='__EFMigrationsHistory');
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "MigrationId", "ProductVersion"
      FROM "__EFMigrationsHistory"
      ORDER BY "MigrationId";
info: Microsoft.EntityFrameworkCore.Migrations[20402]
      Applying migration '20211130132934_firstmigration'.
Applying migration '20211130132934_firstmigration'.
infoDone.
: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (113ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "Notes" (
          id integer GENERATED BY DEFAULT AS IDENTITY,
          text text NOT NULL,
          done boolean NOT NULL,
          CONSTRAINT "PK_Notes" PRIMARY KEY (id)
      );
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (10ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
      VALUES ('20211130132934_fi
// Rest of the output omitted for brevity

As we have successfully applied migrations, we will proceed to implement RESTful endpoints using the Minimal web API.

Implementing RESTful endpoints of a Minimal web API

We will start with a RESTful endpoint to Create a Note. We will then proceed by adding the remaining endpoints that perform Read, Update and Delete operations in our microservice.

For those who are new, CRUD stands for Create, Read, Update and Delete operations. The following table summarizes the endpoints that we are building the microservice using ASP.NET Core Minimal web API with CRUD on PostgreSQL for .NET 6.

Endpoints for Implementing a Minimal web API with CRUD on PostgreSQL

It’s show time! Let’s get started with implementing the RESTful endpoints for microservice that perform CRUD on a PostgreSQL database using ASP.NET Core for .NET 6.

Create Note

To implement creating the Note endpoint, the HTTP Verb we will use will be POST. The POST request will send a JSON representation of the Note object as a payload to /notes url. Upon successful creation of Note, the response body will have the new Note created along with the id. The field id is a primary key in the database, it is automatically generated by the database. The response status code will be 201 Created. Additionally, the response will have a Location header set to the resource to help find the new created Note.

POST endpoint for Implementing a Minimal web API to create a Note

Let’s proceed to implement the POST endpoint for our RESTful Minimal web API.

app.MapPost("/notes/", async(Note n, NoteDb db)=> {
    // Code goes here
});

From the preceding code, we are using MapPost extension method on an instance of app. It takes two parameters as follows.

• First parameter as a string that represents the pattern for the endpoint
• Second parameter as a delegate to handle the Post request

In our case, we have an async handler that takes two input parameters as follows.

• First parameter is a Note which represents the incoming JSON payload in the request body
• Second parameter performs the dependency injection of database context NoteDb, available as a part of EF Core’s DbContext

With Minimal web API, as you don’t have to use Controller classes and its constructors, we are performing the dependency injection in the delegate’s input parameter. If you are familiar with FastAPI, it has the similar concept of Dependency Injection.

    db.Notes.Add(n);
    await db.SaveChangesAsync();

The MapPost has the handler injected with Note payload and with DbContext dependency of NoteDb injected will add the incoming Note to the database. As the delegate handler is operating in asynchronous mode, which is evident from the keyword async, we will await for the database context to save changes in the database.

After successfully creating a Note record in the database, we will build the response with 201 Created status code along with the location representing the url of the new Note that is just created.

    return Results.Created($"/notes/{n.id}", n);

Adding bits of code, we will see the MapPost method for the POST endpoint will appear as follows.

app.MapPost("/notes/", async(Note n, NoteDb db)=> {
    db.Notes.Add(n);
    await db.SaveChangesAsync();

    return Results.Created($"/notes/{n.id}", n);
});

Curl request to create note

With POST RESTful endpoint in place, run the application using the command dotnet run. We will now query the endpoint that creates a Note in the database. From the command line terminal, execute the following curl request.

curl --location --request POST 'http://localhost:5000/notes/' \
--header 'Content-Type: application/json' \
--data-raw '{
    "text":"Buy LED Bulb",
    "done": false
}'

Let’s create another Note using the following curl request.

curl --location --request POST 'http://localhost:5000/notes/' \
--header 'Content-Type: application/json' \
--data-raw '{
    "text":"Get some milk",
    "done": false
}'

Read Note

We will use the GET HTTP Verb to implement an endpoint that reads a single Note given its id. The GET request will have a route parameter id in its endpoint url. The id is of type integer that represents the id of the resource in the database.

GET endpoint for Implementing a Minimal web API to read a Note given its id

If the requesting id of the resource is found in the database, the response will have a JSON representation of Note object with 200 OK status code. If the id is not found, the status code will be 404 Not Found.

Let’s proceed to implement the GET endpoint for our RESTful Minimal web API.

app.MapGet("/notes/{id:int}", async(int id, NoteDb db)=> 
{
    return await db.Notes.FindAsync(id)
            is Note n
                ? Results.Ok(n)
                : Results.NotFound();
});

In the preceding code, we are calling MapGet to perform the GET operation. The url pattern also has a route parameter {id:int} to indicate the id of the Note. We also notice that we are finding the Note given its id. The result is OK along with found Note if there is a matching id otherwise the response will be shown as 404 Not Found.

Request to read note by id

With GET single Note by id RESTful endpoint in place, run the application using the command dotnet run. We will now query the endpoint that reads a Note given its id from the database. From the command line terminal, execute the following curl request.

curl --location --request GET 'http://localhost:5000/notes/1'

Read all Notes

We will now implement an endpoint to retrieve the collection of Note objects. The HTTP verb will be GET, and the response body will be an array of JSON representation of Note objects. The status code will be 200 OK.

GET endpoint for Implementing a Minimal web API to retrieve all Notes

Let’s proceed to implement GET endpoint for our RESTful Minimal web API that returns collection of Note objects.

app.MapGet("/notes", async (NoteDb db) => await db.Notes.ToListAsync());

In the preceding code, we are calling MapGet to perform the GET operation. We also notice that the retrieve all notes is simply one liner.

Request to read all notes

With GET all Notes RESTful endpoint in place, run the application using the command dotnet run. We will now query the endpoint that reads all Note from the database. From the command line terminal, execute the following curl request.

curl --location --request GET 'http://localhost:5000/notes/'

Update Note

To update the Note object, we will use the PUT HTTP Verb. The endpoint will update a single Note given its id. The PUT request will have an id in its endpoint url. The id is of type integer that represents the id of the resource in the database.

PUT endpoint for Implementing a Minimal web API to update a Note given its id

If the requesting id of the resource is found in the database, the response will have a JSON representation of the Note object with 200 OK status code. If the id is not found, the status code will be 404 Not Found.

Let’s proceed to implement the PUT endpoint for our RESTful Minimal web API.

app.MapPut("/notes/{id:int}", async(int id, Note n, NoteDb db)=>
{
    if (n.id != id)
    {
        return Results.BadRequest();
    }

    var note = await db.Notes.FindAsync(id);
    
    if (note is null) return Results.NotFound();

    //found, so update with incoming note n.
    note.text = n.text;
    note.done = n.done;
    await db.SaveChangesAsync();
    return Results.Ok(note);
});

In the preceding code, we are calling MapPut to perform the PUT operation. The url pattern also has a route parameter {id:int} to indicate the id of the Note. Also, we notice from the preceding code that the following routines are done to update a Note.

• Checks if the id from the endpoint path parameter matches the id of the Note obtained from the JSON payload.
  • when these id do not match, then, the result is a 400 Bad Request
• Next, find the Note based on the input id
  • If the Note with the id being requested for updating doesn’t exist in the database, then the result will be 404 Not Found
• When the Note is found, we will update its fields with the incoming payload and save the updated Note to the database.
• We finally respond with the updated Note as a JSON response with status code 200 OK.

Request to update note

With PUT a Note by id RESTful endpoint in place, run the application using the command dotnet run. We will now query the endpoint that Updates a Note given its id in the database. From the command line terminal, execute the following curl request.

curl --location --request PUT 'http://localhost:5000/notes/1' \
--header 'Content-Type: application/json' \
--data-raw '{
    "id" : 1,
    "text":"Buy LED Bulb",
    "done": true
}'

Delete Note

To delete a Note from the database, we will use the DELETE HTTP Verb. The endpoint will delete a single Note from the database given its id. The DELETE request will have an id in its endpoint url. The id is of type integer that represents the id of the resource in the database.

DELETE endpoint for Implementing a Minimal web API to delete a Note given its id

Let’s proceed to implement the DELETE endpoint for our RESTful Minimal web API.

app.MapDelete("/notes/{id:int}", async(int id, NoteDb db)=>{

    var note = await db.Notes.FindAsync(id);
    if (note is not null){
        db.Notes.Remove(note);
        await db.SaveChangesAsync();
    }
    return Results.NoContent();
});

In the preceding code, we are calling MapDelete to perform the DELETE operation. The url pattern also has a route parameter {id:int} to indicate the id of the Note. Also, from the preceding code, we notice the following routines are performed to delete a Note from the database.

• We are finding for the Note by its id obtained from the route parameter {id:int}
• If the Note is obtained with a matching id, we are removing it from the database
• Note that we are returning the result with an empty response body with status code 204 No Content by calling Result.NoContent()

The DELETE endpoint in this case is idempotent in nature. This means you will see the same response with status code 204 No Content regardless of the number of times you perform deletion of Note for a given id.

Request to delete note

With DELETE a Note by id RESTful endpoint in place, run the application using the command dotnet run. We will now query the endpoint that deletes a Note given its id from the database. From the command line terminal, execute the following curl request.

curl --location --request DELETE 'http://localhost:5000/notes/1'

Source Code

The full source code of this tutorial is available on minimal-web-api-crud-postgresql-net6 branch of minimal-web-api-tutorials GitHub repository.

Summary

In this tutorial, we learnt how to build a Minimal web API that performs CRUD operations on PostgreSQL database. We started off by creating a simple Minimal web API as our project to work on. We then learnt how to add a connection string for the PostgreSQL database. We then performed database modeling using record type in C#. We learnt how to configure Entity Framework Core as an ORM and then use Npgsql as EF Core provider to work with PostgreSQL database.

In the later parts of this tutorial, we performed EF Core database migrations and tabulated the list of endpoints that we are going to implement in our microservice. We then implemented our microservice in a RESTful approach with proper status codes and JSON response wherever necessary. We learnt that the resulting microservice is a RESTful interface that performs Create, Read, Update and Delete operations on a table in the PostgreSQL database. We also learnt how to run Minimal web API with CRUD on PostgreSQL and learnt how to send queries to Minimal web API using curl requests.

If you like this tutorial, please bookmark 🔖 (Ctrl +D) it and spread the word 📢 by sharing it across your friends and colleagues.