6 ms·

I’ve seen quite a few shops which effectively hit every python db/networking/pooling foot gun you could possibly encounter while using SQLAlchemy. People carg

I’ve seen quite a few shops which effectively hit every python db/networking/pooling foot gun you could possibly encounter while using SQLAlchemy. People cargo cult the flask intro tutorial and have no clue how session binding works, where the transactions are, how committing works, etc because it’s all tucked away in magical middleware and singletons. As the code base grows so does the mess of blocking txns, accidental cross joins, pool exhaustion, and so on. It’s a great tool from a technical expressiveness perspective but terribly full of operational foot guns. Beware and use Django until you’re sure you AND your team know what you’re doing.

zzzeek 6y ago
SQLAlchemy author here. Your comment states the problem space addressed by SQLAlchemy 2.0, which is what the above document is about, really well! So for your comment to have value, what did you think of SQLAlchemy 2.0's direction in how it seeks to vastly improve the very issues you speak of, "where the transactions are", "how committing works", etc.? For example: "accidental cross joins" - SQLAlchemy 1.4 now warns/ raises for these: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#built-in-from-linting-will-warn-for-any-potential-cartesian-products-in-a-select-statement https://docs.sqlalchemy.org/en/14/changelog/migration_14.htm... cool huh? The new docs are worth a read before commenting. It sounds like you were relying on flask-sqlalchemy in any case which unfortunately makes some poor decisions in these areas, but in SQLAlchemy 2.0 these things are brought forward unconditionally in all cases so you really can't talk to the database without having your transaction and its scope front and center.
- parhamn 6y ago
  I'm sorry if my comment struck negatively with you. The library has been very useful to me and teams Ive worked with in the past. In fact, I've lobbied for corporate sponsorship of your work a few times. Thank you. The problem I'm describing isn't really the ORM libraries' fault as its outside the scope (hah). Your library is great. Most of the trouble I see is where the ORM meets the web framework. I think that gets tricky for lower experience developers because it's fundamentally tricky in Python. What the scheduler (e.g. gunicorn) does and how it forks and how sessions are handled at the wsgi layer are where you have to be very careful. Django has brought that in scope and you haven't -- which is why thats a better wholistic webdev experience and SQLAlchemy is the more powerful ORM. With that said I stand by my comment having read the full changelog (commendably thorough btw). Unless you can afford to figure those things out as a team, it would behoove most web devs to reach for Django first.
  - zzzeek 6y ago
    no worries, I'm pretty known for being reactive to comments of all kinds, it's both a feature and a bug. SQLAlchemy was never meant to be as "simple" as django but I just dont want people to be scared off of it; that said, I'm super glad everyone who uses django sticks with django because they would be very grumpy SQLAlchemy users. the idea is supposed to be, make a larger investment upfront, get a bigger payoff. it's a tough balloon to float which is why I'm always nosing around people's twitter threads.
    - mixmastamyk 6y ago
      If you've got the best design in the long run there's nothing to worry about. That said, it takes a while to find it! And the target is always moving. 2.0 definitely looks like a step in the right direction. Can't wait to use it with psycopg3 and the latest Python. Thanks. ;-)
  - joshbuddy 6y ago
    This exactly mirrors the situation where I work. They picked flask/sqlalchemy early on, the actual glue code is hard to get right, people were confused over the session api vs core api and problems ensue. As much as we want to tell people you should read docs carefully, libraries should make it hard to misuse and introduce footguns, and this is an aspect sqlalchemy maybe struggles with.
