Posted inPython

SQLModel: The ‘2-in-1’ Solution for Cleaning Up the Mess Between SQLAlchemy and Pydantic

Python tutorial - IT technology blog
Python tutorial - IT technology blog

The “Model Duplication” Nightmare

If you’ve ever built an API with FastAPI and SQLAlchemy, you’ve likely experienced the frustration of having to define the same data structure twice.

First, you write a SQLAlchemy class to map to the database. Immediately after, you have to copy almost that exact same class over to Pydantic to validate input data and format JSON output.

# SQLAlchemy Model (for Database)
class UserDB(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    username = Column(String, index=True)
    email = Column(String, unique=True)

# Pydantic Model (for API)
class UserOut(BaseModel):
    id: int
    username: str
    email: str

With just 1 or 2 tables, it’s manageable. But as a project scales to 50-100 tables, maintaining synchronization becomes a massive burden. If you change a data type from String to Text in the DB but forget to update Pydantic, the system will throw a 500 error at your users immediately.

Why Do We Have to Suffer Like This?

The problem lies in their differing goals. SQLAlchemy focuses on converting objects into SQL statements. Meanwhile, Pydantic excels at parsing data and JSON type casting. These two libraries weren’t originally designed to understand each other.

In a real project I worked on, refactoring 30 database fields took an entire afternoon. Most of that time was spent finding and fixing the corresponding Pydantic classes to avoid schema mismatches. That’s when we need unification.

Common Workarounds

Before SQLModel arrived, developers usually chose one of two ways:

  • Manual Copy-Paste: This is fast at first but acts as a “time bomb” for maintenance.
  • Using conversion libraries (like pydantic-sqlalchemy): These tools work reasonably well but often struggle with new Pydantic v2 features or complex relationships.

Neither approach solves the root cause: you’re still managing two different “Sources of Truth” for the same entity.

SQLModel: One Class, Two Responsibilities

SQLModel is the brainchild of Tiangolo (the creator of FastAPI). This library inherits directly from both SQLAlchemy and Pydantic. Its goal is clear: transform a single Python class into both a database model and a data validation schema.

Quick Installation

You only need to install a single package. If you want to quickly test with SQLite, no complex drivers are required.

pip install sqlmodel

Defining a “Hybrid” Model

The magic lies in the table=True parameter. It signals to SQLModel that this class should be mapped to the database.

from typing import Optional
from sqlmodel import Field, SQLModel, create_engine, Session, select

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None

Now, Hero plays a dual role. You use it to receive data from FastAPI’s request body and simultaneously use it to save directly to the database without any conversion back and forth.

Safe Data Manipulation

A hard-learned lesson: always use Session within a context manager (the with block). This ensures the connection is closed as soon as the work is done, preventing database hangs due to connection leaks.

def create_hero():
    hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
    
    with Session(engine) as session:
        session.add(hero_1)
        session.commit()
        session.refresh(hero_1) # Get the ID that was automatically generated by the DB
        print("New Hero:", hero_1)

Layering Models in Practice

Never expose your entire database to the API. Sensitive information like hashed_password should be hidden. SQLModel supports highly flexible inheritance to handle this.

I usually organize code into 3 layers to ensure security:

  1. Base: Contains common fields visible to everyone.
  2. Create: Contains fields used only during registration (like password).
  3. Table: The actual class mapped to the DB, containing additional private fields.
class HeroBase(SQLModel):
    name: str
    age: Optional[int] = None

class HeroCreate(HeroBase):
    password: str # Used only when receiving data from the client

class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    hashed_password: str # Saved to the DB, not returned to the API

Why Should You Use SQLModel Now?

If you’re starting a new FastAPI project, choose SQLModel. It reduces boilerplate code by up to 50%. More importantly, its IntelliSense support in VS Code works flawlessly. You’ll never run into typos in field names without the IDE warning you beforehand.

SQLModel doesn’t just make code shorter. It makes your code cleaner, more readable, and most importantly, easier to maintain in the long run. Try applying it to a small module, and you’ll see the difference immediately.

Share:
Tags: