Dev.to
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("%@example.com")).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("%@example.com")).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
2 hours ago
