With so many ORM tools for most modern languages, is there still a use case for writing and executing SQL in a program, in a language/environment that supports them? If so why?

For clarity: I'm not asking about if programmers need to know SQL, or if I should have a SQL tool on my desktop. I'm asking specifically why I might have it in code (or config, or whatever) as opposed to an ORM.

  • Can an ORM handle dynamic queries? I know you can change the where clauses without much issue (in most ORMs). But is there one where you can change the whole sql statement on the fly? I know a few uses where that would be impotent and where raw sql would probably be easier to deal with.
    – Tony
    Dec 16, 2010 at 12:04
  • short answer, YES.
    – gabe.
    Dec 29, 2010 at 22:33

17 Answers 17

  • You're more comfortable writing SQL to query data than you are writing procedural code.
  • Your ORM, while beautiful to look at, generates horrifically slow SQL in some critical area.
  • You need to take advantage of functionality exposed by your RDBMS that is not / cannot be made available through your ORM.
  • Every time you type SELECT * FROM..., God kills a kitten. And you hate kittens.
  • You aren't using an ORM, OOP, or a modern language.
  • 9
    All good reasons. Efficiency would be my number one though. Certain situations an ORM as you said generates SQL that can be hand optimzed and fine tuned.
    – Chris
    Dec 15, 2010 at 19:13
  • 23
    If I know what I want to say in English already, Why would I write French and pass it through a black-box which will translate it to English? I'd much rather write it eloquently myself instead of manipulating the French in hopes of it creating the correct English.
    – Mike M.
    Dec 15, 2010 at 19:24
  • 9
    die kitty die!! Dec 15, 2010 at 20:58
  • 3
    I speak french and english natively. The analogy is very good: You can carry the main message, but subtle nuances will be lost. Subtle nuances are sometimes more important, as they act like body language to indicate unspoken positions. I prefer to write SQL. Dec 15, 2010 at 21:37
  • 2
    Leaky abstractions aside, people here seem to forget that most ORMs have many advantages over plain SQL queries: first and second level cache, lazy loading (with eager loading being an option in order to avoid the N + 1 issue) and being able to unit-test the data access layer (by switching the DBMS for a in-memory database), just to name a few...
    – rsenna
    Dec 17, 2010 at 18:44

Writing complex SQL

ORM are great for basic things. However for complex situations you will have to write SQL.

So in short, there most certainly is a need for SQL and it will always remain so.

  • 1
    I recently rewrote a co-worker's project. I replaced 9 of his C# projects (which included 2 data access layers) with a single 150 line SQL script. Running the project (which imported data from SchemaLogic into SharePoint 2010's managed metadata service) now takes 3 minutes instead of 15. The SQL version is so much faster and shorter that it's not even remotely funny.
    – Ant
    Jan 25, 2011 at 23:11
  • One hundred percent agree. For real business applications for any large institution, complex situations always occur. Mar 22, 2018 at 20:01
  • Views
  • Triggers
  • Constraints
  • Packages (SSIS, DTS, etc)
  • Anytime you need to control execution flow precisely

ORM does not help you build, tune or automate the database. It just gives you an alternative way to interact with the database once you have done all that stuff.

  • I agree, but the question is about whether there needs to be SQL in my C# (for example) code, not about whether DB work needs to be done. ORM would lie over most of this stuff.
    – C. Ross
    Dec 16, 2010 at 12:57
  • @c. ross Yes, and it is certainly not the common case, I have had reason to build/alter/parse all of these things through code at points in my career. If you don't have a reason to be doing those things in code then you don't, but I am not aware of an ORM that does a very good job on schema operations. The precise control of execution flow is a more common case for most people, but there are cases where that is not required as well. You are also right that an ORM can lie on top, and I would think that is true in many of these cases as long as you don't alter schema enough to anger the ORM.
    – Bill
    Dec 16, 2010 at 17:35


  • Generally ORM encapsulates a lot, but in the end you should know what happens behind the scenes. This is crucial for performance and scalability. Though inside application I don't write that much SQL but I know roughly how the SQL or DDL looks like.
  • Direct SQL is often nice to write read-only queries. Much easier as to formulate it in ORM query language and you can also limit result set (e.g. 'select id from .....').
  • ORM should not keep you away from SQL at all. I use SQL a lot for ad-hoc queries directly on the DB-client (like mysql-client). It gives you very nice uniform interface and grouping functionalities.

ORMs exist due to the impedance mismatch between our common relational DB implementations and our OO language features. They are only a bridge, yet most folks treat SQL like the Limburger cheese in the fridge.

If you can justifiably say that you will always use your ORM or other abstracted data access layer instead of treating SQL/stored procedures/views as a first-class interface(s), then you'd likely be better off without touching SQL.

In practice, I've never seen a pure-ORM project that didn't require at least SQL to query the the database for final validation.


ORMs are a tool in the programmers tool box. They have their own issues. Some examples are:

  • Can't control sql
  • Suffers from n+1 problem
  • 2
    What is the "N+1 problem"? I'm not familiar... Dec 15, 2010 at 20:06
  • I think it's this: stackoverflow.com/questions/97197/…
    – Axe
    Dec 15, 2010 at 21:05
  • 2
    Not sure I agree. A good ORM should let you execute raw SQL when necessary, and N+1 issues are generally rookie mistakes (e.g. nested loops over lazy-loaded collections) that can be easily avoided.
    – richeym
    Dec 15, 2010 at 23:52
  • @richeym: Those where the first things that popped into my mind. The other answer have my back though. The point being is an ORM is just a tool, there are cases where raw sql is preferred.
    – Tony
    Dec 16, 2010 at 11:58

