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

What ORMs have taught me: just learn SQL

wozniak.ca

Post reply on HN
Print view View original

231–240 of 245 posts

Re: What ORMs have taught me: just learn SQL

#231

Post by JohnBooty »

Hakeashar wrote: post #230
JohnBooty wrote: post #212

Earlier quoted context omitted.

> With AR, if this is the worst case where you write some > raw SQL, what's the alternative? The alternative seems > far more painful pragmatically speaking and this, while > being a little ugly, seems to work just fine without > impacting productivity or performance. Do you know if other ORMs allow this kind of relatively painless use of "raw SQL?" I've only used ActiveRecord and some of the .NET "micro ORMs" like D…

Even the bulkiest ORMs allow you to use raw SQL. That's why you can use 80 - 90% of the features on the regular basis and hand-tweak regions which cause performance problems or places where you just have to write SQL (e.g. recursive queries). In EF, there's either: Database.SqlQuery - http://msdn.microsoft.com/en-us/library/gg696545%28v=vs.113%... which can return any object or: DbSet .SqlQuery - http://msdn.microsof…

Oh man, that's great to know. Thank you for that reply!

Re: What ORMs have taught me: just learn SQL

#232

Post by Hakeashar »

JohnBooty wrote: post #231
Hakeashar wrote: post #230

Earlier quoted context omitted.

Even the bulkiest ORMs allow you to use raw SQL. That's why you can use 80 - 90% of the features on the regular basis and hand-tweak regions which cause performance problems or places where you just have to write SQL (e.g. recursive queries). In EF, there's either: Database.SqlQuery - http://msdn.microsoft.com/en-us/library/gg696545%28v=vs.113%... which can return any object or: DbSet .SqlQuery - http://msdn.microsof…

Oh man, that's great to know. Thank you for that reply!

No problem! Happy to help :)

ORMs get a lot of flak and while some of it is truly earned, the rest comes from the misuse/abuse of the tool. I always thought that using ORM functionality (where convenient) together with SQL (where necessary or convenient for different facet of the application) was the best from both worlds.

And then you can of course mix different ORMs in one project, so you can use EF in areas where performance does not really matter that much or if you're doing a lot of CRUD and Dapper (or something like Insight.Database if you like stored procedure-to-interface mapping) in hot paths or analytic-heavy piece.

Re: What ORMs have taught me: just learn SQL

#233

Post by hcarvalhoalves »

Grue3 wrote: post #185
hcarvalhoalves wrote: post #32

Earlier quoted context omitted.

To be sincere, I find Django's ORM is one of the weakest ones (e.g., the API doesn't support a simple GROUP BY). If you want to make a good case for ORMs, Django's may not be a very strong argument. In about 5 years working with Django the ORM has been the only component that consistently gave headaches. I have filled a couple bug reports about it generating non-sense/slow queries too (like generating queries with `D…

>the API doesn't support a simple GROUP BY This is just plain wrong (see aggregate/annotate).

Let's word it differently: the API doesn't support a simple way to emit an arbitrary GROUP BY clause.

The aggregate/annotate APIs do not cover all legitimate uses of GROUP BY besides very simple cases (sum, avg, etc).

Re: What ORMs have taught me: just learn SQL

#234

Post by lukaseder »

drbawb wrote: post #200
terhechte wrote: post #27

I used to write raw SQL for many years, then, around 2005 switched over to ORMs in order to be able to target different databases, have a nice model, etc. Lets be honest here, the ease of justing doing: p.username = "Carl" p.age = 33 p.save instead of "update users set username=:username, age=:age where id=:id" has a ton of advantages. For one, some sort of syntax or type checker is actually trying to understand your…

>Strongly typed lanaguages are even cooler here ... No expereince with Slick in particular; but I've been using jOOq[1] which I believe is similar. To be honest I'm not entirely sold that these DSLs are what I'd consider "strongly typed." I can get jOOq to pretty easily yield queries that won't work if I switch out database dialects. (Ignoring, for a moment, that jOOq will let you embed SQL fragments as strings.) As…

> I can get jOOq to pretty easily yield queries that won't work if I switch out database dialects.

In fact, you can do this with SQL. The following is perfectly valid in MySQL but not in any of the other dialects:

    SELECT a, b, COUNT(*)
    FROM t
    GROUP BY a
SQL is so heavy with implicit semantics that cannot be expressed in syntax alone...

Re: What ORMs have taught me: just learn SQL

#235

Post by gaius »

anko wrote: post #67
endlessvoid94 wrote: post #26

Earlier quoted context omitted.

Yes, but the ORM often influences the schema design. That can be very painful down the road when you realize your tables are actually tables, rather than instances of objects, which would be what your ORM led you to believe.

