Sqlphobia: The Irrational Fear of SQL
javaworld.com
javaworld.com
First, it's ugly. It's from an era when it was popular to try to make programming languages look like English. Most people have since decided that trying to make code look like English text is a Bad Thing.
Second, it is very common to create SQL queries by splicing together strings in whatever language you're using for the rest of the application. That's a horrible way to write any kind of code. ORM is, I think as much an attempt to avoid that as an attempt to avoid SQL itself. CLSQL provides SQL semantics with Lisp syntax that can be used directly in Lisp programs. I'm not sure what other languages have similar tools, but that seems more like the Right Way to me.
Back when Rails exploded, this is one of the selling point of using Ruby...
Yeah, SQL is bad because it's aesthetically "ugly". SQL is bad because of how some people use it (not using prepared statements, and simply concatenating strings yup is very amateurish).
And somehow this leads to LISP as the savior? Note that the savior is to make SQL be LISP! That clue lends meaning to: "trying to make code look like English text is a Bad Thing". The LISP crowd loves obfuscation and esoteric syntax....it makes the programmer seem smart.
Keep studying, you have lots of good things to learn yet.
When you attempt to make a language more like English, you inadvertently make it's parse tree more complex, therefore making attempts to manipulate that code more difficult. SQL, which is often generated on the fly, is affected by this.
Programmers want to avoid SQL for the same reason they don't want to write their applications in assembler. Assembler doesn't abstract computational abstractions, it abstracts away the CPU microcode. Since we want to write programs in terms of computations and not CPU micro-operations, assembly is tedious and pointless. SQL is the same way; we don't want to write apps in terms of inner joins. We want to write them in terms of the high-level relationships. ORMs allow this.
(Key/value stores are also the wrong abstraction, they don't abstract much except physically writing the bits to disk. "Yay.")
SELECT P.*
FROM blog.Posts P
INNER JOIN ( --postid and count of replies
SELECT P.PostId, COUNT(UserReplies.PostId) NumReplies
FROM blog.Posts P
INNER JOIN ( --Replies with users karma more than 42
SELECT R.PostId
FROM blog.Replies R
INNER JOIN blog.Users U ON R.UserId = U.UserId
WHERE U.Karma > 42
) UserReplies ON P.PostId = UserReplies.PostId
GROUP BY P.PostId
) PostReplies ON P.PostId = PostReplies.PostId
WHERE PostReplies.NumReplies > 8
My take is, most people fall into the trap of programming language X is THE language of this application. If an application has a relational database component to it, it'd be best if there was someone who understood SQL and the connection points to the application.
It's really tiresome.
I respectfully disagree.
SQL is not a particularly well thought-out language Uhm, that not a very substantiative argument - care to clarify? Bear in mind that the structure of the language is 100% dictated by the Relational Algebra - form follows function to the letter. And the very constraints that algebra puts on what you can do are the same constraints that allow the system to function based on your intent, so that you don't need to define or in most cases even chose the algorithms. The only wiggle room you have in designing a language based on relational algebra is the choice of tokens and ordering of language constructs (e.g. FROM before SELECT of after). So?
But even if it were, the syntax is still poorly thought out. There's no consistency in SQL; each command has its own grammar. You "SELECT ... FROM" and then "INSERT INTO ...". Command syntax is effectively arranged at random.
Worse, there's no extensibility. Most programming languages, when faced with the problem of manipulating data, make use of functions. Instead of having specific syntax for selecting columns or filtering by a condition, a more sensible approach would be to make them functions that lazily transform tables. Then the user could define their own operations, and we'd no longer need to generate a page of SQL each time we wanted to do something complex.
Next problem; integration with calling languages. SQL is not designed to integrate well with ORMs or other client libraries. We either want a language that's basically little more than a serialized AST, or take an approach similar to CouchDB, and farm out specific calculations to some server or library running our programming language of choice.
Finally; optimization opaqueness. It can often be difficult to predict how the database server will optimize a particular piece of SQL. It might be useful if we could drop down to more primitive, but more predictable, operations when necessary.
In short, I think that if a relational database system were to be designed from scratch by experienced programmers, it would look very different to the SQL databases that are around today.
I spent a lot of time writing SQL and the ordering of SELECT ... FROM doesn't bother me at all. Frankly, I find this argument silly and I do not want to entertain it - when buying offroad SUV I will only compare the color of door handles when all else is equal, which it isn't. The only serious part of it is intellisense problems which admittedly could use some work (this is why LINQ is the other way around - table name is always spelled first).
The other two points are entirely valid. The integration is a real pain. The opaqueness can be mitigated by vendors as well - MSSSQL allows hints to force desired behavior - but that doesn't so much drop down in abstraction level as it bastardizes the existing one.
users where(u => u.login = "foo" and u.passwd = SHA1("secret" + u.salt))
authenticate(login, password) :=
where(u => u.login = login and u.passwd = SHA1(password + u.salt))
users authenticate("foo", "secret")
Because SQL is so limited in what you can abstract, there's very little room to factor out common code. Views, stored procedures and even T-SQL functions just operator on set tables. With a more flexible syntax, one could have third-party libraries containing more complex table transformations.
Personally, I wonder whether we need a separate language for database queries at all. It would be nice if there were a just a library we could use to create our own database server inline, using whatever language we wish.
My guess as to why it isn't possible is that in the database world there is an assumption that all records of the same type lie in one table, i.e. the table is the type. And if you look at it form theoretical relational theory point of view, that is so. Alas in practice we do have things like third-party components and we have to worry about lifetime (e.g. transient table-typed variables).
To be fair Oracle I think has something called "package" which allows writing reusable code. Don't know much about it, but supposedly that's what it does.
Also, MSSQL has notion of Table Type which can be used to declare variables. I actually implemented (a part of) it while I worked there. :)
See, this goes back to my point - people aren't learning their tools and then complain about perceived lack of functionality.
I suggest hitting stackoverflow with this type of questions in the future. That's what I always do when I wonder "can I do X with Y?".
The reason why I don't like SQL much, is not because of SQL itself, but because of its poor fit into the host programming language.
In PL-SQL, Oracle's own programming language, the SQL statements are simply part of the program. That is how it should be.
But in other languages (like Perl), the SQL is like code you need to `eval` at runtime. There is no syntax check until you actually run it. And that makes it cumbersome to work with. Plus, you often have to do optimizations (like to separate `prepare` from the rest of the code) by hand, which only makes it worse.
It's all just very low level.
When programming in a declarative language, you really need to trust the action it takes make as much or more sense than a developer picking the step.
One could also say that the more you know about SQL and RDBMS's, the better you are able to properly use an ORM.
And the point someone else made about putting SQL together as text is very valid. I used the Roguewave C++ libraries for generating SQL a few years back. Whilst it was quite clever, and useful in some circumstances, most of the time is was a real pain to figure out how to express SQL in C++.
The need to be able to express yourself iteratively was a motivator for Microsoft's DryadLINQ - a programming language for distributed computation that has SQL-like constructs integrated into C#, giving you a high-level language with some of the declarative benefits as well as some of the imperative benefits (including more natural ways to express iteration).
But fortunately we have (tail) recursion to fight back in most declarative languages. Which is something that's completely missing from SQL. There should be a way to express recursion in SQL queries in my opinion.
For most ORM advocates, it is not a "fear" of SQL, it is a productivity issue. A good ORM gives you an entity model with inter-object relations, a powerful query language, in-memory object cacheing, and all sorts of other useful things, for free. They can be a huge productivity boost. And for those "what about this" scenarios that are always raised, a good ORM will allow you to easily drop down into calling native storedprocs when that makes sense. (This also applies to the supposed performance issue with ORM's)
I've read most of the anti-orm articles out there and I always get a disingenuous feeling from the author, like they are knowingly not being entirely honest.
A few other issues I've found with SQL: - business rules or perhaps more appropriately, behaviours, when implemented down at the stored procedure level (which in a non-orm environment is exactly the proper thing to do) are largely hidden from the calling source code. So when reading the code, much of what is happening is not obvious unless one examines each SP. When using an ORM, this logic can be brought up into a higher level of your application making it much more visible and accessible.
- in large projects, every time you need a certain subset of data (filtered), or a different set of fields in the resultset, you end up writing yet another stored procedure, likely just for that task. Often, a stored procedure may already exist that would server your needs, but in a large project with multiple developers, how does one know of its existence, and even then, find it? I've found almost without exception that large projects start to degrade into a quite disorganized mess that is very hard to understand for a new maintenance developer. Of course, many of these problems can be avoided with proper organization and discipline, but the fact is in many places, they aren't. I find a good ORM largely prevents the problem in the first place.
employee_array = myCompany.employees
and out pops all the records.
It's easy to understand in the base case. Anything with any real complexity to it is doomed to failure, and then you have to start monkeying around with SQL in your mapper anyway.
I won't start a project with a pre-existing database with ORM, it's too squirrely to get the mapping right. However, if I need some persistence for my app easily, ORM provides a good fit.