실무 실습 가이드 · 기준일: 2026년 10월 30일

2편. PostgreSQL 연결과 ASP.NET Core CRUD 구현

PostgreSQL, EF Core, CRUD를 실습합니다.

1. 패키지 설치

프로젝트 폴더에서 EF Core와 PostgreSQL 공급자를 설치합니다. .NET 10과 호환되는 패키지 버전을 맞춥니다.

Set-Location D:\Dev\DockerCourse\TaskApi
dotnet add package Microsoft.OpenApi --version 2.13.0
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 10.0.3
dotnet add package Microsoft.EntityFrameworkCore.Design --version 10.0.14
dotnet tool install --global dotnet-ef --version 10.0.14

EF CLI가 이미 있다면 마지막 명령 대신 dotnet tool update --global dotnet-ef --version 10.0.14를 실행합니다.

2. 모델과 DbContext

Models, Data 폴더를 만들고 파일을 추가합니다.

New-Item -ItemType Directory -Force Models,Data | Out-Null

Models/TaskItem.cs

namespace TaskApi.Models;
public sealed class TaskItem { public int Id { get; set; } public string Title { get; set; } = string.Empty; public bool IsCompleted { get; set; } }

Models/TaskRequests.cs

namespace TaskApi.Models;
public sealed class CreateTaskRequest { public string? Title { get; init; } }
public sealed class UpdateTaskRequest { public string? Title { get; init; } public bool IsCompleted { get; init; } }

Data/TaskDbContext.cs

using Microsoft.EntityFrameworkCore;
using TaskApi.Models;
namespace TaskApi.Data;
public sealed class TaskDbContext(DbContextOptions<TaskDbContext> options) : DbContext(options)
{
 public DbSet<TaskItem> Tasks => Set<TaskItem>();
 protected override void OnModelCreating(ModelBuilder m) {
  var e=m.Entity<TaskItem>(); e.ToTable("tasks"); e.HasKey(x=>x.Id);
  e.Property(x=>x.Title).HasMaxLength(200).IsRequired();
  e.Property(x=>x.IsCompleted).HasDefaultValue(false).IsRequired();
 }
}

3. CRUD API

기존 Program.cs를 교체합니다. 제목은 필수이며 200자 이하여야 합니다.

using Microsoft.EntityFrameworkCore;
using TaskApi.Data;
using TaskApi.Models;
var b=WebApplication.CreateBuilder(args);
b.Services.AddHealthChecks();
b.Services.AddDbContext<TaskDbContext>(o=>o.UseNpgsql(b.Configuration.GetConnectionString("DefaultConnection")!));
var app=b.Build();
app.MapGet("/",()=>Results.Ok(new {service="TaskApi",framework=".NET 10",status="running"}));
app.MapHealthChecks("/health");
app.MapGet("/api/tasks",async (TaskDbContext d)=>Results.Ok(await d.Tasks.AsNoTracking().OrderBy(x=>x.Id).ToListAsync()));
app.MapGet("/api/tasks/{id:int}",async (int id,TaskDbContext d)=>{var t=await d.Tasks.FindAsync(id);return t is null?Results.NotFound():Results.Ok(t);});
app.MapPost("/api/tasks",async (CreateTaskRequest r,TaskDbContext d)=>{var title=r.Title?.Trim();if(string.IsNullOrWhiteSpace(title)||title.Length>200)return Results.BadRequest(new{message="Title required; max 200 characters."});var t=new TaskItem{Title=title};d.Add(t);await d.SaveChangesAsync();return Results.Created($"/api/tasks/{t.Id}",t);});
app.MapPut("/api/tasks/{id:int}",async (int id,UpdateTaskRequest r,TaskDbContext d)=>{var t=await d.Tasks.FindAsync(id);if(t is null)return Results.NotFound();var title=r.Title?.Trim();if(string.IsNullOrWhiteSpace(title)||title.Length>200)return Results.BadRequest();t.Title=title;t.IsCompleted=r.IsCompleted;await d.SaveChangesAsync();return Results.Ok(t);});
app.MapDelete("/api/tasks/{id:int}",async (int id,TaskDbContext d)=>{var t=await d.Tasks.FindAsync(id);if(t is null)return Results.NotFound();d.Remove(t);await d.SaveChangesAsync();return Results.NoContent();});
app.Run();

