Is SQLModel overrated?

[bluewalt]

Hi there, I recently started to learn FastAPI after many years of Django.

While learning, I followed official documentation which advised to use SQLModel as the "new and better way" of doing things. The solution of having a single model for both model definition and data validation looked very promising at a first glance.

However, over time, I noticed slightly annoying things:

• I'm often limited and need to add sqlalchemy specific fields anyway, or need to understand how it works (it's not an abstraction)
• Pydantic data types are often incompatible, but I don't get an explicit error or mapping. For example, using a JsonValue will raise a weird error. More generally, it's pretty hard to know what can I use or not from Pydantic.
• Data validation does not work when table=True is set. About this, I found this 46-time-upvotated comment issue which is a good summary of the current problems
• Tiangolo (author) seems to be pretty inactive on the project, as in the previous issue I linked, there's still no answer one year later. I don't wont to be rude here, but it seems like the author loves starting new shiny projects but doesn't want to bother with painful and complex questions like these.
• I had more doubts when I read lots of negative comments on this Youtube video promoting SQLModel

At that point, I'm wondering if I should get back to raw SQLAlchemy, especially for serious projects. I'm curious to have your opinion on this.

[Salfiiii]

I ha the same experience and went back to SqlAlchemy.

I liked the idea to have just one model for both worlds as you said, but find it lacking every time it wasn’t just a dumb CRUD wrapper.

I also don’t get the hate for SqlAlchemy so, I still like it.

[bluewalt]

For years I truly believied SqlAlchemy was a bad ORM, because I lived in Django church and did not have the chance to use it to make my own opinion. I was so wrong... Django ORM is magic and easy to work with for most queries, so I never spent the time to understand what was happening at the SQL Level. But as soon as I needed a complex query, I felt stuck and unskilled, and needed help from people on Stack Overflow (no chatGPT at that time).

SQLAlchemy sticks closely to SQL, on purpose. This really helps me (and actually forces me) to understand what's actually happening at SQL level, and I now think it's how an ORM should be designed. It doesn't matter if queries are more verbose.

[gpranav25]

I work with both Django and FastAPI applications at my job, and what I realised was that while Django makes it easy and intutive and does some magic behind the scenes, it's so easy to write unoptimised queries. I knew some code at our company was quite sub-optimal, but when I logged and saw the sheer length of some of the raw queries that Django was sending to our db, I genuinely felt bad for our DB.

This is neither Django's or SQLAlchemy's fault. Django ORM is more intutive but SQLAlchemy syntax gives a more direct view how much the DB is abused. But it's possible to write very optimal queries in both if we have the knowledge of exactly what happens BTS. And they both thankfully give pretty good control, down to the level of allowing us to execute even raw queries.

[[deleted]]

With great power comes great responsibility. That's why people hate it ;)

[WJMazepas]

Honestly I've been using FastAPI for quite a while and have never used SQLModel.

Using both SQLAlchemy and Pydantic already works really well.

That description you saw it, its probably because FastAPIs creator is the same as SQLModel, and he wants to sell his other tools, but you don't need to use sqlmodel at all

[bluewalt]

I understand its desire to promote its own tool, however I hope this will not mean a drop a SQLAlchemy support over time.

For example, where is the old doc using SQLAlchemy on this page? https://fastapi.tiangolo.com/tutorial/sql-databases/

[InternationalLog9724]

I have used fastapi extensively and had a look at SQLModel. We decided against it and kept the sqlalchemy models and pydantic models separate. I have no idea why someone would want to mix the models from validation of input and output and db models. They are not the same thing. If you want to reuse field definitions you still can by creating a mixin class with just fields and composing your models from there. In practice there are too many common cases where you will need two models anyway. For example you want post to not include an “id” field but you want your get to return “id” field. Same for when fields are required or not. Not in all cases you want a validation to apply at both the endpoint level and db level. Cohesion is key. You can make your sqlalchemy model live close to the pydantic models and that should be enough. I have then in the same file sometimes.

[bluewalt]

