Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

And that's without even touching on readability after the fact:

# Exhibit A:  A typical DAL
rows = db(db.ips_x_users.ip_addr == '')
    .inner_join(db.ips_x_users.user_id ==
    .select(order=(db.ips_x_users.last_seen, 'desc'), limit=10)

# Exhibit B:  Another typical DAL
rows = db.ips_x_users
    .join(db.users, on=db.ips_x_users.user_id ==
    .filter(db.ips_x_users.ip_addr == '')
    .select(sort=~db.ips_x_users, limit=10)

# Exhibit C:  A hypothetical DAL based on standard SQL syntax
rows = db('''SELECT * FROM ips_x_users
             INNER JOIN users ON
                 (ips_x_users.user_id =
             WHERE ips_x_users.ip_addr = ip
             ORDER BY last_seen DESC LIMIT 10''', ip='')

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?

share|improve this question
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 Loritsch Apr 15 '11 at 20:06

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.

share|improve this answer
"RDBMSs doesn't go well together with OOP" -- Does everything in software have to be OO? – quant_dev Apr 15 '11 at 21:47
@quant_dev: Nope. But 'abstraction'-layers are inherently at least 'abstraction oriented'. Also the code snippets provided suggest, that we are talking of OO-code. – back2dos Apr 16 '11 at 0:55

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.

share|improve this answer
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 name Apr 15 '11 at 19:29
@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 Cave Apr 15 '11 at 19:33
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 name Apr 15 '11 at 20:02
@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 Harding Apr 15 '11 at 20:59
"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_dev Apr 15 '11 at 21:49

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.

share|improve this answer

Joel had written nice article 10 years ago: Don't Let Architecture Astronauts Scare You

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.

share|improve this answer
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_dev Apr 15 '11 at 21:50
@quant_dev Yes, that is the trap - it's easy to start doing simple things with Hibernate or JPA. – m5ba Apr 15 '11 at 21:57

Your Answer


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.