Using ORMs vs Writing Raw Queries

Using ORMs vs Writing Raw Queries

This is not exactly a stand off between the two concepts, rather an opinion piece based on my experiences on where ORMs shine, and where raw queries have their unwavering place. This article will use SQL for examples, but the points may apply to any database that you're using.

Here's my opinion: it's best if you use a combination of both. When you're building basic CRUD APIs and such, ORMs are fantastic. When your application becomes complex with more complex queries, and your data volume grows, then it becomes imperative that you use raw queries for a number of reasons.

So let's get into it!

Your team is building a production-grade application connected to a database, and you want the application to be scalable, maintainable, and fault tolerant. How you choose to interact with your database can have a significant impact on your development as well as your live product / service.


Pros of ORMs

ORMs are awesome in their own way, where some concepts have been built and improved over decades. First let's look at some of the key benefits of using ORMs:

  1. Productivity: ORMs can significantly speed up development for basic CRUD APIs since the common queries are typically built-in the ORM models. Many common use cases are already implemented. The ORM models also provide interfaces for writing more complex queries, so you can manage your queries as code.
  2. Connectivity Features: Modern ORMs offer built-in features such as safeguards against SQL injection attacks for security, connection pooling, retry mechanisms, support for read replicas, etc. All of this stuff is crucial when it comes to any production application, so many ORMs provide interfaces / abstractions and take care of these things behind the scenes.
  3. Managing Change: ORMs often provide tools for managing database schema changes, allowing you to keep track of historical changes in your version control i.e. Git. whether it's schema changes or seed scripts
  4. Type Safety: Goes without saying, any typed language with an ORM will have respective models that will provide typed objects when getting results from your queries. This makes development easier as you know what to expect. Although, keep in mind that the model mappings (column types and properties) should match the real properties of the database schema.


Cons of ORMs:

  1. Loss of Control: A lot of it is abstracted, especially for complex queries or schemas, so you don't exactly have control over how the queries are generated by the ORM. Knowledge of the raw query syntax and respective database capabilities can help you write more efficient queries.
  2. Debugging: If you want to investigate any bugs or issues, first of all you need to know what the query is. ORMs provide debug-modes to print the generated query when it runs, but this is a drag any time you have to investigate query issues.
  3. Performance Optimization: When dealing with complex queries with joins, subqueries etc. ORMs can prove to be more of a hassle. It is more difficult to tune the performance of a query with an abstraction layer, rather than working with the raw query in a query IDE. You will have greater control over query optimization and indexing.
  4. Loss of Readability: ORMs generally make the query code readable (since you might have your code formatting syntax etc.), but for complex queries it goes backwards where the raw query is much more readable (and also more easily writable) rather than using the abstraction layers.


One general con of using any abstraction layer is that you lose sight of the inner workings of the technology. For example, for most applications you will probably get by with most of your business logic using ORMs even if you don't learn any SQL. This is very bad.

It might seem like a good thing because you can get an application up and running without knowing all that much, but in the long run you as an engineer will suffer if you don't equip yourself with the skills and knowledge of SQL features and writing optimized queries. This will not only help you grow as an engineer but will help you build resilient and performant applications.

So if you're learning about ORMs for the first time, they might seem amazing! However, it does not come without trade offs. Hope this article helps give some insight into what you might deal with when building real world applications.

Shah Md. Arafatul Rahman

Software Engineer | Laravel | PHP | jQuery | WordPress | Database | Git |

1 年

it depends

Md Mamunur Rahman Moon

Django | REST API | ML | Web Development | Python | JavaScript

1 年

As a beginner, I believe that working with ORM can be overwhelming without a basic understanding of minimum SQL queries. By the way, I gained some important insights from this article.

Priya Raj

Freelance Consultant at Freelancer

1 年

ORMs are always useful and we get a chance to use multiple database according to the company requirements. At the same time it is a overhead as well when there is no need of using multiple database. Hence combined usage will be better.

Mujammal Ahmed

Software Engineer | Competitive-Programmer

1 年

Shawon Majid, that's what we were talking about ??

要查看或添加评论,请登录

Sabbir Siddiqui的更多文章

  • Cron Jobs vs Events for async data processing

    Cron Jobs vs Events for async data processing

    Let's consider this scenario: You are building a system which integrates with a payment provider, and you're storing…

    12 条评论
  • Joins vs Split queries in SQL

    Joins vs Split queries in SQL

    Welcome to my newsletter! In “It Depends”, I’ll be discussing various trade-offs and challenges that software engineers…

    16 条评论
  • Conversational Commerce in Bangladesh

    Conversational Commerce in Bangladesh

    The internet has allowed people to be in tune with the rest of the world, no matter where they are, and Dhaka is not…

  • Quest for a practical NodeJS API Framework

    Quest for a practical NodeJS API Framework

    While working on REST APIs with NodeJS/Express, I came across some common challenges: I had to configure Express the…

    2 条评论

社区洞察

其他会员也浏览了