ergo14 6y ago
We are running sqlalchemy in a giant environment - hundreds of millions of users. SQLAlchemy has been a blessing for us. Blame users that use flask-alchemy or whatever else is cool. This is exactly the same type of comment that blames ORM-s for ineffective SQL being used because people don't know how to write the queries in the first place.
- TomBombadildoze 6y ago
  You put it better than I could. There are certainly naive examples of how to use it out in the wild but that's not SQLAlchemy's fault. It's a sophisticated tool that accurately reflects how a database works. I caution people against using Django because the ORM makes so many weak, simplified assumptions about how a database works that don't bear out in practice. It's fine for a blog. It falls apart when you're working with Real Data.
  - kennydude 6y ago
    I've used Django with "real data", as in accountancy software for multiple years of transactions across 200+ fairly large clients and it's absolutely fine. Whereas I've used Sqlalchemy and getting it to do a basic join, turns out there's 3 different ways of doing it all with an insane amount of crap to find the right way of doing things. I actually left my old job in part due to Sqlalchemy being too painful to work with.
    - BiteCode_dev 6y ago
      There is absolutely a lack of a decent SQLA tutorial for IRL code. The doc is very formal, and if you want to make a website, a quick script or a data migration, you are left on your own to find the proper setup.
    - ergo14 6y ago
      Like... Query.join(), https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying-with-joins https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying... .
    - 6y ago
