MySQL stored procedures or php code?

Question

A general question, without a specific case in mind - is it usually preferred to use MySQL stored procedures over writing a PHP script that performs the same calculations and queries?

What are the benefits of each method?

Answer 1

Point/Counter Point with Jeff Atwoods "Who Needs Stored Procedures, Anyways?" from 2004:

1) Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.

Response: The "S" in "SQL" means "Structured", not "Standardized" - PLSQL and TSQL are both custom extensions of SQL, which also bring ANSI SQL into play because there is very little SQL that is database agnostic. Generally, if you want a query that performs well you can't rely on ANSI SQL.

ORM isn't a silver bullet - because of the database abstraction, most support running native stored procedures/functions in order to get a well performing query. Which is nice, but utterly defeats the purpose of ORM...

I'll never understand why web development, a cobbling of countless technologies (HTML, Javascript/AJAX, Flash...) always segregates SQL as the black sheep of the family. Like all the others, you have to learn it to get something out of it. Must be the instant gratification you get when using the other technologies...

2) Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.

Response: Was there originally a Javascript debugger in Eclipse or Visual Studio? No, they allow plugins in order to get the product out the door & invigorate a previously non-existent market. Most don't have a problem using Firebug outside of Visual Studio/Eclipse, why should SQL debugging be any different?

3) Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?

Response: Your lack of familiarity does not a poor language make. Like you've never had to google for a weird error in your language of choice... At least Oracle & MySQL give you error reference numbers.

4) Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.

Response: SQL is SET based, completely unlike procedural/OO programming. Types are close to objects, but at some point there needs to be a mapping between procedural/OO objects and database entities.

5) Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

Response: This is a Good Thing(tm) - that's how you get Model-View-Controller (MVC), so you can have a front end in any multitude of languages without having to duplicate the logic every time while dealing with each languages' quirks to replicate that logic. Or is it good that the database allows bad data to be added if someone connects directly to the database? Trips back & forth between the application and the database waste time & resources your application will never recoup.

Answer 2

I think Jeff Atwood hit the nail on the head in 2004 regarding stored procs:

Who Needs Stored Procedures, Anyways?

Having used both stored procedures and dynamic SQL extensively I definitely prefer the latter: easier to manage, better encapsulation, no BL in the data access layer, greater flexibility and much more. Virtually every major open-source PHP project uses dynamic SQL over stored procs (see: Drupal, Wordpress, Magento and many more).

This conversation almost seems archaic: get yourself a good ORM, stop fretting over your data access and start building awesome applications.

Answer 3

For us using stored procedures is absolutely critical. We have a fairly large .net app. To redeploy the entire app can take our users offline for a brief period which simply is not allowed.

However, while the application is running we sometimes have to make minor corrections to our queries. Simple things like adding or removing a NOLOCK, or maybe even change the joins involved. It's almost always for performance reasons. Just today we had a bug caused by an extraneous NOLOCK. 2 minutes to locate the problem, determine solution, and deploy new proc: zero downtime. To do so with queries in code would have caused at least a minor outage potentially pissing off a lot of people.

Another reason is security. With proc's we pass the user id (non-sequential, non-guessable) into each proc call. We validate the user has access to run that function in the web app, and again inside the database itself. This radically raises the barrier for hackers if our web app was compromised. Not only couldn't they run any sql they want, but even to run a proc they would have to have a particular authorization key.. Which would be difficult to acquire. (and no that's not our only defense)

We have our proc's under source control, so that isn't an issue. Also, I don't have to worry about how I name things (certain ORM's hate certain naming schemes) and I don't have to worry about in flight performance. You have to know more than just SQL to properly tune an ORM.. You have to know the ORM's particular behaviors.

Answer 4

Stored procedure 99 times out of 100. If I were topick 1 reason then it would be that if your php web app does all database acces via stored procedures and your the database user only has permision toexecute said stored procedures then you are 100% protected against SQL injection atacks.