Why are database features being ignored, and instead reinvented in the middle tier?

What are the top reasons (**apart from "database independence"**) that most IT projects today seem to ignore the wealth of features that exist in modern database engines such as Oracle 11g and SQL Server 2008?

Or, to borrow from the [Helsinki Declaration blog][1] which puts it this way:

> In the past twenty years we observe that the functionality (features) that is available to us inside the DBMS, has exponentially grown. These features enabled us to build database applications. Which is what we all started doing in the booming nineties.
>
> But then at the dawn of the new millennium, something happened. And that something mysteriously made the role of the DBMS inside a database application project diminish to insignificant. (...) As of the new millennium we are pushing all application logic out of the DBMS into middle tier servers. The functionality of stuff implemented outside the DBMS has exploded, and the feature rich DBMS is hardly used for anything but row-storage.


We are talking about stuff like

- Stored procedures used as data APIs (for security and to avoid excessive network traffic)
- Materialized views
- Instead-Of triggers
- Hierarchical queries (connect by)
- Geography (spatial data types)
- Analytics (lead, lag, rollup, cube, etc.)
- Virtual Private Database (VPD)
- Database-level Auditing
- Flashback queries
- XML generation and XSL transformation in database
- HTTP callouts from database
- Background job scheduler

Why are these features not being used? Why are most Java, .NET and PHP developers sticking with the "SELECT * FROM mytable" approach?


[1]:

[To see links please register here]

I guess one reason is the fear of vendor lockin. These DBMS features are not standardized - for example, stored procedures are very DB specific, and if you implemented stuff using stored procedures (instead of, say, web services exposed via a middle tier), then you are forever stuck with the DBMS first chosen, (that is, unless you're willing to spend time/money to re-implement it in another DBMS if you wanted to change DBMS).

I would say the biggest reason is that most people don't know about them. Once someone has figured out a solution to a problem, that becomes the default solution to similar ones. SELECT * FROM table has worked for a lot of people for a long time, so they don't bother looking at new approaches to old problems.

The other reason is that sometimes writing it in code is much easier than using a database. Its the same idea as rolling your own vs. buying an off the shelf component. Using a pre-written feature can solve your problems many times, but every once in a while, you need to do something which is outside of the capabilities of what the pre-written components can perform.

For me, the reason is not only my applications being database agnostic, but a database best preforms the basic CRUD functions. Yeah, databases are highly optimized, and might be able to make an HTTP callout, but why would you do it? A webservice / web application is optimized for HTTP calls, not a database. Just like an application is not designed to connect directly to a datafile and retrieve the data. Can it be done? Yes, but why? That is not what your application EXCELLS at.

I personally feel that everything you mentioned, out side of stored procedures belongs in the application. If you know your architecture is X then take advantage of X's features, hand load off to the DB server when appropriate, etc... If it could be X or Y (or Z), then your application should be agnostic, unless you are trying to create job security by ensuring that you might have to refactor the application :). I think a little bit of laziness, combined with comfortablity might have something to do with it. I know I would rather do it in C# than SQL if I can . . . my C# skills are just better.

> I guess one reason is the fear of vendor lockin.

This doesn't get said all that often, but the benefits of using vendor-specific features need to be weighed against the cost. Mainly the cost of having to rewrite the parts that rely on vendor-specific features for every database you want to support. There is also a performance cost if you implement something in a general purpose way when the vendor provides a better way.

I'll bring up this example: one might find the "lockin" of SQL Server to be more acceptable once one realizes all of the things Analysis Services, Reporting Services, and so on can do for your application. For major commercial database systems, it is not "just" the SQL database engine that needs to be taken into account.

Because developers don't know about SQL. They rely on DDL and DML generated by tools like Hibernate and language level constructs like JPA annotations. Developers don't care if these are horribly inefficient because they are mercifully hidden by normal log levels and because DBAs are not part of development teams.

That's why I like tools [iBATIS][1]. They make you write and understand SQL, including DBMS specific features.


[1]:

[To see links please register here]

SQL is failing for the same reason as e.g. Haskell. The metric that determines language success is not purity, not ease of interpretation by computers, but how hard it is to maintain programs written in it.

Mere mortals fail with even the most simple language. Perhaps 1 in 10 people do have the skills to use a straightforward language like C#. But of those 10%, only 1 in 10 or 1% of all people can effectively use languages like SQL or Haskell.

Now, SQL is incomplete as a language, in the sense that there are very few things you can do with just SQL. You'll always need another language. What role does that leave for SQL? Developers will understand the ACID advantages over flat-file storage, but besides that databases really have nothing to offer them.

A second problem is that SQL effectively is not very compatible with Source Versioning. SQL seems really built along the notion that you get it right the first time. So, it's not just ill-suited for developers, it's also a poor match for the development process.

Nice question, and good discussion.

Another way to put it is "why haven't object DBs caught on?" which is the other side of the coin. DBs continue to be an annoying abstraction that still leaks its way into every app out there, but they're incompatible with the OO logic of modern applications.

It is indeed a strange state of affairs that we hide and duplicate the functionality of DBs in ActiveRecord, Hibernate, and other middlewares. But this is what happens to paradigms at the point of breakage (the "Object-relational impedance mismatch"). Will we ever transition to database technologies that are similar to our OO apps (e.g., object DBs)?

The answer is "not for a long time" and in the meantime, expect the DB to be ignored and squashed down and used for just row storage in many cases, as the middle-tier grows in functionality to bridge the gap.

Another question is "why would I do it in the DB if the middle-tier can do it?" The middle-tier is familiar and gaining ground in speed and functionality all the time. Again, we use the middle tier to avoid the OO-RDMS mismatch.

It's easier to fix/redeploy the middle tier than the DBMS.

This probably depends on your architecture, but it is our reason. Couple that with the fact that we have one DBA who is busier and (probably) is paid more than our developers. All the developers know SQL and some of them are semi-versed in the procedural language. If a really hairy production problem comes up, it would be easier and faster for the developers to work on the middle tier than the database, regardless of whether the architecture would be better one way or the other.

There are not enough developers knowing all those features at a level that would really make the difference to a normal 'middle tier' programmer, when it comes to implementing the same logic into DB or middle tier. Maybe the single people that really have in-depth knowledge of that features are DBAs. And those focus on other problems than development. There are more 'normal' developers out there than DBAs. So it would be very difficult and costly to find the right people for your team.

Another point is, that you will normally only gather the in-depth knowledge about *one* database system, and not all of them. So you can have SQL Server experts or Oracle experts, but not both. This leads (to an extent) to narrow application fields where high specialization counts. Then, the market for such applications isn't that big, even if it's there.