Typed ORM with automatic migrations: Fitz vs SQLAlchemy + Alembic + Pydantic

For a typed ORM with automatic migrations in Python you need to maintain 3 sources of truth (SQLAlchemy + Pydantic + Alembic). In Fitz it's ONE type with decorators. Plus 8× RPS and 5× less memory than SQLAlchemy in a reproducible benchmark.

The double (triple) typing of the Python stack

For each entity of your DB in a modern FastAPI you have to maintain:

• models.py — SQLAlchemy model (defines the schema).
• schemas.py — Pydantic models (defines request/response).
• alembic/versions/*.py — autogenerated migrations.

# models.py
from sqlalchemy import Column, Integer, String, DateTime, ForeignKey
from sqlalchemy.orm import relationship, declarative_base
from datetime import datetime

Base = declarative_base()

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    email = Column(String, nullable=False, unique=True)
    name = Column(String, nullable=False)
    role = Column(String, nullable=False, default="user")
    created_at = Column(DateTime, default=datetime.utcnow)
    posts = relationship("Post", back_populates="user")

class Post(Base):
    __tablename__ = "posts"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    title = Column(String, nullable=False)
    body = Column(String, nullable=False)
    user = relationship("User", back_populates="posts")

# schemas.py
from pydantic import BaseModel
from datetime import datetime

class UserCreate(BaseModel):
    email: str
    name: str
    role: str = "user"

class UserOut(BaseModel):
    id: int
    email: str
    name: str
    role: str
    created_at: datetime

    class Config:
        from_attributes = True

class PostCreate(BaseModel):
    title: str
    body: str

class PostOut(BaseModel):
    id: int
    user_id: int
    title: str
    body: str

    class Config:
        from_attributes = True

class UserWithPosts(UserOut):
    posts: list[PostOut] = []

# main.py
@app.post("/users", response_model=UserOut)
async def create_user(user: UserCreate, db: AsyncSession = Depends(get_session)):
    new_user = User(**user.model_dump())
    db.add(new_user)
    await db.commit()
    await db.refresh(new_user)
    return new_user

@app.get("/users/{id}", response_model=UserWithPosts)
async def get_user(id: int, db: AsyncSession = Depends(get_session)):
    result = await db.execute(
        select(User).options(selectinload(User.posts)).where(User.id == id)
    )
    user = result.scalar_one_or_none()
    if not user:
        raise HTTPException(404, "user not found")
    return user

Three files. Two models of the same entity maintained in parallel. And for the schema:

$ alembic revision --autogenerate -m "add user role"
# Review the generated file — autogenerate sometimes gets confused
$ alembic upgrade head

Plus configuring Alembic (env.py, alembic.ini, target_metadata...).

The same thing in Fitz

@table("users")
type User {
    @primary id: Int,
    email: Str,
    name: Str,
    role: Str = "user",
    created_at: Str = "now()",
    @has_many("Post", "user_id") posts: List<Post>,
}

@table("posts")
type Post {
    @primary id: Int,
    user_id: Int,
    title: Str,
    body: Str,
    @belongs_to user: User?,
}

@post("/users")
async fn create_user(db: DbConn, user: User) -> Result<User> {
    return User.insert(db, user).await
}

@get("/users/{id}")
async fn get_user(db: DbConn, id: Int) -> Result<User> {
    return User.where(fn(u) => u.id == id).preload("posts").first(db).await
}

ONE type. ONE source of truth. The compiler generates:

• The parameterized SQL for queries.
• The OpenAPI schema from the same type.
• The body JSON deserializer from the same type.
• The validation of required / nullable / default fields.

And the DB schema:

$ fitz db diff
+ CREATE TABLE users (
+     id BIGSERIAL PRIMARY KEY,
+     email TEXT NOT NULL,
+     name TEXT NOT NULL,
+     role TEXT NOT NULL DEFAULT 'user',
+     created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+ );
+ CREATE TABLE posts (
+     id BIGSERIAL PRIMARY KEY,
+     user_id BIGINT NOT NULL REFERENCES users(id),
+     title TEXT NOT NULL,
+     body TEXT NOT NULL
+ );

$ fitz db migrate
✓ applied migration 20260616_120300_initial.sql

The raw table

Item	Python (SQLAlchemy + Alembic + Pydantic)	Fitz
Sources of truth per entity	3 (models.py, schemas.py, alembic/)	1 (@table type)
Generate schema	alembic revision --autogenerate -m "..."	fitz db diff
Apply	alembic upgrade head	fitz db migrate
Rollback	alembic downgrade -1	fitz db rollback
Query DSL	.filter(User.role == "admin") (ORM DSL)	.where(fn(u) => u.role == "admin") (closure-to-SQL at compile time)
Eager loading	.options(selectinload(User.posts))	.preload("posts")
Postgres operators	User.email.ilike(...), .contains(...), etc.	Same —u.email.ilike(...), u.tags.has(...)
Transactions	with session.begin(): + remember to flush	db.transaction(fn(tx) async { ... }).await
Body request validation	Separate Pydantic	Same type
OpenAPI schema	Pydantic infer	Auto from @table type
Driver	libpq via psycopg2/asyncpg	Pure Rust (no libpq, no tokio-postgres)
Performance vs SQLAlchemy	baseline	8× RPS, 5× less memory (bench below)

Piece by piece

Closure-to-SQL at compile time

This is what took me the most effort and what I'm proudest of. SQLAlchemy translates its DSL to SQL at runtime — each .filter(User.role == "admin") builds the AST at runtime, takes overhead, allocates, and emits SQL.

Fitz does it at compile time:

let admins = User.where(fn(u) => u.role == "admin")
    .order_by(fn(u) => u.created_at, "desc")
    .limit(10)
    .all(db).await?

The compiler analyzes the closure fn(u) => u.role == "admin" and emits, once at binary compile time, the constant SQL string:

SELECT id, email, name, role, created_at FROM users
WHERE role = $1 ORDER BY created_at DESC LIMIT 10

Plus the code that binds $1 = "admin". Zero runtime overhead for SQL construction — the string already exists in the binary as &'static str.

Comparable in performance to Diesel or sqlx (which also generate SQL at compile time via macros), but with natural language syntax instead of procedural macros.

Native Postgres operators

SQLAlchemy:

admins = session.query(User).filter(User.email.ilike("[e-mail]")).all()
tagged = session.query(User).filter(User.tags.contains(["admin"])).all()
pro_users = session.query(User).filter(User.metadata["plan"].astext == "pro").all()

Fitz (operators are methods on the field):

let admins = User.where(fn(u) => u.email.ilike("[e-mail]")).all(db).await?
let tagged = User.where(fn(u) => u.tags.has("admin")).all(db).await?
let pro_users = User.where(fn(u) => u.metadata.get("plan") == "pro").all(db).await?

Operators covered:

Category	Methods
String	.like(p), .ilike(p), .starts_with(p), .ends_with(p), .contains(p) (with auto-escape of %/_)
Lists/IN	.is_in([...]), .is_null(), .is_not_null()
Arrays	.has(x) (?), .contains_all(xs) (@>), .contained_in(xs) (<@)
JSONB	.has_key(k) (?), .has_all_keys(ks) (?&), .has_any_keys(ks) (`?\

Relations + eager loading

SQLAlchemy:
{% raw %}

class User(Base):
    posts = relationship("Post", back_populates="user", lazy="select")

class Post(Base):
    user = relationship("User", back_populates="posts")

# Eager loading
users = await db.execute(
    select(User).options(selectinload(User.posts))
)

Plus configuring lazy= correctly to avoid the oldest N+1 in the world (lazy queries firing one per user).

Fitz:

@table("users")
type User {
    @primary id: Int,
    name: Str,
    @has_many("Post", "user_id") posts: List<Post>,
}

@table("posts")
type Post {
    @primary id: Int,
    user_id: Int,
    @belongs_to user: User?,
}

// Explicit eager loading (default is NOT eager — no surprises)
let users = User.preload("posts").all(db).await?

.preload("posts") emits a single batched query with WHERE user_id IN (...) and builds the Fitz structs. The default is non-eager: if you don't preload, the virtual field posts is empty and you know you didn't hit the DB.

Typo in the field name:

let users = User.preload("postss").all(db).await?
//                        ^^^^^^^ compile error: User has no relation "postss"

The compiler statically checks the match. SQLAlchemy tells you at runtime when the request lands.

Transactions

SQLAlchemy:

async with db.begin():
    user = User(...)
    db.add(user)
    await db.flush()  # remember to flush or user.id is empty
    post = Post(user_id=user.id, ...)
    db.add(post)

Fitz:

let result = db.transaction(fn(tx) async {
    let user = User.insert(tx, User { id: 0, name: "Ada", role: "user" }).await?
    let post = Post.insert(tx, Post { id: 0, user_id: user.id, title: "hi", body: "..." }).await?
    return Ok(post)
}).await

The block inside db.transaction(...) receives a tx: DbConn that writes against the transaction. If the closure returns Err, rollback; if it returns Ok, commit. If the closure panics, automatic rollback.

And User.insert(...) returns the row with the BIGSERIAL id already assigned, no "remember to flush".

Declarative migrations

Change to the type:

 @table("users")
 type User {
     @primary id: Int,
     email: Str,
     name: Str,
     role: Str = "user",
+    avatar_url: Str?,
     created_at: Str = "now()",
 }

Diff:

$ fitz db diff
+ ALTER TABLE users ADD COLUMN avatar_url TEXT;

Apply:

$ fitz db migrate
✓ applied migration 20260616_153020_add_user_avatar_url.sql

Rollback:

$ fitz db rollback
✓ reverted migration 20260616_153020_add_user_avatar_url.sql

The compiler compares the @table types in your code with the live schema and emits idempotent diffs. The migration is committed (it's a .sql file) for review in the PR. The OPS phase is still your responsibility (deploy timing, downtime planning for ALTER COLUMN type that rewrites the table, etc.) — but the SQL is already generated and reviewed.

In Alembic, --autogenerate sometimes generates incorrect SQL (especially for constraints, composite indexes, complex defaults) and you're always told to review the file manually. Fitz emits the diff without touching the user's SQL — you see it and you apply it.

How fast is it? — reproducible benchmark

Empty promises are easy. The repo ships a reproducible benchmark between two equivalent boilerplates:

• api-postgres-fitz — Fitz + native ORM.
• api-postgres-python — FastAPI + SQLAlchemy 2.x async.

Same Postgres, same endpoints, same response shape, same docker compose. Headline at v0.10.13 (Intel Core Ultra 7 155H, Docker 29.2.1, 30s sustained, concurrency 10):

Metric	Fitz ORM	Python + SQLAlchemy	Speedup
Memory peak	9.2 MB	51 MB	5.5× more efficient
GET /users p50	4.88 ms	37.85 ms	7.76×
GET /users RPS	1944	246	7.91×
GET /users/{id} p50	3.60 ms	31.87 ms	8.85×
GET /users/{id} RPS	2604	296	8.80×
Cold start	0.14 s	0.22 s	1.57×
Image size	131 MB	258 MB	2× lighter

Reproduce the numbers with bash benchmarks/orm-vs-sqlalchemy/run.sh (~5-8 min with warm Docker cache; requires oha + jq). Full methodology, raw output, and where the comparison is *un*fair to Fitz (e.g.: SQLAlchemy does more work on the ORM side with identity map tracking) are in the bench README.

Why Fitz is fast: constant SQL at compile time (zero runtime overhead for building queries), pure Rust Postgres driver (no libpq overhead), JSON serialization with concrete types (no reflection), Arc<Mutex<>> for shared HTTP state (multi-thread without GIL).

Why SQLAlchemy is slow in this test: identity map, lazy loading flags, events, column descriptors — many features that earn their cost when you need them, not when you don't.

What Fitz does NOT give you (yet)

Honesty about the debts:

• Postgres only. No MySQL, no SQLite. Postgres is the choice for the ecosystem shape (clean binary protocol, rich types, JSONB, arrays). MySQL/SQLite are for when demand appears.
• BelongsTo in .preload(...) — only HasMany/HasOne in eager loading for now. BelongsTo eager: residual debt.
• Composite PKs — @primary only on one field. Tables with composite PKs (rare in new projects): not supported.
• Postgres JSON operators in .where — the most used ones are there (.has_key, .contains_json, .get), but jsonb_path_query and similar only via raw db.query(...).
• Read replicas / sharding. The conn pool is against a single DB. For read replicas you have to coordinate by hand.
• Zero-downtime migrations for NOT NULL columns without default. The diff emits it but the OPS phase remains manual.
• Performance tooling (EXPLAIN ANALYZE integrated in LSP) — not in MVP.

Escape hatch: raw SQL when you need it

For complex CTEs, window functions, queries the ORM doesn't cover:

let result = db.query("
    WITH ranked AS (
        SELECT id, name, ROW_NUMBER() OVER (PARTITION BY role ORDER BY created_at DESC) AS rn
        FROM users
    )
    SELECT * FROM ranked WHERE rn <= 3
", []).await?

db.query(sql, params) returns List<Map<Str, Any>>. No static checking of SQL or column names — you know. For 90% of CRUD you use the typed ORM; for crazy queries, escape hatch.

Closing

The most legitimate complaint against ORMs is "they add a layer of abstraction that you then have to break to do serious queries". SQLAlchemy solves this with a highly expressive DSL, at the cost of runtime overhead and learning curve. Diesel/sqlx solve it by generating SQL at compile time, at the cost of procedural macros and verbosity.

Fitz takes the Diesel/sqlx route but with language syntax (not macros), a type checker that validates the closure-to-SQL, and schema migrations integrated as a binary subcommand. And because it's in the language, the same @table type is used as an HTTP request body, as a response, as an argument to User.insert(...), as an element of List<User> that serializes to JSON — ONE source of truth.

If your project fits in Postgres and you don't need composite PKs or read replicas in the short term, Fitz's ORM closes the cycle "HTTP request → DB → response" with fewer moving parts and better performance than the typical Python stack.

If your project needs one of the "not in MVP" items, Python interop is still a valid option (from python import sqlalchemy), or the debt in question lives in the roadmap.

---

This closes the series of 9 posts: intro, tutorial, deployment, CLI builder, WebSockets, cron jobs, auth, observability, and ORM. The whole stack. What's next are language releases + chapter 31 of the guide (Postgres + native ORM) if you want to go deeper.

Repo: github.com/Thegreekman76/fitz
Chapter 31 of the guide (Postgres + native ORM): docs/guide.md
Dedicated DB and ORM doc (~2600 LoC, 30 sections): docs/db-orm.md
Reproducible bench: benchmarks/orm-vs-sqlalchemy