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.
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.
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.
Being more hands on with sqlalchemy is probably the right approach.
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 ;-)
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.
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.
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
I am, and I'm warning them, whats wrong with that?