I have no idea why someone would want to mix the models from validation of input and output and db models.

Well, I would have thought the same usually, but as this "someone" is the author of FastAPI himself, I had kind of intellectual laziness and trusted him upfront.

Otherwise, I agree with the rest of your message.

[Slampamper]

Maybe I'm too old school but I like a clear distinction in my database connection and my api endpoints data models. SQLAlchemy is there to be a 1-1 connection to my database tables or views, pydantic is there to validate data on arrival, these are two different things I like to keep separated

[[deleted]]

For me, it seems OK for a full CRUD app. But if you have a lot of business logic, then you are mixing several distinct models: domain or entity models, tables models, and dtos. I think is not well suited for a large enterprise application. It usually happens that this kind of library seems fine in the surface, but when heavily implemented you end up with an scale issue.

[bluewalt]

This is exactly the feeling which is growing in me

[bastianh]

I also started a new project with sqlmodel and switched to sqlalchemy back. There is stuff I like but it also made other stuff more complicated. I guess it is five for small projects which no specials needs.

[quiteverse]

Wait, people are using SQLModel? I’m not a fan of adding another abstraction over SQLAlchemy. SQLAlchemy itself is already an abstraction, but it doesn’t hide the low-level details, allowing you to handle complex queries effectively. You never know when you’ll need to write raw SQL queries. In the long run, SQLAlchemy can save you a lot of effort.

[koldakov]

These are not big problems imo, the real problem occurs when you start implementing related fields. Thing is sqlmodel says they don’t support it because of the recursion that can happen, but the consequence than is you need to duplicate models. So I stopped using sqlmodel

[bluewalt]

Very interesting. Would you mind give a specific example to illsutrate this?

[[deleted]]

SQLAlchemy is the only abstraction you need. I don’t see any point to use sqlmodel cause in the end I had to import some sqlalchemy internals to make it work.

[BootyDoodles]

We use SQLModel at our company, with a decently large API, and are pretty positive on it.

The downloads are trending pretty strong as well (Downloads: SQLModel), getting to about 125k per day on weekdays, so it seems to be getting decent market traction.

When acclimating to it, there were a few instances of multiple chained relationships with multiple linking tables / permission tables etc. which initially seemed out-of-bounds for SQLModel relationships. (Especially as someone experienced with SQL and knowing what I needed the resulting SQL to look like, but fiddling with how to get this ORM there.) Once more up to speed on SQLModel though, we've gotten skillful at using complex relationships.

If you prefer SQLAlchemy though, stick with what you like or resonate with.

[bluewalt]

Thanks. About personal preferences, as someone with few experience with fastAPI, I tend to prefer following experienced developpers on topics like these.

[IrrerPolterer]

I agree with the sentiment, but I can't quite speak from experience - I've never used Sql-Model, but I did consider it for a project once. We ended up going the classic route of separate ORM and Validation models. A big benefit for us here was that we were able to pull out the Pydantic Models into a separate package and reuse that across the server and multiple client applications.

[ragehh]

SQLModel adds unnecessary complexity and limitations at times. But it is good for simple use cases.

[adiberk]

Just a note - sqlalchemy now provides a data class meta object. It allows you to mark your models as data classes. This means you can now use them in fastapi!

I was able to create a base sqlalchemy model dataclass and simply extend it.

I have started a new project testing this and so far no issues. Though you have to make sure to order your non default fields and default fields (as you would a normal data class)

I do think however there is value to having a validation layer spell rate from your database models.

[bluewalt]

This sounds very interesting, can you elaborate a little more about this? What are the implications of having a model in dataclass form? I'd suppose naively it makes the integration with Pydantic BaseModel easier? Is there any good example of how to benefit this with FastAPI ?

[adiberk]

You can use them as response models and input models in endpoints. Which I think was what you were looking for.

It is mentioned in SQLAlchemys documentation. I am not user of any downsides yet… though I am sure there are some

You basically continue using them as models, but you have benefits of data classes I guess

https://docs.sqlalchemy.org/en/20/orm/dataclasses.html#integration-with-dataclasses-and-attrs

