Luna Tech

Tutorials For Dummies.

Deploy a Dotnet Web API with Postgresql Integration

2022-04-10

0. Objectives

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

Resources

Deploy a Dotnet Web API on Ubuntu with Nginx Reserve Proxy - Luna Tech (lunawen.com)

lunawen/dotnetwebapi-postgresql (github.com)

1. Setup Postgresql on your local machine

Install Postgresql and make sure the server is started

Check out other tutorials for your local setup

Login as the default user (macOS)

psql -U postgres
# show all dbs
\l
# show all users
\du
# show tables
\dt
# exit
\q

Create a new user (macOS)

psql -U postgres
# create a new user
create user dotnetwebapi with superuser password 'dotnetwebapi';
# create a new db
create database dotnetwebapi;
# exit
\q
# login as the newly created user
psql -U dotnetwebapi -d dotnetwebapi

2. dotnet webapi setup

Install dotnet-ef tools

dotnet tool install --global dotnet-ef
# verify
dotnet ef

Install EF Core as ORM

ORM connects C# objects to postgresql db records.

dotnet add package Microsoft.EntityFrameworkCore

Add Connection String

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Port=5432;Database=dotnetwebapi;User Id=dotnetwebapi;Password=dotnetwebapi"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

Install postgresql DB provider

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL

Install EF Core DB Migration packages

dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore

Make some code changes

Note: you can download this repo for quickstart

lunawen/dotnetwebapi-postgresql (github.com)

Add DB migration

dotnet ef migrations add firstmigration --project dotnetwebapi.csproj

You might need to update the EF tool version

dotnet tool update --global dotnet-ef

Check pending migrations

dotnet ef migrations list --project dotnetwebapi.csproj

Update database with pending migrations

dotnet ef database update firstmigration --project dotnetwebapi.csproj

Add record in db

# go to the dotnetwebapi db (macOS)
psql -U dotnetwebapi -d dotnetwebapi
# insert a record
insert into "WeatherForecasts" ("Date", "TemperatureC") values (CURRENT_TIMESTAMP, 20);
# check
select * from "WeatherForecasts";

Run the app and you should see data being displayed

http://localhost:xxxx/weatherforecast

3. Setup Postgresql on ubuntu remote server

Note: please read Deploy a Dotnet Web API on Ubuntu with Nginx Reserve Proxy - Luna Tech (lunawen.com) before you continue with the following steps

Install Postgresql

# install
sudo apt install postgresql postgresql-contrib
# start the service
sudo systemctl start postgresql.service

Login as the default user

sudo -u postgres psql
# show all dbs
\l
# show all users
\du
# show tables
\dt
# exit
\q

Create a new user and a new db

sudo -u postgres psql
# create a new user
create user dotnetwebapi with superuser password 'dotnetwebapi';
# create a new db
create database dotnetwebapi;
# exit
\q
# login as the newly created user
sudo -u dotnetwebapi -d dotnetwebapi

4. Deploy to remote server

Create migration script (run on your local project path)

dotnet ef migrations script --output "migration.sql" --idempotent

Create published files (run on your local project path)

dotnet publish --configuration Release -o published

Copy the published files to remote server

Check out this article if you don’t know how.

Setup the postgresql on remote server

Note:

Run the migration script after logged in on the remote db

# login as the newly created user
psql -U dotnetwebapi -d dotnetwebapi

Run the dotnet app (remote)

Test the endpoint with curl (on your ubuntu server)

curl -v http://localhost:5000/weatherforecast

Test the url from your browser

If everything goes well, setup pm2

pm2 start "dotnet dotnetwebapi.dll" --name dotnetwebapi
# check
pm2 list

5. Summary