.NET 6 with PostgreSQL

#dotnet #postgres #database #tutorial

In this article, we will be exploring how to utilise PostgreSQL with .NET 6. The steps we need need to take to setup, configure and utilise PostgreSQL with .NET 6 WebAPI.

You can watch the full video on YouTube

You can also find the source code on Github
https://github.com/mohamadlawand087/Net6-PostgreSQL

First we will be focusing on installing PostgreSQL on our machine.

Installing Postgres on your machine

brew install postgresql

Starting and Stoping Postgres is running

brew services start postgresql 
brew services stop postgresql

Connect with Postgres

psql postgres

Create User and Pass so we can utilise them in our .NET app

CREATE ROLE mohamad WITH LOGIN PASSWORD '12345678';
ALTER ROLE mohamad CREATEDB;
ALTER ROLE mohamad WITH Superuser;

Create a Database

create database sampledb;

Grand permission to the db

GRANT CONNECT ON DATABASE sampledb TO mohamad;

Installing EF tool

dotnet tool install --global dotnet-ef

Creating the .NET web application

dotnet new webapi -n DbExploration

Installing Packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools

Update AppSettings with connection string

"ConnectionStrings": {
    "SampleDbConnection": "User ID =mohamad;Password=12345678;Server=localhost;Port=5432;Database=sampledb; Integrated Security=true;Pooling=true;"
  }

Let us add our db context, we create a folder called Data and inside the folder we create a class called ApiDbContext

using Microsoft.EntityFrameworkCore;

namespace DbExploration.Data;

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

Adding connection string to Program.cs

builder.Services.AddEntityFrameworkNpgsql().AddDbContext<ApiDbContext>(opt =>
        opt.UseNpgsql(builder.Configuration.GetConnectionString("SampleDbConnection")));

Now start by adding the Models folder, inside the Models folder we will create our first Model would be a generic class called BaseEntity

public abstract class BaseEntity
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public DateTime UpdatedDate { get; set; } = DateTime.UtcNow;
    public string UpdatedBy { get; set; } = "";
    public string AddedBy { get; set; } = "";
    public DateTime AddedDate { get; set; } = DateTime.UtcNow;
    public int Status { get; set; } = 1;
}

Next we will need to create a new model called Team

public class Team : BaseEntity
{
    public Team()
    {
        Drivers = new HashSet<Driver>();
    }

    public string Name { get; set; } = "";
    public string Year { get; set; } = "";

    public virtual ICollection<Driver> Drivers { get; set; }
}

Next model would be the driver

public class Driver : BaseEntity
{
    public Guid TeamId { get; set; }
    public string Name { get; set; } = "";
    public int RacingNumber { get; set; } 
    public virtual Team Team { get; set; }
}

Next model would be DriverMedia

public class DriverMedia
{
    public int Id { get; set; }
    public byte[] Media { get; set; }
    public string Caption { get; set; }

    public Guid DriverId { get; set; }
    public Driver Driver { get; set; }
}

Now we update the Db context, we will be utilising fluent API

public class ApiDbContext : DbContext
{
    public virtual DbSet<Driver> Drivers { get; set; }
    public virtual DbSet<Team> Teams { get; set; }

    public ApiDbContext(DbContextOptions<ApiDbContext> options):base(options) {  }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);


        modelBuilder.Entity<Driver>(entity =>
        {
                        // One to Many relationship
            entity.HasOne(d => d.Team)
                .WithMany(p => p.Drivers)
                .HasForeignKey(d => d.TeamId)
                .OnDelete(DeleteBehavior.Restrict)
                .HasConstraintName("FK_Driver_Team");

                        // One to One
                        entity.HasOne(d => d.DriverMedia)
                    .WithOne(i => i.Driver)
                    .HasForeignKey<DriverMedia>(b => b.DriverId);
        });


    }
}

To create the tables in the database from our .Net application we need to do the followigin

dotnet ef migrations add "initial_migrations"
dotnet ef database update

Changing Table Name in the d

Top comments (3)

mochsner • Nov 5 '22

Have you had any experience with supporting Postgres in production vs something like MS-SQL? Wanting to try this integration for an application, but worried about Microsoft's EF support for a "second class" citizen (in their mind).... :/