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

231–240 of 360 posts

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

#231

Post by pjmlp »

gldalmaso wrote: post #205
JamesBarney wrote: post #123

Earlier quoted context omitted.

4. The pain in the butt of writing and maintaining your own mapping code. 5. Type checking all your queries. 6. In code query composability. But I totally agree that ORMs are too leaky of an abstraction for 2 to be really useful, and that 3 is much harder than it appears to be.

I find that jOOQ (java) solves all that for me. Very happy to not have and ORM on our stack for many years. I believe that the interaction with the database is just about the most important part of code that you need to rely on. We have had messy data written to the database due to misused or misconfigured ORM (perfomance issues, bad orphan handling, session state problems, overflown sequences, to list a few) and dec…

Yep, MyBatis is also another nice alternative.

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

#232

Post by pjmlp »

mattmanser wrote: post #196
rjbwork wrote: post #60

Earlier quoted context omitted.

I've been writing C# professionally for ~5 years at this point. While I was initially quite infatuated with LINQ2SQL and EF, I have gone through the same situation as this fellow. I just write SQL in-line using Dapper for parameterization/data mapping, and use stored procs when I need some of the more arcane features of SQL (merges, CTE, etc).

I've been writing C# professionally for ~12 years at this point. I'm extremely comfortable with SQL, the first startup I worked for for 3 or 4 years in the mid-2000s did amazing things with it and was extremely anti-ORM. We did things like write SQL that would automatically get translated into XML, which we'd combine with xslt to create dynamic pages. Yes, I've hit major problems with the EF (including one on Friday…

We aren't even considering Core it reaches feature parity with EF 6.

Not only lazy loading, ODP.NET and designer support are also a must have.

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

#233

Post by ProblemFactory »

saryant wrote: post #7

I have to agree. ORMs can be great when an application is just starting, because that's when you're writing the most tedious queries and statements, but beyond that I personally find that ORMs just get in the way. As soon as I need to write something more complicated than select-by-id I end up reaching straight for SQL. Otherwise I have to learn both the ORM's query API or DSL and have the proper mental model for how…

Any discussion on ORMs needs to consider what sort of app and queries you are writing.

ORMs are fantastic for the very common case:

* Fetch 20 rows and display them as a table,

* Fetch 1 row by primary key and display it as a form,

* Write updated fields from the form back into the 1 row in the database.

Anything more complicated, and direct SQL starts being more attractive. But for the common case that ORMs are designed for, they are a major productivity boost.

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

#234

Post by kamyarg »

normaljoe wrote: post #104
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…

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…

an ORM does not have to solve every single problem you have, write your raw sql and run it.

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

#235

Post by gtsteve »

zkomp wrote: post #137
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…

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...

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.

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

#236

Post by cosarara97 »

akvadrako wrote: post #197
avmich wrote: post #107

Earlier quoted context omitted.

> 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.

SqlAlchemy Core vs SqlAlchemy ORM

And doctrine DBAL vs doctrine ORM

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

#237

Post by scarface74 »

walshemj wrote: post #227
scarface74 wrote: post #209

Earlier quoted context omitted.

Just use stored procedures? Then you lose the ability to do unit testing without a database dependency, it's a lot easier to rollback code than to rollback code and stored procedures as one and you don't get full visibility on what the code is doing just by looking at the source code.

Not in my experience though our DBA was very good (oh my first boss was Dijkstra he mentioned down the pub one lunch time) And would you not have your IDE on one monitor and your SQL IDE in another so you could look at both sets of code.

Usually with most modern automated deployments, you keep your build artifacts in a package (zip file, tar, etc.) and run a script to deploy it to your target system.

Rolling back is a simple matter of installing the previous archive. That doesn't just apply to code anymore. You can treat "infrastructure as code" also.

You can do A/B upgrades, rollbacks, etc. There is so much better tooling around regular code than sql/stored procedures. How many times have you seen stored procedures with hundreds of lines, duplicated stored procedures with V1,V2, etc appended to it, commented out logic etc?

I've had to wade through some hairy code to but at least with a code, I can do some automated guaranteed safe refactoring, find dependencies, keep the interfaces backwards compatible, etc.

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

#238

Post by mgkimsal »

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?

> 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?

The point may be that 98% of the queries are just fine, and you've saved time vs writing by hand, and it may be easier to read/understand for the next people to have to touch the code.

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

#239

Post by oceanswave »

spdionis wrote: post #218
erikpukinskis wrote: post #79

Earlier quoted context omitted.

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…

5 years down the road, good luck refactoring your application to not query the same object from the database 3-4 times, and load it only once instead.

You’ve probably refactored the ORM Data layer 3 times in that time period as ORM producers have a hard time figuring out the interface which they wish to provide you

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

#240

Post by oceanswave »

scarface74 wrote: post #204
majewsky wrote: post #183

Earlier quoted context omitted.

I think the best ORMs are those that just leave out the "Relational" part entirely. So... "OM"? For example, in Go, I use Gorp, which has a Select() function where you pass in the SELECT query string (plus bound values) and the target type, and it loads every result row into an object of that type. So you can have an arbitrarily complex SQL query as long as it starts with `SELECT one_table.* FROM`. That's a marvelous…

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
Post reply on HN