Sharing a new article: “For an application based on FastAPI + SQLAlchemy ORM, is it necessary to switch to FastAPI + SQLModel?” https://juejin.cn/post/7648902852933304360…

For an application based on FastAPI + SQLAlchemy ORM, is it necessary to switch to FastAPI + SQLModel?

Source: https://juejin.cn/post/7648902852933304360 In Python web development (especially the FastAPI + SQLAlchemy ecosystem), Models and Schemas are two frequently mentioned concepts with completely different responsibilities.

In simple terms: Models are responsible for “how to store data in the database,” while Schemas are responsible for “how to validate and filter data for API input and output.”

I. Core Concept Comparison

II. A Vivid Analogy for Understanding

Imagine you are running an exclusive private club (system):

• Models are the “employee files” inside the club:- They contain employees’ ID numbers, real names, home addresses, contract numbers, bank card numbers (equivalent to all sensitive and physical fields in the database).
  - Only the manager (database/ORM) can see this file; it must never be shown to guests.
• Schemas are the “employee name badges”:- The badge only shows: stage name (Nick Name), job position (exposes only the information guests need to see).
  - Additionally, when a guest wants to join, they fill out a “membership application form” (input Schema), and the format needs to be checked before the badge is made (data validation).

III. Data Flow Diagram (Their Position in the System)

In a complete API request, each stands guard at a different checkpoint:

📥 [Client Request (JSON)]
              │
              ▼
   ┌──────────────────────┐
   │ Schemas (Pydantic)   │ <--- 1. Intercept and validate data (e.g., Is the password long enough? Is the Email format correct?)
   └──────────────────────┘
              │ (Validation passes, convert to Python object)
              ▼
   ┌──────────────────────┐
   │ Service / Controller │ <--- 2. Business logic processing
   └──────────────────────┘
              │ (Convert to DB Model object)
              ▼
   ┌──────────────────────┐
   │ Models (SQLAlchemy)  │ <--- 3. Map to a database table row, write to DB
   └──────────────────────┘
              │
              ▼
       💾 [Database (DB)]

IV. Code Example Comparison

1. Models Implementation (using SQLAlchemy)

It focuses on underlying database details.

# app/models/user.py
from sqlalchemy import Column, Integer, String, Boolean
from app.database import Base

class DBUser(Base):
    __tablename__ = "users"  # Physical table name

    id = Column(Integer, primary_key=True, index=True) # Database primary key
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=False) # Database stores hashed ciphertext
    is_active = Column(Boolean, default=True)

2. Schemas Implementation (using Pydantic)

It focuses on API input and output.

# app/schemas/user.py
from pydantic import BaseModel, EmailStr, Field

# Data sent by client during registration (Input Schema)
class UserCreate(BaseModel):
    email: EmailStr  # Automatically validates that the format is a valid email
    password: str = Field(min_length=8)  # Forces password to be at least 8 characters

# Data returned to the client (Output Schema / DTO)
class UserResponse(BaseModel):
    id: int
    email: EmailStr
    # 🚫 Note: Never write password or hashed_password here
    # This ensures sensitive password information never leaks to the frontend through the API

V. Why Can SQLModel Combine the Two?

Because in traditional development, DBUser and UserResponse have a lot of field duplication (e.g., id, email). Modifying one field requires changing two files.

SQLModel cleverly uses Python’s multiple inheritance to let one class play both roles simultaneously:

• When it includes table=True, it acts as a Model (generates database table).
• When it does not include table=True, it acts as a Schema (performs API validation and filtering).

For an existing project that is stably running on FastAPI + SQLAlchemy ORM, there is usually no absolute necessity to perform a full refactoring to upgrade to SQLModel.

Although SQLModel brings an excellent development experience, the refactoring cost and potential risks of a full migration often outweigh the direct benefits it provides.

Here is an in-depth comparative analysis of migration benefits, costs/risks, and decision recommendations:

I. Core Benefits of Migrating to SQLModel (Why do it?)

If you choose to migrate, you will gain the following three most significant “sweet spots”:

1. Eliminate Dual Definitions, Halve the Code (DRY - Don’t Repeat Yourself)

• Current State (SQLAlchemy): Adding a new table field requires modifying models.py (SQLAlchemy), schemas.py (UserCreate), and schemas.py (UserResponse). It’s easy to miss an update.
• SQLModel: You only need to add one line in the BaseModel. Database migration and API input/output validation will automatically synchronize and take effect.

2. Perfect IDE Autocomplete and Type Safety (Developer Experience)

• Current State (SQLAlchemy): Executing db.execute(select(User)) returns a Result object. You must convert it via .scalars().all(), and the IDE (VSCode/PyCharm) often cannot infer that the returned list contains User entities, resulting in no property hints when writing subsequent code.
• SQLModel: db.exec(select(User)).all() will be accurately recognized by the IDE as list[User]. From query to field access, the autocomplete experience is excellent throughout.

3. Native Seamless Integration with FastAPI

• SQLModel is developed by FastAPI’s author, Tiangolo, himself. SQLModel instances can be directly returned as FastAPI’s response_model or directly received as Request Body, saving many conversion steps like BaseModel.model_validate(db_obj).

II. Costs and Potential Risks of Migration (The Cost & Risk)

1. Compatibility Risks with Advanced ORM Features

Although SQLModel is built on top of SQLAlchemy, it wraps some complex advanced features of SQLAlchemy. If your project heavily uses the following features, you will encounter significant resistance during migration:

