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)

woz.posthaven.com

Post reply on HN
Print view View original

251–260 of 360 posts

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

#251

Post by FLUX-YOU »

zimpenfish wrote: post #229
scarface74 wrote: post #206

Earlier quoted context omitted.

How so? I can log the the sql being generated and look at the logs. Something I should be doing either way.

If you have to log the generated SQL to understand what's happening, you're already behind the curve. And then what do you do if the ORM is generating junk? If the answer is "use a querybuilder/handcrafted SQL for that one", what's the point of the ORM in the first place?

>you're already behind the curve.

SQL syntax is extremely verbose, and compounds the more tables are involved in a query. You're not counting the time the ORM has saved you before having to resort to logging SQL.

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

#252

Post by manigandham »

One of the most endless and pointless debates.

99% of the time, an ORM is fantastic and will make you more productive while providing performance, security, and maintainability. They come in many sizes from thin wrappers around a db connection to full-featured frameworks.

For the other 1%, use raw SQL, or perhaps a query building tool to help with parameterization, composability, etc. In fact, modern ORMs will even let you input raw SQL and handle the conversion back to objects if you need it.

Saying ORMs are always wrong is just as dumb of a statement as saying all database access needs to be in raw SQL. They are just a tool and abstraction, like everything else you use in software development. You know the right time to use it.

That being said, not knowing SQL at all means a lack of general understanding in how relational databases work and will almost always cause problems.

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

#253

Post by Someone1234 »

useerup wrote: post #154
Someone1234 wrote: post #67

Earlier quoted context omitted.

With or without lazy loading enabled the result was the same. Generating the structure took seconds and went OOM with enough tables. LazyLoading impacts what data is retrieved from the database (or more to the point when), this is a structural issue before a query was even sent to the database. It would die while generating the query, not sending the query or populating the result. You likely should have asked for mo…

I have been working with EF for years now. It has it quirks - but this is not something that I have ever experienced, nor have I heard of anything like it before today. What I have heard of is traversing the entire graph and causing cascading loading of navigational properties. Yes, I have done that. Something like AutoMapper will do that to you, if you are not careful. Been there and done that. How did you determine…

> How did you determine that it OOMed while generating the query?

By looking at the call stack when the exception was thrown (and the fact that nothing hit the database).

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

#254

Post by red_admiral »

spdionis wrote: post #250
red_admiral wrote: post #226

My 0.02 BTC on the matter: Object-oriented programming 101 assumes that all your objects are in memory, in a graph, so you can do things like person.getFriends()get(0).getName() [assuming the person in question has >0 friends]. Each step in the graph is essentially a pointer dereference, costing a constant effort. (If your data is small enough to fit in memory, that's what you should generally be doing. People who us…

> My standard example why it sometimes does matter: PersonDAO.fetchAll().size() is silly because it forces the database to fetch all Person objects, send them over the network, your application creates the necessary objects for them - and then you throw it all away again because all you needed was the number of people. PersonDAO.count() is much better, even if you have to implement it yourself. I mean, PersonDAO.fetc…

> You need to know about databases before using an ORM.

Pretty much the TL;DR of my whole post.

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

#255

Post by icebraining »

zkomp wrote: post #129
wvenable wrote: post #84

Earlier quoted context omitted.

> ORMs are bad, because objects aren't particularly useful to deal with most data. Objects are containers of data; that's a crazy statement to make. The relational structure maps pretty cleanly to objects, properties, and collections. However, objects are not good for reporting. And the author mentioned doing 14 joins and hundreds of columns - that smells like a reporting query.

But why do you need these containers of data, when you could have more direct access to both the data itself and the whole set (not individual objects, nor collections of objects) I dont like ORMs but did struggle some years trying to use them which imho was a detour. SQL and stored procedures in plpgsql is so much better, easier to maintain, easier to reason about etc.

How would you calculate the average of the sum of three columns (AVG(A + B + C)) which are chosen by the user at runtime?

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

#256

Post by zkomp »

scarface74 wrote: post #212
zkomp wrote: post #173

Earlier quoted context omitted.

As far as I know, in my experience. Stored procedures in postgres are good when you really use the database and care about the data, you need transactions, need to handle races and concurrency etc. Whereas ORMs break down at this point or prevent you even getting to a point when you can use your database as a database. Why pretend your SQL database is about objects? It is not... (it is about data) A stored procedure…

Every single implementation where I've seen "business logic in the database" has been an unmitigated disaster. On the other hand, having well factored microservices (out of process) or in process modules have worked out really well with modern devops and software engineering principals - easy push button deployments and rollbacks, unit testing, A/B deployments, etc.

I think bussiness logic in the database has prevented disasters in the projects I have worked on. I honestly dont see how it could have been solved better...

It probably depends on the domain/problems.

My experience is with transaction heavy financial systems or similar, with web frontends, microservices sprinkled around in different languages...

The web app or java worker should be allowed to focus in its problems, the bussiness logic needs to live in one central place, which happens to be in the database accessed through thightly controled interface in the form of stored procedures.

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

#257

Post by icebraining »

minitech wrote: post #125
jtchang wrote: post #13

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…

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

Security is not just about injection. The ORM we use automatically applies security access rules to the queries, for example.

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

#258

Post by jcadam »

andybak wrote: post #177
emilsedgh wrote: post #174

Earlier quoted context omitted.

ORM: You ask for something and you don't care _how_ its fetched. Query Builder: You build a query, just not in SQL. So you can get around SQL's limitations (like composition)

> ORM: You ask for something and you don't care _how_ its fetched. Try this: > ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query. or possibly: > ORM: You ask for something and you accept the tradeoffs vs hand-written queries but you're content that it's the correct tradeoff for your use-case. There's plenty of places for bottlenec…

> ORM: You ask for something and because you've researched and found a well-written, quality ORM you trust that it will create a sane query.

Ha. More like:

ORM: You've just joined a project already using an ORM selected by an 'architect' that no longer works here. Everything is fine until you start testing your system with a database sufficiently populated with real-world data. You and the DBA spend the next next 6 months trying to get the damn ORM to generate performant SQL (you even call Oracle support, which promptly dispatches an "engineer" who tries to sell you on another $500k of crap you don't need that won't really address the problem). You eventually just start writing queries by hand wherever you find a bottleneck caused by the naive ORM, which you could have done at the outset, but no one who actually knew SQL well enough was on the team back then.

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

#259

Post by majewsky »

oceanswave wrote: post #240
scarface74 wrote: post #204

Earlier quoted context omitted.

Why is it an either or? EF can do that but then you loose the benefits of a type safe language.

If type safety is so great, why isn’t sql statically type checked

SQL is statically type-checked, at least in Postgres.

  postgres=# SELECT * FROM domains WHERE id = 'foo';
  ERROR:  invalid input syntax for integer: "foo"
  LINE 1: SELECT * FROM domains WHERE id = 'foo';
                                           ^

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

#260

Post by zkomp »

scarface74 wrote: post #210
zkomp wrote: post #163

Earlier quoted context omitted.

you solved isolation decoupled much of the db logic from app-logic and made security easier you can deploy schema changes independently you can change everything and the app should not notice

Why would you want to deploy schema changes separately? I would be horrified if someone changed my DB back end without running a full (hopefully automated) set of tests.

The db is separate and the interfaces are defined and the test is for this interface (as part of the schema repository)

You dont need an ORM for testing your code...

But I think this varies from project to project. How many different applications, in different languages are using your db and do you tolerate downtime?

Post reply on HN