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)

wozniak.ca

Post reply on HN
Print view View original

11–20 of 305 posts

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

#11

Post by ams6110 »

maxxxxx wrote: post #3

I think ORMs are a great tool to get something off the ground quickly. Like with most tools you will hit a point where they make things more difficult and then it's probably time to switch to SQL only or mix SQL with ORM especially for performance critical queries. In most applications I have seen the ORM provided a lot of value but there were cases where it needed to be augmented with raw SQL. I never understand why…

It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.

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

#12

Post by bunderbunder »

tfigment wrote: post #6

I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I don't even want to talk about Java and some of its ORMs as its too painful to think about. I agree with the sentiment of the post but in compiled languages I really want an ORM to simplify unpacking result sets. LINQ is great when it works but joins sort of suck as well as calling in-built sql functions and it can some times generate highly…

On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets.

Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability.

The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. That one is head-and-shoulders above any other option I've found for working with databases. It does require that you write your data access layer in F#, though, which may make it a hard one to sell at work.

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

#13

Post by vrdabomb5717 »

Animats wrote: post #8

ORMs tend to assume that there is "the application" with "its database". If the application changes, so does the database. If the data is used by more than one application, it's better to have the data defined in the database and write applications as database clients.

At the big tech firm I work at, there's a best practice where any database (whether that's a traditional RDBMS or a NoSQL client) is abstracted away by a microservice with a defined API, and every other application that wants to get that data needs to interact with the microservice. That way, the database schema can change without it affecting multiple applications. There's still the traditional mismatch between ORM and database, but doesn't feel as painful because only a single application is using that data, and that application can have special knowledge of the persistence layer underneath (meaning, use database hints if necessary, defer to raw SQL, etc.)

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

#14

Post by EToS »

I love ORMs, they help abstract the database technology which has been really useful in a recent project switching an app from MSSQL to PostgreSQL. I also personally as developer appreciate the programmatic syntax.

I would caveat the performance issue, sometimes there is no better way, however i would always try to make things work in the ORM first before switching into native SQL to get the job done.

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

#15

Post by YeGoblynQueenne »

Sigh. Whenever I have to hurt myself at work on such issues, I find myself thinking of the web that could have been, if only...

Let me backtrack a bit. There's a much better way to do things than the way we do it right now. A way that completely obviates the need for ORMs, or indeed any way to deal with the interface between application and database.

You see, there would be no object-relational Impedance Mismatch (and therefore no need for such clunky kludges like ORMs) if the database and the application were both written in languages of the same paradigm, either both OOP or both relational. Ideally, that would be a single language, that could handle both data and application logic.

There is such a language: Prolog [1]. Prolog is implemented as a relational database. So your program itself is the database. There is no separation between data and operation and therefore no ORIM and no need for ORMs, or stored procedures or anything, really.

And, yes, it's perfectly possible to do your web dev in Prolog. Here, see this explainer on the Swi-Prolog website, titled "Can I replace a LAMP stack with SWI-Prolog?":

http://www.swi-prolog.org/FAQ/PrologLAMP.txt

Hint: Yes. Yes, you can. You can replace LAMP (or LAM-whatever) with an LP stack, where all you need on top of your OS is Prolog itself. Its built-in Definite Clause Grammars notation [2] can be used to parse and generate javascript, html, xml, css, YAML, whatever you like. Swi-Prolog even offers translation to RDF, which is as natural as you can expect given RDF is also a relational language.

Here's a big fat tutorial to help you started:

www.pathwayslms.com/swipltuts/html/index.html

Can this be done for real? OMG yes it can. The Swi-Prolog website itself runs on an LP stack. There's a few more websites that do too:

www.pathwayslms.com/swipltuts/html/index.html

In short: you don't need to hurt yourself as badly as you 're currently doing it. There's no other need than of course, nobody wants to learn Prolog. I know that, I've made my peace with that and I've spent all of my so-far career hurting myself against the ORIM just like everyone else.

... but we could have had a better web.

___________________

[1] https://en.wikipedia.org/wiki/Prolog

[2] https://en.wikipedia.org/wiki/Definite_clause_grammar

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

#16

Post by PavlovsCat »

sirn wrote: post #5
vinceguidry wrote: post #2

The thing I love about ActiveRecord is that it makes it easy to, anywhere you want, and at any level of the abstraction stack you want, to just toss in SQL fragments. This gets you the best of both worlds, the fluidity of being able to just define methods on model objects, and the ability to utilize database tech to the fullest. You can take any query and call .to_sql on it and it shows you exactly what it's passing…

But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord, without 1+N queries, or caching comments_count in a column somewhere? Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite Act…

What's more, even with just bare prepared statements.. how do I use dynamically built SQL queries and prepared statements together? And please don't just say "don't", at least not without telling me how to achieve what I need the proper way :)

For example, let's say you have a query that gets search results, and depending on whether the visitor is logged in or not you also may want to know whether a given search result happens to be a favourited item. The way I understand it, I would have to do for example:

    $query_text = 'blah';
    if (user is logged in)
    {
        $query_text .= 'SQL pertaining to favourites';
    }
    $stmt = $dbh->prepare($query_text);
    if (user is logged in)
    {
        $stmt->bindParam(':user_id', $user_id);
    }
Or am I missing something?

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

#19

Post by sirn »

samwillis wrote: post #7
sirn wrote: post #5

Earlier quoted context omitted.

But how do you do: SELECT posts.*, (SELECT COUNT(1) FROM comments WHERE post_id = posts.id) AS comments_count FROM posts; In ActiveRecord, without 1+N queries, or caching comments_count in a column somewhere? Admittedly, that was not the best example. The last time I need something more intertwined than a simple COUNT in subquery, the answer was "give up and just use Arel." But at this point it is no longer quite Act…

I don't know about active record but with Django it's simply: Post.objects.all().annotate(Count('comments')) Which produces (simplified, Django would actually explicitly select each column): SELECT posts.*, COUNT(comments.id) AS comments_count FROM posts LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id Nice, easy and without 1+N queries.

That is actually very nice and elegant!
Post reply on HN