sqlmodel

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQLModel

SQLModel is a Python library for SQL databases built on top of SQLAlchemy and Pydantic. It uses Python type annotations to define both database table schemas and API data schemas in a single class hierarchy.

SQLModel是基于SQLAlchemy和Pydantic构建的Python SQL数据库库。它使用Python类型注解，在单一类层级中同时定义数据库表结构和API数据结构。

Installation

安装

bash

pip install sqlmodel

For PostgreSQL or MySQL, install the appropriate driver alongside SQLModel:

bash

pip install sqlmodel psycopg2-binary   # PostgreSQL
pip install sqlmodel pymysql           # MySQL

bash

pip install sqlmodel

如果使用PostgreSQL或MySQL，请在安装SQLModel的同时安装对应的驱动：

bash

pip install sqlmodel psycopg2-binary   # PostgreSQL
pip install sqlmodel pymysql           # MySQL

Core Concepts

核心概念

Table Models vs Data Models

表模型 vs 数据模型

SQLModel has two distinct model kinds:

Table models —
```
SQLModel
```
subclasses with
```
table=True
```
. Map to real database tables. Are also SQLAlchemy models and Pydantic models.
Data models —
```
SQLModel
```
subclasses without
```
table=True
```
. Pydantic models only. Used for API schemas, input validation, and response shaping. Never create tables.

python

from sqlmodel import Field, SQLModel

SQLModel包含两种不同类型的模型：

表模型 — 继承
```
SQLModel
```
且设置
```
table=True
```
的子类，映射到真实数据库表，同时也是SQLAlchemy模型和Pydantic模型。
数据模型 — 继承
```
SQLModel
```
但未设置
```
table=True
```
的子类，仅作为Pydantic模型，用于API结构、输入验证和响应格式定义，不会创建数据库表。

python

from sqlmodel import Field, SQLModel

Data model only — no table created

仅为数据模型 — 不会创建表

class HeroBase(SQLModel): name: str = Field(index=True) secret_name: str age: int | None = Field(default=None, index=True)

Table model — creates the

hero

table

表模型 — 创建

hero

表

class Hero(HeroBase, table=True): id: int | None = Field(default=None, primary_key=True)

undefined

class Hero(HeroBase, table=True): id: int | None = Field(default=None, primary_key=True)

undefined

Nullable Fields and Primary Keys

可空字段与主键

Declare nullable fields with
```
int | None
```
(Python 3.10+) or
```
Optional[int]
```
.
Primary keys must be
```
int | None = Field(default=None, primary_key=True)
```
— the database generates the value; code holds
```
None
```
until the row is saved.

python

