"We are now using SqlAlchemy via schema reflection; no declarative layer." (emphasis mine)
What next? Will web development come full circle to using raw SQL queries like Phil Greenspun suggested years ago? (Not that there is anything wrong with it)
What next? Will web development come full circle to using raw SQL queries like Phil Greenspun suggested years ago? (Not that there is anything wrong with it)
At the start of the project, when queries are few and datasets are small, all of the above worked great. But the further you get into the project, and the more complex things you are trying to do with larger and larger datasets, the ORM ends up blowing up in your face. For example, LINQ to SQL--which I want to emphasize I'm picking on only because I'm using it righ tnow--happens to silently and transparently convert from IQueryable (meaning that your LINQ will execute in the DB) to IEnumerable (which means you'll suck in all data required for filtering past that point), which means that you never have a good idea whether your query is going to be optimized or not unless you check the SQL being generated. When you have to do this a few dozen times, you start wishing you'd just written SQL in the first case.
The situation gets worse when you start getting into join-heavy queries. I've never seen an ORM that actually can cover everything you need to make an app performant. It doesn't handle full joins, or compound indices, or distinguishing between clustered and nonclustered indices, or calculated columns, or some other thing that, if you were in raw SQL, would easily solve your problem, so at that point, you start introducing raw SQL. The older your app gets, the more instances of this you get, until you'd have been better off simply using SQL the entire time anyway.
I love the idea of an ORM in theory, but I'm pretty solidly convinced these days that if you want objects, you should us an OODBMS, and otherwise, you should just embrace the relational model for what it is, and not pretend you're working with objects. For most people, that'll mean writing SQL and getting back query sets.
Linq to Objects, however, is freaking amazing. C#'s functional and querying features are like Python's generator expressions on crack, speed, steroids, and acid. Absolutely love them.
LINQ is great. LINQ to SQL is actually very good, so far as that goes; it just happens to be trying to do something that I'm not convinced can be done well.
Schema synchronization is just a major headache, no matter from which end you approach it (declarative -> evolution or Raw DDL -> reflection).
Once your number of databases exceeds 1 you are in trouble either way.
The implicit assumption that a schema definition would ideally be set in stone once and then never changed is a legacy carried over from the very early RDBMS days. And it's one of the biggest albatrosses to date.
result = db.select('table', what='id, name',
where='id>3', order='id', limit=5)
I used to be against ORMs ("Another layer? How inefficient"), but now I see them like compilers: They're easier to work with and, most of the time, SQLAlchemy does the job better than me (lazy-loading, etc.).