6 ms·

It looks like the author is generalizing shortcoming in SQLAlchemy as faults with the concept of ORMs. ActiveRecord for example, treats SQL as “the source of tr

It looks like the author is generalizing shortcoming in SQLAlchemy as faults with the concept of ORMs. ActiveRecord for example, treats SQL as “the source of truth”, per his definition. Also, there is a whole section called "ORMs take over connection management and migration" and not a single point as to why connection management handled by the ORM is bad, only comments on migrations. Moreover, migrations are not an unsolved problem at all. In Ruby, ActiveRecord solves all scenarios I have encountered gracefully, and trust me, I have dealt with tons of edge case projects. For Python, I recommend Alembic: https://pypi.org/project/alembic/ https://pypi.org/project/alembic/

vinceguidry 7y ago
They mention the case where the ORM shadows the db schema. The argument isn't terribly salient, it's not like you can't build type checking in, it just means more work if that's what you want to do. Essentially according to the author the only way to do it right if you have an existing db is to write all queries from scratch. My preferred solution for existing DBs is Ruby's Sequel or ROM if you want to go nuts. But I'm a diehard Rails lover and will choose ActiveRecord over all other solutions if I can. To me there's only one decent ORM and everything else is either good for special purposes or junk.
afcapel 7y ago
ORMHate is nothing new under the sun. Martin Fowler wrote a more balanced article about the issue years ago https://www.martinfowler.com/bliki/OrmHate.html https://www.martinfowler.com/bliki/OrmHate.html
Diggsey 7y ago
ActiveRecord does not treat SQL as "the source of truth". ActiveRecord objects maintain their own state, and still try to be a "proxy" for rows in the database. When you call "save", it's tracking the identity of the object using the primary key, and generating SQL from the runtime state of the object. This is backwards. The biggest problem for me is this "proxy" behaviour. When you start treating objects as proxies for database rows, your database logic inevitably leaks into your business logic. Someone returns an ActiveRecord `User` object from a database method and now random other parts of your application are intricately linked to your database implementation. On top of that you suffer terrible performance problems. Even if you're smart enough to avoid the whole N+1 query issue, you lose control (and more importantly visibility) over where database updates are coming from, and your ORM is either not smart enough to efficiently apply updates, or so smart that building the query takes longer than actually running it! (I have this problem with SQLAlchemy right now, where it's taking several seconds per request CPU bound in the ORM). Undoing this is literally impossible without a near total rewrite. And it all serves no purpose! The application-specific interface to its database is usually quite simple. You can just have it be a literal interface/trait/whatever and implement each method by executing a bit of SQL, or calling a stored procedure, or however you like. Arguments and return values should be plain-old-data (no magic proxy objects) and each method should correspond roughly to a transaction so that the rest of the application doesn't have to worry about that database-specific stuff. There are a ton of other benefits to doing stuff this way, too many to list here, but it really helps with complex migrations (like if you need to migrate from one database to another), maintainability, testing, etc.
- joevandyk 7y ago
  I’ve found that you want to be super careful in application code about where you do updates and inserts when using AR. Sprinkling calls to order.save! or order.update(foo: bar) in random places is a recipe for disaster.
- wavesplash 7y ago
  While you're not technically wrong at the SQL level, I believe there's a higher level of business problem that ORMs like ActiveRecord solve for that's being overlooked. (aka ActiveRecord got Airbnb and Shopify a long way in scaling both developers and product). CPU time building a query is rarely the business bottleneck until you're at a huge scale - scaling up the engineering team uniformly and having business logic at abstraction level closer to the rapidly evolving Product specs is the bottleneck that ActiveRecord solves for, and does pretty well. If you'd like something that avoids object/hidden state - check out how Elixir/Phoenix's Ecto[1] was designed - it avoids many of the shortcomings of ActiveRecord and SLQAlchemy: 1: https://hexdocs.pm/ecto/Ecto.html https://hexdocs.pm/ecto/Ecto.html
  - kangoo1707 7y ago
    +1 for Ecto, the most powerful ORM that I have seen so far (among PHP, Node, Ruby, Python and Elixir)
- jrochkind1 7y ago
  If "SQL is the source of truth" means you have no in-memory state, I'm not sure that's feasible, with or without an ORM. Once you fetch something from the db, you have it in variables in memory. With or without an ORM. That could no longer be sync'd with the db a ms later. That you will probably re-use for more than one access (if you need it more than once), because issuing the same SQL again the second time you need to reference any data returned, in a given routine, would be insane.
  - derefr 7y ago
    I think what they’re proposing is something like “if I want to read and then update a value, incrementing it by one, the proper way to do that is to tell the DB to increment whatever value it has stored; not to increment my local representation of the value and then tell the DB to overwrite the value with it.”
zzzeek 7y ago
> ActiveRecord for example, treats SQL as “the source of truth”, per his definition. SQLAlchemy author here. I can't imagine how someone would not consider SQLAlchemy to treat "SQL" as the "source of truth" as well. All of SQLAlchemy's constructs map directly to SQL syntactically. Our most grumpy users are the ones who don't know SQL. This blog post would certainly benefit from some actual examples. "ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?
- deleted 7y ago
  [deleted]
- taffer 7y ago
  What the blog probably means by "SQL as a source of truth" is that you first design your database and then generate the domain classes from it and not vice versa. This is for example what myBatis and JOOQ do.
  - deathanatos 7y ago
    I don't think there is enough information from the database schema to feed an ORM reliably. E.g., take a "boolean" in MySQL: MySQL doesn't have proper booleans, it just represents them as a `tinyint`. But you'd really like your ORM to convert those to your language's `bool`! There is certainly a lot of information in a schema, and you definitely want your ORM's idea of the database and the database's idea of it to be in close alignment, but I feel like any serious attempt would find all sorts of holes like the above when it actually came to doing it.
    - setr 7y ago
      Wouldn’t you just offer user-modifiable translators for such cases (presumably with some sane default)? It seems more preferable than writing the domain model in code first, resulting in N copies of the schema definition for N applications utilizing the db..
deleted 7y ago
[deleted]

vinceguidry 7y ago

They mention the case where the ORM shadows the db schema. The argument isn't terribly salient, it's not like you can't build type checking in, it just means more work if that's what you want to do. Essentially according to the author the only way to do it right if you have an existing db is to write all queries from scratch. My preferred solution for existing DBs is Ruby's Sequel or ROM if you want to go nuts. But I'm a diehard Rails lover and will choose ActiveRecord over all other solutions if I can. To me there's only one decent ORM and everything else is either good for special purposes or junk.

afcapel 7y ago

ORMHate is nothing new under the sun. Martin Fowler wrote a more balanced article about the issue years ago https://www.martinfowler.com/bliki/OrmHate.html https://www.martinfowler.com/bliki/OrmHate.html

Diggsey 7y ago

ActiveRecord does not treat SQL as "the source of truth". ActiveRecord objects maintain their own state, and still try to be a "proxy" for rows in the database. When you call "save", it's tracking the identity of the object using the primary key, and generating SQL from the runtime state of the object. This is backwards. The biggest problem for me is this "proxy" behaviour. When you start treating objects as proxies for database rows, your database logic inevitably leaks into your business logic. Someone returns an ActiveRecord `User` object from a database method and now random other parts of your application are intricately linked to your database implementation. On top of that you suffer terrible performance problems. Even if you're smart enough to avoid the whole N+1 query issue, you lose control (and more importantly visibility) over where database updates are coming from, and your ORM is either not smart enough to efficiently apply updates, or so smart that building the query takes longer than actually running it! (I have this problem with SQLAlchemy right now, where it's taking several seconds per request CPU bound in the ORM). Undoing this is literally impossible without a near total rewrite. And it all serves no purpose! The application-specific interface to its database is usually quite simple. You can just have it be a literal interface/trait/whatever and implement each method by executing a bit of SQL, or calling a stored procedure, or however you like. Arguments and return values should be plain-old-data (no magic proxy objects) and each method should correspond roughly to a transaction so that the rest of the application doesn't have to worry about that database-specific stuff. There are a ton of other benefits to doing stuff this way, too many to list here, but it really helps with complex migrations (like if you need to migrate from one database to another), maintainability, testing, etc.

joevandyk 7y ago
I’ve found that you want to be super careful in application code about where you do updates and inserts when using AR. Sprinkling calls to order.save! or order.update(foo: bar) in random places is a recipe for disaster.
wavesplash 7y ago
While you're not technically wrong at the SQL level, I believe there's a higher level of business problem that ORMs like ActiveRecord solve for that's being overlooked. (aka ActiveRecord got Airbnb and Shopify a long way in scaling both developers and product). CPU time building a query is rarely the business bottleneck until you're at a huge scale - scaling up the engineering team uniformly and having business logic at abstraction level closer to the rapidly evolving Product specs is the bottleneck that ActiveRecord solves for, and does pretty well. If you'd like something that avoids object/hidden state - check out how Elixir/Phoenix's Ecto[1] was designed - it avoids many of the shortcomings of ActiveRecord and SLQAlchemy: 1: https://hexdocs.pm/ecto/Ecto.html https://hexdocs.pm/ecto/Ecto.html
- kangoo1707 7y ago
  +1 for Ecto, the most powerful ORM that I have seen so far (among PHP, Node, Ruby, Python and Elixir)
jrochkind1 7y ago
If "SQL is the source of truth" means you have no in-memory state, I'm not sure that's feasible, with or without an ORM. Once you fetch something from the db, you have it in variables in memory. With or without an ORM. That could no longer be sync'd with the db a ms later. That you will probably re-use for more than one access (if you need it more than once), because issuing the same SQL again the second time you need to reference any data returned, in a given routine, would be insane.
- derefr 7y ago
  I think what they’re proposing is something like “if I want to read and then update a value, incrementing it by one, the proper way to do that is to tell the DB to increment whatever value it has stored; not to increment my local representation of the value and then tell the DB to overwrite the value with it.”

zzzeek 7y ago

> ActiveRecord for example, treats SQL as “the source of truth”, per his definition. SQLAlchemy author here. I can't imagine how someone would not consider SQLAlchemy to treat "SQL" as the "source of truth" as well. All of SQLAlchemy's constructs map directly to SQL syntactically. Our most grumpy users are the ones who don't know SQL. This blog post would certainly benefit from some actual examples. "ORM-light tools that coerce responses into native structs and allow for type-checking are less offensive to me." - that is...an ORM?

deleted 7y ago
[deleted]
taffer 7y ago
What the blog probably means by "SQL as a source of truth" is that you first design your database and then generate the domain classes from it and not vice versa. This is for example what myBatis and JOOQ do.
- deathanatos 7y ago
  I don't think there is enough information from the database schema to feed an ORM reliably. E.g., take a "boolean" in MySQL: MySQL doesn't have proper booleans, it just represents them as a `tinyint`. But you'd really like your ORM to convert those to your language's `bool`! There is certainly a lot of information in a schema, and you definitely want your ORM's idea of the database and the database's idea of it to be in close alignment, but I feel like any serious attempt would find all sorts of holes like the above when it actually came to doing it.
  - setr 7y ago
    Wouldn’t you just offer user-modifiable translators for such cases (presumably with some sane default)? It seems more preferable than writing the domain model in code first, resulting in N copies of the schema definition for N applications utilizing the db..

deleted 7y ago

[deleted]