18

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 == '127.0.0.1')
    .inner_join(db.ips_x_users.user_id == db.users.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 == db.users.id)
    .filter(db.ips_x_users.ip_addr == '127.0.0.1')
    .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 = users.id)
             WHERE ips_x_users.ip_addr = ip
             ORDER BY last_seen DESC LIMIT 10''', ip='127.0.0.1')

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?

Note to self - think of a name
  • 3,916
  • 1
  • 26
  • 24
  • 2
    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

4 Answers4

10

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.

back2dos
  • 29,980
  • 3
  • 73
  • 114
  • 3
    "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 00:55
  • I always thought that embedding SQL in to C or whatever was just the stupidest thing imaginable. When I had to do something like that, I created a means of defining the relationships between tables and storing that in a database and then using the relationships there to create SQL at run time to talk with the database. My C code was just: "Find this entity using this key", "Save the changes to it". –  Oct 05 '17 at 18:42
9

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.

Bernard
  • 8,859
  • 31
  • 40
  • 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
  • 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 Cave Apr 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 name Apr 15 '11 at 20:02
  • 3
    @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
  • 1
    For the record, I also prefer to use "native" SQL. Most of my projects have never needed to support more than one database, so it was never a problem for me. – Dean Harding Apr 15 '11 at 21:00
  • 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_dev Apr 15 '11 at 21:49
  • @quant_dev: I tend to agree with you, but supporting multiple database platforms should at least be taken into consideration before development begins. – Bernard Apr 18 '11 at 13:58
5

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.

4

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.

m5ba
  • 475
  • 2
  • 9
  • 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