Direct database manipulation an anti-pattern

anti-patternssqlstored-procedures

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?

Best Answer

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.

Related Topic