4. 비밀 값과 Compose 설정

프로젝트 루트에 .env를 만듭니다. 아래 비밀번호는 로컬 학습용 예시이며 운영에 재사용하지 않습니다. .gitignore에는 .env와 backups/를 추가합니다.

POSTGRES_DB=taskdb
POSTGRES_USER=taskuser
POSTGRES_PASSWORD=TaskApi-local-Only-ChangeMe-2026!

compose.yaml

services:
  api:
    build: .
    ports: ["127.0.0.1:5080:8080"]
    environment:
      ASPNETCORE_ENVIRONMENT: Production
      ASPNETCORE_HTTP_PORTS: "8080"
      ConnectionStrings__DefaultConnection: "Host=db;Port=5432;Database=${POSTGRES_DB:?set .env};Username=${POSTGRES_USER:?set .env};Password=${POSTGRES_PASSWORD:?set .env}"
    depends_on:
      db: {condition: service_healthy}
    restart: unless-stopped
  db:
    image: postgres:18
    environment:
      POSTGRES_DB: ${POSTGRES_DB:?set .env}
      POSTGRES_USER: ${POSTGRES_USER:?set .env}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:?set .env}
    ports: ["127.0.0.1:5433:5432"]
    volumes: ["postgres_data:/var/lib/postgresql"]
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U $${POSTGRES_USER} -d $${POSTGRES_DB}"]
      interval: 5s
      timeout: 5s
      retries: 10
      start_period: 10s
    restart: unless-stopped
volumes:
  postgres_data:

API는 db:5432, Windows 마이그레이션은 localhost:5433으로 연결합니다.

5. 마이그레이션과 API 테스트

DB를 실행하고 상태를 확인합니다.

docker compose config --services
docker compose up -d db
docker compose exec db pg_isready -U taskuser -d taskdb
docker compose exec db psql -U taskuser -d taskdb -c "SELECT current_database(), current_user;"

마이그레이션용 연결 문자열을 현재 PowerShell 세션에 설정합니다.

$e=@{}; Get-Content .env | ForEach-Object { if ($_ -match '^\s*([^#=]+)\s*=\s*(.*?)\s*$') { $e[$matches[1].Trim()]=$matches[2].Trim() } }
$env:ConnectionStrings__DefaultConnection="Host=localhost;Port=5433;Database=$($e['POSTGRES_DB']);Username=$($e['POSTGRES_USER']);Password=$($e['POSTGRES_PASSWORD'])"
dotnet build
dotnet ef migrations add InitialCreate
dotnet ef database update
docker compose exec db psql -U taskuser -d taskdb -c "\dt"

API를 다시 빌드하고 CRUD를 테스트합니다.

docker compose up --build -d api
curl.exe -i http://localhost:5080/health
curl.exe -i http://localhost:5080/api/tasks
$t=Invoke-RestMethod -Uri http://localhost:5080/api/tasks -Method Post -ContentType application/json -Body (@{title='Docker CRUD 실습'} | ConvertTo-Json -Compress)
$id=$t.id
Invoke-RestMethod "http://localhost:5080/api/tasks/$id"
Invoke-RestMethod -Uri "http://localhost:5080/api/tasks/$id" -Method Put -ContentType application/json -Body (@{title='수정 완료';isCompleted=$true} | ConvertTo-Json -Compress)
docker compose exec db psql -U taskuser -d taskdb -c 'SELECT "Id","Title","IsCompleted" FROM tasks ORDER BY "Id";'
curl.exe -i -X DELETE "http://localhost:5080/api/tasks/$id"

정상 결과: POST는 201, GET·PUT은 200, DELETE는 204가 정상입니다. DB 결과도 비교합니다.