Chapter 44: Relational Databases for Agents with SQLModel

Build production-ready async database layers for AI agent backends using SQLModel with PostgreSQL.

Overview

Your AI agents need persistent state. When users create tasks, assign projects, or track workers, that data must survive server restarts, scale across instances, and maintain integrity under concurrent access. This chapter teaches you to build the database layer that makes your agent backends reliable.

You'll learn to think like a data architect—identifying entities, applying normal forms, and making informed design decisions—before implementing with SQLModel and async patterns.

Learning Outcomes

By completing this chapter, you will be able to:

1. Design database schemas by identifying entities, relationships, and applying normalization principles
2. Evaluate normalization trade-offs for agent-specific workloads (when to denormalize)
3. Configure async database engines for PostgreSQL with proper pooling and connection management
4. Design SQLModel tables with JSONB columns, foreign keys, and self-referential relationships
5. Implement async CRUD operations using AsyncSession with proper commit/flush patterns
6. Prevent N+1 queries using selectinload and other eager loading strategies
7. Manage database transactions with proper rollback and error handling
8. Create and run migrations using Alembic with async support
9. Build a complete database layer for the Task API running example

Prerequisites

• Chapter 40: FastAPI for Agents - You've built basic APIs and seen SQLModel briefly
• Python async/await - You understand async def, await, and async context managers
• Docker - You can run a PostgreSQL container locally

Technology Stack

Technology	Purpose
SQLModel	ORM combining Pydantic + SQLAlchemy
SQLAlchemy 2.0+	Async database engine
asyncpg	PostgreSQL async driver
PostgreSQL	Production database
Alembic	Database migrations

Lessons

#	Title	Duration	Focus
0	Build Your Database Skill	25 min	Create your relational-db-agent skill
1	Why Agents Need Structured Data	15 min	Persistence, ACID, async motivation
2	Database Design & Normalization	40 min	Entity modeling, ERD diagrams, 1NF-3NF, when to denormalize
3	SQLModel + Async Engine Setup	35 min	Engine creation, pooling deep dive, security, URL conversion
4	Implementing Data Models	35 min	SQLModel syntax, JSONB, indexing deep dive (GIN, composite, partial)
5	Async Session Management	25 min	AsyncSession, dependencies, lifecycle
6	CRUD Operations Pattern	35 min	CRUD with async, soft delete pattern, query optimization
7	Testing Database Code	30 min	pytest-asyncio, fixtures, test isolation
8	Relationships and Eager Loading	40 min	N+1 problem, selectinload, common mistakes
9	Transactions and Error Handling	25 min	Transactions, rollback, error patterns
10	Migrations with Alembic	35 min	Async migrations, backup/recovery, autogenerate
11	Capstone: Complete Database Layer	50 min	Full Task API implementation, multi-tenancy patterns
12	Chapter Quiz	20 min	18 questions including normalization

Total time: ~410 minutes (6.8 hours)

The Design-First Approach

This chapter follows a design-first methodology:

1. L02: Think Like a Data Architect - Before writing any SQLModel code, you'll learn to:

   • Identify entities from requirements
   • Create ERD diagrams with Mermaid
   • Map relationships and cardinalities
   • Apply normal forms (1NF, 2NF, 3NF)
   • Decide when to denormalize for performance

2. L03-L10: Implementation - With a solid design, implementation becomes straightforward:

   • Engine setup with production pooling and security
   • Models with advanced indexing (GIN, composite, partial)
   • Testing patterns for database code
   • Common mistakes and how to avoid them
   • Backup/recovery strategies

3. L11: Integration - Combine design and implementation into a production-ready layer with multi-tenancy patterns

Running Example

This chapter deepens the Task API you started in Chapter 40. You'll build a complete database layer with:

• Task model with JSONB columns and relationships
• Project model for organizing tasks
• Worker model (human and AI agents)
• Self-referential parent/child task relationships
• Eager loading for efficient queries
• Transaction handling for complex operations

Skill-First Approach

You'll start by creating your own relational-db-agent skill in Lesson 0. This skill grows with you—each lesson ends with a "Reflect on Your Skill" section where you test and improve your skill based on what you learned.

By chapter end, you'll have a production-ready skill that thinks like a data architect AND generates correct async SQLModel code.