I honestly don't understand all the hate on stored procedures. Tracking changes is trivial, since they're a part of the schema -- use version control. It's quite possible to use them with a properly sharded DB architecture, and still enjoy their performance and security benefits.
As a quick example, how else would you do an upsert (update or create) with a single trip to the database server?
I didn't see the presentation but it looked to me like the hate was really focused on the dev team structure that had DBAs owning all the sprocs. I've seen this on other teams I've worked with and it is indeed a nightmare.
For whatever it's worth, you shouldn't need sprocs for security; in fact, sprocs provide one more way for SQLI to sneak into your code (dynamic queries based on tainted input inside the procedure) --- something that comes up a lot when DBAs are forced to be a first line of defense against attacks.
There's nothing wrong with stored procedures per se, but the architecture where every database call is a stored procedure matched to a use case from a higher tier is error-prone. PL/pgSQL is not a great programming language.
Otherwise, you can choose to write stored procedures in Ruby, Perl, Python, Lua, C, R ...
> There's nothing wrong with stored procedures per se, but the architecture where every database call is a stored procedure matched to a use case from a higher tier is error-prone.
Agreed in general; I prefer to use stored procs tactically. The worst stored proc architecture I've seen is the idea that every table should be wrapped in a stored proc API. It's hideous.
> Tracking changes is trivial, since they're a part of the schema -- use version control.
It's not quite that simple. You can't just "install" the new schema onto an existing database with data like you can by just copying files. You have to write your own set of alter statements to keep things synchronized (sometimes with complicated data migrations to new tables).
It works ok for 1 or 2 databases since you just do it manually - but then you lose many of the benefits of automatic source control.
And if you have a large number of database servers - or many unrelated (client) installations - you need a much more complicated system, and it's far from trivial.
Putting the SQL commands in the code is a lot simpler. There are no security benefits to stored procedures vs bound SQL statements. Perhaps it's a bit faster, but I'm not so convinced, you are trading parsing time for execution time since the stored procedure is now a program instead of a data update command.
Stored procedures have different security semantics than bound sql statements. See the security definer attributes.
Briefly, a stored procedure can run with the calling user's permissions, or the definer's permissions. If you set up a function as a security definer, you can do things with data that protect it from disclosure in a way that you can't in a sql statement. You can do it similarly with views, but they're more of a read only case.
And that right there is why they are generally a bad idea. You can write a condition to check how to save your data or you can write the code necessary to verify a user's email address. Assuming you have time for one task, which is more valuable?
Taking the example further, would you hand write how to verify that email address? Probably not, someone has obviously done that before and you shouldn't reinvent the wheel. Why would you hand write persisting a record to the database then?
I apologize if this comes off as attacking, but stored procedures are one of the greatest time sinks ever created wasting countless dollars that could have been spent fixing real problems. Like this guys, http://news.ycombinator.com/item?id=3067740
As a quick example, how else would you do an upsert (update or create) with a single trip to the database server?