Flask + SQLAlchemy 2.0 + Alembic 的最小可用骨架

Flask 的微框架哲学意味着每个项目都要自己组装数据层。下面是一套
经过几个生产项目验证的最小骨架：Flask 3.x + SQLAlchemy 2.0 + Alembic
迁移 + 应用工厂模式。

项目结构

myapp/
├── pyproject.toml
├── alembic.ini
├── migrations/
│   ├── env.py
│   └── versions/
└── src/myapp/
    ├── __init__.py        # create_app()
    ├── extensions.py      # db, ...
    ├── models.py
    ├── routes.py
    └── config.py

1. 依赖

uv add flask 'sqlalchemy>=2.0' alembic psycopg[binary] python-dotenv

2. extensions.py

from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import DeclarativeBase

class Base(DeclarativeBase):
    pass

db = SQLAlchemy(model_class=Base)

SQLAlchemy 2.0 推荐用 DeclarativeBase 替代 declarative_base()，
类型提示更友好。

3. models.py

from datetime import datetime
from sqlalchemy import String, DateTime, ForeignKey
from sqlalchemy.orm import Mapped, mapped_column, relationship
from .extensions import db, Base

class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(120), unique=True)
    nickname: Mapped[str] = mapped_column(String(60))
    created_at: Mapped[datetime] = mapped_column(default=datetime.utcnow)
    posts: Mapped[list['Post']] = relationship(back_populates='author')

class Post(Base):
    __tablename__ = 'posts'
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    body: Mapped[str]
    author_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
    author: Mapped[User] = relationship(back_populates='posts')

Mapped[...] + mapped_column(...) 是 SA 2.0 的新风格，
完全类型化，mypy / pyright 直接懂。

4. config.py

import os
from dotenv import load_dotenv

load_dotenv()

class Config:
    SQLALCHEMY_DATABASE_URI = os.environ.get(
        'DATABASE_URL', 'postgresql+psycopg://localhost/myapp')
    SQLALCHEMY_TRACK_MODIFICATIONS = False
    SECRET_KEY = os.environ['SECRET_KEY']

5. init.py（应用工厂）

from flask import Flask
from .config import Config
from .extensions import db

def create_app(config_object=Config):
    app = Flask(__name__)
    app.config.from_object(config_object)
    db.init_app(app)

    from . import routes
    app.register_blueprint(routes.bp)
    return app

6. routes.py

from flask import Blueprint, jsonify, request
from sqlalchemy import select
from .extensions import db
from .models import User, Post

bp = Blueprint('main', __name__)

@bp.get('/users/<int:uid>/posts')
def user_posts(uid):
    stmt = select(Post).where(Post.author_id == uid)
    posts = db.session.scalars(stmt).all()
    return jsonify([{'id': p.id, 'title': p.title} for p in posts])

@bp.post('/users')
def create_user():
    data = request.get_json()
    u = User(email=data['email'], nickname=data['nickname'])
    db.session.add(u)
    db.session.commit()
    return jsonify({'id': u.id}), 201

SA 2.0 用 select() + db.session.scalars()；老的 Model.query.filter_by(...)
仍可用但官方建议迁移。

7. Alembic 初始化

uv run alembic init -t async migrations
# 或同步：uv run alembic init migrations

改 alembic.ini：

sqlalchemy.url = postgresql+psycopg://localhost/myapp

改 migrations/env.py，让它能找到 metadata：

from myapp import create_app
from myapp.extensions import db, Base
# ...
target_metadata = Base.metadata

app = create_app()
with app.app_context():
    ...

8. 第一次迁移

uv run alembic revision --autogenerate -m 'init users + posts'
# 检查生成的 migrations/versions/*.py
uv run alembic upgrade head

9. 运行

uv run flask --app src.myapp run --debug
# 或：
uv run gunicorn 'src.myapp:create_app()' -b 0.0.0.0:8000

10. 测试

# tests/conftest.py
import pytest
from src.myapp import create_app
from src.myapp.extensions import db

class TestConfig:
    SQLALCHEMY_DATABASE_URI = 'sqlite:///:memory:'
    SECRET_KEY = 'test'

@pytest.fixture
def app():
    app = create_app(TestConfig)
    with app.app_context():
        db.create_all()
        yield app

@pytest.fixture
def client(app):
    return app.test_client()

踩过的坑

Alembic autogenerate 看不到 model：检查 env.py 是否真的 import 了
所有 model 模块（光 import models 子模块不自动加载）。我们一般写
from myapp import models # noqa。
加字段时 default 是 Python 端的，DB 端不会自动加 default。
改 model 后跑 autogenerate 会生成 op.add_column 但 nullable 字段
没初值时升级会失败。手动给 server_default 或者分两步迁移。
SA 2.0 的 lazy loading 默认严格：N+1 查询会触发警告。生产建议
select(...).options(selectinload(Post.author)) 显式预取。
Flask-SQLAlchemy 3.x 配 SA 2.0 时，model 必须用 db.Model 或自己定义
的 Base，不能混用。