Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What ORMs have taught me: just learn SQL (2014)

wozniak.ca

Post reply on HN
Print view View original

51–60 of 654 posts

Re: What ORMs have taught me: just learn SQL (2014)

#51

Post by bob1029 »

Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on all SQL operations after rewriting using direct SQL with Dapper handling mapping of result sets into models.

Writing your own SQL migrations is actually incredibly straightforward if you just think for a few minutes about how you would do it if ORMs didn't exist. Many database systems have ways to store metadata like schema versions outside the scope of any table structure, so you can leverage these really easily in your scripts/migration logic. EF6 uses an explicit migration table which I was never really a huge fan of.

One thing we did do that dramatically eased the pain of writing SQL was to use JSON serialization for containing most of our complex, rapidly-shifting business models, and storing those alongside a metadata row for each instance. Our project would be absolutely infeasible for us today if it weren't for this one little trick. Deserializing a JSON blob to/from a column into/from a model containing 1000+ properties in complex nested hierarchies is infinitely faster than trying to build up a query that would accomplish the same if explicit database columns existed for each property across the many tables.

Re: What ORMs have taught me: just learn SQL (2014)

#52

Post by blunte »

philwelch wrote: post #4

I’ve come to the same conclusion even working with ActiveRecord. SQL is very literally a domain-specific language for working with relational data; why would we go so far out of our way to eschew writing code in it?

I'm a SQL fan as well, having used it before there were ORMs. I still prefer it for complex queries. However, ActiveRecord is so nice for reducing start-up tedium with DDL and migrations, and especially for mapping relations to data structures (objects in this case).

Re: What ORMs have taught me: just learn SQL (2014)

#53

Post by dsego »

BerislavLopac wrote: post #22

My personal gripe about ORMs is that they have two main usage patterns, and each of them has major drawbacks. The first pattern, common in frameworks like Django, is to cram the business logic into the ORM instance objects. This creates a tight coupling between the two separate concerns (business logic and data persistence), and will cause problems as soon as the two structures deviate from each other. The second pat…

The old Data Mapper vs Active Record debate.

Re: What ORMs have taught me: just learn SQL (2014)

#54

Post by Roboprog »

bjoli wrote: post #38
hashkb wrote: post #5

SQL just isn't composable. I know this article is old, but these days it's not black and white. In the space between ORM and raw SQL there are things like AREL which can save a ton of dev effort without the "impedance mismatch".

Can you elaborate? SQL queries compose just fine, it is just that most developers don't understand the relational part.

Predicate builder type stuff is probably the reference.

Whether or not parallel arrays of template and value pairs to be anded together is better or worse is debatable.

Re: What ORMs have taught me: just learn SQL (2014)

#55

Post by matthewmacleod »

philwelch wrote: post #4

I’ve come to the same conclusion even working with ActiveRecord. SQL is very literally a domain-specific language for working with relational data; why would we go so far out of our way to eschew writing code in it?

Because the process of building that SQL, executing it, and bringing the data it returns into the object oriented universe is a tedious and fiddly pain in the arse.

Re: What ORMs have taught me: just learn SQL (2014)

#57

Post by look_lookatme »

jackschultz wrote: post #8

When I started web work ~9 years ago, it was with Rails and ActiveRecord, which turns out to be incredibly good for basic queries and pretty basic apps. So good to the point where I never bothered to go too far into SQL until years later, which was a mistake. When doing work in python, I don't feel it has a comparable ORM, to where I kind of write my own files that have things like finders and updaters and creators.…

We use ActiveRecord, which, from my perspective is a good ORM. It's very simple. If you are diligent you can use pluck and count and a ton of other features to ensure you are selecting only what you need. It's easy to drop down to sql in partial form or in the full form. We tend to not get too crazy with AREL.

In particular I like that it behaves way better using surrogate primary keys (serial id column) instead of natural primary keys... it just answers that question for you that would otherwise result in bike shed conversations.

We minimize logic in the AR models, instead those are in glue objects and this pattern works real well. AR models are mostly there for describing relationships in the code and doing data validations in the code on top of CRUD operations.

Re: What ORMs have taught me: just learn SQL (2014)

#58

Post by Roboprog »

JimmyRuska wrote: post #16

We used a third party tool for mailers and it was having severe performance problems after moving the server to a cloud based solution. We confirmed what the DBAs said, the latency was only 34 seconds round trip and the database was pretty fast. I installed wireshark to see what was going on. It was querying a table with 80,000 entries, and then for each entry it would do another, "select ... limit 1" query to get th…

Wait, this is displayed gray. It got downvoted???

Re: What ORMs have taught me: just learn SQL (2014)

#59

Post by hdfbdtbcdg »

bob1029 wrote: post #51

Swapping from EF6 to Dapper was one of the best choices we ever made with our project stack. It is so relieving to be able to hand-tune queries and transactions now. Initially, we were sold on the apparent simplicity of EF6, but as with many things there is a cost for an abstraction like this. In our case, the performance penalties and opaqueness were dealbreakers after a while. We saw an average speedup of 10x on al…

I am seriously glad I don't work on this project!

Re: What ORMs have taught me: just learn SQL (2014)

#60

Post by acd »

Will tend to agree with the author.

Initially ORMs can save time when developing as you get an easy mapping between objects and the database.

However in practise ORM tends to give you quite horrible JOINs that quite frankly are hard to understand for humans.

Further more I think that ORM can lead to a bad practice in the sense that you do not need to think about your data layout first. But for database performance data layout is of utter most importance. One need to have data in lay out in a form that makes the application run fast. ORMs does not necessarily provide that. One need to normalize the database.

ORM save you time during initial development but you pay later in the maintenance phase when what is complex queries that humans may not understand are hard to optimize for performance.

Database normalization https://en.wikipedia.org/wiki/Database_normalization https://en.wikipedia.org/wiki/Third_normal_form

Post reply on HN