I think the problem is not that an ORM often influences schema design, it's that Relational Databases/SQL often influence application design. People complain that an ORM isn't using a relational database effectively. The greatest contribution of the rise of ORMs is that relational databases are hard to use properly. Bring on the ACID compliant document databases.

No you have it backwards. RDBMS are as they are because maths (relational algebra and calculus). There is deep theory behind doing things this way. You can put data in without needing to know how it will be accessed and used (and vice versa). NoSQL just doesn't have this rigor. You have to tightly couple what creates the data with what consumes it. THAT is just begging for trouble down the line.

Re: What ORMs have taught me: just learn SQL

#236

Post by lloeki »

adamlett wrote: post #191
lloeki wrote: post #180

Earlier quoted context omitted.

> The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic access to the SQL AST, so you can generate syntax as opposed to concatenate strings together. Kind of like a DOM API, but for SQL. Congratulations, you just described Arel. I liberally use rails/active_record where it shines (operating on a single record, or writing composable scopes) but very…

> Congratulations, you just described Arel. Unfortunately SQL leaks through Arel's abstractions and make it behave in surprising ways. Arel falls short of achieving the goal of being able to modularise and compose queries. The same is true of nearly every SQL connectivity library (or so this article claims: http://www.try-alf.org/blog/2013-10-21-relations-as-first-cl... ).

> Unfortunately SQL leaks through Arel's abstractions

This is voluntary. Arel allows one to write SQL almost as is, only with native types and features that make it possible to compose and aggregate queries. It's not a 1:1 mapping but it's not a blackbox abstraction either since the goal is to finely control your SQL. Of note, Arel is neither ActiveRecord nor ActiveRecord::Relation (which is what you get when you use #all, #where and scopes, sadly often mistaken for Arel)

The article you linked to is wrong, Arel is perfectly able to compose projections and joins, see this gist[0] implementing what the author demands.

[0]: https://gist.github.com/lloeki/2bc0ece35b2ba42b681d

Re: What ORMs have taught me: just learn SQL

#237

Post by Dowwie »

Can anyone tell me which of the OP's arguments against ORMs isn't already addressed in SQLAlchemy? I can't find any, but maybe I've overlooked something.

Anyone who is already coding in Python and hasn't yet discovered SQLAlchemy would find that doing so is a great investment in the little time that is available.

Re: What ORMs have taught me: just learn SQL

#238

Post by walshemj »

Daishiman wrote: post #224
walshemj wrote: post #208

Earlier quoted context omitted.

Sorry Toad or Work manager and the MYSQL tools are perfectly acceptable IDE's for SQL development.

"Perfectly acceptable" doesn't reach the standard of type inference and feedback that you can get with modern IDEs for their supported languages. Visual Studio gives far more feedback for LINQ than SQL, and it's damn more useful for debugging queries.

I suggest you need to learn to code SQL and the procedural extensions for the dialect in use then - and OO is not the be all and end all of development.

Re: What ORMs have taught me: just learn SQL

#239

Post by nesyt »

wickawic wrote: post #130
nesyt wrote: post #34

Earlier quoted context omitted.

If it was released today, they would skip the awkward do-i-spell-it-out-or-do-i-add-vowels part in favor of the catchy-but-meaningless-project-name and just call it Sequel. Then they could be fresh and say it's the "sequel" to ORM. (Though you might go for Seequill or something so people could google it.)

From Wikipedia: "...SEQUEL (Structured English Query Language), was designed to manipulate and retrieve data stored in IBM's original quasi-relational database...The acronym SEQUEL was later changed to SQL because "SEQUEL" was a trademark of the UK-based Hawker Siddeley aircraft company."

Huh. Interesting. That'll teach me to make jokes without consulting the history books.

Re: What ORMs have taught me: just learn SQL

#240

Post by sytelus »

CmonDev wrote: post #196
sytelus wrote: post #170

After looking at kind of monstrosity like Entity Framework, I've given up on traditional ORMs. My current choice for db access is micro-ORMs like PetaPOCO.

Check the EF7 - they started from scratch: https://github.com/aspnet/EntityFramework

Doesn't look that promising: https://github.com/aspnet/EntityFramework/wiki/Using-EF7-in-...

Look at the simplicity and beauty of micro-ORM like PetaPOCO: http://www.toptensoftware.com/petapoco/

The fact is you don't want to put in large teams in creating ORMs because that would invariably mean feature creep and lot of black magic happening behind the scene. ORMs should be super light weight, a single page doc should be enough to explain everything with it and there should be no "magic".

Post reply on HN