When was the last time you reached for raw SQL instead of your framework's ORM?
In my experience, both with production Rails and Django web apps, I've found myself eventually writing raw SQL for complex queries. I enjoy writing SQL so this has never felt like a problem for me, but I know that's not always a popular opinion.
I'm trying to understand how common this really is and how people have approached solving this when the default ORM is a less than satisfactory solution.
I switched to micro-orm a long time ago. For .Net, I'm a fan of Dapper. I've looked at sequalize and persistence for Node as well.
I stopped using full-blown ORMs a while ago, but I still like to use SQL query builders for simple queries (I use the query builder bits of Sequel for that in Ruby, for instance). I haven't had to write an entire query in raw SQL in a while, but I do use raw fragments for things that just look unintuitive in code instead of SQL. For instance, things like JOINs, aggregations, window functions and the like, I end up writing a small SQL fragment for that and plug into the rest of the query in Ruby.
@juliogreff this is generally my approach too. I just find it too hard to ignore the power SQL has to offer in so many cases.
I'm curious, how did you learn more advanced SQL? I started my career in data warehouse development and eventually transitioned into web development, but I'm guessing that isn't the most common path.
I learned mostly by working with very capable and very helpful DBAs in my previous job, or from making mistakes and having them blow up in my face (and to have the DBAs come save me and teach me better ways). That taught me so much more than whatever was taught in the university I attended.
For MVPs I use ORM bc it's faster (PHP -> Symfony -> Doctrine ORM) BUT for many big projects at trivago I always wrote raw SQL for performance and code simplicity.
It depends like many things in development and the use case. As you mention some people prefer to write their SQL.
I think the big problem with SQL traditionally was when people started mixing business logic at the data layer with stored procedures or something.
I think as long as you have this in mind and you know the query you need to write is complex which the orm wouldn't do as well as you why not.
In work when we do anything new we try and use EF but to be honest in my personal projects I'm happy to use a micro orm and write the SQL myself, I spent 5 years working on SQL server writing stored procedures in a massive db so I'm comfortable doing so.
Besides its your project do what you think is best.
@NealCallaghan I appreciate your thoughts!
This is a great point. There's a delicate balance between abstraction and keeping business logic visible where it matters.