SQLAlchemy can get pretty complicated with some of the hairier use cases; raw SQL can too, but SQLA adds another dimension to it:
* SQLA can infer joins, but sometimes it can't and needs hints - then you may worry that you miss some combination of table join it doesn't have enough information for.
* In one case, SQL inferred a join incorrectly and I ended up with dupe results of a cross join. this is possibly because of a certain primary key duplicated on a few tables. The way SQLA infers joins is a bit magical, and it's not always obvious how to modify that behavior.
* dealing with joining a table on itself can be complicated, when you need to distinguish between those two versions of itself (using an alias?)
* how about this use case: modify a set of filters to apply at two levels: one in filtering rows before a select query, then again to filter the result of joining those original results.
* If you operate with objects/models, for saving/retrieving things, it can often be hard to mix with raw SQL which can't easily be modeled (at least without committing everything, and then executing the SQL). That means you need to communicate that SQL in object syntax, which is far more complicated.
* for migrations, I need to redefine some models, because I need two - one representing the old DB structure, and another representing the new structure.
It's possible some of these have better solutions than I've found, but I find it's often hard to figure them out.
* SQLA can infer joins, but sometimes it can't and needs hints - then you may worry that you miss some combination of table join it doesn't have enough information for.
* In one case, SQL inferred a join incorrectly and I ended up with dupe results of a cross join. this is possibly because of a certain primary key duplicated on a few tables. The way SQLA infers joins is a bit magical, and it's not always obvious how to modify that behavior.
* dealing with joining a table on itself can be complicated, when you need to distinguish between those two versions of itself (using an alias?)
* how about this use case: modify a set of filters to apply at two levels: one in filtering rows before a select query, then again to filter the result of joining those original results.
* If you operate with objects/models, for saving/retrieving things, it can often be hard to mix with raw SQL which can't easily be modeled (at least without committing everything, and then executing the SQL). That means you need to communicate that SQL in object syntax, which is far more complicated.
* for migrations, I need to redefine some models, because I need two - one representing the old DB structure, and another representing the new structure.
It's possible some of these have better solutions than I've found, but I find it's often hard to figure them out.