PostgreSQL with EF

Happy new year ladies and gentlemen. Today is the first day of the 2021 year and vacation day. I prepare new study topics for myself every year and plan to write projects. In this post, you will learn how to use PostgreSQL with entity framework code first. Also, you will learn how to get docker image PostgreSQL and install it. At the same time, you'll learn Entity Framework migrations after that write some basic queries in Visual Studio Code. Let's start.

Important: Please install Visual Studio Code and .Net Core 3.1 or the latest version.

Create Project

Let's create a new Web API project. Here is the project structure. Also please install these packages.

Also here is the appsettings.Development.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "Postgre": "UserID=postgres;Password=123;Server=localhost;Port=5432;Database=Community;Integrated Security=true;Pooling=true;"
  }
}

Migrations

Create Initial Migration.

dotnet ef migrations add InitialCreate -p Infrastructure/ -s API -o Data/Migrations

API :

• Microsoft.EntityFrameworkCore.Design

Infrastructure:

• Microsoft.EntityFrameworkCore.Design
• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Tools
• Npqsql.EntityFrameworkCore.PostgreSQL

Let's update the database.

dotnet ef database update -p Infrastructure/ -s API

If you want to remove migration you can use this command.

dotnet ef migrations remove -p Infrastructure -s API

Open the terminal go to the project location

If you do not install "Microsoft.EntityFrameworkCore.Design" package you'll get this error.

Let's migrate our entity

After this command, InitialMigration will be created

then update database

Pull Image

docker pull postgres

docker run --name postgres -e POSTGRES_PASSWORD="123" -d -p 5432:5432 -v /var/lib/postgresql/data postgres

check docker image status

Install Query Editor

I found a very useful tool for PostgreSQL

Query Editor

{
  "label": "postgre",
  "host": "localhost",
  "user": "postgres",
  "port": 5432,
  "ssl": false,
  "database": "Community",
  "password": "123"
}

Basic Queries

SELECT * FROM "Authors" LIMIT 1000;

Part II
Happy new year 🎄