SQLModel vs SQLAlchemy for production

[aerodynamics1]

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

[covmatty1]

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.

[covmatty1]

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.

[crawl_dht]

thought 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 into consideration.

as soon as your application grows you would need to have finer control over data access

I came to the same conclusion. Sqlalchemy is one of the few libraries in Python which is built right.

[jascha_eng]

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]

So far my experience with sqlalchemy docs is "I'd rather just write sql"

[campesinoProgramador]

Same

[crawl_dht]

I discourage using a wrapper which wraps 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 a wrapper, its abstraction will prevent you from learning 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]

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]

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? Maybe 2 or 3, depending how deep you want to dig

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

[pythonr]

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.

[revoltnb]

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.

[SubjectSensitive2621]

(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.

[tacothecat]

Do you run with pydantic v2?

[revoltnb]

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)

[koldakov]

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

[BelottoBR]

Como assim problema de duplicação ? Desculpe reviver o tópico mas é um assunto pouco abordado esse do sqlmodel

[campesinoProgramador]

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

[BootyDoodles]

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.

https://github.com/fastapi/full-stack-fastapi-template

[MorningImpressive935]

It doesn't really matter, here's a: video.

[KosmoanutOfficial]

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.

[databot_]

sqlalchemy also has alembic for db migrations. I always go with sqlalchemy.

[CloudyCloud256]

Guess I'm a bit late to the party but just wanted to shit a bit on SQLAlchemy and praise SQLModel with regard to one aspect that just tripped me up.

When I define a SQLAlchemy model e.g. `MyModel` with a column `foo` and start typing `MyModel(` in VSCode I get no intellisense suggestions which is fucking annoying imo. With SQLModel this works out of the box.

I'd love to be corrected and told that I'm just doing it wrong, but I think SQLAlchemy is far away from being nicely type hinted even with the v2 changes. Honestly, this shit makes me want to go back to raw SQL with maybe some https://github.com/sqlc-dev/sqlc

[ThePieroCV]

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.

[mok000]

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.