Comment by roel_v
12 hours ago
Why would one want to couple these two? Doesn't that couple, say, your API interface with your database schema? Whereas in reality these are separate concepts, even if, yes, sometimes you return a 'user' from an API that looks the same as the 'user' in the database? Honest question, I only just recently got into FastAPI and I was a bit confused at first that yes, it seemed like a lot of duplication, but after a little bit of experience, they are different things that aren't always the same. So what am I missing?
The ORM doesn't force you to use the DB model as your API schema. It's a regular Pydantic BaseModel, so you can make separate request/response schemas whenever you need to. For simple CRUD, using the model directly saves boilerplate. For complex cases, you decouple them as usual. The goal is not one model for everything, it's one contract. Everything speaks Pydantic, whether it's your API layer or your database layer.
You might be interested in a library that flips around the concept of an ORM, like sqlc [1] or aiosql [2]. You specify just what you want from the database, without needing to couple so much API with database tables.
[1] https://sqlc.dev/ [2] https://nackjicholson.github.io/aiosql/
Nice! But what about type checking?
I think they should be related but not the same.
With proper DRY, you shouldn't need to repeat the types, docstrings and validation rules. You should be able to say "these fields from user go on its default presenter, there are two other presenters with extra fields" without having to repeat all that info. I have a vague memory that Django Rest Framework does something of the sort.
This feels hard to express in modern type systems though. Perhaps the craziness that is Zod and Typescript could do it, but probably not Python.
Yeah I have always struggled to figure out why I would use SQLModel.
Big fan of FastAPI but I think SQLModel leads to the wrong mental model that somehow db model and api schema are the same.
Therefore I insist on using SQLAlchemy for db models and pydantic for api schemas as a mental boundary.
I also find it really strange this weird ORM fascination. Besides the generic "ORM are the Vietnam War of CS" feeling, I feel that with average database to ORM/REST things, end up with at least one of:
a) you somehow actually have a "system of record", so modelling something in a very CRUD way makes sense, but, on the other hand, who the hell ends up building so many system of record systems in the first place to need those kinds of tools and frameworks?
b) you pretend your system is somehow a system of record when it isn't and modelling everything as CRUD makes it a uniform ball of mud. I don't understand what is so important that you can uniformly "CRUD" a bunch of objects? The three most important parts of an API, making it easy to use it right, hard to use it wrong, and easy to figure out the intent behind how your system is meant to be used, are lost that way.
c) you leak your API to your database, or your database to your API, compromising both, so both sucks.
The vietnam of computer science was written 20 years ago (2006 even), and didn't kill off ORMs then. We've only had 20 years of improvement of ORMs since then. We've long ago accepted Vietnam (the country) as what it is and what it will be in the forseeable future. We should do the same with ORM.
I for one don't want to write in a low level assembly language, and shouldn't have to in 2026. Yet, SQL still feels like one.
I've written a lot of one off products using an ORM, and I don't regret any of the time savings from doing so. When and if I make $5-50M a year on a shipped product, okay, maybe I'll think about optimizing. And then I'll hire an expert while I galavant around europe.
SQL is a pretty high-level, declarative language. It's unnecessarily wordy though, and not very composable.
The problem with ORMs is that they usually give you a wrong abstraction. They map poorly on how a relational database works, and what it is capable of. But the cost of it is usually poor performance, rarely it's obvious bugs. So it's really easy to get started to use; when it starts costing you, you can optimize the few most critical paths, and just pay more and more for the DB. This looks like an okay deal for the industry, it seems.
A lot of people have this misconception that Pydantic models are only useful for returning API responses. This is proliferated by all-in-one frameworks like FastAPI.
They are useful in and of themselves as internal business/domain objects, though. For a lot of cases I prefer lighter weight dataclasses that have less ceremony and runtime slowdown with the validation layer, but we use Pydantic models all over the place to guarantee and establish contracts for objects flowing thru our system, irrespective of external facing API layer.
Pydantic models are validating. Hence their natural place is interfaces with external systems, outside the reach of your typechecker.
With this you don't need to have two sources of truth on the backend. Previously you will end up with one set of DB ORM level validation and a second set of validation on the data transfer object and you have to make sure the types line up. Now the data transfer object will automatically inherit the correct types.
The two sources of truth are for two disparate adapters. Neither the API nor the DB define the domain, but both must adapt the domain to their respective implementation concerns.
The ontology of your persistence layer should be entirely uncoupled from that of your API, which must accommodate an external client.
In theory, anyway.
I'd rather define my db domain in-code so I do not have to worry about writing the queries without type hinting.
Raw sql -> eh, I don't have the patience Raw sql w type hints -> better, I at least get a compilation error when I do something wrong, and preferably a suggestion ORM -> this usually introduces it's own abstraction that needs it's own maintenance, but at least it's more code-oriented.
Yes, SQL is an awesome solution to querying DB's, hence I prefer option 2
Exactly, that's the idea.