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)
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).... :/
Microsoft doesnt maintain Npgsql.EntityFrameworkCore.PostgreSQL
Could you please describe what are the benefits over using linqtodb for postgresql over Npgsql?
Which one is and why?