At 17:18, I'm glad Arjan talked about coupling and separation trade-offs. I always felt awkward with ORM classes being so tightly coupled to DB tables which makes the domain classes hard to use in isolation. e.g., ORM assumes that you need a session when perhaps you have some behavior that doesn't involve the DB. One approach on this is to flip the dependency. Rather than make your domain model depend on ORM -- by using a class that inherits from SQLAlchemy or SQLModel to combine domain, validation, and/or DB logic -- consider making the ORM class extend your domain model.
SQLC is the best if you already know your way around SQL. Automatically generate pydantic models from raw SQL queries. You get both flexibility of raw queries (which you already probably already have) and type safety, no need to deal with ORMs.
IME, it is better to always keep a service layer between db transaction logic and api endpoint logic So it should be like this: API -> Service Layer -> DB Transaction For better decoupling and easy for testing
I don't think this is a good idea, there's no good reason to use sqlmodel: - you're coupling your api with database - sqlalchemy models can be declared using type hints, but you've shown essentially a deprecated way of defining them - there are some issues with sqlmodel, e.g. with model inheritance Also I have a lot of questions regarding the code in the video: - why didn't you use sessionmaker as a context manager but did that with session in sqlmodel? - why did you use deprecated query api in the sqlalchemy example? - why are you committing your changes manually whel that could be done in a dependency? - why is autoflush disabled?
I don't like this. I can see the value if you're writing a prototype just to get something that works, but for anything serious, you really don't want that level of coupling between the public API and the internal database representation. The docs show how to separate the table models from the API models, but at that point you might as well just use SQLAlchemy and Pydantic separately.
Just keep sqlalchemy and pydantic separate, adding a layer on top of sqlalchemy is just asking for weird bugs and you only need to refer to one set of documentation for sqlalchemy
Using a base class is mandatory if you want to add your own pydantic validators. E.g. if you want to check if a field value is a valid email address. If you use a class A(SQLModel, table=True), you cannot add validators to A. Also, working with database schemas is a pain in the a... like in sqlalchemy.
Uhm, I am not convinced, we are removing all the DTO / DAO differences with SQLModel, constraining the DB datastructure with the needs of the clients (what DTO actually provides)...
I honestly don't see the benefit of using SQLModel when 1. you can't do validations if they are tables, 2. sqlalchemy orm has the Mapped feature to allow native types.
I am concerned about how to teach less skilled engineers how to use this "facade" library correctly. As you pointed out, in practice the API and DAL models should never be combined for several reasons - proper app layering polices, security, SRP, etc. I think it is best to keep the API and DAL layer boundaries clear, and not use something like SQLModel which will definitely be the source of bugs and constant, er, teaching opportunities. In practice, API layers should NEVER be doing CRUD directly. I blame MS for teaching devs who are just starting to do this. We are still getting bit by that blunder. Shame on you MS. Sorry, I think this one is a pass for me.
Imma be "that guy" and say that the benefits of abstraction and ease of switching between backends are far outweighed by the benefits of rawdogging SQL directly and taking advantage of native functionality that may be specific to an engine
At 17:18, I'm glad Arjan talked about coupling and separation trade-offs. I always felt awkward with ORM classes being so tightly coupled to DB tables which makes the domain classes hard to use in isolation. e.g., ORM assumes that you need a session when perhaps you have some behavior that doesn't involve the DB.
One approach on this is to flip the dependency. Rather than make your domain model depend on ORM -- by using a class that inherits from SQLAlchemy or SQLModel to combine domain, validation, and/or DB logic -- consider making the ORM class extend your domain model.
Thumbnail 10/10
💯
SQLC is the best if you already know your way around SQL. Automatically generate pydantic models from raw SQL queries. You get both flexibility of raw queries (which you already probably already have) and type safety, no need to deal with ORMs.
IME, it is better to always keep a service layer between db transaction logic and api endpoint logic
So it should be like this:
API -> Service Layer -> DB Transaction
For better decoupling and easy for testing
I don't think this is a good idea, there's no good reason to use sqlmodel:
- you're coupling your api with database
- sqlalchemy models can be declared using type hints, but you've shown essentially a deprecated way of defining them
- there are some issues with sqlmodel, e.g. with model inheritance
Also I have a lot of questions regarding the code in the video:
- why didn't you use sessionmaker as a context manager but did that with session in sqlmodel?
- why did you use deprecated query api in the sqlalchemy example?
- why are you committing your changes manually whel that could be done in a dependency?
- why is autoflush disabled?
Cringe when I see ppl using the old way
Love SQLModel, it's pretty useful, although a bit limiting for the more "in the weeds" SQL stuff.
I don't like this. I can see the value if you're writing a prototype just to get something that works, but for anything serious, you really don't want that level of coupling between the public API and the internal database representation. The docs show how to separate the table models from the API models, but at that point you might as well just use SQLAlchemy and Pydantic separately.
💯 agree
It's highly incovenient and less flexible
Just keep sqlalchemy and pydantic separate, adding a layer on top of sqlalchemy is just asking for weird bugs and you only need to refer to one set of documentation for sqlalchemy
I just realized how much I love django orm.
Using a base class is mandatory if you want to add your own pydantic validators. E.g. if you want to check if a field value is a valid email address. If you use a class A(SQLModel, table=True), you cannot add validators to A.
Also, working with database schemas is a pain in the a... like in sqlalchemy.
your studio looks awesome!!
Thanks! 🙏
And what about Alembic? How does it integrate here?
would love to see how you integrate mongodb and fastapi, including pydantic etc.
Uhm, I am not convinced, we are removing all the DTO / DAO differences with SQLModel, constraining the DB datastructure with the needs of the clients (what DTO actually provides)...
A question i think sqlite is prone to database errors like dirty reads and has no locking mechanism. What is your understanding.
I wonder how well SQLModel would integrate with kedro-pydantic.
Honestly it burns my eyes seeing 1:12 two classes with same attributes, it\s probably the reason i'll champion flask.
uv is great! im waiting for video about it
My experience is to stick to the standard: SQL.
I honestly don't see the benefit of using SQLModel when 1. you can't do validations if they are tables, 2. sqlalchemy orm has the Mapped feature to allow native types.
I am concerned about how to teach less skilled engineers how to use this "facade" library correctly.
As you pointed out, in practice the API and DAL models should never be combined for several reasons - proper app layering polices, security, SRP, etc.
I think it is best to keep the API and DAL layer boundaries clear, and not use something like SQLModel which will definitely be the source of bugs and constant, er, teaching opportunities.
In practice, API layers should NEVER be doing CRUD directly. I blame MS for teaching devs who are just starting to do this. We are still getting bit by that blunder. Shame on you MS.
Sorry, I think this one is a pass for me.
Imma be "that guy" and say that the benefits of abstraction and ease of switching between backends are far outweighed by the benefits of rawdogging SQL directly and taking advantage of native functionality that may be specific to an engine
Peewee is still my favorite
IMHO: The usefulness of using ORM is highly questionable.
Maybe if limited in sql syntax
🫸 / 👉