What ORMs have taught me: just learn SQL • Hacker News Board

Re: What ORMs have taught me: just learn SQL

#121

Post by snarfy » Tue, Aug 05, 2014, 1:39 AM UTC

virtualwhys wrote: post #120

snarfy wrote: post #87

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. What you described is basically EF+LINQ. var query = context.Users.Include("Users.Group").Where( u => u.UserType == UserTypes.Basic).Select( u => u.Group); That's go…

The same in Scala + Slick would be: val q = Users.filter(_.userType == UserTypes.Basic).map(_.group) Although I'm not sure what the context.Users.Include bit in the EF example is doing. Both will generate statement at compile time, and I assume EF queries are composable as is the case with Slick. Slick's readability does suffer though with more complex queries -- unfortunate they strayed away from SQL semantics in fa…

Include() on an ObjectContext will return the corresponding foreign key objects. It's a poor man's join to related entities. There is also Join(), but that will join by anything.

Re: What ORMs have taught me: just learn SQL

#122

Post by plaguuuuuu » Tue, Aug 05, 2014, 1:52 AM UTC

JohnBooty wrote: post #103

I wrote "raw" SQL for many many years before using ORMs, so I feel a lot of this guy's pain and agree with most of his points. Especially the part about still having to know SQL even though you're using an ORM. Not sure I understand his solution to this one, though! Window functions are relatively advanced SQL that is painful to write with ORMs. Not writing them into the query likely means you will be transferring a…

Let's talk practicality rather than sitting in some ivory tower and muttering about best practices

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.

Re: What ORMs have taught me: just learn SQL

#123

Post by digitalpacman » Tue, Aug 05, 2014, 2:03 AM UTC

Pretty sure you are missing one of the biggest rules of ORMs. This rule being you don't use them for reporting software or reporting queries.

Re: What ORMs have taught me: just learn SQL

#124

Post by [deleted] » Tue, Aug 05, 2014, 2:09 AM UTC

GrinningFool wrote: post #116

aikah wrote: post #100

Earlier quoted context omitted.

that's absolutely not true.Sure you cant write a procedure with an ORM. But good ORM have query builders that with allow you not to write ANY sql.At all.ORMs are not just about CRUD at all.

The biggest downside to this that I found is that you have to have a lot of trust in the creators of that ORM. SUre, it will generate a query that gets you what you want - but will it use the right indices? Will use correct ordering for optimal results? Generally (unless this has changed in more recent years) the answer is 'no'.

[deleted]

Re: What ORMs have taught me: just learn SQL

#125

Post by [deleted] » Tue, Aug 05, 2014, 2:13 AM UTC

jasode wrote: post #81

doctorpangloss wrote: post #39

Earlier quoted context omitted.

> 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. I think this is the appeal of MongoDB's driver on Node: You really do have programmatic access to the AST, insofar as the microlanguage is just a plain old Javascript object. Though SQL is more universal, Mongo's approach definitely has thought hard about th…

>Though SQL is more universal, Mongo's approach definitely has thought hard about the balance between abstract and concrete that me and my other developers find very intuitive. I think you've got the causality and conclusion backwards here. MongoDB's easy programming API is a consequence of its storage layout on disk -- the JSON/BSON bytes on disk. From that principle, you naturally get an "ORM" type of API exposed i…

[deleted]

Re: What ORMs have taught me: just learn SQL

#126

Post by kayman » Tue, Aug 05, 2014, 2:17 AM UTC

Learning ORM without learningn sql is great for beginnners. Ex: Django Framework for new comers. But like any abstracction, learning SQL will allow you to optimize w raw sql as needed. Its like trying to learn coffeescript without learning JavaScript. It always helps to learn from bottom up..

Re: What ORMs have taught me: just learn SQL

#127

Post by daigoba66 » Tue, Aug 05, 2014, 2:19 AM UTC

endlessvoid94 wrote: post #26

lpolovets wrote: post #3

Earlier quoted context omitted.

(caveat: I'm a developer but I haven't used ORMs very much.) Don't most ORMs let you write raw SQL when you really want to? In that case, you could use the ORM for simple things, but revert to raw SQL when you need more power. Or is that not the case?

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 more specifically the rows in your tables is data. And data tends to long outlive even through many application redesigns.

Re: What ORMs have taught me: just learn SQL

#128

Post by [deleted] » Tue, Aug 05, 2014, 2:20 AM UTC

krapp wrote: post #105

aikah wrote: post #101

Earlier quoted context omitted.

> At the very least, it makes the code more portable How writing SQL make your code more portable ? using an abstraction make something portable.SQL implementations are differents from one table to another.

Using an ORM ties you to that implementation. I can more easily take a raw SQL statement and use it elsewhere.

[deleted]

Re: What ORMs have taught me: just learn SQL

#129

Post by daigoba66 » Tue, Aug 05, 2014, 2:23 AM UTC

bkeroack wrote: post #106

jurre wrote: post #97

Earlier quoted context omitted.

This is simply not true, lots of tools are able to tell you exactly which line of code is responsible for which query.

Such as? Using ASP.NET and NHibernate, I've not seen any way to do this other than old fashioned log statements and guesswork.

http://www.hibernatingrhinos.com/products/nhprof

Worth every penny. You get full stack traces and line numbers automatically with each query.

Re: What ORMs have taught me: just learn SQL

#130

Post by wickawic » Tue, Aug 05, 2014, 2:28 AM UTC

nesyt wrote: post #34

glhaynes wrote: post #16

Earlier quoted context omitted.

"Object-oriented programming is great. It's what's enabled developers to create the vast world of amazing applications available today. But there's an impedance mismatch between OOP and relational databases. That's why today we're reinventing database access. Say hello to SQL."

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