4 ms·

> I’ve had a bad experience with ORM in the recent past, and I generally recommend against introducing this sort of abstraction to code. Yeah I don’t get ORMs.

> I’ve had a bad experience with ORM in the recent past, and I generally recommend against introducing this sort of abstraction to code. Yeah I don’t get ORMs. Most ORMs advertise “type safety” and not having to learn SQL. But actually you’re just writing SQL code in a non-SQL language. Usually the type-safety is weak, and a good IDE will provide type-checking and even data-source checking to raw SQL anyways. Converting complex object operations into relational operations is non-trivial. ORM objects auto-query and auto-update from the database, but these queries/updates are often unoptimal or happen at invalid times. It’s much easier to write and execute the SQL queries manually, and then auto-convert the results to/from regular objects.

davidw 5y ago
> Most ORMs advertise “type safety” and not having to learn SQL. But actually you’re just writing SQL code in a non-SQL language I think you're doing it wrong if you end up doing really complex queries in your ORM. It's kind of a judgement call, but at a certain point, you need to switch over to SQL. Good ORM's let you do that without too much hassle.
- dboreham 5y ago
  ORM selling point: you can bypass it.
  - davidw 5y ago
    Well, sure. It makes it easy to do simple stuff and gets out of the way when you want to bypass it rather than being from the "sorry Dave, I can't let you do that" school of interface design.
- piyh 5y ago
  I wrote a django app using postgres full text search. I ended up going with direct sql calls for anything more than a single record get because of all the specialized syntax. It's nice to keep stuff like new record creation clean, but once you get more than one join deep it's time to break out the best domain specific language ever created.
dboreham 5y ago
If you think an ORM is dumb, you haven't tried an FRM.
- jmnicolas 5y ago
  What is a FRM? A quick search didn't get me anything relevant to databases.
dragonwriter 5y ago
> Most ORMs advertise “type safety” and not having to learn SQL. But actually you’re just writing SQL code in a non-SQL language. That's...not writing SQL code, just as advertised. In the same way that writing code in a higher-level language isn't writing native machine code.
- armchairhacker 5y ago
  The difference is you’re still writing SQL keywords, just as functions e.g. userPosts = Posts.InnerJoin(Users).SelectAll().Where(user => user.id == userID) It’s not always exactly the same, but the point remains that you have to understand SQL anyways and the code usually isn’t any clearer. Maybe i’m confusing this with query builders. ORMs also let you form queries by interacting with objects directly, e.g. ‘user = Users.Find(userID); userPosts = user.Posts’. But that leads to the other issue with inefficient and unpredictable queries.
mdavis6890 5y ago
As a person that writes my own SQL and hasn't had much/any use for ORMs in the past - I have the imagination that if you were using an ORM you could switch the underlying database without a lot of trouble. Is there any truth to this?
- pphysch 5y ago
  When has anyone ever a) had to switch from RDBMS to RDBMS in a production system and b) it not be a massive pain in the neck?
- armchairhacker 5y ago
  It depends on how big the switch is. I assume it would be a bit easier because the query and ORM syntax is uniform across databases. And it also probably wont support many database-specific operations. But it would still be work. Some databases don’t support basic operations (e.g. UPDATE with LIMIT in postgres) which you can still do with the query syntax. And different databases usually have different preferred (usually faster) ways of doing indexes, joins etc. Plus you still have to migrate your data. ORMs are ideally supposed to be a complete abstraction where you can pretend the database doesn’t exist, so if you switch it shouldn’t matter. The problem in, in practice you can’t pretend the database doesn’t exist. So you either end up writing code for your specific dialect anyways, or you write inefficient code. It’s also like, you can just write plain SQL and ignore your dialect’s extra features. And that would make migration much easier, but it would also be less efficient and much harder to write.