If you know what you are doing you can effectively use an ORM to replace much of your CRUD type code. They are not as effective for complex things though, they are hard to performance tune (you do know that performance is one of the critical parts of database design, not emulating objects) and they are downright horrible and dangerous in the hands of someone who doesn't understand or write SQL themselves.

I also want to point out that complex reporting is not easy to effectively do with an ORM. And further if you don't learn the simple SQL in the easy crud stuff, how will you ever get to the point where you can write complex SQL for reporting? I've never worked in an application that didn't have reporting needs and often quite complicated ones.

Nor are ORMs useful for BI or ETL processes for the most part. Nor are they useful for database admin queries or for finding information in the audit tables and undoing a particular set of database changes. There are many many things that are still most effectively done with SQL. The application querying the database is a small portion of what needs to query it in an Enterprise environment.

I also see many questions about how to do something using an ORM that the poster already knows how to do in SQL. It's nice to learn new things, but when they are causing extra time and effort with no real gain over the orginal method (and often a real loss of performance), why are you using them other than they are "fashionable" right now.


Sometimes a client just wants a quick and easy query to return data for a report, export, datadump, etc and want to wait for an entire program be developed.

Also, a good SQL programmer can always write faster, more efficent SQL than any ORM I have used. Also, I have found a lot of people just have the ORM point to stored procedures - really ignoring the benefits of the ORM cause ORM's are not great for complex processes.

Also, when using a database like Oracle with a very rich and powerful procedure language, you can do a lot without ever needed a "program". PL/SQL on Oracle in the right hands is very fast and efficent.

  • 2
    PL/SLQ stands for "Procedural Language/Structured Query Language" so how is that not a "program"? Dec 15, 2010 at 21:40
  • Christopher Mahan: Exactly. The main product of a company I've worked for consists of ~5 times more PL/SQL code than Java Code (LOC).
    – user281377
    Dec 15, 2010 at 21:46

If you want to use a database yourself, yes. Most of the ORMs that I tried to use just weren't sufficient or didn't cover my database. Besides, how are you going to troubleshoot or write custom queries that aren't covered?


It's still required for writing triggers and stored procedures. Stored procedures may have fallen out of favor currently, but they're still very useful in some situations. SQL may also be required for some particularly hairy multi-table joins.


Yes you can avoid writing SQL. But you will end up writing HQL instead (or Linq, or DQL...)!

Seriously, I'm am a big fan of ORMs, and I do think one may avoid using pure SQL most of the time. But we must remember that an ORM is just one big abstraction, and all big abstractions leak...

(But about the N + 1 problem: this is lazy-loading related, and most ORM tools have some way of requesting eager-loading, thus avoiding that particular issue.)


ORM will only get you to some point. But there are cases when you have to execute a really complicated query, with complicated joins, subquery, unions, minus, analytical functions etc. That's when you need SQL. But how are you supposed to write complicated queries when you leave all simple queries to the ORM?


Even if you didn't need to write it in your code, it's pretty handy to be able to use it when you have terminal access to a database server.

Also, most of what makes programming a challenge is working within the restrictions that life sets us- often we are working with old code, or old versions of databases and don't have the opportunity to install the latest ORM library for whatever language we are working with. In that situation you will need any tool at your disposal.

The rest of the time you may not need SQL for your CRUD stuff, but there is a lot more to SQL than simple SELECT, INSERT, UPDATE and basic JOIN queries. You can do very clever things with it and although you may not use them often, it's useful to know what they are.

Increasingly, I think we will find ourselves in a post SQL world, however- most of the Cloud services use non-sql table storage and for simple CRUD type work the full power of SQL is unnecessary. But that doesn't mean there will be no value in understanding it.

Also, of course, someone has to know enough to write a better ORM system if the current ones aren't up to much. It would help them if they knew SQL...

  • Exactly: having written a reporting engines and fancy reports from an oracle data warehouse environment, I can emphatically tell you that knowing SQL isn't the same as knowing how to use an ORM. Dec 15, 2010 at 21:42

For building large scale web applications its completely unnecessary and can make things much more stressful and time wasting than they need to be. The reason for this is that any large-scale app should be making use of a memory persistence layer (in RAM) which should be memory caching parts of the DB that are frequently used.

If you have to do things with mobile devices, etc that don't have a lot of memory to work with, where the application that is being programmed is running as a "client" or standalone app on a mobile device, tablet, etc than things like using SQL are still common and are important because the memory on the device is so small that you can't cache a lot of things in memory.

  • 2
    "any large-scale app should be making use of a memory persistence layer (in RAM) which should be memory caching parts of the DB that are frequently used." -- Any decent database will be doing this, too. Dec 15, 2010 at 23:34
  • Android and iPhoneOS both use SQLite, a SQL-92 compliant database system. See stackoverflow.com/questions/2011724/… Jan 25, 2011 at 23:34

The complexity and the amount of the SQL I write is not sufficient to make hooking an ORM framework in reasonable.

(I write SQL maybe once per month, if)


I've came across many large corps with DBA's who actively fear devs using ORM tools.

They are DBA's who are involved in tuning and performance.

And yes, ORM tools can be managed to do stuff like this, but I've seen places where they will only accept a stored procedure (Sql Server) and will question you about what you're doing in it.

Also, ORM tools can be badly abused and produce just as poor Sql as writing the Sql yourself.

  • I guess the DBA who worries about a dev using an ORM is akin to a dev worrying about an analyst/manager/customer given a tool that lets them write code!
    – gbjbaanb
    May 30, 2011 at 20:13

One biggie -- DDL & database migrations. Sometimes you need to hand stitch that stuff to update things without breaking existing data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.