6 ms·

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

by exelius 11y ago

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.

BinaryIdiot 11y ago
> 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.
- BHSPitMonkey 11y ago
  > 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. Did you mean to say the opposite of this?
  - BinaryIdiot 11y ago
    Basically. Who knows what upvotes support my screwed up text versus my actual opinion lol. Can't go back and edit. Oh well.
dsugarman 11y ago
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.
- michaelbuckbee 11y ago
  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.)
  - jeffasinger 11y ago
    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.
    - matwood 11y ago
      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.
- smadge 11y ago
  Does Liquidbase[1] do what you describe? [1] http://www.liquibase.org/ http://www.liquibase.org/
skrowl 11y ago
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.
- politician 11y ago
  > Hand-tuned T-SQL is typically more performant than ... ORMs It's not ORMs vs Stored Procs unless your data access framework is garbage.
  - spdustin 11y ago
    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.
- spdustin 11y ago
  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.
steego 11y ago
TLDR: You don't mind stored-procedures if the database schema is in source control like it should be?
jessaustin 11y ago
I personally hate stored procedures... Ah yes, SQL injection will be with us for many years yet.
- bcoates 11y ago
  If you're using sprocs to fix SQL injection you're Doing It Wrong.
  - jessaustin 11y ago
    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.
    - sokoloff 11y ago
      (Fan of sprocs here, but) Parameterized queries can give you the same level of protection from SQL injection as sprocs.
      - jessaustin 11y ago
        Haha I think we mostly agree but I doubt that's what 'bcoates had in mind...
        bcoates 11y ago
        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.
        jessaustin 11y ago
        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!

by exelius 11y ago

BinaryIdiot 11y ago

> 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.

BHSPitMonkey 11y ago
> 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. Did you mean to say the opposite of this?
- BinaryIdiot 11y ago
  Basically. Who knows what upvotes support my screwed up text versus my actual opinion lol. Can't go back and edit. Oh well.

dsugarman 11y ago

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.

michaelbuckbee 11y ago
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.)
- jeffasinger 11y ago
  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.
  - matwood 11y ago
    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.
smadge 11y ago
Does Liquidbase[1] do what you describe? [1] http://www.liquibase.org/ http://www.liquibase.org/

skrowl 11y ago

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.

politician 11y ago
> Hand-tuned T-SQL is typically more performant than ... ORMs It's not ORMs vs Stored Procs unless your data access framework is garbage.
- spdustin 11y ago
  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.
spdustin 11y ago
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.

steego 11y ago

TLDR: You don't mind stored-procedures if the database schema is in source control like it should be?

jessaustin 11y ago

I personally hate stored procedures... Ah yes, SQL injection will be with us for many years yet.

bcoates 11y ago
If you're using sprocs to fix SQL injection you're Doing It Wrong.
- jessaustin 11y ago
  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.
  - sokoloff 11y ago
    (Fan of sprocs here, but) Parameterized queries can give you the same level of protection from SQL injection as sprocs.
    - jessaustin 11y ago
      Haha I think we mostly agree but I doubt that's what 'bcoates had in mind...
      - bcoates 11y ago
        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.
        jessaustin 11y ago
        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!