msluyter 6y ago
Example: a team I was on was using SQLAlchemy and we had a couple of services, one that did read only queries, and another that did writes and occasionally DDL (rebuilding the table on the fly, (which is unwise but that's a topic for another post.)) Because we didn't understand SQLAlchemy's default semantics, the read side would effectively open a transaction and leave it open, meaning that subsequent DDL statements would block, then causing the entire table to lock. (I think our solution was to use `autocommit=True`, which iirc is now deprecated.) That said, I really like SQLAlchemy.
- ergo14 6y ago
  The trick is to always auto close transactions at the end of the request.
BiteCode_dev 6y ago
> Beware and use Django until you’re sure you AND your team know what you’re doing. That's one of my pet peeve in the Python world: people thinks Flask is for beginners and Django for advanced users. The Flask API is simple, the hello world is 5 lines. By the time you render your first Django ViewModel, you have read and tried out the full Flask doc. So it's easy to say, "I'll start with Flask, and check out this Django thing later". But in reality, it's exactly the opposite. Flask requires you to know a lot of things and make a lot of decisions, not to mention select/write additional libraries for features Django packs. So if you use Flask for anything non trivial, you should show your trade or you are going to mess up, big time. If you are a beginner, you should always start with Django. Yes, it's more work up front, but it will give you an idea of how important concepts work, plug in to each others, and can be organized in a code base. Django is not perfect, and its ORM is really far from SQLAlachemy, but it's good enough for a loads of thing. And it will be mostly secure and extensible by default.
- mrRandomGuy 6y ago
  100% The upfront cost of Django makes people thing it's much more complicated than a micro-framework like Flask. I like Flask, but not because it's a simple easy to use framework, especially once you chuck SQLAlchemy into the mix. Shit, even now if I don't touch it for a while I end up trying to remember how things work
- gchamonlive 6y ago
  In the context of database transactions, any APIs serve only as backend endpoints. For this purpose I question whether flask is the best choice. For rest API, I always go for Falcon.
  - BiteCode_dev 6y ago
    While I do keep my eyes on FastAPI, even in this case I would recommend Django to a beginner, solely because of the amazing Django Rest Framework. Not only it makes creating an API from ORM models quick and easy (although the documentation doesn't let you believe so), but it also let you do custom views seaminglessly, including goodies such as standardized permissions, authentication and pagination with several levels of granularity. Having that being able to be integrated with all the rest of the Django ecosystem transparently (your website, a wagtail CRM, etc.) is really sweet. But the biggest advantage for a beginner is again that it will prevent them from having to make decisions they are likely to get wrong. Especially on the serialization, format, identifier policy and homogeneity of the API: people crafting that by hand often make a big mess and don't even know about it.
nodamage 6y ago
Do you mind elaborating on these what these foot guns are and how to avoid them?
You-Are-Right 6y ago
Please describe footguns and how to avoid. Thanks!
jokull 6y ago
The flipside of this is that SQLAlchemy teaches people about pooling, threading, processes etc. .... but yes, by painful trial in some cases.
brainless 6y ago
I feel you are mixing totally different libraries and blaming SQLAlchemy for it. I use SQLAlchemy a lot and go through its documentation. I use it with FastAPI or other micro-frameworks. But I need SQLAlchemy to really work with ... SQL! And it is solid at that. The glue libraries between bigger ones like SQLAlchemy and Flask usually are less maintained and have less eyeballs on them. These are weaker points IMHO, not SQLAlchemy.

zzzeek 6y ago

SQLAlchemy author here. Your comment states the problem space addressed by SQLAlchemy 2.0, which is what the above document is about, really well! So for your comment to have value, what did you think of SQLAlchemy 2.0's direction in how it seeks to vastly improve the very issues you speak of, "where the transactions are", "how committing works", etc.? For example: "accidental cross joins" - SQLAlchemy 1.4 now warns/ raises for these: https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#built-in-from-linting-will-warn-for-any-potential-cartesian-products-in-a-select-statement https://docs.sqlalchemy.org/en/14/changelog/migration_14.htm... cool huh? The new docs are worth a read before commenting. It sounds like you were relying on flask-sqlalchemy in any case which unfortunately makes some poor decisions in these areas, but in SQLAlchemy 2.0 these things are brought forward unconditionally in all cases so you really can't talk to the database without having your transaction and its scope front and center.

parhamn 6y ago
I'm sorry if my comment struck negatively with you. The library has been very useful to me and teams Ive worked with in the past. In fact, I've lobbied for corporate sponsorship of your work a few times. Thank you. The problem I'm describing isn't really the ORM libraries' fault as its outside the scope (hah). Your library is great. Most of the trouble I see is where the ORM meets the web framework. I think that gets tricky for lower experience developers because it's fundamentally tricky in Python. What the scheduler (e.g. gunicorn) does and how it forks and how sessions are handled at the wsgi layer are where you have to be very careful. Django has brought that in scope and you haven't -- which is why thats a better wholistic webdev experience and SQLAlchemy is the more powerful ORM. With that said I stand by my comment having read the full changelog (commendably thorough btw). Unless you can afford to figure those things out as a team, it would behoove most web devs to reach for Django first.
- zzzeek 6y ago
  no worries, I'm pretty known for being reactive to comments of all kinds, it's both a feature and a bug. SQLAlchemy was never meant to be as "simple" as django but I just dont want people to be scared off of it; that said, I'm super glad everyone who uses django sticks with django because they would be very grumpy SQLAlchemy users. the idea is supposed to be, make a larger investment upfront, get a bigger payoff. it's a tough balloon to float which is why I'm always nosing around people's twitter threads.
  - mixmastamyk 6y ago
    If you've got the best design in the long run there's nothing to worry about. That said, it takes a while to find it! And the target is always moving. 2.0 definitely looks like a step in the right direction. Can't wait to use it with psycopg3 and the latest Python. Thanks. ;-)
- joshbuddy 6y ago
  This exactly mirrors the situation where I work. They picked flask/sqlalchemy early on, the actual glue code is hard to get right, people were confused over the session api vs core api and problems ensue. As much as we want to tell people you should read docs carefully, libraries should make it hard to misuse and introduce footguns, and this is an aspect sqlalchemy maybe struggles with.

ergo14 6y ago

We are running sqlalchemy in a giant environment - hundreds of millions of users. SQLAlchemy has been a blessing for us. Blame users that use flask-alchemy or whatever else is cool. This is exactly the same type of comment that blames ORM-s for ineffective SQL being used because people don't know how to write the queries in the first place.

TomBombadildoze 6y ago
You put it better than I could. There are certainly naive examples of how to use it out in the wild but that's not SQLAlchemy's fault. It's a sophisticated tool that accurately reflects how a database works. I caution people against using Django because the ORM makes so many weak, simplified assumptions about how a database works that don't bear out in practice. It's fine for a blog. It falls apart when you're working with Real Data.
- kennydude 6y ago
  I've used Django with "real data", as in accountancy software for multiple years of transactions across 200+ fairly large clients and it's absolutely fine. Whereas I've used Sqlalchemy and getting it to do a basic join, turns out there's 3 different ways of doing it all with an insane amount of crap to find the right way of doing things. I actually left my old job in part due to Sqlalchemy being too painful to work with.
  - BiteCode_dev 6y ago
    There is absolutely a lack of a decent SQLA tutorial for IRL code. The doc is very formal, and if you want to make a website, a quick script or a data migration, you are left on your own to find the proper setup.
  - ergo14 6y ago
    Like... Query.join(), https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying-with-joins https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying... .
  - 6y ago

msluyter 6y ago

Example: a team I was on was using SQLAlchemy and we had a couple of services, one that did read only queries, and another that did writes and occasionally DDL (rebuilding the table on the fly, (which is unwise but that's a topic for another post.)) Because we didn't understand SQLAlchemy's default semantics, the read side would effectively open a transaction and leave it open, meaning that subsequent DDL statements would block, then causing the entire table to lock. (I think our solution was to use `autocommit=True`, which iirc is now deprecated.) That said, I really like SQLAlchemy.

ergo14 6y ago
The trick is to always auto close transactions at the end of the request.

BiteCode_dev 6y ago

> Beware and use Django until you’re sure you AND your team know what you’re doing. That's one of my pet peeve in the Python world: people thinks Flask is for beginners and Django for advanced users. The Flask API is simple, the hello world is 5 lines. By the time you render your first Django ViewModel, you have read and tried out the full Flask doc. So it's easy to say, "I'll start with Flask, and check out this Django thing later". But in reality, it's exactly the opposite. Flask requires you to know a lot of things and make a lot of decisions, not to mention select/write additional libraries for features Django packs. So if you use Flask for anything non trivial, you should show your trade or you are going to mess up, big time. If you are a beginner, you should always start with Django. Yes, it's more work up front, but it will give you an idea of how important concepts work, plug in to each others, and can be organized in a code base. Django is not perfect, and its ORM is really far from SQLAlachemy, but it's good enough for a loads of thing. And it will be mostly secure and extensible by default.

mrRandomGuy 6y ago
100% The upfront cost of Django makes people thing it's much more complicated than a micro-framework like Flask. I like Flask, but not because it's a simple easy to use framework, especially once you chuck SQLAlchemy into the mix. Shit, even now if I don't touch it for a while I end up trying to remember how things work
gchamonlive 6y ago
In the context of database transactions, any APIs serve only as backend endpoints. For this purpose I question whether flask is the best choice. For rest API, I always go for Falcon.
- BiteCode_dev 6y ago
  While I do keep my eyes on FastAPI, even in this case I would recommend Django to a beginner, solely because of the amazing Django Rest Framework. Not only it makes creating an API from ORM models quick and easy (although the documentation doesn't let you believe so), but it also let you do custom views seaminglessly, including goodies such as standardized permissions, authentication and pagination with several levels of granularity. Having that being able to be integrated with all the rest of the Django ecosystem transparently (your website, a wagtail CRM, etc.) is really sweet. But the biggest advantage for a beginner is again that it will prevent them from having to make decisions they are likely to get wrong. Especially on the serialization, format, identifier policy and homogeneity of the API: people crafting that by hand often make a big mess and don't even know about it.

nodamage 6y ago

Do you mind elaborating on these what these foot guns are and how to avoid them?

You-Are-Right 6y ago

Please describe footguns and how to avoid. Thanks!

jokull 6y ago

The flipside of this is that SQLAlchemy teaches people about pooling, threading, processes etc. .... but yes, by painful trial in some cases.

brainless 6y ago

I feel you are mixing totally different libraries and blaming SQLAlchemy for it. I use SQLAlchemy a lot and go through its documentation. I use it with FastAPI or other micro-frameworks. But I need SQLAlchemy to really work with ... SQL! And it is solid at that. The glue libraries between bigger ones like SQLAlchemy and Flask usually are less maintained and have less eyeballs on them. These are weaker points IMHO, not SQLAlchemy.