DEV Community

loading...
Cover image for PostgreSQL with EF

PostgreSQL with EF

bseyhan profile image bseyhan Updated on ・2 min read

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.

Screen Shot 2021-01-01 at 7.55.26 PM

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

Enter fullscreen mode Exit fullscreen mode

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

Screen Shot 2021-01-01 at 7.59.43 PM

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

Screen Shot 2021-01-01 at 8.01.18 PM

Let's migrate our entity

Screen Shot 2021-01-01 at 8.03.11 PM

After this command, InitialMigration will be created

Screen Shot 2021-01-01 at 8.21.41 PM

then update database

Screen Shot 2021-01-01 at 8.20.13 PM

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

Screen Shot 2021-01-01 at 8.26.55 PM

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

Screen Shot 2021-01-01 at 9.06.59 PM

Basic Queries

SELECT * FROM "Authors" LIMIT 1000;

Screen Shot 2021-01-01 at 9.06.40 PM

Part II
Happy new year 🎄

Discussion (0)

pic
Editor guide