.NET 6 with PostgreSQL
Tags: dotnet, postgres, database, tutorial
Published: 2022-08-14 08:09:46

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

{% embed https://youtu.be/FoYsxFBY1os %}

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

```bash
brew install postgresql
```

Starting and Stoping Postgres is running

```bash
brew services start postgresql 
brew services stop postgresql 
```

Connect with Postgres

```bash
psql postgres
```

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

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

Create a Database

```bash
create database sampledb;
```

Grand permission to the db

```bash
GRANT CONNECT ON DATABASE sampledb TO mohamad;
```

Installing EF tool

```bash
dotnet tool install --global dotnet-ef
```

Creating the .NET web application

```bash
dotnet new webapi -n DbExploration
```

Installing Packages

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

Update AppSettings with connection string

```bash
"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

```csharp
using Microsoft.EntityFrameworkCore;

namespace DbExploration.Data;

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

Adding connection string to Program.cs

```bash
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

```csharp
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

```csharp
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

```csharp
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

```csharp
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

```csharp
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

```csharp
dotnet ef migrations add "initial_migrations"
dotnet ef database update
```

Changing Table Name in the d