Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

17 Answers 17

up vote 30 down vote accepted
  • 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.
share|improve this answer
8  
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 '10 at 19:13
19  
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 '10 at 19:24
7  
die kitty die!! –  jellyfishtree Dec 15 '10 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. –  Christopher Mahan Dec 15 '10 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 '10 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.

share|improve this answer
  • 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.

share|improve this answer

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.

share|improve this answer

Sure:

  • 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.
share|improve this answer

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
share|improve this answer
2  
What is the "N+1 problem"? I'm not familiar... –  RationalGeek Dec 15 '10 at 20:06
1  
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 '10 at 23:52

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.

share|improve this answer

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?

share|improve this answer

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.

share|improve this answer
1  
PL/SLQ stands for "Procedural Language/Structured Query Language" so how is that not a "program"? –  Christopher Mahan Dec 15 '10 at 21:40

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.)

share|improve this answer

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.

share|improve this answer

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?

share|improve this answer

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...

share|improve this answer

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.

share|improve this answer
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. –  Matthew Frederick Dec 15 '10 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)

share|improve this answer

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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