SQLModel vs SQLAlchemy for production
Posted by aerodynamics1@reddit | Python | View on Reddit | 25 comments
Hey everyone, I was wondering whether the current releases of SQLModel is appropriate for production? Couldn’t find a recent post about this I’m trying to set up a web app with fastapi backend and react frontend. Was deciding whether to pick SQLAlchemy or not
crawl_dht@reddit
I discourage using wrappers that wraps ORM like SQLAlchemy. SQLAlchemy is one of the few libraries that are built right both by design and features. Its documentation is complex but not poor. By using wrappers, their abstraction will prevent you to learn the basics of SQLAlchemy. If you don't learn its basics, you will not be able to use its advance functionalities that wrappers do not cover.
saint_geser@reddit
But for simple enough tasks SQLModel is easier to use and having full SQLAlchemy backend is a bit of an overkill.
Of course, I agree, it's important to learn the basics of SQLAlchemy, but I'm sure just as many people would also say that using ORM is wrong and you need to know your basic SQL before everything else.
Anru_Kitakaze@reddit
I don't think that using ORM is wrong in general, but I do think that you have to learn basic SQL ideas anyway. It takes you... A day?
Anyway, you must be able to create custom migration scripts when Alembic cannot handle it. And you'll have an idea what ORM should be able to do even if you don't know how to do it in ORM
databot_@reddit
sqlalchemy also has alembic for db migrations. I always go with sqlalchemy.
campesinoProgramador@reddit
I really like SQLAlchemy, I think it depends on how much control you want to have and If your project needs some complex operations.
If this is the case, I recommend you using sqlalchemy (be carefull with the subqueries that sqlalchemy does when doing some joins, it impacts on the performance).
If it is a basice crud, just go for SQLModel
AstronomerTerrible49@reddit
I would suggest sqlalchemy, since SQLModel is based on sqlalchemy, for more advanced usage than some very basic CRUD, you would need to use sqlalchemy code in your SQLModel anyways.
jascha_eng@reddit
SQLAlchemy is truly a beast in ORM space. It's been around since forever and developed and changed with the spirit of times. The docs are really indepth and it can basically solve any db need you might ever have.
SQLModel ist just like fastapi(on starlette) a thin wrapper around a very well designed library, just that in this case it doesn't really provide enough benefit over the core library itself.
tacothecat@reddit
So far my experience with sqlalchemy docs is "I'd rather just write sql"
campesinoProgramador@reddit
Same
covmatty1@reddit
To play devil's advocate, that could also been seen as an argument for SQLModel though.
You get everything SQLAlchemy provides, plus a very nice abstraction over the simple stuff, which will likely be a large proportion of your database interactions.
AstronomerTerrible49@reddit
well, when I first started using sqlalchemy I did not quite like it and think it is over designed and being too complex, only later I realized that it is as complex as it needs to be, database is not easy to deal with especially when there are so many factors to take consider into.
SQLModel is very godo for simple stuff, yes, but as soon as your application grows you would need to have finer control of data access, then you will need to figure out how to do certain things in SQLModel, there is good chance that it is covered in SQLModel and you need to look up sqlalchemy's docs, and figure out if it would work / how to make it work in SQLModel.
SQLModel combines two great tools together pydantic and sqlalchemy, both of these are very complex itself, when it works, it is fantastic but when it does not the complexity just explodes.
crawl_dht@reddit
I came to the same conclusion. Sqlalchemy is one of the few libraries in Python which is built right.
covmatty1@reddit
But it doesn't explode at all. Nothing breaks or stops working, you just move to using a different tool for certain parts.
I'm only using SQLModel on a home project currently, but yeah absolutely, as soon as there are some more complex things to do I just pepper in a little SQLAlchemy on top and everything works absolutely fine. You get nice, simple Pydantic abstraction when you want it, which is most of the time, but you're totally free to mix in Alchemy bits when you need them.
My team don't have any Python projects with SQL databases currently, they're all either Mongo or stateless, but if we do I'd have no qualms about telling them to use SQLModel.
tomasemilio@reddit
https://github.com/tomasemilio/FastAPI-Boilerplate
I use pure SQLAlchemy 2.0 ORM with async support
It support native python types with Mapped. And I use pydantic schemas to customize my responses.
revoltnb@reddit
SQLModel is a wonderful amalgamation of SQLAlchemy and Pydantic. This provides an ORM with very strong data initialisation and validation capabilities.
For me, the most powerful thing is that it allows for a single source of truth for database and data validation - the ability to have this in the one class, rather than having to have separate pydantic and SQLAlchemy classes.
We are using it in production, and occasionally have to use the ability to pass through SQLAlchemy specific options when SQLModel does not support what we are trying to do. Eg:
`` status: str = Field(max_length=32, default=StatusEnum.ACTIVE,
sa_column_kwargs={"server_default": StatusEnum.ACTIVE,},
index=True)``
We use SQLModel, and atlas to define and manage databases
We use several mixins to define standard table attributes such as all tables having an id, a status, along with other key columns.
Highly recommended if you have a complex schema requiring validation, and more than one person working on the project. It One source of truth is less things that can go wrong.
Highly recommended for new projects.
tacothecat@reddit
Do you run with pydantic v2?
revoltnb@reddit
Absolutely we use pydantic v2 - We had the good fortune to start the project earlier this year, and have been able to use some great tooling (now using uv instead of poetry)
SubjectSensitive2621@reddit
(I'm assuming you're from django background) Ideally data validation and database schema/model should not be the same. Having it like that is design smell. The former is for application layer, whereas the latter for data(database) layer.
Also, the purpose of SQLModel is to model the schema easily leveraging pydantic instead of having to learn the nuances of SQLAlchemy and defining it in its syntax. And it's not intended to unify data input validation and db schema.
KosmoanutOfficial@reddit
I am not sure but I have been using pydantic for 3 years on all my projects and more recently started using sqlalchemy. I was thinking of using sqlmodel and just kind of thought I would rather do my validation in the database and work with the orm objects, and anything from an external source I will be ok with a separate validation using pydantic. I haven’t tried sqlmodel but I think I will wait.
MorningImpressive935@reddit
It doesn't really matter, here's a: video.
mok000@reddit
Check out Arjancode's video on exactly this topic. He also goes into a discussion of when it's advantageous to use SQLModel, and when to use SQLAlchemy.
koldakov@reddit
Sqlmodel is quite good, until you have relations, in that case you will have the same problem with duplicating models as it were using sqlalchemy
pythonr@reddit
Sqlmodel is neat and the documentation is very beginner friendly, but for anything serious I just go with straight sqlalchemy. I just don’t see the benefits of sqlmodel. You get the benefit that you can directly return your ORM objects in fastapi, but I am not actually sure I always want that. Most of the time I write custom response objects for my endpoints anyway.
In turn you also get a lot of magic and another layer of abstraction that makes it harder to reason what is actually happening in the background.
BootyDoodles@reddit
We've been using SQLModel and like it.
The FastAPI team also manage an example template that uses FastAPI, SQLModel, Postgres, and React, which seems to match your tech stack — which you can use for reference, along with the docs.
ThePieroCV@reddit
Both are actually okay. I would go for SQLModel as it has already less boilerplate code and its documentation is well made. Works pretty well with FastAPI, so another good reason to use it. As the already mentioned answer, you’ll use SQLAlchemy code anyways. Nothing against pure SQLAlchemy, but if it makes your life easier by using it, go ahead.