Hacker News Reader

Comment by parhamn | Hacker News Reader

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.

View on HN

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

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.

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.

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. ;-)

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.

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

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

If you don’t use Django’s ORM or SQLAlchemy’s ORM, then do you really need to use Django?

Like an adjacent commenter said, ORM is ok for simple blog websites. But I can’t imagine using an ORM for complex data tables and relationships with normalized data.

I used the Django ORM for surprisingly complex applications

I created a video streaming website, with 600k visitor days, that features tagging, categories, related vidéos, user upload, encoding queue, search with auto-completion and so on. It relies heavily on the ORM.

Django migration system and db model generation also make it a very nice tool for data export/migration/exploration.

The ORM provides a nice API, but above all, a standardized one, which lead the Django ecosystem to become so reach because of the assumptions the libraries could do, making you very productive.

Now, one has to remember:

- you can, and should by pass the ORM if you need it, writing raw SQL. Django let you do that.

- plenty of things can end up be in a cache, using varnish, redis, etc. Plus a lot of things should be done in task queues anyway, not in the django views. So the cost of the ORM is rarely an issue.

- complex data tables are sometimes better presented using SQL views, making the ORM model simple.

As usual with coding, it's a matter of gain and cost, and the right answer is "it depends". But you can go very far, and very comfortably with this tools, as long as you don't fight against its nature.

So yes, definitely use Django with the ORM. I am not advocating to use Django + SQLA, but rather contrast Django with Flask + SQLA.

>But I can’t imagine using an ORM for complex data tables and relationships with normalized data.

Whenever I read a statement like this from someone, I always feel that they actually haven't thoroughly read the docs for Django's ORM. Can you give me one concrete complex example that you can't imagine doing with the ORM? Because 95% of the time, these "complex" models are trivially easy to pull off with the ORM (from my experience).

Django orm doesn't support composite primary keys.

The beauty of the Django ecosystem, is that you have packages for everything: djangopackages.org

E.G: https://github.com/Arisophy/django-compositepk-model

Fair enough. There is `unique_together` though, which you may look into.

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.

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.

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.

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.

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.

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.

I use Django with "real data" as well and, generally, it's pretty good once you learn how to wield it. My biggest issues are lack of multi-column primary keys and inefficient queries when doing .count(), that often introduce unnecessary group by statements in the subquery. If either of those could be addressed, I'd enjoy it so much more.

Like... Query.join(), https://docs.sqlalchemy.org/en/13/orm/tutorial.html#querying... .

Django is optimizing for the 99.99% of people rather than the 0.01% of people who manage billions of rows on a single table. I like SQLAchemy, but for most people the added cost of managing connections, transactions, have to decide whether to use the 'ORM' or not, manually having to 'commit' changes, eager-loading vs. lazy-loading, is a complete waste of time if all they want is a website that will be used by - at best - a couple hundred people concurrently.

What weak assumptions does it make?

Yeah our org should really move away from flask-sqlalchemy and be more declarative about what session changes should actually be made on.

Being more hands on with sqlalchemy is probably the right approach.

I passed on flask-sqlalchemy because it was not obvious to me how it worked together, and I did not see the value over just using Flask and SQLAlchemy with no special integration. Has worked now a couple of years in, will see how it is in a few years more.

Not having touched this stack in a while: what is best current practice for wiring together sqlalchemy and flask (including unit tests)?

Potentially don't wire them together? That is, isolate them in separate parts of the code.

I always found Flask-SQLAlchemy a strange approach because it ties opposite and somewhat orthogonal parts of the request lifecycle together. When requests-to-data-transactions is a 1-1 relationship it's a convenience, but once you step out of that I think it's better to do things by hand.

My main app has a simple layer structure of "request > business logic > data layer". All of the business logic functions start a SQLAlchemy transaction using a Python decorator that is ~10 lines of SQLAlchemy code.

Check out how things are wired with in this Pyramid cookie cutter.

https://github.com/Pylons/pyramid-cookiecutter-starter/blob/...

I think same approach should work well for flask - just tie the session to the request object on first access.

Side note: I think we met on IRC few years ago ;-)

There is a lot going on in that cookie cutter. The “transaction” library for transaction management, wired into Pyramid with pyramid_tm, wired into SQLAlchemy with zope.sqlalchemy. Works nicely for Pyramid, but I don’t know how that translates to Flask.

Think on it like a middleware that wraps a transaction. It's fine, till you start consuming other Io based services (elastic/solar/...) And you find that this blocks one connection per request..

Doesn't, I wanted to show how it can be wired to request.

> hundreds of millions of users

Just curious, but how many employees? How many specifically are responsible for writing code that interacts with SQLAlchemy and/or the objects it creates. While I don't doubt it is _possible_ to make SQLAlchemy work great, I think it's mandatory to overstaff with engineers/programmers in order to do so.

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

I don't think this blame is as unfair as you imply, even if if were the primary cause of ORM induced performance disasters. In my experience the main selling point of ORMs has always been the promise for people who fancy themselves programmers to leverage DB technology without having to sully themselves or their cherished OO model by learning the relational model, databases or SQL. The other, equally false promise has been that the ORM would somehow allow you to "abstract" over your concrete DB so you could easily swap out one for another. Both these ideas now seem utterly misguided to me (although many years ago I probably fell for the same nonsense). For all of SQL's warts, I find the relational model conceptually much superior to OO. Moreover since the way data moves in and out of the DB (and the consistency/transactional and performance constraints around this) is often more architecturally central than the python code that orchestrates it, what ORMs are trying to do, shoe-horning the well thought out relational model into the badly thought out OO model seems utterly backwards to me -- spanning the cart before the horse so to speak. Now I will readily admit that SQLAlechemy is about the least bad ORM I have encountered, but I still absolutely fail to see what the point of it's ORM part is (I can see a bit of the appeal of being able to dynamically construct or compose queries with core, although I'd try hard to avoid the necessity).

Clearly you know what you are doing with hundreds of millions of users and you are not the sole large user of SQLAlchemy either, so I am evidently missing something. Can you maybe give one or two examples of things that are much easier/robust/... because of SQLAlchemy, compared to just writing SQL, in separate files, and executing that from you python code?

To be clear, there are various things that I think are broken about how databases work, and I'd really like to see them fixed but I don't see how SQLAlchemy helps much with most of the below (with the notable exception of query parameterization).

- no high level support for migrations: it should be possible to get a readable aggregate schema defintion out for example and it isn't

- parameterization and composition of queries is awkward and limited

- (for most DBs) lack of in-process support

- bad testing framework support

> Blame users that use...

I am, and I'm warning them, whats wrong with that?

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.

The trick is to always auto close transactions at the end of the request.

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.

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

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

Please describe footguns and how to avoid. Thanks!

Reply on news.ycombinator.com