3 ms·
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 h
by JohnBooty 12y ago
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 lot of extra data from the
database to your application.
In these cases, I've elected to write queries using a
templating system and describe the tables using the ORM.
Templating system? Whaaaat? Here's what I do in ActiveRecord + Postgres and/or SQLite. (Edit: OK, I think I understand! Other people in this very HN thread have discussed templated approaches, like https://news.ycombinator.com/item?id=8134170 https://news.ycombinator.com/item?id=8134170 )
1. I stick to ActiveRecord whenever possible for the simple stuff. It's really good for like 75% of what I want to do. Being able to chain scopes and things is neat and a good example of something you can't easily do in SQL.
2. When I need to work some SQL magic that ActiveRecord can't do (or can't easily do) that's when I write a SQL view or function. So my class might be something like:
# Example is Ruby+ActiveRecord but concepts should be applicable elsewhere
class SomeClass < ActiveRecord::Base
def some_simple_method_that_uses_activerecord
SomeClass.where(foo: bar).order(:baz)
end
...
def some_method_that_needs_sql_magic
SomeClass.find_by_sql "select a,b,c,d,foo,baz from some_crazy_view_or_table"
end
end
This has been working pretty well. For the simple CRUD stuff that ActiveRecord is good at, I use ActiveRecord's built-in query interface. For the other stuff, it's easy to write a SQL view/function and simply reference that. It's not perfect because obviously you risk having too much of your app logic live in the database layer... although, then again, I feel equally icky about having too much of the data logic live in the app layer.
- plaguuuuuu 12y agoLet'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.
- JohnBooty 12y ago> 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. Do you know if other ORMs allow this kind of relatively painless use of "raw SQL?" I've only used ActiveRecord and some of the .NET "micro ORMs" like Dapper and PetaPOCO. Dapper and PetaPOCO definitely support raw SQL. That's kind of their main focus - they take a row of SQL results and map those database columns to your class's properties, and by design they don't do a whole lot else. But I don't know about other big/popular ORMs like Hibernate...
- Hakeashar 12y agoEven the bulkiest ORMs allow you to use raw SQL. That's why you can use 80 - 90% of the features on the regular basis and hand-tweak regions which cause performance problems or places where you just have to write SQL (e.g. recursive queries). In EF, there's either: Database.SqlQuery<T> - http://msdn.microsoft.com/en-us/library/gg696545%28v=vs.113%29.aspx http://msdn.microsoft.com/en-us/library/gg696545%28v=vs.113%... which can return any object or: DbSet<T>.SqlQuery - http://msdn.microsoft.com/en-us/library/gg696332%28v=vs.113%29.aspx http://msdn.microsoft.com/en-us/library/gg696332%28v=vs.113%... which returns tracked entities, so you can write raw SQL (e.g. call a stored procedure) and just use the 'mapper' part of the framework. NHibernate has CreateSQLQuery - http://www.nhforge.org/doc/nh/en/#querysql-creating http://www.nhforge.org/doc/nh/en/#querysql-creating I like micro-ORMs, but when you want to skip writing tedious INSERT or UPDATE queries, you have to add extensions to them (at least to Dapper); that, and SQL strings do not really lend well to refactoring and type safety...