DEV Community

Cover image for .NET 6 with PostgreSQL
Mohamad Lawand
Mohamad Lawand

Posted on

.NET 6 with PostgreSQL

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
Enter fullscreen mode Exit fullscreen mode

Starting and Stoping Postgres is running

brew services start postgresql 
brew services stop postgresql 
Enter fullscreen mode Exit fullscreen mode

Connect with Postgres

psql postgres
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Create a Database

create database sampledb;
Enter fullscreen mode Exit fullscreen mode

Grand permission to the db

GRANT CONNECT ON DATABASE sampledb TO mohamad;
Enter fullscreen mode Exit fullscreen mode

Installing EF tool

dotnet tool install --global dotnet-ef
Enter fullscreen mode Exit fullscreen mode

Creating the .NET web application

dotnet new webapi -n DbExploration
Enter fullscreen mode Exit fullscreen mode

Installing Packages

dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet add package Microsoft.EntityFrameworkCore.Tools
Enter fullscreen mode Exit fullscreen mode

Update AppSettings with connection string

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

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) {  }
}
Enter fullscreen mode Exit fullscreen mode

Adding connection string to Program.cs

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

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;
}
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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; }
}
Enter fullscreen mode Exit fullscreen mode

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);
        });


    }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Changing Table Name in the d

Top comments (3)

Collapse
 
mochsner profile image
mochsner

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).... :/

Collapse
 
stianhave profile image
Stian Håve

Microsoft doesnt maintain Npgsql.EntityFrameworkCore.PostgreSQL

Collapse
 
darshana2000 profile image
darshana2000

Could you please describe what are the benefits over using linqtodb for postgresql over Npgsql?

Which one is and why?