9 ms·

Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM o

Why are we even having this debate? There are some ORMs that bring so much value to the table that you'd be stupid not to use them. Example being Django's ORM or SqlAlchemy. Also be specific in what ORM you are comparing to raw SQL. Are you talking about Hibernate or SQLAlchemy. Are you talking about a query builder? Good ORMs help tremendously with maintainability and security. They also let you drop down to raw SQL when needed. I don't think rails would have been as popular if you had to use SQL.

djrobstep 9y ago
I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.
- wasted_intel 9y ago
  Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.
  - jsmeaton 9y ago
    `create proc if not exists get_customer_v1243(params)` .. I've seen this.
  - 0xCMP 9y ago
    I mean you could, but it’d need to be through some kind of migration system to update it.
    - other_herbert 9y ago
      Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
    - zkomp 9y ago
      There are several such tools, which are specialized and thus tend to perform a better job than orms (who try to do everything) But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)
  - vectorpush 9y ago
    Sure you can, just store them in .sql files and have your CI system auto deploy them.
  - programmarchy 9y ago
    Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.
  - sbuttgereit 9y ago
    Of course you can, at least I do. In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest. The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database. I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore. Consider a tool like: http://sqitch.org/ http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code. There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with. Anyway, I just don't buy this argument.
  - GFischer 9y ago
    Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.
- deleted 9y ago
  [deleted]
- BurningFrog 9y ago
  One problem with having logic in the DB is that it's much harder to scale if you need 10x more computing power.
  - ckarmann 9y ago
    Scaling is like optimization in general: not everything will need to be able to scale, and even if they will, they often do not need to scale yet.
  - ako 9y ago
    Moving the definition of your sql from your app to the database doesn't impact scalability, as in both cases the database needs to perform the query.
    - BurningFrog 9y ago
      We're talking about stored procedures, which are executable code/business logic.
      - ako 9y ago
        Yes, and if all your stored procedure is doing is execute a query and return a cursor to the result set, it's using just as much database cpu as with a regular query.
normaljoe 9y ago
Django's ORM is actually a good example of where identity is an issue. It lacks composite primary keys. If I have a dependent table that has my real ID, say an order number that is a varchar I have to join to the parent table to do lookups by the order number. I can't doing something like key(order_number, line_number) so I end up hydrating a parent object for operations that only require operations on the dependent objects.
- kamyarg 9y ago
  an ORM does not have to solve every single problem you have, write your raw sql and run it.
- nikisweeting 9y ago
  It doesn't have to hydrate the parent object, just use .only('relation_id').
minitech 9y ago
> Good ORMs help tremendously with maintainability and security. ORMs don’t help with security compared to query builders or even typed text.
- icebraining 9y ago
  Security is not just about injection. The ORM we use automatically applies security access rules to the queries, for example.
  - zimpenfish 9y ago
    > The ORM we use automatically applies security access rules to the queries, for example. But surely that's better handled in the database itself?
    - icebraining 9y ago
      How would you apply user-configured security rules in the database?
      - zimpenfish 9y ago
        Good question. It would depend what kind of rules they were and how they were configured. Wasn't specified that they were "user-configured" in the original statement, though.
        icebraining 9y ago
        Fair enough, ours are :) The kind of rules we use are boolean expressions that identify which rows the user's groups can read/write/delete. The ORM automatically combines all the rules as a single expression and applies it to the query.
        zimpenfish 9y ago
        As best I can tell, Postgres lets you do this albeit constraining your boolean checks to things you can efficiently do in the database. But then I can definitely see how this could get unwieldy very quickly. https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.h...
zkomp 9y ago
I disagree. ORMs dont bring value, they dilute value and piles abstractions upon apstractions. Your application becomes hard to maintain, database hard to refactor, queries slow. ORMs seems nice for simplified problems but becomes a horrible mess for real problems imho... I've worked on some rails apps, and the ORMs caused more problems than they solved...
- gtsteve 9y ago
  A few years ago, I worked on an application which would occasionally run into performance issues. Every time the solution was to rewrite the Entity Framework usage into standard SQL. Sometimes it would generate the strangest but somehow legal queries which would be challenging for SQL Server to optimise. For my next app, I just started using SQL only and never looked back. Simple queries are easily generated with a nice internal API (SELECT * FROM Table WHERE ID = X, etc). However, more complex selects are all written using hand-written SQL. New developers sometimes find this a bit strange, especially the younger ones, but nobody can fault the system's speed.

djrobstep 9y ago

I've used SQLalchemy a lot. I've even written a keyset paging extension for SQLalchemy. But recently I've switched to writing stored procedures and calling them directly, instead of going through an ORM for everything... And it's so much easier.

wasted_intel 9y ago
Keeping logic in the database like that means you can’t version those procedures alongside the rest of your code. That’s a pretty big downside.
- jsmeaton 9y ago
  `create proc if not exists get_customer_v1243(params)` .. I've seen this.
