6 ms·

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.

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.”
  - jrochkind1 7y ago
    The number of times I've had to increment a value by one are minimal. Or similar things where you want to update one value based on the other values, and do it in an atomic way. But if I had to increment specifically, and were using ActiveRecord... I'd use the ActiveRecord increment! method. Which has the proper db-atomicity semantics you want, it does execute `UPDATE x SET n = n+1 WHERE pk = y ...`
  - Scarblac 7y ago
    ORMs do that fine, for instance it'd be MyModel.objects.filter(pk=...).update(myfield=F('myfield') + 1) In Django's ORM.
    - jayd16 7y ago
      So obvious and simple! ...or you could just use SQL.
    - rubber_duck 7y ago
      This is not obvious unless you think about it - I've seen issues like this because the code looks correct if you ignore the update semantics, especially when it wont fail on your local dev env because in memory state will be the same as DB state since you're the only one hitting it... Having to think about these things because the abstraction is leaky instead of just using the SQL where the transformation is explicit is not worth the hassle. Using Clojure and yesql was a real pleasure for this - Clojure is immutable so you're working with values all over the place - and working with SQL query is just passing values in it and getting values out - no magic mapping - it's just values - if you want the latest value - query again, if you want to store a new value - send the new value. No mutation, no magic, just data.