class Article(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    body: str
    views: int | None = None  # nullable column, default NULL

使用
```
int | None
```
（Python 3.10+）或
```
Optional[int]
```
声明可空字段。
主键必须定义为
```
int | None = Field(default=None, primary_key=True)
```
— 由数据库生成值，在数据保存到数据库前，代码中该字段值为
```
None
```
。

python

class Article(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    title: str
    body: str
    views: int | None = None  # 可空列，默认值为NULL

Engine — One Per Application

引擎 — 每个应用一个

Create a single engine for the whole application and reuse it everywhere. Do not recreate it per request.

python

from sqlmodel import SQLModel, create_engine

DATABASE_URL = "sqlite:///database.db"
engine = create_engine(DATABASE_URL)

为整个应用创建单个引擎并在各处复用，不要为每个请求重新创建引擎。

python

from sqlmodel import SQLModel, create_engine

DATABASE_URL = "sqlite:///database.db"
engine = create_engine(DATABASE_URL)

PostgreSQL example:

PostgreSQL示例：

engine = create_engine("postgresql+psycopg2://user:pass@host/db")


Use `echo=True` during development to log generated SQL, but remove it in production.


开发阶段可设置`echo=True`来记录生成的SQL语句，但生产环境需移除该配置。

Table Creation

表创建

Call

SQLModel.metadata.create_all(engine)

after all table model classes have been imported. The order matters — classes must be registered in

SQLModel.metadata

first.

python

undefined

必须在所有表模型类都导入后，再调用

SQLModel.metadata.create_all(engine)

。顺序很重要 — 类必须先注册到

SQLModel.metadata

中。

python

undefined

db.py

from sqlmodel import SQLModel, create_engine from . import models # import models before create_all

engine = create_engine("sqlite:///database.db")

def create_db_and_tables() -> None: SQLModel.metadata.create_all(engine)


For production, use Alembic for schema migrations instead of `create_all`.

from sqlmodel import SQLModel, create_engine from . import models # 在create_all前导入模型

engine = create_engine("sqlite:///database.db")

def create_db_and_tables() -> None: SQLModel.metadata.create_all(engine)


生产环境中，应使用Alembic进行 schema 迁移，而非`create_all`。

Session Management

会话管理

Use

with

Blocks — Never Manual

.close()

使用

with

块 — 禁止手动调用

.close()

Always open sessions in a

with

block. This guarantees cleanup even on exceptions.

python

from sqlmodel import Session

with Session(engine) as session:
    hero = Hero(name="Deadpond", secret_name="Dive Wilson")
    session.add(hero)
    session.commit()
    session.refresh(hero)  # populates auto-generated fields like id
    print(hero.id)         # now has the DB-assigned value

始终在

with

块中打开会话，这样即使发生异常也能保证资源被清理。

python

from sqlmodel import Session

with Session(engine) as session:
    hero = Hero(name="Deadpond", secret_name="Dive Wilson")
    session.add(hero)
    session.commit()
    session.refresh(hero)  # 填充自动生成的字段如id
    print(hero.id)         # 现在拥有数据库分配的值

One Session Per Request (FastAPI)

每个请求一个会话（FastAPI）

Create a new session per request using a FastAPI dependency with

yield

. The engine is shared; sessions are per-request.

python

from fastapi import Depends
from sqlmodel import Session

def get_session():
    with Session(engine) as session:
        yield session

@app.post("/heroes/", response_model=HeroPublic)
def create_hero(
    *,
    session: Session = Depends(get_session),
    hero: HeroCreate,
) -> HeroPublic:
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

The

yield

dependency ensures the session's

with

block finalizes after the response is sent.

使用FastAPI的依赖项结合

yield

，为每个请求创建新会话。引擎是共享的，会话是每个请求独立的。

python

from fastapi import Depends
from sqlmodel import Session

def get_session():
    with Session(engine) as session:
        yield session

@app.post("/heroes/", response_model=HeroPublic)
def create_hero(
    *,
    session: Session = Depends(get_session),
    hero: HeroCreate,
) -> HeroPublic:
    db_hero = Hero.model_validate(hero)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

yield

依赖项确保在响应发送后，会话的

with

块能完成清理。

Multiple Models Pattern (Best Practice)

多模型模式（最佳实践）

Avoid exposing the table model directly in API routes. Use a model hierarchy:

Model	Purpose	`table=True`
`HeroBase`	Shared fields (base data model)	No
`Hero`	DB table model	Yes
`HeroCreate`	API input — no `id`	No
`HeroPublic`	API output — required `id: int`	No
`HeroUpdate`	Partial update — all fields optional	No

python

class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

class HeroCreate(HeroBase):
    pass  # same fields as HeroBase, named explicitly for clarity

class HeroPublic(HeroBase):
    id: int  # required in responses (always present after DB save)

class HeroUpdate(SQLModel):
    name: str | None = None
    secret_name: str | None = None
    age: int | None = None

Never inherit data models from table models. Only inherit from other data models to avoid confusion and accidental table creation.

避免在API路由中直接暴露表模型，建议使用模型层级结构：

模型	用途	`table=True`
`HeroBase`	共享字段（基础数据模型）	否
`Hero`	数据库表模型	是
`HeroCreate`	API输入 — 无 `id` 字段	否
`HeroPublic`	API输出 — 必填 `id: int`	否
`HeroUpdate`	部分更新 — 所有字段可选	否

python

class HeroBase(SQLModel):
    name: str = Field(index=True)
    secret_name: str
    age: int | None = Field(default=None, index=True)

class Hero(HeroBase, table=True):
    id: int | None = Field(default=None, primary_key=True)

class HeroCreate(HeroBase):
    pass  # 与HeroBase字段相同，显式命名以提升可读性

class HeroPublic(HeroBase):
    id: int  # 响应中必填（保存到数据库后始终存在）

class HeroUpdate(SQLModel):
    name: str | None = None
    secret_name: str | None = None
    age: int | None = None

切勿从表模型继承数据模型。仅从其他数据模型继承，以避免混淆和意外创建表。

Creating from Input Model

从输入模型创建表模型

Use

Hero.model_validate(hero_create_instance)

to convert a data model to a table model:

python

db_hero = Hero.model_validate(hero)  # hero is HeroCreate
session.add(db_hero)
session.commit()
session.refresh(db_hero)
return db_hero  # FastAPI serializes via HeroPublic response_model

使用

Hero.model_validate(hero_create_instance)

将数据模型转换为表模型：

python

db_hero = Hero.model_validate(hero)  # hero是HeroCreate实例
session.add(db_hero)
session.commit()
session.refresh(db_hero)
return db_hero  # FastAPI通过HeroPublic响应模型序列化

Partial Update Pattern

部分更新模式

Use

model_dump(exclude_unset=True)

and

sqlmodel_update()

for PATCH operations:

python

@app.patch("/heroes/{hero_id}", response_model=HeroPublic)
def update_hero(
    *,
    session: Session = Depends(get_session),
    hero_id: int,
    hero: HeroUpdate,
) -> HeroPublic:
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        raise HTTPException(status_code=404, detail="Hero not found")
    hero_data = hero.model_dump(exclude_unset=True)
    db_hero.sqlmodel_update(hero_data)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

使用

model_dump(exclude_unset=True)

和

sqlmodel_update()

实现PATCH操作：

python

@app.patch("/heroes/{hero_id}", response_model=HeroPublic)
def update_hero(
    *,
    session: Session = Depends(get_session),
    hero_id: int,
    hero: HeroUpdate,
) -> HeroPublic:
    db_hero = session.get(Hero, hero_id)
    if not db_hero:
        raise HTTPException(status_code=404, detail="英雄不存在")
    hero_data = hero.model_dump(exclude_unset=True)
    db_hero.sqlmodel_update(hero_data)
    session.add(db_hero)
    session.commit()
    session.refresh(db_hero)
    return db_hero

Querying

查询

SELECT with

select()

使用

select()

进行查询

python

from sqlmodel import select

with Session(engine) as session:
    # All rows
    heroes = session.exec(select(Hero)).all()

    # Single result
    hero = session.exec(select(Hero).where(Hero.name == "Deadpond")).first()

    # By primary key (preferred for single-row lookup)
    hero = session.get(Hero, hero_id)

    # Filtering, ordering, pagination
    statement = (
        select(Hero)
        .where(Hero.age >= 18)
        .order_by(Hero.name)
        .offset(offset)
        .limit(limit)
    )
    heroes = session.exec(statement).all()

python

from sqlmodel import select

with Session(engine) as session:
    # 查询所有行
    heroes = session.exec(select(Hero)).all()

    # 查询单个结果
    hero = session.exec(select(Hero).where(Hero.name == "Deadpond")).first()

    # 通过主键查询（推荐用于单行查找）
    hero = session.get(Hero, hero_id)

    # 过滤、排序、分页
    statement = (
        select(Hero)
        .where(Hero.age >= 18)
        .order_by(Hero.name)
        .offset(offset)
        .limit(limit)
    )
    heroes = session.exec(statement).all()

Indexes

索引

Declare indexes with

Field(index=True)

on frequently queried columns. Indexes speed up

WHERE

ORDER BY

, and

JOIN

operations at the cost of slightly slower writes.

python

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)           # indexed
    age: int | None = Field(default=None, index=True)  # indexed
    secret_name: str                         # not indexed

在经常被查询的列上使用

Field(index=True)

声明索引。索引会加速

WHERE

、

ORDER BY

和

JOIN

操作，但会略微降低写入速度。

python

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)           # 已索引
    age: int | None = Field(default=None, index=True)  # 已索引
    secret_name: str                         # 未索引