• Multi-table inheritance (e.g., Joined Table Inheritance, Polymorphic Queries).
• Complex composite primary/foreign key relationships.
• Highly customized relationship attributes (e.g., custom association proxies or non-standard join conditions).
• In these scenarios, SQLModel may have type system conflicts or force you to regress to writing sa_column = Column(...), thus losing the simplicity that SQLModel originally offers.

2. Workload for Existing Code and Testing Regression

• Modify table definitions: All models inheriting from declarative_base() must be refactored to inherit from SQLModel(table=True). All Column fields must be rewritten as Field.
• Modify query statements: Although old SQLAlchemy queries (db.execute(...)) are still compatible in SQLModel, you will likely need to gradually rewrite them to db.exec(...) for type safety.
• Alembic migration scripts: Generated database migration scripts (Migrations) need careful handling after changing the model base class to prevent Alembic from mistakenly detecting tables as being dropped and recreated.

III. Decision Matrix: How to Choose?

💡 Final Recommendation

• If the current project runs stably and has no frequent model change requirements, do not modify it.
• If you are suffering from “API DTO and database Model fields being out of sync” and “IDE has no SQL hints”, you can first pick a non-core sub-business module for a partial refactoring with SQLModel, verify the results, and then decide whether to roll it out fully.

Comparing the low-level native driver (psycopg2) with high-level ORM (SQLAlchemy / SQLModel) is essentially a trade-off between “extreme performance and fine-grained control” and “development efficiency and engineering maintainability”.

Here is an in-depth comparison of the pros and cons of the two:

Compared to using psycopg2 directly, what are the pros and cons of using FastAPI + SQLAlchemy ORM or FastAPI + SQLModel?

I. Direct Use of psycopg2 (Native Driver)

psycopg2 is a low-level C language driver for PostgreSQL that executes raw SQL directly.

Advantages:

1. Extreme Performance (Zero Overhead): No ORM framework in the middle does “SQL translation” and “object instantiation (Row -> Python Object)” overhead. Performance nearly reaches the physical limit of Python operating PostgreSQL.
2. Full Control Over SQL: You can write any complex PostgreSQL-specific syntax (e.g., window functions, recursive CTEs, JSONB operations, full-text search) without having to research the corresponding weird syntax of an ORM.
3. Lighter Weight: Fewer project dependencies, faster startup, and no complex ORM state management (e.g., pitfalls from Session states, lazy loading, etc.).

Disadvantages:

1. Extremely Low Development Efficiency: Even simple CRUD operations require writing complete SQL statements manually, and you must handle placeholders (e.g., %s) manually to prevent SQL injection.
2. Lack of Type Safety and Autocomplete: SQL is written as string literals in the code. An IDE (e.g., VSCode/PyCharm) cannot perform syntax checking, autocomplete, or refactoring on fields within SQL strings.
3. Manual Result Set Mapping: psycopg2 by default returns a list of tuples (list[tuple]) or dictionaries (dict). You must write your own code to parse and map them into Python objects or JSON, which is error-prone.
4. Synchronous and Blocking (Conflicts with FastAPI): psycopg2 is a synchronous blocking driver. In FastAPI’s asynchronous event loop, directly executing slow queries will block the entire service. Although this can be mitigated with thread pools or switching to psycopg3/asyncpg, it increases development complexity.

II. Using FastAPI + SQLAlchemy / SQLModel (High-Level ORM)

Advantages:

1. Extremely High Development Efficiency: Provides an object-oriented operation approach. For example, creating a record is just db.add(user); querying is just select(User), without writing tedious INSERT/UPDATE statements.
2. Automatic Data Validation and Serialization (with Pydantic):- Input: FastAPI automatically validates and converts frontend JSON into Pydantic/SQLModel objects.
   - Output: Returns ORM objects directly; FastAPI automatically filters sensitive fields (e.g., passwords) and serializes them to JSON.
3. Seamless Async/Await Support: SQLAlchemy (v1.4+) and SQLModel perfectly support async context (with the asyncpg driver), fitting flawlessly with FastAPI’s asynchronous architecture without blocking the event loop.
4. Database Migration Tool (Alembic): You can automatically generate database schema migration scripts (Schema Migrations) from Python code (Models), making version control extremely simple.
5. Defense Against SQL Injection: The ORM automatically uses parameterized queries under the hood, eliminating most SQL injection risks at the source.

Disadvantages:

1. Performance Overhead: Due to multiple layers of wrapping (SQL generation, result set object mapping, Pydantic validation, etc.), CPU and memory usage in high-concurrency, high-throughput scenarios is significantly higher than psycopg2.
2. N+1 Query Pitfall: If you are not familiar with ORM’s “Lazy Loading” mechanism, it’s easy to write code that queries the database in a loop, generating many unnecessary database connection requests and drastically slowing down the system.
3. Steep Learning Curve: SQLAlchemy is an extremely large and complex library. The learning cost and troubleshooting effort when dealing with complex joins, transaction isolation levels, and connection pool tuning can be high.

III. Core Dimension Comparison Table

IV. Engineering Practice Recommendations: How to Choose?

In real-world enterprise FastAPI development, we rarely go to either extreme. We usually adopt a Hybrid Approach:

1. 80% of business (standard CRUD / business logic): Use FastAPI + SQLModel / SQLAlchemy.
   - Benefit from automatic validation, rapid development, type completion, and Alembic automatic migrations.
2. 20% of business (high performance / reports / complex joins): Execute raw SQL within the ORM Session.
   - SQLAlchemy allows you to execute raw SQL directly via session.execute(text("SELECT ...")). This retains the ORM’s connection pool management while gaining the extreme performance and flexibility of raw SQL.
   - Note: If using only raw SQL, it’s recommended to use asyncpg instead of synchronous psycopg2 in FastAPI.