- jmnicolas 5y ago
  I think the database change thing is way overrated. I'm 12 years in the same company, I juggle with Postgres, MySQL, SQL Server (and Oracle previously). Most of the databases I access are from external providers. We changed our main provider 2 years ago, so their DB went from Oracle to SQL Server. So in 12 years that would have been one database change. Except all the schema has changed and they don't allow SQL queries on it but provide good old SOAP web services... So even if I had used a full ORM (I use Dapper, you write the SQL, it "just" converts the result of the query to objects) I still would have to change my code to adapt to the new provider. However going from SQL to webservices really convinced me SQL is a superpower. With it you get exactly the data you need, no need to re-process it in code. Before this change I never had to really look at my apps performances, it was good enough. But since this provider webservices are so inefficient (for example I can't "query" a single customer, I have to get all of them) I had to optimize my app in every possible way to mitigate these inefficiencies.
theshrike79 5y ago
Every single project I've seen that has used an ORM has had to spend days debugging "why is this simple query pulling half the database" -bugs.
- henvic 5y ago
  2. This is why I'm pushing so hard to keep things with plain SQL at work. So far, another team has experimental with squirrel (a SQL query builder) and it looks sane, but I myself wouldn't introduce not even this. In my previous company, I was forced to use an outdated forked version of gorm. I'd just do my best to do anything else other than leading with databases there. Running the test suite took minutes, and to run way fewer test cases. It was a nightmare.
orangepanda 5y ago
> write SQL queries manually, and then auto-convert the results to/from regular objects. In any meaningul sized codebase, converting to/from objects will be used all over the place. Then someone adds an abstraction on top, and congratulations - there is now an inhouse orm.
- systemvoltage 5y ago
  That's not the same thing as ORM. ORM constructs queries based on arbitrarily complex request. Abstraction layers that we add on top of existing 'handcrafted' SQL queries is often expected and necessary. With base foundation rock solid, explicit and unchanging – you can build abstractions safely and with peace.
fy20 5y ago
The only good ORM I've used is Rail's ActiveRecord. Sure there are gotchas you need to know about, maybe it's just experience, but I find these to be a non-issue. Every other ORM in other languages I've tried seems to have WTF moments. It could be that Ruby is a dynamic enough language that it allows you to work around these things easily, that stricter languages do not.
captainmuon 5y ago
One benefit of using an ORM is that you don't write SQL strings, you write statements and method calls. So you get better IDE support, and detect syntax errors before they hit the database. If you are just writing a CRUD application, maybe with one or two `joins` or `order by`, then an ORM is perfect. If you are doing anything more complex, then by all means drop to plain SQL. The real question to me is, why can't we drop the relational database? In many small projects (where you would use an ORM) it is overkill. Why can't I create a list of plain-old-data objects in my language, then add indicies on that, and do fast queries directly in the language? Most languages already have a data structure with an index on one key (a dict, hashmap or similar). With C#'s LINQ or Pythons list comprehensions we already have half of the solution.
- Zababa 5y ago
  From what I understand, data duplication can lead to consistency errors. And if you want to avoid data duplication, you're going to rewrite a relational database yourself, since you would want to put your data into normal forms to avoid duplication. I think I've read a few times on Hacker News that we already went through the alternatives to the relational database around the time of its birth, and that a few papers were published at the time on why this was a better solution than others.
twoquestions 5y ago
> a good IDE will provide type-checking and even data-source checking to raw SQL anyways I've never in my life heard of this, which IDE are you talking about? I'd love to use something like that! I'm in C# land where I can use Management Studio to write queries which works well enough, but going between that and Visual Studio does have some friction. Even though we use Entity Framework here, I mostly use it to manage migrations than to work with data. Most of the time unless a query is really trivial, I'll just write some SQL and use Dapper for object mapping, I find it a lot easier once the query gets to be over three or four lines or so.
- armchairhacker 5y ago
  IntelliJ IDEA. For example in Java if you put // language=SQL before a string literal or @Language(“SQL”) before a function parameter at declaration and call with the string literal, the literal text will be highlighted. You “configure SQL dialect” and “configure data source” to get it to point to your database
chakkepolja 5y ago
> auto-convert the results to/from regular objects. Sadly this functionality, which I think is not much more complex than a JSON parser, is absent from many toolkits / libraries, and an immediate option people see is an ORM. Libraries that just do serialization / deserialization from SQL are much less popular than something like Hibernate, which could be a reason.

davidw 5y ago

> Most ORMs advertise “type safety” and not having to learn SQL. But actually you’re just writing SQL code in a non-SQL language I think you're doing it wrong if you end up doing really complex queries in your ORM. It's kind of a judgement call, but at a certain point, you need to switch over to SQL. Good ORM's let you do that without too much hassle.

dboreham 5y ago
ORM selling point: you can bypass it.
- davidw 5y ago
  Well, sure. It makes it easy to do simple stuff and gets out of the way when you want to bypass it rather than being from the "sorry Dave, I can't let you do that" school of interface design.
piyh 5y ago
I wrote a django app using postgres full text search. I ended up going with direct sql calls for anything more than a single record get because of all the specialized syntax. It's nice to keep stuff like new record creation clean, but once you get more than one join deep it's time to break out the best domain specific language ever created.

dboreham 5y ago

If you think an ORM is dumb, you haven't tried an FRM.

jmnicolas 5y ago
What is a FRM? A quick search didn't get me anything relevant to databases.

dragonwriter 5y ago

> Most ORMs advertise “type safety” and not having to learn SQL. But actually you’re just writing SQL code in a non-SQL language. That's...not writing SQL code, just as advertised. In the same way that writing code in a higher-level language isn't writing native machine code.

armchairhacker 5y ago
The difference is you’re still writing SQL keywords, just as functions e.g. userPosts = Posts.InnerJoin(Users).SelectAll().Where(user => user.id == userID) It’s not always exactly the same, but the point remains that you have to understand SQL anyways and the code usually isn’t any clearer. Maybe i’m confusing this with query builders. ORMs also let you form queries by interacting with objects directly, e.g. ‘user = Users.Find(userID); userPosts = user.Posts’. But that leads to the other issue with inefficient and unpredictable queries.

mdavis6890 5y ago

As a person that writes my own SQL and hasn't had much/any use for ORMs in the past - I have the imagination that if you were using an ORM you could switch the underlying database without a lot of trouble. Is there any truth to this?

pphysch 5y ago
When has anyone ever a) had to switch from RDBMS to RDBMS in a production system and b) it not be a massive pain in the neck?
armchairhacker 5y ago
It depends on how big the switch is. I assume it would be a bit easier because the query and ORM syntax is uniform across databases. And it also probably wont support many database-specific operations. But it would still be work. Some databases don’t support basic operations (e.g. UPDATE with LIMIT in postgres) which you can still do with the query syntax. And different databases usually have different preferred (usually faster) ways of doing indexes, joins etc. Plus you still have to migrate your data. ORMs are ideally supposed to be a complete abstraction where you can pretend the database doesn’t exist, so if you switch it shouldn’t matter. The problem in, in practice you can’t pretend the database doesn’t exist. So you either end up writing code for your specific dialect anyways, or you write inefficient code. It’s also like, you can just write plain SQL and ignore your dialect’s extra features. And that would make migration much easier, but it would also be less efficient and much harder to write.
jmnicolas 5y ago
I think the database change thing is way overrated. I'm 12 years in the same company, I juggle with Postgres, MySQL, SQL Server (and Oracle previously). Most of the databases I access are from external providers. We changed our main provider 2 years ago, so their DB went from Oracle to SQL Server. So in 12 years that would have been one database change. Except all the schema has changed and they don't allow SQL queries on it but provide good old SOAP web services... So even if I had used a full ORM (I use Dapper, you write the SQL, it "just" converts the result of the query to objects) I still would have to change my code to adapt to the new provider. However going from SQL to webservices really convinced me SQL is a superpower. With it you get exactly the data you need, no need to re-process it in code. Before this change I never had to really look at my apps performances, it was good enough. But since this provider webservices are so inefficient (for example I can't "query" a single customer, I have to get all of them) I had to optimize my app in every possible way to mitigate these inefficiencies.

theshrike79 5y ago

Every single project I've seen that has used an ORM has had to spend days debugging "why is this simple query pulling half the database" -bugs.

henvic 5y ago
2. This is why I'm pushing so hard to keep things with plain SQL at work. So far, another team has experimental with squirrel (a SQL query builder) and it looks sane, but I myself wouldn't introduce not even this. In my previous company, I was forced to use an outdated forked version of gorm. I'd just do my best to do anything else other than leading with databases there. Running the test suite took minutes, and to run way fewer test cases. It was a nightmare.

orangepanda 5y ago

> write SQL queries manually, and then auto-convert the results to/from regular objects. In any meaningul sized codebase, converting to/from objects will be used all over the place. Then someone adds an abstraction on top, and congratulations - there is now an inhouse orm.

systemvoltage 5y ago
That's not the same thing as ORM. ORM constructs queries based on arbitrarily complex request. Abstraction layers that we add on top of existing 'handcrafted' SQL queries is often expected and necessary. With base foundation rock solid, explicit and unchanging – you can build abstractions safely and with peace.

fy20 5y ago

The only good ORM I've used is Rail's ActiveRecord. Sure there are gotchas you need to know about, maybe it's just experience, but I find these to be a non-issue. Every other ORM in other languages I've tried seems to have WTF moments. It could be that Ruby is a dynamic enough language that it allows you to work around these things easily, that stricter languages do not.

captainmuon 5y ago

One benefit of using an ORM is that you don't write SQL strings, you write statements and method calls. So you get better IDE support, and detect syntax errors before they hit the database. If you are just writing a CRUD application, maybe with one or two `joins` or `order by`, then an ORM is perfect. If you are doing anything more complex, then by all means drop to plain SQL. The real question to me is, why can't we drop the relational database? In many small projects (where you would use an ORM) it is overkill. Why can't I create a list of plain-old-data objects in my language, then add indicies on that, and do fast queries directly in the language? Most languages already have a data structure with an index on one key (a dict, hashmap or similar). With C#'s LINQ or Pythons list comprehensions we already have half of the solution.

Zababa 5y ago
From what I understand, data duplication can lead to consistency errors. And if you want to avoid data duplication, you're going to rewrite a relational database yourself, since you would want to put your data into normal forms to avoid duplication. I think I've read a few times on Hacker News that we already went through the alternatives to the relational database around the time of its birth, and that a few papers were published at the time on why this was a better solution than others.

twoquestions 5y ago

> a good IDE will provide type-checking and even data-source checking to raw SQL anyways I've never in my life heard of this, which IDE are you talking about? I'd love to use something like that! I'm in C# land where I can use Management Studio to write queries which works well enough, but going between that and Visual Studio does have some friction. Even though we use Entity Framework here, I mostly use it to manage migrations than to work with data. Most of the time unless a query is really trivial, I'll just write some SQL and use Dapper for object mapping, I find it a lot easier once the query gets to be over three or four lines or so.

armchairhacker 5y ago
IntelliJ IDEA. For example in Java if you put // language=SQL before a string literal or @Language(“SQL”) before a function parameter at declaration and call with the string literal, the literal text will be highlighted. You “configure SQL dialect” and “configure data source” to get it to point to your database

chakkepolja 5y ago

> auto-convert the results to/from regular objects. Sadly this functionality, which I think is not much more complex than a JSON parser, is absent from many toolkits / libraries, and an immediate option people see is an ORM. Libraries that just do serialization / deserialization from SQL are much less popular than something like Hibernate, which could be a reason.