Relationships

关系建模

Foreign Keys and

Relationship

外键与

Relationship

Define foreign keys with

Field(foreign_key="table.column")

and use

Relationship

for ORM-level access to related objects.

python

from sqlmodel import Relationship

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    heroes: list["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")

```
back_populates
```
keeps both sides of the relationship in sync in memory.
Use string forward references (
```
"Hero"
```
) when the referenced class is defined after the current class.
Relationship attributes are not columns — they do not appear in the table schema.

使用

Field(foreign_key="table.column")

定义外键，使用

Relationship

实现ORM层面的关联对象访问。

python

from sqlmodel import Relationship

class Team(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    heroes: list["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    team_id: int | None = Field(default=None, foreign_key="team.id")
    team: Team | None = Relationship(back_populates="heroes")

```
back_populates
```
用于保持内存中关系的双向同步。
当引用的类在当前类之后定义时，使用字符串前向引用（
```
"Hero"
```
）。
关系属性不是列 — 不会出现在表结构中。

Many-to-Many

多对多关系

Use an explicit link model with

table=True

as the association table:

python

class HeroTeamLink(SQLModel, table=True):
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    teams: list["Team"] = Relationship(back_populates="heroes", link_model=HeroTeamLink)

使用显式的链接模型（设置

table=True

）作为关联表：

python

class HeroTeamLink(SQLModel, table=True):
    hero_id: int | None = Field(default=None, foreign_key="hero.id", primary_key=True)
    team_id: int | None = Field(default=None, foreign_key="team.id", primary_key=True)

class Hero(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    teams: list["Team"] = Relationship(back_populates="heroes", link_model=HeroTeamLink)

Code Structure

代码结构

Organize multi-model projects across files to avoid circular imports and enforce

create_all

ordering:

app/
├── models.py        # all SQLModel table models
├── schemas.py       # data models (HeroCreate, HeroPublic, etc.)
├── database.py      # engine, get_session dependency
└── routers/
    └── heroes.py    # FastAPI router

Import models in

database.py

before calling

create_all

, or import them explicitly in the startup handler:

python

undefined

将多模型项目按文件拆分，避免循环导入，并确保

create_all

的执行顺序：

app/
├── models.py        # 所有SQLModel表模型
├── schemas.py       # 数据模型（HeroCreate、HeroPublic等）
├── database.py      # 引擎、get_session依赖项
└── routers/
    └── heroes.py    # FastAPI路由

在调用

create_all

前，在

database.py

中导入模型，或者在启动处理器中显式导入：

python

undefined

main.py

from app import models # ensures registration before create_all from app.database import engine, create_db_and_tables

@app.on_event("startup") def on_startup() -> None: create_db_and_tables()

undefined

from app import models # 确保在create_all前完成注册 from app.database import engine, create_db_and_tables

@app.on_event("startup") def on_startup() -> None: create_db_and_tables()

undefined

Quick Reference

速查指南

Operation	Code
Create engine	`create_engine(url)`
Create tables	`SQLModel.metadata.create_all(engine)`
Open session	`with Session(engine) as session:`
Insert row	`session.add(obj); session.commit()`
Fetch by PK	`session.get(Model, pk)`
Query rows	`session.exec(select(Model).where(...)).all()`
Update row	`session.add(obj); session.commit()`
Delete row	`session.delete(obj); session.commit()`
Refresh from DB	`session.refresh(obj)`
Convert input→table	`Model.model_validate(input_obj)`
Partial update dict	`obj.model_dump(exclude_unset=True)`

操作	代码
创建引擎	`create_engine(url)`
创建表	`SQLModel.metadata.create_all(engine)`
打开会话	`with Session(engine) as session:`
插入行	`session.add(obj); session.commit()`
通过主键查询	`session.get(Model, pk)`
查询行	`session.exec(select(Model).where(...)).all()`
更新行	`session.add(obj); session.commit()`
删除行	`session.delete(obj); session.commit()`
从数据库刷新数据	`session.refresh(obj)`
输入模型转表模型	`Model.model_validate(input_obj)`
生成部分更新字典	`obj.model_dump(exclude_unset=True)`

Additional Resources

额外资源

references/relationships-and-queries.md
— Relationship patterns, lazy loading, many-to-many with extra fields, advanced query techniques
references/fastapi-patterns.md
— Complete FastAPI integration: dependencies, lifespan, testing, response model patterns

references/relationships-and-queries.md
— 关系模式、延迟加载、带额外字段的多对多关系、高级查询技巧
references/fastapi-patterns.md
— 完整FastAPI集成：依赖项、生命周期、测试、响应模型模式

sqlmodel

Original

Translation

SQLModel

SQLModel

Installation

安装

Core Concepts

核心概念

Table Models vs Data Models

表模型 vs 数据模型

Data model only — no table created

仅为数据模型 — 不会创建表

Table model — creates the hero table

表模型 — 创建hero表

Nullable Fields and Primary Keys

可空字段与主键

Engine — One Per Application

引擎 — 每个应用一个

PostgreSQL example:

PostgreSQL示例：

engine = create_engine("postgresql+psycopg2://user:pass@host/db")

engine = create_engine("postgresql+psycopg2://user:pass@host/db")

Table Creation

表创建

db.py

db.py

Session Management

会话管理

Use with Blocks — Never Manual .close()

使用with块 — 禁止手动调用.close()

One Session Per Request (FastAPI)

每个请求一个会话（FastAPI）

Multiple Models Pattern (Best Practice)

多模型模式（最佳实践）

Creating from Input Model

从输入模型创建表模型

Partial Update Pattern

部分更新模式

Querying

查询

SELECT with select()

使用select()进行查询

Indexes

索引

Relationships

关系建模

Foreign Keys and Relationship

外键与Relationship

Many-to-Many

多对多关系

Code Structure

代码结构

main.py

main.py

Quick Reference

速查指南

Additional Resources

额外资源

Table model — creates the
`hero`
table

表模型 — 创建
`hero`
表

Use
`with`
Blocks — Never Manual
`.close()`

使用
`with`
块 — 禁止手动调用
`.close()`

SELECT with
`select()`

使用
`select()`
进行查询

Foreign Keys and
`Relationship`

外键与
`Relationship`