After being exposed to numerous database abstraction layers, I'm starting to wonder what the point is of every library inventing their own different paradigm to access data. Picking up a new DAL feels like learning a new language all over again, when usually all I want to do is just convince the layer to output an SQL query that I've already written in my head.

What's wrong with standard SQL syntax? It was created for a specific purpose, and it fits that purpose beautifully. Maybe it's just me, but I understand snippet C far more readily than the first two. The renamed keywords and syntax tricks are cute, but IMO, when it comes right down to it, they don't make retrieving rows any easier for the coder.

This probably seemed like a long rant, but there is a real question here. Since every DAL seems to invent a new DSL for queries rather than just parsing tried-and-true SQL, there must either be benefits of using different syntax, or deficiencies in standard SQL syntax that I don't realize are there. Could anyone please point out what I'm overlooking here?

The biggest problem with standard SQL is that there are a number of queries that are database specific. Outer join syntax varies wildly, as does the process of getting a "windowed" query. That's the need for DALs to begin with. Now, if there was a standard variant of SQL used by the DAL that knows how to deal with the idiotsyncrasies of the various SQL vendors, I would welcome that.
–
Berin LoritschApr 15 '11 at 20:06

4 Answers
4

The most fundamental problem of common SQL use is, that SQL queries are strings, that are somehow composed from another language. This is where SQL injections and other vulnerabilities and WTFs come from (your example is quite poorly chosen, because your query doesn't actually have any parameters).

The next problem is actually a corollary: if you just have some SQL written in your code, the compiler can't do anything about it. Errors such as typos in column names will only come up at runtime. This is basically, why you don't want just a string representation of your query in your source code, but something the compiler can statically analyze to prevent 95% of all facepalm-bugs.

And the last problem occurs, when you try to map a relational database to your language semantics and programming model: RDBMSs don't go well together with OOP (or navigational data retrieval). Actually, this is quite a terrible idea combining those two, but it's what all object oriented DAL for SQL databases (i.e. ORMs) are about. But all these abstraction layers are condemned to leak. I think this is basically why there's so many of them: Because you work with them, you see they are flawed, you set out to write a DAL that does it right and ultimately fail.

So while problems one and two suggest to have DALs that get rid of SQL, problem three implies there's no straight forward solution of having one (at least for OOP) and there will thus always be a sea of DALs with different strengths and limitations. In the end, all you can do is carefully choose a few and stick to them.

I think it's exactly the case. I was using abstraction layer in my own applications since I found a patterns and it was easy for me to do this way. But it was my DAL I knew every single line in source code => full control. But I would not suggest to use that framework to anybody outside of my team/projects.

When you use something like that it's important to know how it's implemented, that means you should spend a lot of time learning the library/tool. If you don't have time to learn it - don't use. Even if it looks very easy from the beginning.

Yes, a company I worked for in the past started to use Hibernate enthusiastically. Then they discovered how surprising (in a weird way) the queries generated by the framework could be.
–
quant_devApr 15 '11 at 21:50

I feel like SQL is experiencing the same major change the pointers were 10 years ago. There is an ongoing attempt to eliminate manual work wit SQL and take it to a higher abstraction level. The same happened with pointers and manual memory management many years ago.

Since the work is currently in progress you enjoy seeing many different approaches suggested, tried, abandoned and integrated. I'm sure we will see more of it before some sort of a common approach or an industry standard if you wish manifests itself.

It certainly gives you an edge when you can manipulate data access code at the same level and with the same paradigm you apply when your work with your application code.

In a few words - simplification, agility, rapidness, these are the goals.

You are overlooking the obvious fact that not all database platforms accept the same SQL syntax, so embedding SQL statements throughout your application just won't work for every database platform. If you ever need to support multiple database platforms, you will have to rethink most (if not all) of these SQL statements.

Ah, but the DAL itself parses the SQL. If it's accessing a backend that doesn't use standard SQL syntax, it's free to rewrite the query into a compatible format.
–
Note to self - think of a nameApr 15 '11 at 19:29

3

@Note - But then the DAL would have to have a full SQL parser and it would have to have its own supported dialect that would be different than any particular database. And then it would have all the complexity it currently has of generating an appropriate database-specific SQL statement. The LIMIT keyword in your example, for instance, is valid in MySQL but not in Oracle or SQL Server.
–
Justin CaveApr 15 '11 at 19:33

2

Now we're getting to the meat of the question. What makes a non-standard set of method names and operator overloads superior to a non-standard dialect of SQL? Using SQL would at least give the coder a familiar foundation to begin learning the framework from.
–
Note to self - think of a nameApr 15 '11 at 20:02

2

@Note to self: probably because it's easier to write a fluent-style API than it is to write a SQL parser in some dialect of SQL and then translate it to some other dialect of SQL.
–
Dean HardingApr 15 '11 at 20:59

3

"You are overlooking the obvious fact that not all database platforms accept the same SQL syntax" - yes, but how often do you write code to be run against any database? Usually a DB platform is a significant investment and is not often changed. Also, there significant efficiency gains to be made from optimizing your queries against a known type of database.
–
quant_devApr 15 '11 at 21:49