13 ms·

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not k

by zzzeek 9y ago

> If you're using an RDBMS, bite the bullet and learn SQL. If this person spent all that time using Hibernate and then SQLAlchemy, and all that time did not know SQL, then their suffering and bad experiences make complete sense. You absolutely need to know SQL if you're going to use an ORM effectively. Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks, providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL, so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). None of those things should be about "hiding SQL", and you need to learn SQL first before you work with an ORM.

manicdee 9y ago
This is especially true where the ORM can mask things such as moving a column to an associated table. The ORM knows that object.attribute is now represented in the object_attribute table with the relationship using object_attribute.pk in the object.attribute column (which may or may not be renamed to attribute_id). No need to rewrite all your SQL, just the ORM description of the model!
- Joeri 9y ago
  Realistically though, if you’re querying the same table from that many places your architecture is already in trouble. So editing the queries shouldn’t be that big of a deal.
mathattack 9y ago
Everything that isn't SQL tuned to your environment forces you to sacrifice performance at some point. At some point it does an inefficient join. How can you not go back and fix performance issues is you don't know SQL? (It isn't like you need to fix it in C)
- X-Istence 9y ago
  SQLAlchemy makes dropping down to SQL where necessary incredibly easy. There where necessary you can tune the queries, but where not necessary you can let the ORM create them for you. This gives you a lot of flexibility and power, but as zzzeek mentioned, you need to know SQL to understand how what the ORM is inefficient and to be able to replace it as necessary while still letting the ORM do what it is really good at.
alexandercrohde 9y ago
I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has been solved, 2 was never okay (point of the article), and 3 isn't really okay either because it's too leaky (performance specifics).
- eru 9y ago
  For 3: the proper abstraction for relational data is, surprise, a relation. I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with. We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.
  - yorwba 9y ago
    How were those relations represented? If I understand correctly, you were not just wrapping tables in a database, but using some other backing implementation. What were the most common operations, and what was the performance like?
    - eru 9y ago
      Oh, the implementation was fairly straight-forward. I think just sorted arrays are something. It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support. Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc. Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data. Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern. Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)
erikpukinskis 9y ago
Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL So this is the real ORM juice. And ya, without an ORM you have to do this by hand. So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface. And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries. (If you don’t like the word “AI” here, use “expert system” instead.)
- avmich 9y ago
  > A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largely a query builder.
  - int_19h 9y ago
    Is it a good one, though?
    - JamesBarney 9y ago
      Probably better than the one I could build by hand, with far better documentation, and greater ease in googling problems :)
      - failedxyz 9y ago