zzzeek 7y ago
> (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. for the write? writes are not usually much of a performance issue except when people are inserting thousands of rows. You can replace flush with direct INSERT/UPDATE or use the bulk API. Pre-setting primary key values will also improve performance by an order of magnitude. Share some of your code and profile results on the mailing list and we can look into speeding it up. SQLAlchemy always provides many techniques to optimize areas that have performance problems, not to mention the primary direction for most major releases is that of new performance features and improvements year after year, despite having to deal with the dog-slow cPython interpreter. No "total rewrite" of your application should be needed.
- Diggsey 7y ago
  The SQLAlchemy ORM is a very complex beast. It provides plenty of methods to optimise the queries it generates. It provides no way to "magically speed up the python code". This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy. The only way to get the performance improvements we needed was to stop using it as an ORM altogether. However, the ORM was too intricately tied with our business logic to do that without a near total rewrite. What we've actually ended up doing to do this rewrite more incrementally is to create a Rust service which implements just the "GET" end-points: this gives us an order of magnitude performance improvement despite it using the exact same database and fetching the exact same data. Most of the business logic did not need to be duplicated as it is not used from "GET" requests, but users of our application will get much snappier loading times, and it will generally feel much better.
  - zzzeek 7y ago
    > It provides no way to "magically speed up the python code". it most certainly does provide many techniques to reduce in-Python computation. Have you read the performance section in the FAQ and worked through the examples given ? Have you looked into baked queries , querying for columns and not objects ? These are often quick wins that make a huge difference. The baked queries concept is going to be much more cleanly integrated in an upcoming release. > This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy. that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python. Here's a runsnakerun I make some years ago of the PyMYSQL driver loading data over a network-connected database: https://techspot.zzzeek.org/files/2015/pymysql_runsnake_network.png https://techspot.zzzeek.org/files/2015/pymysql_runsnake_netw... Notice how just the Python driver spends TWO THIRDS of the time an the actual waiting for the database 1/3rd ? that's Python. If the database is on localhost, that pink box shrinks to nothing and all of the time is spent in Python, just reading strings and building tuples. It's a very slow language. Rust language however is blazingly fast and is one of the fastest languages you can use short of straight C code. This is not the fault of an ORM, this is just the reality of Python. You certainly wouldn't think that if you used an ORM that runs under Rust, it would be as slow as your Python application again. It would continue to be extremely fast. The Hibernate ORM is an enormous beast but runs immensely faster than SQLAlchemy because it's running on the JVM. These are platform comparisons. SQLAlchemy is really fast for the level of automation it provides under pure Python. You made the right choice rewriting in a fast compiled language for your performance critical features but that has little to do with whether or not you use an ORM. As long as your database code was in your Python application, you'd end up writing an ORM of your own in any case, it would just be vastly more code to maintain.
jayd16 7y ago
>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. Indeed. Its a rookie mistake but hey, you work with rookies all the time! Orms can be useful but they certainly allow for some dangerous patterns that are hard to notice if you're a young developer.

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.”
- jrochkind1 7y ago
  The number of times I've had to increment a value by one are minimal. Or similar things where you want to update one value based on the other values, and do it in an atomic way. But if I had to increment specifically, and were using ActiveRecord... I'd use the ActiveRecord increment! method. Which has the proper db-atomicity semantics you want, it does execute `UPDATE x SET n = n+1 WHERE pk = y ...`
- Scarblac 7y ago
  ORMs do that fine, for instance it'd be MyModel.objects.filter(pk=...).update(myfield=F('myfield') + 1) In Django's ORM.
  - jayd16 7y ago
    So obvious and simple! ...or you could just use SQL.
  - rubber_duck 7y ago
    This is not obvious unless you think about it - I've seen issues like this because the code looks correct if you ignore the update semantics, especially when it wont fail on your local dev env because in memory state will be the same as DB state since you're the only one hitting it... Having to think about these things because the abstraction is leaky instead of just using the SQL where the transformation is explicit is not worth the hassle. Using Clojure and yesql was a real pleasure for this - Clojure is immutable so you're working with values all over the place - and working with SQL query is just passing values in it and getting values out - no magic mapping - it's just values - if you want the latest value - query again, if you want to store a new value - send the new value. No mutation, no magic, just data.

zzzeek 7y ago

> (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. for the write? writes are not usually much of a performance issue except when people are inserting thousands of rows. You can replace flush with direct INSERT/UPDATE or use the bulk API. Pre-setting primary key values will also improve performance by an order of magnitude. Share some of your code and profile results on the mailing list and we can look into speeding it up. SQLAlchemy always provides many techniques to optimize areas that have performance problems, not to mention the primary direction for most major releases is that of new performance features and improvements year after year, despite having to deal with the dog-slow cPython interpreter. No "total rewrite" of your application should be needed.

Diggsey 7y ago
The SQLAlchemy ORM is a very complex beast. It provides plenty of methods to optimise the queries it generates. It provides no way to "magically speed up the python code". This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy. The only way to get the performance improvements we needed was to stop using it as an ORM altogether. However, the ORM was too intricately tied with our business logic to do that without a near total rewrite. What we've actually ended up doing to do this rewrite more incrementally is to create a Rust service which implements just the "GET" end-points: this gives us an order of magnitude performance improvement despite it using the exact same database and fetching the exact same data. Most of the business logic did not need to be duplicated as it is not used from "GET" requests, but users of our application will get much snappier loading times, and it will generally feel much better.
- zzzeek 7y ago
  > It provides no way to "magically speed up the python code". it most certainly does provide many techniques to reduce in-Python computation. Have you read the performance section in the FAQ and worked through the examples given ? Have you looked into baked queries , querying for columns and not objects ? These are often quick wins that make a huge difference. The baked queries concept is going to be much more cleanly integrated in an upcoming release. > This is not just for writes, but also for loading complex relationships from the database: we've gone to great lengths to ensure we're using relationships optimally so as to cache query results and not fall into the N+1 query problem, but our application spends all its time inside SQLAlchemy. that's typical because a CRUD application is all about its object model and SQLAlchemy is doing all of that work. if you wrote your own object layer using Core then you'd see all the time spent in your own objects<->core layer. This is just the reality of Python. Here's a runsnakerun I make some years ago of the PyMYSQL driver loading data over a network-connected database: https://techspot.zzzeek.org/files/2015/pymysql_runsnake_network.png https://techspot.zzzeek.org/files/2015/pymysql_runsnake_netw... Notice how just the Python driver spends TWO THIRDS of the time an the actual waiting for the database 1/3rd ? that's Python. If the database is on localhost, that pink box shrinks to nothing and all of the time is spent in Python, just reading strings and building tuples. It's a very slow language. Rust language however is blazingly fast and is one of the fastest languages you can use short of straight C code. This is not the fault of an ORM, this is just the reality of Python. You certainly wouldn't think that if you used an ORM that runs under Rust, it would be as slow as your Python application again. It would continue to be extremely fast. The Hibernate ORM is an enormous beast but runs immensely faster than SQLAlchemy because it's running on the JVM. These are platform comparisons. SQLAlchemy is really fast for the level of automation it provides under pure Python. You made the right choice rewriting in a fast compiled language for your performance critical features but that has little to do with whether or not you use an ORM. As long as your database code was in your Python application, you'd end up writing an ORM of your own in any case, it would just be vastly more code to maintain.

jayd16 7y ago

>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. Indeed. Its a rookie mistake but hey, you work with rookies all the time! Orms can be useful but they certainly allow for some dangerous patterns that are hard to notice if you're a young developer.