I personally hate stored procedures because they invite bad design. The code for them rarely makes it into source control, and even when it does there are often contextual parts of the schema that aren't included.
If you can do them properly (i.e. source control everything, proper schema update scripts, etc) they can work ok. Just hope you marketing guys don't want to do any A/B testing of various algorithms.
Stored procedures, IMO, are a case where DBAs should be pushing their pain down to the software engineering teams. I think a lot of this delineation will disappear as cross-functional product teams become more integrated across the industry, however.
> I personally hate stored procedures because they invite bad design. The code for them rarely makes it into source control, and even when it does there are often contextual parts of the schema that aren't included.
That's kinda like saying "I personal hate <Language> because it invites bad design. The code for them rarely makes it into source control". I've never worked on a project that used stored procedures where they were not only checked into source control but provided the full necessary context so any "fresh" dev box could grab them and test them. Half the projects I've worked on with stored procedures even had unit tests!
> Stored procedures, IMO, are a case where DBAs should be pushing their pain down to the software engineering teams.
DBAs are just specialized developers or dev-ops. There is no reason stored procedures shouldn't be written by whoever is writing the application itself.
Personally I loved sprocs! I can fine tune my DB access control so it's impossible to do anything but call a set of sprocs, the sprocs has zero dynamic code so no injection of commands are possible, each db user can access different sprocs and I have this nice separation of concerns where my DB layer is simply a dumb, black box to take in input and return output. The same for all of the other layers of the app.
> I've never worked on a project that used stored procedures where they were not only checked into source control but provided the full necessary context so any "fresh" dev box could grab them and test them.
I love stored procedures but I feel the pain of source control management. I hope a system comes around to make this easy, ideally a system where you give your current version number and branch when you are trying to update and it generates a sql update file, which really is just all the patches appended in order.
Ideally it would also allow you to restore to a previous point but anything that isn't purely schema related (like an UPDATE OR INSERT) probably needs to be done by hand.
Would something like the Rails database migration systems work for this (aka a DSL for describing common db transformation tasks with tooling to put you at the right version, rollbacks,etc.)
Where I work we have a big directory with one file for each stored procedure.
On deploy, all the stored procedures will get updated from that directory. Rolling back would consist of deploying an earlier version of the repository with the procedures in it.
Years ago I wrote some custom scripts to do exactly this. Deploy consisted of having all of the sprocs migrate. At the time I also encrypted all of the sprocs because some admins had a hard time not making changes to live system instead of in source control.
I couldn't disagree more. Hand-tuned stored procedures are always more performant (and sometimes even more SQL injection resistant) than anything spit out by ORMs.
It's more minor / maybe just me, but I also like the fact that I can replace logic in a stored procedure without having to push out a whole new software build unless I change the inputs or outputs.
I think of stored procedures as an API, and I check them into source control. Especially with SQL Server and the debugging hooks available to me, there's a good chance that my stored proc will execute much faster than any other interpreted language/DSL/ORM
But I limit the ways in which I use them. Only to enforce rules intended solely for the database, for example, like shadowing an entry or changeset to another table. I also sometimes use them to optimize the retrieval of some cursors where I know from testing that SQL Server's query plan will end up returning faster than a view or (shiver) an app-side aggregation.
In short, like everything else in our world, fellow hackers, there's a time and a place for everything, and it's (in my opinion) irresponsible to say that you know better than someone else with different domain experience than you.
Edit: to my parent post, I'm agreeing with you, btw. Just happened to be a good place to hang my reply.
I don't think it's healthy to have engineers who are actually willing to sneak in invisible and unreviewed changes, and deployment shouldn't be so painful that they're even tempted to do such a reckless thing.
Is that fair? What if the language of an application that the developer must extend has only "garbage" (in your opinion) ORMs? Which ORMs allow me to update an object without querying for it first? And before you say "you shouldn't do that", remember: your domain experience is different from mine. My domain experience includes guaranteed single-concurrent-accessor systems where the service isn't in any danger of racing another use to the data.
Many ORMs will double the DB<->app traffic when a single column of a single row is changed, even if only the primary key of an existing record and the NEW column value are the only things that need to be sent, and the front-end application is the only possible source for BOTH values. Sure, I'd need to check for consistency - is this record the same as when I first queried it? - but a trigger (don't laugh, that'd be a mistake) that locks a row and increments a "row_version" value on update could allow me to optimize the consistency check to a simple "SELECT id, row_version FROM my_table". I'm personally not aware of any ORM that allows for that kind of optimization, though I'm also certainly aware that I'm not an expert in all ORMs and all things related to RDBMSs.
ORMs have their place. Many solve common problems well. Just as many miss some optimizations that may be relevant to one person, but not to you.
There are corner cases in which stored procs don't totally protect the foolish, but they fix 95% of the problem. What "Right" solution are you using that eschews stored procedures completely? [EDIT:] Of course if your use case allows you can just get by with a tiny whitelist. Nice work if you can get it.
No he's right parameterized queries are how you prevent SQL injection (imo, the only correct answer). Doing a stored procedure for an SQL one-liner is just bringing a world of pain.
Sprocs give you finer grained access control though (a sproc for which a user has permissions can perform actions that the user can't do directly) as well as being very quick to patch in a prod emergency (useful in the case where you are using a compiled or otherwise slow to deploy language). I've made many a one-line sproc in my day; the overhead at dev time seems minimal by comparison, but that's just my opinion. I can see the other side as well.
Do you find that all your service endpoints are naturally just a one-liner? I guess tastes differ with respect to schema organization, etc. Sorry for misunderstanding!
That seems sarcastic to the point of ignorance. Don't conflate ORMs with prepared statements. Input to a stored procedure can be sanitized like any other query.
If you can do them properly (i.e. source control everything, proper schema update scripts, etc) they can work ok. Just hope you marketing guys don't want to do any A/B testing of various algorithms.
Stored procedures, IMO, are a case where DBAs should be pushing their pain down to the software engineering teams. I think a lot of this delineation will disappear as cross-functional product teams become more integrated across the industry, however.