ORM and Query Layers: Mastering SQLModel

Writing raw SQL (e.g., SELECT * FROM users) in your application code is the "Old Way." It's prone to typos, vulnerable to SQL Injection, and hard to maintain across different databases.

The "New Way" is using an ORM (Object Relational Mapper). In the FastAPI ecosystem, the gold standard is SQLModel.

---

1. What is SQLModel?

SQLModel is a library for interacting with SQL databases from Python code, with Python objects. It was built by the same creator as FastAPI.

The Problem it Solves:

Normally, you need two separate models:

1. A Pydantic Model for data validation and API documentation.
2. An SQLAlchemy Model for database tables.

The Solution:

SQLModel merges them. One class defines both your API schema and your database table.

from sqlmodel import Field, SQLModel, create_engine

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    username: str = Field(index=True)
    email: str
    hashed_password: str

---

2. The Repository Pattern

Instead of putting your database logic directly in your route handlers, you should use the Repository Pattern. This means creating a specific set of functions (often called "CRUD" for Create, Read, Update, Delete) that handle the database work.

from sqlmodel import Session, select

def get_user_by_username(db: Session, username: str):
    statement = select(User).where(User.username == username)
    results = db.exec(statement)
    return results.first()

---

3. Relationships: One-to-Many and Many-to-Many

Real data isn't flat. Users have Posts, and Posts have Tags. SQLModel makes defining these relationships intuitive using Python type hints.

from typing import List
from sqlmodel import Relationship

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    # Define a relationship to Posts
    posts: List["Post"] = Relationship(back_populates="author")

class Post(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    author_id: int = Field(foreign_key="user.id")
    # Define the back-link to the User
    author: User = Relationship(back_populates="posts")

---

4. Query Performance (The "N+1" Problem)

When working with ORMs, it's easy to accidentally write slow code. For example, grabbing 10 posts and then making 10 separate database calls to get the author for each post. This is called the N+1 problem.

The Solution: Use Eager Loading (e.g., selectinload) to tell SQLModel to fetch the authors along with the posts in a single, efficient query.

---

Visualizing the ORM Bridge

graph LR
    A["Python Code"] -- SQLModel --> B["SQL Table"]
    A -- Pydantic --> C["JSON API"]
    
    subgraph "Single Source of Truth"
    D["SQLModel Class"]
    end
    
    D -.-> A
    D -.-> B
    D -.-> C

---

Summary

• SQLModel: Merges Pydantic and SQLAlchemy for the ultimate FastAPI experience.
• table=True: The flag that turns a Pydantic model into a database table.
• CRUD Functions: Centralize your data access logic for better testing.
• Relationships: Define how models link together using type hints.

In the next lesson, we’ll look at the Database Lifecycle and Migrations, ensuring your database can evolve alongside your code.

---

Exercise: The Relationship Builder

Design a model for a Comment.

1. It must belong to a User (The commenter).
2. It must belong to a Post.
3. How would the User model change to include a list of their comments?