SQLAlchemy Models — Designing Database Tables
In this chapter, you will learn to define data models using native SQLAlchemy ORM and manage database sessions through FastAPI dependency injection.
Why doesn't FastAPI have a built-in ORM?
Django has a built-in ORM, while Flask recommends Flask-SQLAlchemy.
FastAPI doesn't have a built-in ORM—it wants you to choose the most suitable tool,SQLAlchemy + Pydanticis the best combination recognized by the community.
(venv) $ pip install sqlalchemy
Configure database connection
Example
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, DeclarativeBase
# SQLite database file path
SQLALCHEMY_DATABASE_URL = "sqlite:///./blog.db"
# Creating engine: check_same_thread is a SQLite-specific parameter
engine = create_engine(
SQLALCHEMY_DATABASE_URL,
connect_args={"check_same_thread": False} # SQLite-specific: allow cross-threading
)
# Create session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
# Base class: all ORM models inherit from it
class Base(DeclarativeBase):
pass
check_same_thread=Falseis the required configuration for SQLite in FastAPI. By default, SQLite does not allow the same connection to be used across threads, but in web applications, each request may be handled in a different thread.
Dependency injection: get_db
Example
from fastapi import Depends
def get_db():
"""FastAPI dependency injection: create an independent database session for each request"""
db = SessionLocal()
try:
yield db # Use this session during the request
finally:
db.close() # Automatically close the session after the request ends to prevent connection leaks
Depends(get_db)is FastAPI's dependency injection system, where route functions automatically obtain database sessions through dependency injection.
Define models: Post and Category
Example
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey
from sqlalchemy.orm import relationship
from datetime import datetime
from database import Base
class Category(Base):
"""Article category"""
__tablename__ = "categories"
id = Column(Integer, primary_key=True, index=True)
name = Column(String(50), unique=True, nullable=False)
slug = Column(String(50), unique=True, nullable=False)
# back_populates bidirectional relationship
posts = relationship("Post", back_populates="category")
class Post(Base):
"""Blog Post"""
__tablename__ = "posts"
id = Column(Integer, primary_key=True, index=True)
title = Column(String(200), nullable=False)
slug = Column(String(200), unique=True, nullable=False)
summary = Column(Text, default="")
content = Column(Text, nullable=False)
category_id = Column(Integer, ForeignKey("categories.id"), nullable=False)
created_at = Column(DateTime, default=datetime.utcnow)
updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
# Bidirectional relationship with Category
category = relationship("Category", back_populates="posts")
What is used in FastAPI isNative SQLAlchemy(not Flask-SQLAlchemy's
db.Model). Note the differences in Column imports, Base inheritance, and relationship syntax. A comparison table will be provided later.
Initialize database tables
Example
from fastapi import FastAPI
from database import engine, Base
from models import Category, Post # Ensure the model classes are imported so that Base.metadata can recognize them
app = FastAPI()
@app.on_event("startup")
def on_startup():
"""应用启动时自动创建数据库表（仅开发使用）"""
Base.metadata.create_all(bind=engine)
Later, we will switch to Alembic migrations (Chapter 6),create_allOnly used in the rapid prototyping stage.
Insert test data
Insert test data in a Python script:
Example
from database import SessionLocal
from models import Category, Post
db = SessionLocal()
# Create Category
py = Category(name="Python", slug="python")
css = Category(name="CSS", slug="css")
fastapi = Category(name="FastAPI", slug="fastapi")
db.add_all([py, css, fastapi])
db.commit()
# Create Article
db.add_all([
Post(title="The Complete Beginner's Guide to FastAPI", slug="fastapi-guide",
summary="Learn FastAPI from Scratch", content="<h2>Why choose FastAPI?</h2><p>...</p>",
category_id=fastapi.id),
Post(title="Python Coroutines Explained", slug="python-coroutine",
summary="Understand asyncio", content=<h2>What is a coroutine?</h2><p>...</p>,
category_id=py.id),
Post(title="CSS Grid Layout in Action", slug="css-grid",
summary="Implement Responsive Layout with Grid", content="<h2>Introduction to Grid</h2><p>...</p>",
category_id=css.id),
])
db.commit()
db.close()
print("Test data insertion completed!")
Runpython seed.py, insert 3 articles into the database.
Comparison of ORM syntax across four frameworks.
| Operation | Django | Flask-SQLAlchemy | FastAPI (native SQLAlchemy) |
|---|---|---|---|
| base class | models.Model | db.Model | Base (DeclarativeBase) |
| Fields | models.CharField() | db.Column(...) | Column(...) |
| Foreign key | ForeignKey(to, on_delete) | db.ForeignKey('table.col') | ForeignKey('table.col') |
| Relationship | Auto reverse | db.relationship + backref | relationship + back_populates |
| Session | Automatic management | db.session | SessionLocal() + Depends |
Chapter summary
In this chapter, you mastered SQLAlchemy integration in FastAPI: create_engine + SessionLocal to configure the database connection, DeclarativeBase to define the model base class, get_db dependency injection to manage sessions, and Column/relationship to define fields and relationships.
Compared with Flask-SQLAlchemy, native SQLAlchemy requires slightly more code but is fully open-source and controllable.
other extensions