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

81–90 of 245 posts

Re: What ORMs have taught me: just learn SQL

#81

Post by jasode »

doctorpangloss wrote: post #39
makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

> 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 in the programming language basically for free. I'm saying you don't have to do a lot of theoretical computer science type of research and pondering to get a disklayout+API that looks like that. In fact, the early 1960s mainframes laid out data records as fully denormalized (similar to JSON/BSON) before the relational DBs became popular in the 1970s.

But, if your app starts expanding into complex data that's not 100% embedded as fields within one document, you start needing to do "relational" type of joins across documents. And those joins will manifest itself as extra programming code/logic on the client side. You're still paying for added complexity. You're just paying in a different way from the ORM+RDBMS programmers.

Many MongoDB projects want it to perform more like relational. Likewise, many RDBMS want it to act more like an object hence ORMs. The impedance mismatch looks like irreducible complexity.

Re: What ORMs have taught me: just learn SQL

#82

Post by GeoffWozniak »

makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

What I didn't mention in my post was that I did use SQLAlchemy Core to write some pretty complicated queries. It's actually quite good. I like it.

There were some spots that things got hairy though, and the code was pretty hard to follow. I don't fault SQLAlchemy here, but I wrote the query in SQL and it was simpler to work with.

SQLAlchemy is absolutely on the right track, but using the core doesn't diminish the fact that you need to know SQL to use it effectively.

I agree with your assertions that just an ORM is not enough, raw SQL is hideous, and that raw SQL spits in the face of programming language advancements. Sadly, it's the assembly language of databases and, unlike CPUs, doesn't have a good abstraction model.

Re: What ORMs have taught me: just learn SQL

#83

Post by th5 »

makmanalp wrote: post #79
mamcx wrote: post #56

For long time, I have no idea that SQL & OO are not friends. I work in a language where such problems don't exist. And it have nice ways to move data between tables and data structures and objects (For example: SELECT..INTO Array NAME). Is FoxPro. Even the stored procedures were foxpro, all along the stack, from UI to inner DB actions. Is a shame that this kind of programming is "lost" today. This is kinda like work…

You know, I remember visual foxpro coming with microsoft visual studio 6.0, and not really understanding much about it. Care to expand upon what made it awesome? I found some stuff here but it doesn't really explain it well: http://www.foxprohistory.org/articles_4.htm

ah yes. foxpro! this is the first web language i ever learned. it was awesome because the db and application language live in the same space. You could run SQL directly inside the language...

When i learned the "best practices" of the time (1996 ish)... i thought it was extremely inefficient to have to "formulate a sql statement", then call it then retreive the result...

Re: What ORMs have taught me: just learn SQL

#84

Post by thom »

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…

YeSQL seems a little bit like it's reinventing the Microsoft data access ecosystem of 10-15 years ago - stored procedures behind a code-generated API. Retro is cool.

The Clojure SQL ecosystem is weird. clojureql seemed wonderful for a while, but has been left to rot and the fact that nobody's really picked it up implies people have just moved on with their lives. Korma and YeSQL seem to handle most of the Rails-like use cases, and I guess everyone else has moved to more esoteric datastores.

Re: What ORMs have taught me: just learn SQL

#85

Post by manicdee »

Learn to recognise anti-patterns and deal with them ASAP: http://pragprog.com/book/bksqla/sql-antipatterns

Also, learn about red/green/refactor. Refactor mercilessly. Learn both ORM and SQL, understand the technology you're working with.

The "wide tables" problem is easily addressed through "associated data" tables. Don't store everything in the one model. You have one model which is for computation, searching, comparison, etc. You have another model which is for storage of stuff that is only required at report time. This is a ORM-level of "partitioning". Just avoid "fetch-related" style ORM requests and you're golden.

It's really worth reading whatever references are available on tuning your particular database for performance. You can take the advice onboard and let the performance advice contribute towards your ORM design.

The SQL-templating approach is tempting, but it will very quickly lead to problems as someone adjusts a template and adds or removes parameters. Do you have tests in place to ensure that the SQL templates still function as expected? How tightly bound is your SQL to one particular vendor's product? Been there, done that, bought the t-shirt. Did you know that MySQL will automatically update the first TIMESTAMP column any time that row is updated? It doesn't even ask!

Re: What ORMs have taught me: just learn SQL

#86

Post by kyllo »

jdotjdot wrote: post #76
dharbin wrote: post #40

Earlier quoted context omitted.

I'm loving all the momentum towards writing templated-sql, in fact, I wrote a library for this myself[1]. By leveraging jinja2/django-style template inheritance, you can even bring some advantages of ORMs (composition, reuse, and extending) into the raw-sql world. The OP also intimated that he's taking a templated approach: "“In these cases, I've elected to write queries using a templating system and describe the tab…

If you're at all interested in opening a kick starter for such a templating library for Django, I'd back it. I have attribute creep all the time and actually generally prefer raw SQL with the exception of its verbosity. The problem is, migrations are awful and SQL injection mistakes easy to come by. Would be great to have the best of both worlds in a SQL templating engine + sort-of ORM wrapper that auto-generates via…

Without the ORM, though, what would be the point of using Django? To me it seems like this would be a better fit for a more minimalist platform like Flask.

Re: What ORMs have taught me: just learn SQL

#87

Post by snarfy »

makmanalp wrote: post #33

Over and over I keep finding that just an ORM is not enough, but raw SQL is hideous in a different way. ORMs map nicely when you are indeed modifying objects, but somethings don't map well that way. So don't map them that way! What we need is a low level abstraction layer alongside the ORM. The main problem with raw SQL is that what you really want is a genuine programming language. You almost want programmatic acces…

>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 going to spit out a SQL statement that joins Users to Group and selects all groups where the UserType is UserTypes.Basic. You can even do ToTraceString() to see the generated statement.

Re: What ORMs have taught me: just learn SQL

#88

Post by thom »

This is fascinating for me, because I've recently had the opportunity to watch someone from a pure maths background learn SQL from scratch. It seems like a very different experience when you arrive on day one appreciating the underlying theory of relational databases, as opposed to my experience from a more enterprisey background. I didn't realise what a mental block the subset of SQL available to Hibernate had imposed until recently, and I suspect things like ActiveRecord hide even more of the good stuff away.

Re: What ORMs have taught me: just learn SQL

#90

Post by steverb »

mamcx wrote: post #56

For long time, I have no idea that SQL & OO are not friends. I work in a language where such problems don't exist. And it have nice ways to move data between tables and data structures and objects (For example: SELECT..INTO Array NAME). Is FoxPro. Even the stored procedures were foxpro, all along the stack, from UI to inner DB actions. Is a shame that this kind of programming is "lost" today. This is kinda like work…

Dear Lord in heaven, no.

Yes, the data language and the application language were all the same language, which led to some awful spaghetti code where data access was strewn all over the place.

Then you also get to deal with scaling issues because your db and application logic all run on the same box.

But hey, you can still download and run Visual FoxPro from MSDN if want,

Source: My team is in the process of slowly strangling a FoxPro application whose only system spec is "what it does".

Post reply on HN