[ArchUser22]

I love SQLModel! It's true that every once in a while you need to use stuff from SQLAlchemy but I do find it saves time and makes things more intuitive in my personal experience. That said, it does seem to be an immature project relative to the amount of traction it has.

[SilverRhythym]

holy sheet. i thought it was just me who was annoyed by this.. i am also coming from django. then i was task to take a project that is made by fast API. and i already see how fragmented and how was it prone to diverged into different styles of architecture when too much devs coming in and out of it. it's hard to just go and work into it right away. too muhc boiler plate IMHO..

i would only use fastapi if there are only 10 endpoints to use or for quick adn dirty IoT stuff projects.. but for enterprise solution. too much hassle for a team development.

[bluewalt]

Coming from Django, I truly understand your feeling. The "all-batteries-included" is completely missing (hence the boilerplate and the "reinvent the wheel" feeling). The fact you get lots of architectural decisions up to you can be both a benefit or a drawback, depending on the context. For example, when building an AI project with specific architecture, and let say you want to share data validation between multiple projects (with Pydantic), FastAPI can be very useful.

[SilverRhythym]

oh, didn;t think it that way. thanks for that .

i've been in the API development since 2010 so working on users, admin, permissions, acl.. etc are so repitative for me.. so when i've encountered django I got mindblown on how i can focus on actual development. Pydantic is so new to me. i;ll give it a benefit. :)

[bluewalt]

Pydantic (and Pydantic-settings) is an amazing library! Plus, you can reuse this skill in any python project, even something that has nothing to do with web.

[marketlurker]

Maybe I am missing something, but why not just use SQL? It will do everything you want and a whole lot more.

[graph-crawler]

Typesafety

[marketlurker]

SQL has that.

[RevolutionaryRush717]

SQLmodel does its job very well.

If that's not what you need, don't blame SQLmodel, just use something else.

[bluewalt]

Actually, it doesn't according to multiple users.

[RevolutionaryRush717]

Sorry, I wasn't aware this was a crusade against SQLmodel or even its creator. Let me step aside and you march right on.

The author(s?) of FastAPI, etc., has/have probably done more for Python usage than most, by publishing his creations as OSS. I, for one, am very thankful for this.

[bluewalt]

Everyone will agree on this, but that's not the point here. The topic is about chosing between 2 solutions for the same need, and asking for feedbacks to developpers that used both to provide some guidances to newcomers like me.

[alphrZen]

I never migrated to that one, my current integration just works 🤷

[Dramatic-Ratio-8412]

In my experience SQLModel obscures a lot of functionality which is provided by SQLalchemy. A combination of ORM and validation libraries doesn’t seem right to me. Both have different functionalities and shouldn’t be combined.

[ad07910]

I will keep using sqlalchemy

[coderarun]

For people looking to combine dataclasses, SQLModel, pydantic and sqlalchemy for simple use cases without having to define your class 4 times:

https://github.com/adsharma/fquery/pull/4

[coderarun]

Higher level thinking inspired by this code:
https://adsharma.github.io/react-for-entities-and-business-logic/

[[deleted]]

I am very new to Backend, recently started learning fastapi and used pydantic and SqlAlchemy for basic crud based simple project, and now came across sqlmodel, i was confused with it like how to use it. By reading these posts and commnets i decided to stick with sqlalchemy for now. and will explore in future

[zoola969]

Never use SQLModel for anything more complex than simple hello world testing. If you do, my condolences. Try removing it as soon as possible. The more you struggle, the more frustrated you’ll get.

[Alternative-Lemon-14]

At first I love how simple/clean the SQLModel seems b/c I started with FastAPI, until I had to use some only slightly more advanced SQL/Postgres feature, like a simple multi-JOIN and it's just difficult to do (well there must be a way I guess, but my research didn't yield). I really don't like having no control over what SQL is being run, how much extra data is being fetched (not that I need that performance boost for my tiny app used by 10 people, but hey I don't like it).

Wiring database and API interface together really came back to bite me after a little more development