7

Every day I see SO questions with some variation on this theme (C#-ey pseudocode)

method MyButtonClickHandler(){
  string sql = "SELECT * FROM table WHERE column = " + myTextBox_Text
  sqlcommand cmd = new sqlcommand(sql, someConnectionString)
  datareader rdr = mycommand.ExecuteAndGetReader()

  while(rdr.HasResultRows)
    string name = rdr.GetString(1) //column 1 is the name
    ...
}

I regard this construct/methodology as pretty low level:

  • Many times over tens of years, people have built some SQL-generating abstraction layer flavoured more like a high level language which makes the SQL part de-facto low level
  • I can't really think of any way lower than writing an SQL statement/getting a reader, to get data out of an SQL based database like SQL Server or Oracle (other than directly reading the bytes of the data store file SQL Server uses)
  • It's the sort of code that breaks out of type safety, compile time sense checking and cleaning up after yourself properly; it's the sort of code that ought be done to some formulaic pattern and hence really should be written by other software, not humans

I routinely find this particular construct in some high level workspace, let's say something like ASP.NET MVC or Web Forms; in and of themselves a much more abstract way to get the server to generate some HTML and send it to the client browser. Here's a high and low level example of what I mean:

//MVC, rendering a dropdown list (some enumerable collection has been put in the page's data model):
@Html.DropDownList("EnumerableCollectionNameHere")

//WebForms, rendering a dropdown list (plus some back end code to populate the Items collection it uses):
<asp:DropDownList id="someList" runat="server"/>

//One possible low level way of getting a drop down list into the user's browser (C#):
httpResponseStream.Write("<select id='someID' name='someList'>");
foreach(KeyValuePair itm in somecollection)
  httpResponseStream.Write("<option value='"+itm.Key+">"+itm.Value+"</option>");
...

We stopped doing the lattermost a long time ago, and for good reasons I'm sure can be imagined - but it's a low level way that still technically can be made to work today; form some raw HTML in code, string concat, whatever, and send it down the socket to the client. It feels to me every bit as low level as the SQL/reader/getstring


I thus find myself looking at something that puzzles me; the question poser seems to be asking a basic question and I thus assume he's a beginner. He is clearly using many high level framework(s) to implement his app and probably in most areas of the workspace you could say "that could be done in a lower level way"

Why, then, is there such a prevalence of doing the data access bit in the lowest level way possible? A way that leads to obscure bugs, no strong typing, security issues and injection attacks abound? A way that effectively embeds a whole other foreign programming language directly in the app being created? Why do beginners still write SQL?

And then the other question from the title:

There usually appears on such questions a litany of comments and answers to the effect of "use parameterized SQL" and I've always wondered why the pro's aren't going one step further, and recommending to use some higher level framework for these simplest of tasks (select * from person where name = 'smith'..) that must surely make up 99% of all queries run

True, an abstraction layer doesn't always cut it (sometimes C++ is just too darn slow and the game engine writer starts optimising in assembler..) but by and large, we don't find questions every day on SO where a block of (e.g. C++) is interspersed with (machine code), yet the inclusion of SQL in high level language X is routine. In all those cases where the first comment out of our fingertips is "use parameters!" why are we not routinely advocating use of some higher level abstraction that provides type safety, eliminates SQL injection and so on?

Note: following a few complaints that the original question was poorly worded/too long/lacked focus I've reworked a lot of the original content to focus on what I come to realise was the core question I had. Some very good answers below may still reference the original wording, and I apologise if my edits have left some answers with orphan commentary

closed as off-topic by gnat, Martin Maat, Bart van Ingen Schenau, amon, RubberDuck Dec 31 '17 at 12:38

  • This question does not appear to be about software engineering within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 14
    Speaking as a practitioner, I've certainly seen far more bad ORMs and query-builder libraries (Django ORM, Elixir, Korma, various employer-specific proprietary handcrafted examples) than good ones (SQLAlchemy). Someone has to have a really, really detailed understanding of SQL and a deep understanding of its deployment in nontrivial use cases (where database-specific extensions, performance-sensitive reformulations, &c. are essential) to design a good one; otherwise, you get extensions tacked onto a core that was designed without really grokking the full set of use cases. – Charles Duffy Dec 31 '17 at 6:15
  • 8
    For pretty much the same reason that you write actual code (which here generates and use SQL) instead of using some Wysiwyg-interface to design your program. You need more flexibility than the abstraction layer can provide. – Thorbjørn Ravn Andersen Dec 31 '17 at 7:01
  • 23
    I'm voting to close this "question". It is not a question, it is a rant, stating a half informed opinion and than "asking" the community why (not if) you are right. And it could be a lot shorter too. – Martin Maat Dec 31 '17 at 9:00
  • 6
    I don't consider myself to be a beginner, and I still write SQL :-) – Laiv Dec 31 '17 at 9:20
  • 2
    @MartinMaat Personally, this question seems like it provides a useful starting point for getting the knowledge needed to challenge reflexive "we have 3 web frameworks in this product, why aren't we using a DB framework?"-style management/design decisions. The question could do with being pared down a bit, of course. (although, I'm less sure whether something that necessitates a frame-challenge fits the criteria for a well-asked question - but I still see value in it) – Ethan Kaminski Dec 31 '17 at 10:48
53

Simple.

Because there is no way you would really understand how to use a database if you're only interacting with it through the ORM.

This already happened in practice to many beginner programmers out there. They have never written a single line of SQL, because their favorite ORM does it for them, and, surprise, surprise, they could even write applications that do work. Until they get a task of optimizing an app which is terribly slow when accessing data, and since they have no clue about execution plans or database normalization, they're screwed.

Even worse, by not understanding SQL and relational databases, they won't understand how to use the ORM properly. The most basic mistake of someone who don't get what is happening under the hood: flush the whole table, and only then filter it to find a single element.¹ Why not. It works pretty well when there are a dozen rows in the table.

Moreover, every ORM I used is a leaky abstraction. Take indexes: to have decent performance for anything but tiny tables, you need them. ORMs with code-first approach allow you to define which columns (should I say “properties”) should have indexes. But this simply makes the ORM leak the underlying database it tries to hide in the first place. I mean, when I manipulate sequences, I don't have indexes. In terms of data structures, I have dictionaries and lookups. Indexes? Don't know what is that thing.

I've seen one example where code-first approach resulted in a clear, well-designed database schema. When the team showed their code, it appeared that the code was filled with adjustments: it's not the ORM which figured this great schema, but the people who pushed the ORM to (and beyond) its limits. Same for the queries: 99% of the time, they are ugly to look at, but do their job well. And then, one time out of one hundred, the ORM is doing some weird stuff,² ending with a request which takes minutes instead of milliseconds. If you know your job, you can either give some hints to the ORM so that he finds a better way to do the thing, or just write the SQL query yourself. If you don't...

we've come a million miles from the low level of concatenating HTML together and writing it to a socket

The comparison is unfair. SQL is already an abstraction. For a business app, writing a table row to disk instead of using SQL is the same as, for a web app, manipulating sockets instead of using a web server.

Similarly, ORM over SQL could be compared to using WordPress over plain PHP. There are cases where WordPress makes perfect sense. There are cases where it doesn't.

to send an email in c# 9 out of 10 cats will use System.Net.Mail; there's probably one oddball who just loves writing an SMTP conversation into a socket, of course

When you need to send an e-mail, you'll use a library provided with your favorite framework. But what would happen if you need to send, say, a few millions of e-mails in a very short amount of time? Exactly, you need to know lots of things about protocols, spam filters, etc. Chances are, as an ordinary developer, you don't know all this stuff, and you'll delegate the task to a specialized company.

With databases, things are different. If you delegate your work to an external company or a consultant every time you need to do something you don't know how to do with something more than the basic ORM skills, you're in a big trouble, because for nearly every project which grows over time, you'll need database skills, and you'll need them repeatedly. E-mails, that's one thing; but if my app uses a relational database, I'd better have at least a basic understanding of SQL and databases.


¹ It happens a lot in .NET world. The developer starts by writing something like that: Products.Single(...). At runtime, Entity Framework complains that it cannot deal with whatever it is within the (...), which happens for anything but the most basic .NET methods which were transcribed to SQL within EF. Either the developer knows what happens under the hood and tries to change the predicate; if not, he does that: Products.ToList().Single(...).

² A general term for that is “object-relational impedance mismatch.” Humans do tend to cope quite well with it when they know SQL well enough; ORMs—not so well.

  • Confession : I'm the oddball. The last par is extremely important and should probably be the first par. For most modern companies, Data IS the company, is core business by definition and outsourcing it is like crossing the streams in Ghostbusters. – WOPR Dec 31 '17 at 6:35
  • I agree with the point, but can the "if you don't get the low level way it's done, you'll never use the high level abstraction effectively" not be applied to every area of computing? Are they doing SQL consciously, an active learning exercise? Beginners still seem to be predominantly getting the idea that even their simplest of data retrieval requirements should be done by writing the SQL themselves; what is special about database interaction that keeps it locked thus, when they already use high level abstractions for other tasks like "get a bunch of HTML down the wire to the client browser"? – Caius Jard Dec 31 '17 at 7:11
  • 3
    @CaiusJard: not every abstraction appears to be so leaky as the typical ORM. And even SQL itself is a leaky abstraction. – Doc Brown Dec 31 '17 at 8:54
  • 1
    No! No! No! A thousand times no! When sending e-mail, you DON'T use a library provided by your favorite framework. You instead select several libraries for consideration, compare their feature set, and select the one that fulfills your needs. Perhaps none do, and you have to write your own code, but usually there is something that does. Also, you future-proof your code by accessing the library through a wrapper. The best library in many cases is not part of any framework, being a genuine library instead of part of a framework. – juhist Dec 31 '17 at 17:16
  • 4
    @juhist: when implementing a task like sending email, I would probably check first if the tools and framework I already have in use for the current project fulfill my needs already before I start investing lots of time to pick a new library, which introduces new dependencies, produces new learning efforts and generates additional maintenance headaches. – Doc Brown Jan 4 '18 at 12:30
25

SQL isn't low level, SQL is a powerful and sophisticated language, well suited to the problem of describing data selection and transformation.

Writing SQL by using an ORM is like generating HTML by converting it from Markdown. That would surely be useful in some situations, but it would be a severely suboptimal way to produce HTML for a general web app. Likewise, an ORM can be useful, but its suboptimal when you start needing more sophisticated queries.

In particular, ORMs work when everything you do consists up creating/retrieving/updating/deleting individual rows. However, sometimes you want to do something more complicated. For example, my current project has a query something like the following:

SELECT entityId, sum(score) FROM tokens WHERE topic = 'foobar' AND age < 5 GROUP BY entityId

Can you do that with your ORM? Possibly, but one or more of the following will probably apply:

  1. It won't be typesafe
  2. You'll end up writing something very like SQL (e.g. HQL)
  3. It'll be more verbose than the SQL

In practice, I like using a SQL builder to build the query. But there advantageous and disadvantageous to it. It is not clearly inferior to write SQL directly rather than some sort of ORM/Query builder.

  • Or throw in a window function, a derived table to join to, or a recursive CTE and see how to make an ORM do those things. Like you say, ORMs and such are fine for CRUD and other simple things but incredibly messy and cumbersome for anything else. – mu is too short Dec 31 '17 at 4:54
  • 2
    An ORM gets you pretty close though, ie Tokens.objects.filter(topic="foobar", age__gt=5).annotate(summed_score=Count("score")). Anyway, I think anything becomes 'low level' when there's enough abstraction above it, so in context of database interaction, SQL is about as low level as you can get. – TankorSmash Dec 31 '17 at 5:44
  • Indeed, by low level I meant "as compared to using MVC(.net) to form a webpage" - SQL in a string, with a DataReader and a loop is he conceptual equivalent (to me) of concatenating HTML together in a string and pushing it down a socket. Everyone would think you were barking mad to do that in this day and age of php/asp and a mixed HTML/code mechanism that allows programmers of different skill sets to work side by side on different bits. I'm certain that 99% of developers who have created a dynamic website have no idea how to use a TCP socket to send a stream of bytes to a client – Caius Jard Dec 31 '17 at 6:50
  • 2
    @caiusjard, the difference is that HTML generation is fairly simple in nature - and also hugely (but tolerably) inefficient relative to the useful data it conveys to the end-user. SQL embeds extremely complex functionality in a relatively terse way (it's a 4GL in which the database itself decides how to formulate an imperative query plan), and inefficiency often cannot be tolerated because of the volumes of data involved or responsiveness requirements. – Steve Dec 31 '17 at 11:06
  • 1
    @CaiusJard, we don't build higher level systems for the sake of having higher level systems, they have to give an advantage over using the lower level system directly. Since SQL is already quite powerful, in many cases there simply isn't any advantage to using a higher level system. – Winston Ewert Dec 31 '17 at 19:53
10

Why don't people automatically parameterise SQL queries? Because it's not functionally necessary (in the ideal case), nor is it built into the client-side language by default, but is a programming pattern implemented for security and robustness reasons. Rather like logging, exception handling, or data validation.

There is also the fact of business culture that most working programmers (including brilliant ones) are basically amateurs who are mostly self-taught and self-developed, in a way that neither construction architects nor bricklayers usually are.

Programmers making real-world programs (unless they are in a large and reputable software firm) often have very little prior exposure to accepted solutions and good practices in the relation to the particular problem area they are tackling - even in middling-sized businesses, it is not unusual that a single developer will be asked to analyse and automate tasks that would be carried out manually by tens or hundreds of other trained staff with specialist qualifications and many years of experience.

It is unheard of in construction, or engineering, that someone in their early 20s would be tasked with designing or building from scratch a multi-storey building, or a car engine, or a complex machine with little (or sometimes no) supervision. Including everything from the selection of metals and materials, the physical act of machining and assembling the parts, the wiring of the electronics, and so on.

At the very least, what we would see in an engineering context if this was permitted nowadays, would be a return to the spinning jennies of the Victorian period, which occasionally rip off the arms, legs, and scalps of its users, which are also extremely unergonomic to use even when used properly, and which lack reliability and durability without constant skilled maintenance and tweaking. In the construction context, we would see buildings leaking, subsiding, and falling down (even sometimes before construction was complete, as is common in IT).

So, the main reason why things are not always done properly in software according to recognised good practice, is because it is expensive and time-consuming to develop and reproduce the people who know how to do it properly, and there are not yet laws in place that effectively mandate minimum standards in software quality or design competence.

As for why ORMs are not used or recommended more frequently? It is a relatively immature technology that does not completely abstract away the concerns of interacting with a relational database, does not fully implement the functionality of the SQL language, and imposes an overhead in terms of the correct use of the ORM layer (which requires skill and experience in itself).

For example, most client-side applications are designed as a single-threaded whole with private state in memory - because those are conditions in which it is easiest to reason about program behaviour. Client-side developers are accustomed to that context. They are also accustomed to storing state in a single place, where updates that are written are automatically propagated to subsequent readers (those readers being different objects in the same, single-threaded program). That is, most applications do not cache state which can be read directly at its source, because reading RAM on the local machine is fast - pointers between objects can be dereferenced on-demand, and repeatedly.

Databases on the other hand are designed fundamentally for concurrent access, by multiple programs that are not necessarily designed and written by the same people (and almost never designed at the same time). They are also slow to access and query, compared to data stored in RAM, which means the data tends to have to be cached - you don't query the database a million times in a loop, for a value that can be queried once and stored locally, whereas you do do that with an in-memory object (you wouldn't normally disassemble the properties of an object into stack variables).

Updates on the server side (caused either by the same user, or a different one) are also not propagated to the client's caches automatically, and certainly not instantaneously (as they effectively are when a single-threaded application updates it's internal in-memory state - those updates are visible to all subsequent parts of the same program that use that state).

Therefore, the similarities that an ORM encourages a developer to perceive between accessing the database and accessing in-memory objects (with a sprinkling of SaveChanges() after every update of the object), actually conceal the fundamental and important differences between ordinary objects that are instantiated on the client machine, and these ORM objects that are wrappers around calls to, and local caches of state held within, an external database.

And while the ORM objects remain uncommitted, they are possibly being used in both roles without differentiation - both to store client-side state in-memory (the results of calculations or user manipulation upon previously-queried data), and as a memorandum of changes that need to be submitted to the database.

Since one has to be concerned with the intricacies of using the database, has to be familiar with how to design one effectively, and has to be reasonably familiar with SQL (or must surely become familiar with it in the course of learning how a relational database works), the actual amount of work that an ORM saves an experienced developer may be relatively minimal (limited to the translation and casting between tuple form and object-oriented form), and to the inexperienced and experienced developer alike the use of automatically-generated ORM objects obfuscates the fundamentally different concerns of the two domains and how they must be treated differently.

  • So, do you think that one day we will see a rise in the use of ORM for the simple stuff, as universities push them more in terms of "in the 99% use case where you want all 5 year old cats from table X, use an ORM. If you want to write the world's most intricate and perform at queries, take the optional database modules beginning with SQL 101". Assembler classes, tcp networking workshops etc were optional for me, but SQL was a core part of the course. When ORMs have matured, do you foresee SQL becoming optional, the preserve of the few nerds who want to be optimising the 1% use cases? – Caius Jard Dec 31 '17 at 6:56
  • 4
    But even for the simple stuff, it's easier just to write the SQL (if you already know any at all) than it is to learn how the ORM works! I have the sneaky suspicion that one of the main use-case of ORMs is where an expert is left to oversee the fundamental design of things, and that expert defines an ORM model to be used by application developers who then (under supervision) need have no knowledge of SQL or database-specific concerns. In other words, it is not designed to eliminate the need for SQL knowledge, it is designed to reduce the number of people on a development team who need it. – Steve Dec 31 '17 at 7:58
  • 2
    Just to add to that same perspective, an ORM that is designed by an expert in both ORM and SQL, would have additional functionality defined (like stored procedures) that would implement any complex, inefficient, or carefully crafted logic in SQL as required. The ORM would be used to pass parameters and retrieve object-oriented results. But again, this only makes sense when there is that division of labour between database expert on the one hand (who is writing the database API behind the ORM), and the application developer on the other who can remain blissfully ignorant of SQL. – Steve Dec 31 '17 at 8:21
  • 1
    Why do find learning and use of SQL so objectionable? I don't mean to sound hostile. You're particularly aggravated by novices writing SELECT statements, but truly, that is how they learn. Why not learn, get an understanding of the power of database queries, then design, and eventually go to a higher level of abstraction if more expeditious or appropriate? As @Steve said, it is just easier to write the SQL already, rather than fuss with learning some ORM's workings. I can't imagine SQL becoming optional at the university level. It just isn't that difficult, and learning is forever useful. – Ellie Kesselman Dec 31 '17 at 13:10
  • @EllieKesselman I don't find it objectionable for developers to learn SQL and I completely agree with Arseni's comments that understanding the low level fundamentals makes for a better high level developer. What I find quite bizarre, however, is the pervasiveness of the pattern of getting data out of a database in a high level app (some framework that generates HTML, like php/mvc) when asked by apparent beginners, is frequently this pattern of "concatenate together an injection prone string, manually create a db connection, execute the sql and use a loop to pull data out of the result reader" – Caius Jard Jan 1 '18 at 12:03
9

I've always wondered why the pro's aren't going one step further, and recommending to use some reasonable ORM/data access library for this, simplest of tasks...

Some of the most popular ORM's are also the most complicated, temperamental and difficult to fine-tune pieces of software I've ever worked with.

There was a period of time (which I'll call the Ballmer period) when enterprise-grade software was all overblown. While there's still impetus to write such monstrosities, our industry is thankfully moving towards simpler architectures and more easily maintainable software structures.

The sweet spot that I see now is using a micro-orm like Dapper to take care of most of the CRUD heavy-lifting, while custom SQL takes care of the remaining 10 to 20 percent of the work. The result is a finely-tuned, highly-performant, lean and maintainable piece of software that takes a fraction of the time and manpower that the former crop of enterprise-grade solutions requires.

What is the basis for our love for writing SQL into a string, loading it into a command, making a connection object, executing a reader, traipsing brought it pulling out weakly typed/boxed data via a string column name and casting it etc

Nobody really does this anymore except for beginners, or folks tasked with maintaining a legacy system (that's a larger group than you might think).

Why, in 2017, in the middle of Programmer X's lovely Windows Forms app, that he built using the visual designer, (thousands of lines of layout code never touched by a human hand; emails sent without a single mention of socket.Send("MAIL FROM: " + myEmail))...

If you were building a greenfield application today, you probably would not build it using this programming style anymore. Instead, you would be employing some form of markup (XAML or HTML5) and either the Model-View-ViewModel pattern or the Model-View-Controller pattern. While you might employ some form of visual designer (like Blend), it is more likely that you would be building simple scaffolding and leveraging the work of others (including visual designers) for most of the heavy lifting. The whole point of MVC and MVVM is to separate out these visual concerns so that other people better at visual design can handle those details.

So where did data access go wrong?

Like so many things in the "Enterprise" era, it tried to be too clever, tried to be too many things to too many people. The name of the game now is simplicity: small, smart, powerful tools that do one job and do it very well.

As to whether or not people should be writing SQL nowadays, I would suggest to you that it is still an essential skill, and will be for many years to come. Why? Because SQL is a well-worn, proven technology with decades of effort and research behind it. Perhaps that's why Hadoop Hive (an Apache open-source project used for big data map/reduce queries) chose SQL as its data query language of choice.

2

For >95% of uses (e.g. simple select with filters, inserting, deleting, or updating a single row), I believe it is fair to suggest using an ORM. So, if the question is really about why people are not encouraging use of an ORM when it is obviously a reasonable tool for the task, then that seems like a reasonable thing to take issue with. But if the question is asking why someone would EVER write raw SQL, then the answer can be found in the numerous examples stated in other answers, of situations where SQL is the best or only solution.

2

If the problem is just writing SQL and you want to keep writing in just one language, you could use some fluent SQL interface. This allows you to write the queries in your source language and have the correct calls to the database generated for you. Just as an example, jOOQ or speedment can do this for Java, but you could find others.
About ORM, you have the impedance mismatch which brings it's own set of problems.

I think that the most important reason to still use SQL is:
SQL is an ANSI standard (SQL-92, SQL-99, ...).
I'm not aware of other standardized way to access data. So, you'll find easily people who know how to write SQL. Given that, you still need to learn some dialect of SQL to be fully productive.

SQL is a declarative language, you write what you need and not how to get the data. This allow separate optimizations at database-level without changing the queries/program.

SQL is used to write queries on relational databases. Relational databases are based on relational calculus which has a mathematical basis. Most people know how to write an entity-relationship (ER) model as opposed to modeling for some noSQL technology.

Getting away from the initial question...

Most of ACID databases are relational and have a SQL interface. NoSQL databases tend to privilege performance over these 4 guarantees.

-4

I think we should expand the question a bit: Why do we still use SQL-databases so much?

In times of yore, the answer was simple: only relational dbs where fast enough to handle our storage needs and helped to minimize the storage size.

With the dominance of relational dbs, sql was also THE standard, which was learned by millions of people - ignoring it's many shortcomings (Strange syntax, insecure, does not match most sane data models etc.).

Today, these constraints does not longer hold true and the vast majority of apps should simply use a no-sql db like ravendb, mongo, azure / aws. But everybody and his dog think SQL dbs are still the first choice, so it's used -alongside with SQL.

  • 4
    Relational databases are however still king as a general purpose technology in data processing and reporting, and therefore there is a desire amongst most to leverage the knowledge and tools available for it for all general purposes. There are other technologies that may solve certain problems more efficiently, but at the cost of time and effort to learn and apply them alongside, rather than instead of, the general purpose technology. Bear in mind that object-oriented programming patterns (and models) are designed for a completely different problem area than relational storage of data. – Steve Dec 31 '17 at 10:51

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