Our great sponsors
-
-
WorkOS
The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.
> If you do any kind of dynamic SQL it can get pretty gnarly though
This can be addressed with typesafe SQL-builders like jooq [1]. I got an OLAP application with plenty of gnarly SQL broken up in individual business rules.
The best is that I don't even touch the SQL much. I expose it read-only to the users, hidden under an advanced mode button; and when they want to change things it often comes already written in SQL (mostly filters and projections). New queries come in requests to mix already-existing parts. And it helps technical knowledge be shared. Win-win all around.
Jooq works well with CRUD/OLTP apps as well. And when you have a problem, you don't have to debug both the ORM and SQL.
> Then it's important to learn how to optimize performance.
Starting with IO at the db level, and execution plan in the db. And ending right there without an ORM. Neither you nor your ORM are going to be smarter than the planner. For starters do you even collect statistics about your data? And your ORM doesn't even know what the downstream processing will be.
> Lastly ORMs deal with the issues an application typically has to deal with anyways. Such as mapping to objects,
Hopefully included with the typesafe SQL builder, but not always needed.
> detecting changes,
Plenty of SQL features for an audit trail
> caching,
Well maybe there. But if read-availability is your problem, reading from replicas gets you very far.
> concurrency
Comes out of the box with MVCC RDBMS; ie good'ol postgres, mysql
----
I'm not going back to ORMs
/rant
[1] https://www.jooq.org/
The vast majority of breaking changes should have been down to the Python 2 to Python 3 upgrade - since then I've seen very few breaking changes affect my projects.
The trick I use to protect against them is to run my test suite against multiple Python versions using GitHub Actions, e.g. here: https://github.com/simonw/sqlite-utils/blob/main/.github/wor...