What ORMs have taught me: just learn SQL (2014)
1–10 of 305 posts
Re: What ORMs have taught me: just learn SQL (2014)
#2The 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 to the database. I prefer Sequel's semantics but ActiveRecord is an excellent workhorse that I have no problems relying on.
Re: What ORMs have taught me: just learn SQL (2014)
#3I 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 people want one or the other exclusively. Both have their place.
I think stored procedures would be very useful if they integrated better with source control and the app code. Maybe we need an ORM for stored procedures that automatically creates stored procedures from the project code?
Re: What ORMs have taught me: just learn SQL (2014)
#4> ORMs are more detriment than benefit
The author mentions Hibernate and SQLAlchemy, which are both DataMapper ORMs. But what about Active Record? It's true that AR will provide even more abstraction and distance from the database, but it also provides a lot more convenience, which for me in small CRUD projects (as most are) has been worth the downsides.
And as another posted mentioned, you can always optimize by replacing slow AR queries with custom SQL ones, and restructuring your database as your project scales.
Re: What ORMs have taught me: just learn SQL (2014)
#5The 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;
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 ActiveRecord, but rather a SQL without strings.
This is my biggest gripe against the Active Record pattern in general, as it ties its model too tightly to the underlying database. It is convenient for a simple CRUD tasks, which may fit about 90% of use case, but that's not the only thing the database is capable of.
Re: What ORMs have taught me: just learn SQL (2014)
#6I 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 unoptimized queries. I frequently pull in data via LINQ to SQL and then massage the data with LINQ for objects where I have better control of performance and operations.
Trying to write pure SQL leads to lots of manual unpacking of the result set which I generally dislike and is much harder to maintain and doesn't work well in practice compared to when LINQ actually works.
I think maybe what I've really learned is use scripting / loosely coupled type systems when working with SQL. In python, I usually just call sql directly rather than use sqlalchemy and its fine because of the loose typing and result set unpacking isn't terrible.
Re: What ORMs have taught me: just learn SQL (2014)
#7The 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…
I don't know about active record but with Django it's simply:
Post.objects.all().annotate(Count('comments'))
SELECT posts.*, COUNT(comments.id) AS comments_count
FROM posts
LEFT OUTER JOIN comments ON (posts.id = comments.post_id) GROUP BY posts.id
Re: What ORMs have taught me: just learn SQL (2014)
#8ORMs 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.
Re: What ORMs have taught me: just learn SQL (2014)
#9I like to say "ORMs make easy thing easier and hard things harder".
What i mean by that is, any simple CRUD operations are much easier in ORM. The hard things, i mean any complex queries that need more than one join you are probably better of writing yourself.
In the end i prefer to do inserts, updates and deletes with ORM (or some other database abstraction tools) but most SELECTs i write myself, fetching exactly what i need and mapping result to objects if needed manually.
Re: What ORMs have taught me: just learn SQL (2014)
#10The 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…
I've actually never used ActiveRecord myself, but when an ORM breaks down and it becomes hard to make certain queries I've had good luck with other ORMs by creating a view in the database and telling the ORM that it's a table. Obviously you can't insert into arbitrary views, but most are happy to give you an object back, with appropriate relationships to the actual database-writable objects.
I'm surprised there isn't more explicit support for this in more ORMs (things like not having 'save' methods on the ORMed classes).