I know that the author of SQLAlchemy is reading these comments and I don't want to be mean, but anecdotally I hear a lot of pain stories and the OP seems to largely deserve the title "Don't Use SQLAlchemy".
Nobody suggests that ActiveRecord is perfect, but it is pretty amazing at what it does. I think a big part of the reason it's more reliably excellent is that Rails in general embraces the notion of "convention over configuration" - there's typically a correct naming scheme (with opinionated pluralization) that starts in the table scheme and flows straight through the models to the controllers and finally the view layer. I can join your Rails project and make guesses about where your logical components are located and what they are called that would beat any TV psychic cold 100:1.
Plus, much like with SQLAlchemy and basically every other modern ORM... you are always one method away from writing raw SQL if you want to be.
As always: use the tools that work best for you and for the vast majority of web development cases, you'd have to pry ActiveRecord out of my hands.
I use SQLAlchemy to serve tens of thousands of requests per second on one of my services at Reddit. It's painful, but possible.
SQLAlchemy uses the data mapper + unit of work patterns. This means that your connections and your record objects have the same lifecycle. If your request takes 200ms, then you are likely holding open your connections for 190ms, even though the only SQL query takes 20ms. If connections are scarce, and they usually are, you will not utilize them well at all.
There are two ways that I know of to solve this, neither of which is pretty: (1) run pgbouncer as a sidecar on your python processes. This has the effect of making connections a far cheaper resource. (2) After every single query, manually close the related transaction/connection/cursor in your code.
ActiveRecord defaults to giving up the connection after every query, which in a high-traffic webapp, is much less of a footgun.
(No significant experience with either SQLAlchemy or ActiveRecord, but work in Java at a shop that really strongly emphasizes the unit-of-work pattern.)
"give up connection after every query" is, IMO, not _less_ of a footgun, but a _more subtle_ footgun. That is to say, sure, for the most part, it will work ok.
_But when it doesn't_... like when your database goes down for a couple hours, or your rack loses network, or your proxy to whatever cloud provider is running your database, or maybe some other dependency had an outage between the queries... you end up not really knowing what executed and what didn't, with some fraction of your 10k req/sec leaving behind some dangling state -- a partially processed $whatsit -- and no clear/easy way to fix it or even necessarily identify how many $whatsits were affected. At least a few times, I've needed to either wait until records make their way into some OLAP system for offline analysis, or deploy some fixup code, or emulate either/both to fix stuff up after something weird happens.
At least that's what I am constantly afraid of. Do you also worry about this kind of stuff? If not, what prevents it from being a problem in your environment?
Signed,
The guy constantly badgering seemingly everyone about minding their transaction boundaries.
From my experience, there’s always one developer in every enterprise dev shop who understands transaction boundaries, isolation levels, and concurrency/reliability scenarios that do happen....and 10 other devs constantly instilling fear in him/her
But he's not saying "don't use transactions." He's saying "Don't keep your transaction open for your entire user connection for no reason. Close your completed transactions as soon as you can."
Fun story: A coworker and I are in the process of tracking down exactly violations of this in a misbehaving app, due to an accumulation of RPC within transactions across the entire app. (I used to be responsible for it as a greenhorn ~5years ago, but walked away for the last several years to work in a different part of the org.)
The approach is actually kinda interesting: we add an interceptor in the RPC layer for every outgoing call that asks the DB machinery “am I currently in a transaction?”, and emits a warning (containing at least the RPC being made, perhaps a (partial) stack trace) if so. We had so many occurrences of those warnings that our logging framework proactively started dropping log lines! So the next step is to only emit the warnings once per $duration per message.
Just to hopefully further contribute, one of the top-deck features of the new Rails 6.0 release is its really thoughtful engineering work around multiple database support. At least, that's how it's named or described; the actual net result is a lot of smart people exhausting themselves making n-instance DB stack reliability and performance better on every chart... including setups where the value of n is 1.
What this translates to is that things should be impressively stable when your DB nodes go down, the backhoe shows up to party, or your BGP is routed to North Korea.
You can divorce your objects from the session if you want. But you lose transactions then, so with any approach that involves giving up the connection it seems to me you’ll create a whole other class of headaches for yourself.
I’m lucky in that I have low requests per second so I never have to worry about any of that too much. I don’t envy you having to deal with that sort of load.
Entity Framework, even with its quirks, has been the best developer experience with an ORM for me no contest. A lot of that is probably tooling; like the standard C# tooling and also Linq Pad.
SQLAlchemy is probably close to the worst experience and that's likely largely due to python and pythons tooling state.. And the docs. Just something about the docs. Still, other than bulk insert performance I got it to do just about everything I needed and came to the conclusion it was the best python ORM for my needs.
Nobody suggests that ActiveRecord is perfect, but it is pretty amazing at what it does. I think a big part of the reason it's more reliably excellent is that Rails in general embraces the notion of "convention over configuration" - there's typically a correct naming scheme (with opinionated pluralization) that starts in the table scheme and flows straight through the models to the controllers and finally the view layer. I can join your Rails project and make guesses about where your logical components are located and what they are called that would beat any TV psychic cold 100:1.
Plus, much like with SQLAlchemy and basically every other modern ORM... you are always one method away from writing raw SQL if you want to be.
As always: use the tools that work best for you and for the vast majority of web development cases, you'd have to pry ActiveRecord out of my hands.