cloverich 9y ago
cause I think its relevant -- note he's the creator of SQLAlchemy.
- simula67 9y ago
  Wikipedia says SQLAlchemy was created by somebody named Michael Bayer : https://en.wikipedia.org/wiki/SQLAlchemy https://en.wikipedia.org/wiki/SQLAlchemy EDIT: Sorry, my mistake. The partent is talking about the parent comment, not the FTA
  - SatvikBeri 9y ago
    (Mike Bayer is zzzeek: http://techspot.zzzeek.org/ http://techspot.zzzeek.org/)
  - xzel 9y ago
    Yeah they're the same person. His HN profile has a link to his blog.
    - LeonidasXIV 9y ago
      If I link to zzzeek's blog in my HN profile, will I also become Michael Bayer?
andrewstuart 9y ago
zzzeek - can I take this chance to praise your work on SqlAlchemy. People say there's not enough thanks given to open source developers... here's thanks to you. It's the work of a craftsman.
- nerdwaller 9y ago
  +1 - personally I really like how SQLAlchemy doesn’t abstract away the database so much where all modeling and power is lost. He and the contributors have done an excellent job.
  - Zancarius 9y ago
    I've always been impressed by zzzeek's near-omnipresent participation. Years ago, I had a question about SQLAlchemy that he'd answered on the mailing list which amazed me given the relative rarity of developer interaction. Yet here we are almost a decade later and he's still answering questions directly, but on many more platforms. I'm actually not convinced he ever sleeps. I wish more people aspired to be like him, because you know there's no more authoritative answer when you run into a Stack Exchange post and he's offering up his assistance.
- href 9y ago
  +1 SQLAlchemy has had a solution for every problem I threw at it in 5 years of heavy use!
- flavio81 9y ago
  +1 SQLAlechemy is an excellent library. Zzzeek, many kind thanks for such a good ORM.
- cybergoat 9y ago
  I want to give thanks to all the SqlAlchemy devs! I am a huge fan of the custom column types -- they have allowed me to have code that works against SQLite as well as a production databases with ease!
- jgraham 9y ago
  I'm pretty sure that SqlAlchemy has caused more grief and frustration than any other single library. After all if I didn't know about the excellence of SqlAlchemy, maybe I wouldn't get so cross when I have to do anything non-trivial with the Django ORM ;) Joking aside, SwlAlchemy is very impressive software, and zzzeek deserves all this praise and more. Every time that there's one of these anti-ORM articles I feel like SqlAlchemy pre-emtively addressed all the substantive criticisms in its flexible, well layered, design. (And in the interests of fairness, Django's ORM is also very good at making simple things simple).
mayankkaizen 9y ago
>, and you need to learn SQL first before you work with an ORM. I did the opposite and faced lots of difficulties. Then I concentrated on plain SQL and suddenly I felt like I am understanding ORM much better.
kbenson 9y ago
> so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). I recommend anyone that has to deal with ORMs create their own at some point, in a non critical project. Not because they will necessarily create the next big thing (but who knows?), but because nothing quite gives you the perspective and appreciation for what these systems can do and why they have their pain points like making one yourself. You'll most likely not use your own module long after you've created it and then again surveyed what's already available, but it's invaluable in making a good assessment of those options as well. Similarly, making a web framework yields similar benefits. In both cases, a good understanding of the underlying technologies they build on (SQL and HTTP), is required, and if you don't have it doing in you'll have it coming out the other side (which is really the reason for this in the end). Similar things exist all along the spectrum. From embedded OS's and compilers to javascript utility libraries. What it comes down to is that a tool is best utilized when the person knows when and how to apply it appropriately, and that's as often as not an understanding of the tool as it is of the context. A person intimately familiar with hammers and their uses can build some interesting wood furniture, but they'll likely never achieve the same level of product as a master woodworker that's just well acquainted with a hammer. Investing time and effort into tools provides only so much benefit. At some point, more knowledge of the craft itself is far more beneficial.
frik 9y ago
He wrote: "Attribute creep and excessive use of foreign keys shows me is that in order to use ORMs effectively, you still need to know SQL. My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL."
needusername 9y ago
> providing abstraction for database-specific and driver-specific quirks That is quite theoretical. My PRs for fixing non-spec compliant behavior in pgjdbc get rejected because they might break some ORMs (mostly Play). My PRs for adding MariaDB sequence support to Hibernate get rejected because there are additional MariaDB features that Hibernate doesn't support as well.
- rickycook 9y ago
  it might be theoretical in hibernate, but sqlalchemy does a TON of this stuff as an example: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/pyodbc.py#L48 https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch... heaps of this kind of thing are nicely dotted around the code so you don’t have to deal with weird driver quirks, maps “Text” column type to whatever it needs to be in your given database to have an unbounded text blob id say that’s far more than theoretical EDIT: typo EDIT 2: also, on DB specific features, SQLA supports a bunch (not all). eg: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/hstore.py https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
  - needusername 9y ago
    In Java land the JDBC driver is supposed to do that. If it doesn't then that's a bug in the driver. If it's a bug in the JDBC driver then the fix needs to go into the JDBC driver, especially if the driver is on GitHub. The presence of an ORM relying on these bugs should not prevent a bug in the driver from being fixed.
jsight 9y ago
Exactly. It is amazing how many bad ORMs that I've seen in systems from people who "just used SQL" instead of an ORM. I've found that the people who know SQL pretty well can actually do good work with or without an ORM. But the maintenance is a lot easier when the abstractions are consistent with the abstractions that are used by a popular ORM.
stcredzero 9y ago
you need to learn SQL first before you work with an ORM. Analogously, I'd say that being able to write a compiler, down to having it generate machine code or assembly, gives you a leg up when using a compiled language. I've met and interviewed a number of coders who had cringeworthy gaps in their knowledge, because to them, a C compiler was just some kind of "magic."

by zzzeek 9y ago

manicdee 9y ago

This is especially true where the ORM can mask things such as moving a column to an associated table. The ORM knows that object.attribute is now represented in the object_attribute table with the relationship using object_attribute.pk in the object.attribute column (which may or may not be renamed to attribute_id). No need to rewrite all your SQL, just the ORM description of the model!

Joeri 9y ago
Realistically though, if you’re querying the same table from that many places your architecture is already in trouble. So editing the queries shouldn’t be that big of a deal.

mathattack 9y ago

Everything that isn't SQL tuned to your environment forces you to sacrifice performance at some point. At some point it does an inefficient join. How can you not go back and fix performance issues is you don't know SQL? (It isn't like you need to fix it in C)

X-Istence 9y ago
SQLAlchemy makes dropping down to SQL where necessary incredibly easy. There where necessary you can tune the queries, but where not necessary you can let the ORM create them for you. This gives you a lot of flexibility and power, but as zzzeek mentioned, you need to know SQL to understand how what the ORM is inefficient and to be able to replace it as necessary while still letting the ORM do what it is really good at.

alexandercrohde 9y ago

I think there are 3 main reasons ORMs came into common use: 1. As a reaction to common SQL injection from poor libraries not implementing parameterized queries. (2004 or so) 2. Novice engineers not wanting to learn SQL (look I learned how to make a blog in RoR, and I like mongo!) 3. As a theoretical abstraction above the data-store (as though you might someday be able to switch the data-store beneath the ORM) 1 has been solved, 2 was never okay (point of the article), and 3 isn't really okay either because it's too leaky (performance specifics).

eru 9y ago
For 3: the proper abstraction for relational data is, surprise, a relation. I used to work in an environment where we had relations as full first class data structures. They are very pleasant to work with. We actually had 'relation-object-mappers', ie when we had to interact with other systems that didn't use relations, we often mapped them to relations internally to make them play nicer.
- yorwba 9y ago
  How were those relations represented? If I understand correctly, you were not just wrapping tables in a database, but using some other backing implementation. What were the most common operations, and what was the performance like?
  - eru 9y ago
    Oh, the implementation was fairly straight-forward. I think just sorted arrays are something. It wasn't about speed of execution, but expressiveness when coding. Later on they even added proper type system support. Common operations were things like map/project, extend, filter, join, collect-by-key / expand, etc. Just as Codd pointed out in his original papers, relations allow you to not have to make a choice about a hierarchy for your data. Using key-value store like a hash-table in your program, or the much vaunted has-a relationship between objects would force you to make these choices. Thus making interacting with the data awkward for all but one access pattern. Relations work best when your program is written in a style that deals largely in immutable data. (What we call 'purely functional', but people in dysfunctional languages have also picked up on the advantages recently.)

erikpukinskis 9y ago

Ok, let’s break this down: > Good ORMs are there to automate the repetitive tasks of composing largely boilerplate DML statements, facilitating query composition, providing abstraction for database-specific and driver-specific quirks None of that requires an ORM. A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. > providing patterns to map object graphs to relational graphs, and marshaling rows between your object model and database rows - that last one is something your application needs to do whether or not you write raw SQL So this is the real ORM juice. And ya, without an ORM you have to do this by hand. So here’s the question: How well can an ORM map to your data model out of the box? In a lot of cases this is where the mess comes from. You need to set up basically a low level AI that can figure out the “right” thing to do as call sites all over your codebase are making arbitrary queries on a huge API surface. And so the argument against ORMs is that it will take less time and be less error-prone to write bespoke loaders that take rows and build your data model than it will be to configure that AI such that it can do the “right” thing with arbitrary queries. (If you don’t like the word “AI” here, use “expert system” instead.)

avmich 9y ago
> A simple query builder will suffice and it will be much easier to debug and much less error prone than an ORM. What's the difference? To me, an ORM is largely a query builder.
- int_19h 9y ago
  Is it a good one, though?
  - JamesBarney 9y ago
    Probably better than the one I could build by hand, with far better documentation, and greater ease in googling problems :)
    - failedxyz 9y ago

cloverich 9y ago

cause I think its relevant -- note he's the creator of SQLAlchemy.

simula67 9y ago
Wikipedia says SQLAlchemy was created by somebody named Michael Bayer : https://en.wikipedia.org/wiki/SQLAlchemy https://en.wikipedia.org/wiki/SQLAlchemy EDIT: Sorry, my mistake. The partent is talking about the parent comment, not the FTA
- SatvikBeri 9y ago
  (Mike Bayer is zzzeek: http://techspot.zzzeek.org/ http://techspot.zzzeek.org/)
- xzel 9y ago
  Yeah they're the same person. His HN profile has a link to his blog.
  - LeonidasXIV 9y ago
    If I link to zzzeek's blog in my HN profile, will I also become Michael Bayer?

andrewstuart 9y ago

zzzeek - can I take this chance to praise your work on SqlAlchemy. People say there's not enough thanks given to open source developers... here's thanks to you. It's the work of a craftsman.

nerdwaller 9y ago
+1 - personally I really like how SQLAlchemy doesn’t abstract away the database so much where all modeling and power is lost. He and the contributors have done an excellent job.
- Zancarius 9y ago
  I've always been impressed by zzzeek's near-omnipresent participation. Years ago, I had a question about SQLAlchemy that he'd answered on the mailing list which amazed me given the relative rarity of developer interaction. Yet here we are almost a decade later and he's still answering questions directly, but on many more platforms. I'm actually not convinced he ever sleeps. I wish more people aspired to be like him, because you know there's no more authoritative answer when you run into a Stack Exchange post and he's offering up his assistance.
href 9y ago
+1 SQLAlchemy has had a solution for every problem I threw at it in 5 years of heavy use!
flavio81 9y ago
+1 SQLAlechemy is an excellent library. Zzzeek, many kind thanks for such a good ORM.
cybergoat 9y ago
I want to give thanks to all the SqlAlchemy devs! I am a huge fan of the custom column types -- they have allowed me to have code that works against SQLite as well as a production databases with ease!
jgraham 9y ago
I'm pretty sure that SqlAlchemy has caused more grief and frustration than any other single library. After all if I didn't know about the excellence of SqlAlchemy, maybe I wouldn't get so cross when I have to do anything non-trivial with the Django ORM ;) Joking aside, SwlAlchemy is very impressive software, and zzzeek deserves all this praise and more. Every time that there's one of these anti-ORM articles I feel like SqlAlchemy pre-emtively addressed all the substantive criticisms in its flexible, well layered, design. (And in the interests of fairness, Django's ORM is also very good at making simple things simple).

mayankkaizen 9y ago

>, and you need to learn SQL first before you work with an ORM. I did the opposite and faced lots of difficulties. Then I concentrated on plain SQL and suddenly I felt like I am understanding ORM much better.

kbenson 9y ago

> so you'll end up inventing that part yourself without an ORM (I recommend doing so, on a less critical project, to learn the kinds of issues that present themselves). I recommend anyone that has to deal with ORMs create their own at some point, in a non critical project. Not because they will necessarily create the next big thing (but who knows?), but because nothing quite gives you the perspective and appreciation for what these systems can do and why they have their pain points like making one yourself. You'll most likely not use your own module long after you've created it and then again surveyed what's already available, but it's invaluable in making a good assessment of those options as well. Similarly, making a web framework yields similar benefits. In both cases, a good understanding of the underlying technologies they build on (SQL and HTTP), is required, and if you don't have it doing in you'll have it coming out the other side (which is really the reason for this in the end). Similar things exist all along the spectrum. From embedded OS's and compilers to javascript utility libraries. What it comes down to is that a tool is best utilized when the person knows when and how to apply it appropriately, and that's as often as not an understanding of the tool as it is of the context. A person intimately familiar with hammers and their uses can build some interesting wood furniture, but they'll likely never achieve the same level of product as a master woodworker that's just well acquainted with a hammer. Investing time and effort into tools provides only so much benefit. At some point, more knowledge of the craft itself is far more beneficial.

frik 9y ago

He wrote: "Attribute creep and excessive use of foreign keys shows me is that in order to use ORMs effectively, you still need to know SQL. My contention with ORMs is that, if you need to know SQL, just use SQL since it prevents the need to know how non-SQL gets translated to SQL."

needusername 9y ago

> providing abstraction for database-specific and driver-specific quirks That is quite theoretical. My PRs for fixing non-spec compliant behavior in pgjdbc get rejected because they might break some ORMs (mostly Play). My PRs for adding MariaDB sequence support to Hibernate get rejected because there are additional MariaDB features that Hibernate doesn't support as well.

rickycook 9y ago
it might be theoretical in hibernate, but sqlalchemy does a TON of this stuff as an example: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/mysql/pyodbc.py#L48 https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch... heaps of this kind of thing are nicely dotted around the code so you don’t have to deal with weird driver quirks, maps “Text” column type to whatever it needs to be in your given database to have an unbounded text blob id say that’s far more than theoretical EDIT: typo EDIT 2: also, on DB specific features, SQLA supports a bunch (not all). eg: https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/dialects/postgresql/hstore.py https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalch...
- needusername 9y ago
  In Java land the JDBC driver is supposed to do that. If it doesn't then that's a bug in the driver. If it's a bug in the JDBC driver then the fix needs to go into the JDBC driver, especially if the driver is on GitHub. The presence of an ORM relying on these bugs should not prevent a bug in the driver from being fixed.

jsight 9y ago

Exactly. It is amazing how many bad ORMs that I've seen in systems from people who "just used SQL" instead of an ORM. I've found that the people who know SQL pretty well can actually do good work with or without an ORM. But the maintenance is a lot easier when the abstractions are consistent with the abstractions that are used by a popular ORM.

stcredzero 9y ago

you need to learn SQL first before you work with an ORM. Analogously, I'd say that being able to write a compiler, down to having it generate machine code or assembly, gives you a leg up when using a compiled language. I've met and interviewed a number of coders who had cringeworthy gaps in their knowledge, because to them, a C compiler was just some kind of "magic."