Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
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.
SELECT * FROM..., God kills a kitten. And you hate kittens.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.
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.
Sure:
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:
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.
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...
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.
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.
One biggie -- DDL & database migrations. Sometimes you need to hand stitch that stuff to update things without breaking existing data.