Direct database manipulation an anti-pattern?

Question

Recently I learned that some teams have moved all their database manipulation to the actual database through the use of stored procedures. I thought that was pretty clever, since the database becomes a black box and any change to how the data is stored and manipulated won't affect application code. Is it accurate to conclude that manipulating the database in application code is an anti-pattern, since it unnecessarily couples application and storage code, or are there circumstances that would make decoupling sql queries from the application a disadvantage?

Answer 1

Using the approach of requiring 100% of the database interaction to be done through stored procedures is actually a bad idea, I would say. The database should be for storing "data" (among the usual CRUD functionality and ACID properties), not for storing procedures to encapsulate the entire database. A few reasons why this is a bad idea include:

• slow tests (assuming you are writing tests)
• possibly harder to test (assuming you are writing tests)
• potentially large effort required if switching DBMSs, or in other words perhaps being tied to a single DBMS vendor
• effort or even possibility of switching to some non-SQL-based vendor or mechanism to store data

However, you should also consider how "likely" or not that the above things may happen during the lifecycle of your project when making your decision.

Answer 2

The answer to this question is "It Depends".

If the stored procedures aren't doing anything fancier than UPDATE, INSERT, DELETE or SELECT with no special logic around them, then using Stored Procedures is an Anti Pattern because most ORMs will do this for you.

If you have half a dozen applications written in several different languages saving data to a particular table, and each application duplicates some business logic around the INSERTs and UPDATEs, then not using stored procedures is an anti pattern.

The general rule I use is Don't Repeat Yourself. If there is common business logic that multiple applications must use, and those applications cannot use shared libraries or using shared libraries becomes a maintenance headache, then by all means use a Stored Procedure.

If the Stored Procedure is not doing anything more than an ORM would do, then a Stored Procedure is like hitting a thumbtack with a sledge hammer. You are creating more work for the Programmer than is necessary.

Answer 3

unnecessarily couples application and storage code

Is this better than coupling your app to a particular database? I think the big question is do I want to couple my developers to the database?

If you're going to work with a database, I think you should understand how the code works, but that doesn't mean the developer has to directly write every single statement. Using the language of choice is hard enough without the additional labor of writing sql. Most programmers would separate the concerns by putting the domain logic (what the app really does) and the storage within the application. There are frameworks that take care of a lot of the "database stuff" for the programmer and can even use the same code and switch from one RDBMS to another. Add a property to a data model and let the ORM create a place in the database.

Stored Procedure Benefits They contain the text for a script. Yes it has been checked by the db to see if it will run (notice I didn't say it works). Like all queries sent to many databases it will try to cache some execution plan for performance (store procedure not required). Table and other object access can be blocked and controlled in the sproc for users/groups. I agree, what a great feature. Why would a programmer want to have to worry about all this stuff in app code. Get it from the database similar to some web service or other api.

Here's why this is a bad idea for many developers. They don't want to write the sql and having an amiable DBA 24/7 is hard to come by. Database development can become a bottle-neck. When a requirement is needed or a bug to be found, the name of the movie would be "Dude! Where's My Code?" It's all in the domain section.

RDBMS Doesn't Like Surprises: which is why some types of coding are not suitable for most database scripting languages If only this worked:

Select @ListOfFields FROM @TheTableOfYourChoice WHERE @TheColumnYouWant = @OnlyParameterValueWhichWouldActuallyWork;

As much as this stored procedure doing all the database stuff sounds so great, there are limitations. Good luck writing the sql statement for the 8 part filter option your client wants. "But what if I NEED to search on the full name or just the abbreviation and possibly phone number? It could happen."

Answer 4

I would say that direct database manipulation is often a bad thing. I don't know if it is an anti-pattern though. I think pattern and anti-pattern are overused buzzwords.

The only disadvantage to decoupling is that a good abstraction layer needs more development time and may be overkill for small applications.

The abstraction layer between an application and a database doesn't need to be stored procedures. It could be "middle-ware." Also known as a library or service that performs database actions for the application.

The good thing about that is that changes don't need to be carefully redone in each application that accesses the database.

For example, say that you had a web browser using a local database to store browsing history and bookmarks. And you can't just add a bookmark with an INSERT because there is a complicated relationship between three different tables. A small library could be used by the browser and by a command line tool to make bookmark changes easy to script instead of complicated and prone to breaking with each update.

(Note that "small library" in my opinion rules out a lot of Java ORMs. I saw one command line tool that took 10 seconds to start up and required access to 12 configuration and JAR files. So that it could send 6 or so INSERT and UPDATE commands. Ridiculous.)

Abstracting database access into a service can also improve performance. There have been many desktop business apps written that talk to a database over a VPN. With a 120 ms delay between each SELECT, UPDATE, INSERT, etc. Sending a single request that performs a set of database commands locally is much faster.