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

21–30 of 305 posts

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

#21

Post by prodigal_erik »

PavlovsCat wrote: post #16
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…

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 resu…

Queries with and without user_id probably have different plans (e.g., table scan vs. index lookup) and need to be prepared separately. I've never seen an ORM that takes prepared statements and query planning seriously, generally they only emit trivial queries.

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

#22

Post by tfigment »

bunderbunder wrote: post #12
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. Tha…

Thanks. That actually doesn't look that bad. It would be nice to have anonymous type objects but I recognize the difficulty in that. This looks like a nice compromise. Now if they could also fix passing in arrays as part of a parameterized query "select x from y where z in ?" where ? is a collection of strings or integers it would be perfect but I think that is a driver/interface problem.

Edit: Looks like it actually does says it does support the enumerable but I'm questioning if it will do what I want. Will have to test out but I'm guessing there is a limit to the size of the array.

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

#23

Post by VLM »

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.

I've slathered in yet another confusing layer by creating an application specific view.

Now you've got the ORM layer, which possibly translates to SQL correctly, to talk to a view, which is a front on the real untouched table. Its kind of a hassle.

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

#24

Post by jjoonathan »

ams6110 wrote: post #11
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.

It's not only easy to manage stored procs separately, it has huge advantages: you can play with your SQL in an IDE built for the task with luxuries like real-time error highlighting, profiling, debugging, and auto-complete, and in some cases you can even iterate on your code without restarting your application (just update the proc).

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

#25

Post by prodigal_erik »

vrdabomb5717 wrote: post #13
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…

We have decades of research into filtering, joining, and aggregating across a complex set of tables and views. With microservices you have to roll your own query planning and stream all the intermediate results on the wire even when you're throwing away most of them.

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

#26

Post by brightball »

ams6110 wrote: post #11
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.

My biggest concern has always been around zero downtime deployment with stored procedures. Your database has to work with both the old and new versions of web code during the deploy in case of rollback to make that process work and that always struck me as the weird edge case that makes things tough. If a procedure never changes or remains backward compatible then it should still be fine though.

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

#27

Post by bluejekyll »

I'm curious how the author would like MyBatis, which is just a thin wrapper for calling SQL, and then translating results to POJOs.

I like it, and it, IMO, keeps the API distinct from the access in the DB. You have to write your own SQL, which is good and bad. Huge amounts of control and performance, but higher portability costs.

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

#28

Post by pixie_ »

vladsanchez wrote: post #17

I've always said: "ORMs are for people who don't know SQL!"

ORMs are not for people who don't know SQL.

The secret to using an ORM is knowing exactly the SQL it will generate underneath. The advantage to using one is static typing as well as writing orders or magnitude less code than SQL. As well as orders of magnitude more understandable code than the equivalent SQL.

If you work on heavy enterprise applications the ORM can be your best friend or worst enemy. It comes down to knowing the tool you're using inside and out. It will make you much more productive in the long run. And sometimes yea, you need straight SQL or a stored proc to get the job done. That's OK.

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

#29

Post by greydius »

ams6110 wrote: post #11
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.

In my limited experience with the enterprise world, the problem with using stored procedures was that the devs had no control over the database. We could have come up with some nicer solutions to certain problems, but no one wanted to deal with the bureaucracy necessary to create and maintain parts of our applications within the database.

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

#30

Post by fbonetti »

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…

That's really easy.

    Post.select("posts.*, count(comments.*) as comments_count").joins(:comments)
Post reply on HN