2 ms·
Neither. Native SQL syntax is not very uniform, and it prevents parts from composing easily. String manipulation is fiddly, even with nicer (named) parameter s
by nine_k 4y ago
Neither.
Native SQL syntax is not very uniform, and it prevents parts from composing easily. String manipulation is fiddly, even with nicer (named) parameter substitution.
A typical tutorial ORM approach, with mapping relational data to objects, and then allowing to add data manipulation methods to the objects, and especially to override save(), kills most of the advantages of relational databases. It is particularly bad when you need to reach for parts of rows, or do updates on multiple rows, or even selects with more more complex conditions / joins.
The sweet spot is a library that allows you to easily describe tables, and write composable, terse statements that translate to SQL.
In Python land, for instance, both SQLAlchemy and Django ORM allow that. They allow more but I usually avoid making table models rich.
SQLAlchemy is the most composable, Lego-like of the two. You can, for instance, factor out complex conditions and share, mix and match them between various selects, updates, deletes, as needed.
Django ORM excels at being terse and describing joins in a select without even mentioning them, just referring to other tables via double__underscore, if the relation is already described in the table models.
Both operate with result sets which can be not only fetched but also combined. You can write a select (or .filter in Django) and e.g. give it as a parameter to another select, and the ORM layer will rewrite the result as a single query with appropriate joins or sub-selects.
You can easily issue updates to multiple records without fetching and hydrating a single one of them. It's a massive performance improvement compared to the naive ORM approach where you operate on DB data as if it were objects in RAM: a loop to fetch, update, and save each object.
You can easily limit the result set to the particular set of columns you want at the time, and avoid hydrating result sets into full-fledged objects which may be too large or unnecessarily nested.
You can of course sometimes use "proper" objects formed from relational data, when you need to compute something interesting from their attributes for rendering / reporting. But I try to avoid even that where possible.