I hear strong opinions among developers for both ways of working with relational database.
Common reasons in favour of raw SQL:
-
We can optimise queries for performance. ORMs can be limited in ways they can be customised, and writing raw SQL allows more control over the craft.
-
We have less overhead. ORM, which stands for object-relational mapping, creates an object (like a Python object) for each of the database records. This additional processing time in object creation can become significant, particularly for queries with large result sets.
These are the main technical reasons. However, I think there is a reluctance among developers who have already mastered the craft of SQL to adopt ORMs. As someone who started their programming career writing PL/SQL, I was skeptical when I first encountered an ORM. I wasn't willing to surrender the joy of meticulously crafting the perfect SQL and let ORM do all the work.
Nevertheless, my perspective on ORMs has evolved. I have realised they are useful abstractions. They help in effective software development. ORMs give us minimal context switching between our chosen programming language and SQL.
I observed that, even with less control over fine-tuning, I could write code more succinctly. I also reused my code conveniently, which meant that I developed software faster. On Django web framework's ORM, I use it's Manager and QuerySet classes to chain SQL clauses, reuse methods across the codebase, work well with other aspects like pagination or authorization. Additionally, auto-generated database migration files are wonderful.
That being said, it does not mean that I can disregard SQL. Like any other tool, an ORM's effectiveness depends on how I use it. It's easy to unwittingly write ORM queries that result in unseen queries or fetch large amounts of data. For more complex use cases, having full control over the executed queries is crucial.
ORM serves as an abstraction. We use abstractions all the time in software. SQL is also an abstraction over the internals of a database. Sometimes we build our own abstractions and sometimes it makes more sense to use existing ones.
As Joel Spolsky stated in his "law of leaky abstractions" all non-trivial abstractions are leaky. When such leaks occur in my ORM, I will be prepared to put on my SQL plumbing hat.