- 0xCMP 9y ago
  I mean you could, but it’d need to be through some kind of migration system to update it.
  - other_herbert 9y ago
    Flyway for Java... For stored procedures you use the repeatable syntax that way it checks the checksum of the file and if it doesn't match what is in the migration table it will run it... Easy and always up to date... That's with Java anyway...
  - zkomp 9y ago
    There are several such tools, which are specialized and thus tend to perform a better job than orms (who try to do everything) But I would recommend writing, reviewing and deploying migrations by hand, esp for critical parts of the schema (automatic tools are almost guaranteed to get something wrong, with locking etc)
- vectorpush 9y ago
  Sure you can, just store them in .sql files and have your CI system auto deploy them.
- programmarchy 9y ago
  Sure you can. SQL is just plain text, so keep all your create table scripts in your repo, along with deploy and rollback scripts that you’d use to extend or migrate your schemas.
- sbuttgereit 9y ago
  Of course you can, at least I do. In the databases I've worked with extensively (PostgreSQL and, somewhat in the past, Oracle) Store Procedures are routinely versioned controlled as part of the application, just these bits of code are in a different language than the rest. The creation of functions/procedures is not tied to state of the database in quite the same way as tables are; the functions/procedures, where they care about the data, do need to recognize the table structure and changes to that structure, but that's no different than any other of the application code which makes use of the data in the database. I think where many people get caught up on this is that they do something like migrations to get code, including procedural code, into the database... but that's not the only game in town. And really, given what's possible with databases today, I'm not sure migrations are even the best way anymore. Consider a tool like: http://sqitch.org/ http://sqitch.org/ which facilitates not treating stored procedures as migrations, but rather as individual files which change just like any other code. There are ways to accomplish having good version control on the table/structure side as well, which again, is something you lose with the migration tools I've worked with. Anyway, I just don't buy this argument.
- GFischer 9y ago
  Microsoft has SQL Server Data Tools which can be integrated into your favorite SCM (we use VSTS but we'll switch to Git soon), can have automated deploys (which we don't) and in general can be a part of a modern development lifecycle.
deleted 9y ago
[deleted]
BurningFrog 9y ago
One problem with having logic in the DB is that it's much harder to scale if you need 10x more computing power.
- ckarmann 9y ago
  Scaling is like optimization in general: not everything will need to be able to scale, and even if they will, they often do not need to scale yet.
- ako 9y ago
  Moving the definition of your sql from your app to the database doesn't impact scalability, as in both cases the database needs to perform the query.
  - BurningFrog 9y ago
    We're talking about stored procedures, which are executable code/business logic.
    - ako 9y ago
      Yes, and if all your stored procedure is doing is execute a query and return a cursor to the result set, it's using just as much database cpu as with a regular query.

normaljoe 9y ago

Django's ORM is actually a good example of where identity is an issue. It lacks composite primary keys. If I have a dependent table that has my real ID, say an order number that is a varchar I have to join to the parent table to do lookups by the order number. I can't doing something like key(order_number, line_number) so I end up hydrating a parent object for operations that only require operations on the dependent objects.

kamyarg 9y ago
an ORM does not have to solve every single problem you have, write your raw sql and run it.
nikisweeting 9y ago
It doesn't have to hydrate the parent object, just use .only('relation_id').

minitech 9y ago

> Good ORMs help tremendously with maintainability and security. ORMs don’t help with security compared to query builders or even typed text.

icebraining 9y ago
Security is not just about injection. The ORM we use automatically applies security access rules to the queries, for example.
- zimpenfish 9y ago
  > The ORM we use automatically applies security access rules to the queries, for example. But surely that's better handled in the database itself?
  - icebraining 9y ago
    How would you apply user-configured security rules in the database?
    - zimpenfish 9y ago
      Good question. It would depend what kind of rules they were and how they were configured. Wasn't specified that they were "user-configured" in the original statement, though.
      - icebraining 9y ago
        Fair enough, ours are :) The kind of rules we use are boolean expressions that identify which rows the user's groups can read/write/delete. The ORM automatically combines all the rules as a single expression and applies it to the query.
        zimpenfish 9y ago
        As best I can tell, Postgres lets you do this albeit constraining your boolean checks to things you can efficiently do in the database. But then I can definitely see how this could get unwieldy very quickly. https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.html https://www.postgresql.org/docs/9.5/static/ddl-rowsecurity.h...

zkomp 9y ago

I disagree. ORMs dont bring value, they dilute value and piles abstractions upon apstractions. Your application becomes hard to maintain, database hard to refactor, queries slow. ORMs seems nice for simplified problems but becomes a horrible mess for real problems imho... I've worked on some rails apps, and the ORMs caused more problems than they solved...

gtsteve 9y ago
A few years ago, I worked on an application which would occasionally run into performance issues. Every time the solution was to rewrite the Entity Framework usage into standard SQL. Sometimes it would generate the strangest but somehow legal queries which would be challenging for SQL Server to optimise. For my next app, I just started using SQL only and never looked back. Simple queries are easily generated with a nice internal API (SELECT * FROM Table WHERE ID = X, etc). However, more complex selects are all written using hand-written SQL. New developers sometimes find this a bit strange, especially the younger ones, but nobody can fault the system's speed.