Week 3–4: Database with PostgreSQL

Goal

Replace the in-memory list with a real PostgreSQL database running in Docker. Learn Entity Framework Core and database migrations.

Essential (75% of time)

Core skills:

• Run PostgreSQL in Docker
• Create a DbContext with Entity Framework Core
• Create and apply migrations
• Replace in-memory List with database queries
• Set up a one-to-many relationship (Author → Books)

Start PostgreSQL locally

docker run --name books-db \
  -e POSTGRES_USER=bookuser \
  -e POSTGRES_PASSWORD=dev123 \
  -e POSTGRES_DB=bookdb \
  -p 5432:5432 \
  -v books-data:/var/lib/postgresql/data \
  -d postgres:16

The -v flag creates a persistent volume so your data survives container restarts.

Key resources

• PostgreSQL Docker Image
• EF Core Getting Started
• Npgsql EF Core Provider
• EF Core Migrations
• Relationships in EF Core
• Docker Getting Started

LLM prompts

“Show me step-by-step: install EF Core with PostgreSQL, create a DbContext, configure connection string in appsettings.json.”

“Create Book and Author entities where Author has many Books. Include navigation properties for Entity Framework Core.”

“Explain the migration workflow: how to create a migration, view the SQL, and apply it to PostgreSQL. What commands do I run?”

“Replace this in-memory code with EF Core: var book = books.FirstOrDefault(b => b.Id == id). Use async/await.”

Brief (15% of time)

• View database tables in Azure Data Studio
• Seed initial test data
• Use Include() to load related data

Optional (skip if needed)

• Understanding the generated SQL
• Migration rollback
• PostgreSQL-specific features

Practical project

Convert the Books API to use a real PostgreSQL database with an Authors table.

Learning activities

1. Start the PostgreSQL container using the command above
2. Verify it is running: docker ps
3. Install NuGet packages: Npgsql.EntityFrameworkCore.PostgreSQL and Microsoft.EntityFrameworkCore.Design
4. Create Author and Book entity classes with a navigation property
5. Create a BooksDb DbContext with DbSet<Book> and DbSet<Author>
6. Add connection string to appsettings.json
7. Register DbContext in Program.cs
8. Run dotnet ef migrations add InitialCreate
9. Run dotnet ef database update
10. Connect to the database in Azure Data Studio and view the created tables
11. Replace all List<Book> code in endpoints with async DbContext calls
12. Test all endpoints still work in Swagger

Useful Docker commands

docker ps                          # Check if container is running
docker stop books-db               # Stop the container
docker start books-db              # Start it again (data is preserved)
docker logs books-db               # View logs if something is wrong

Key concepts to master

• Starting, stopping, and checking Docker containers
• DbContext setup and registration in Program.cs
• Entity classes and navigation properties
• One-to-many relationships and foreign keys
• Migration commands: migrations add, database update
• Async database operations: ToListAsync(), FindAsync(), SaveChangesAsync()

Deliverable

Books API reads and writes from PostgreSQL. Authors table exists with a relationship to Books.

---

Week 1–2: Minimal APIs | Back to Overview | Week 5: Validation