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?
4
-
1Its very difficult to answer "could it" or "is it possible" questions negatively. Be more direct with what you are asking.– GrandmasterBCommented May 19, 2014 at 20:31
-
that's much better!– GrandmasterBCommented May 19, 2014 at 21:26
-
3You might find this question interesting.– BlrflCommented May 20, 2014 at 13:04
-
possible duplicate of Stored Procedures a bad practice at one of worlds largest IT software consulting firms?– gnatCommented Oct 15, 2014 at 13:11
Add a comment
|
4 Answers
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.
-
1There are very different schools of thought about this. Some folks use a specific database system as strategic platform and use stored procedures for keeping business logic centralized. Others want to avoid a vendor lock and prefer not to use any kind of stored procedures at all. So saying "no business logic in the database, regardless of the system you are building" could trigger a holy war. Commented May 19, 2014 at 20:24
-
1You should also evaluate the possibility of whether the need to update many tables when you insert an object in one table is really some business rule in disguise or something. For example suppose you have a table for audit history or something.– jordanCommented May 19, 2014 at 20:31
-
3Triggers work well until a table gets altered 5 triggers deep through a custom view. When something goes wrong, you'll spend 3 months looking for the problem only to realize a chain reaction of triggers is to blame. It's like a 14 car pile up on the freeway during rush hour. It's hard to tell who's to blame. Commented May 19, 2014 at 20:47
-
2sorry, but your "answer" is completely bogus. There are very good reasons to centralise your business logic in the database, and allow access exclusively through stored procedures and non-updatable views. Safeguarding data integrity comes to mind as the prime reason, and is vitaly important.– jwentingCommented May 20, 2014 at 7:57
-
3@JeffO yes, and that means nothing. The answer is still invalid, as it claims that using stored procedures for business logic is universally wrong. It isn't, there are places where it's very valid. And remember there are many desktop applications that use a central database rather than a local one. Of maybe you're too young to have heard of "client-server"?– jwentingCommented May 20, 2014 at 13:11
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.
answered May 19, 2014 at 20:22
-
Thanks for your insight! Would you say that a stored procedure that does only use
UPDATE,INSERT,DELETEorSELECT, but does so on multiple tables, since the db is normalized is an anti-pattern? In other words, are you saying that stored procedures that are purely wrapper functions for native sql are to be avoided? Commented May 19, 2014 at 20:31 -
1For the most part, yes. If the stored procedure just does
UPDATE foo set blah = foobar where something, have an ORM do that. If it is updating more than one table, or manipulates the data, then a stored procedure is a good idea, especially if more than one application needs access to that business logic. Commented May 19, 2014 at 20:44 -
1I would take this a step further and argue that if other systems need to manipulate that data, then all applications should use public services to do that. I do not like splitting application logic between the code and database. Not to say I've never used a procedure, there should just be a very good, well understood reason to do so. Commented May 19, 2014 at 21:03
-
Also a good point, @mklinker. The upside of Service Oriented Architecture is that you don't have to duplicate complex business logic. The downside to Service Oriented Architecture is the increased complexity, the sometimes unclear integration between systems, and greater difficulty in troubleshooting production problems. Especially if the other teams around you are incompetent and refuse to learn the greater system... not that I haven't had direct experience with that... :) Commented May 20, 2014 at 13:20
-
@DougM, in the third paragraph of my answer I agree with you completely. Commented May 20, 2014 at 15:43
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."
-
""But what if I NEED to search on the full name or just the abbreviation and possibly phone number? It could happen."" - It could indeed, and when it does, the end-user will be much happier with the performance of a properly-tuned PROC than a non-SQL-coder-written SELECT that scans millions of rows on an unindexed column. Commented May 27, 2014 at 12:16
-
@RossPatterson - If you can write a properly tuned stored procedure you can write a high performing select statement. I just think building sql statement in code is easier in these cases. You want to search by abbreviation and phone number, why would my select statement bother to contain anything related to full name in this case?– JeffOCommented May 28, 2014 at 18:48
-
I thought your whole point was that database expertise was in short supply. My point is that, if data is important to your app, the database is as critical a component as the code, and that when the database is treated as a first-class citizen, you will have DBAs 24/7 just like you have coders (and hopefully Operations folks). Commented May 29, 2014 at 2:08
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.
