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?