5 ms·
It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that th
by ams6110 10y ago
It's quite trivial to keep stored procedure code in source control with the app code or in its own repo. I am always mystified when I hear the complaint that this is difficult because in my experience it's no more difficult than managing any other code in a SCM repo.
- jjoonathan 10y agoIt's not only easy to manage stored procs separately, it has huge advantages: you can play with your SQL in an IDE built for the task with luxuries like real-time error highlighting, profiling, debugging, and auto-complete, and in some cases you can even iterate on your code without restarting your application (just update the proc).
- brightball 10y agoMy biggest concern has always been around zero downtime deployment with stored procedures. Your database has to work with both the old and new versions of web code during the deploy in case of rollback to make that process work and that always struck me as the weird edge case that makes things tough. If a procedure never changes or remains backward compatible then it should still be fine though.
- platz 10y agoGive the new proc a different name (e.g. append a version number). On the n+1 deployment, clean up the old proc.
- Nullabillity 10y agoAnd so you've reinvented the version control system on top of your deployment system, on top of your version control system. That also sounds fun if you use the proc from more than one location...
- platz 10y agoZero downtime deployment with stored procedures should not be confused with "version control". It's not a version control scheme; once deployed the procs are never updated.
- brightball 10y agoYea, I suppose backwards compatibility is really the best policy there. If a proc is going to be updated to be more efficient it's one thing. If it's going to change what it does then we just need a new proc.
- platz 10y agoYep. (if there are schema, changes too, zero downtime & backward compatability is difficult to pull off, but thats a chore for any strategy )
- greydius 10y agoIn my limited experience with the enterprise world, the problem with using stored procedures was that the devs had no control over the database. We could have come up with some nicer solutions to certain problems, but no one wanted to deal with the bureaucracy necessary to create and maintain parts of our applications within the database.
- nickpeterson 10y agoAlso Java and C# developers often produce bad SQL procedures because they tend to think procedurally about problems.
- Something1234 10y agoWhat would be the better thought process for writing sql procedures? It seems like PL/SQL is fairly procedural.
- nickpeterson 10y agoThe rule of thumb would be, use SQL statements to model and execute business logic, not cursors/variables. While the execution is still procedural, the goal is to make all the decisions 'at once' rather than explicitly looping. Behind the scenes a similar set of steps are taken, but the dbms is free to perform the operations as it sees fit.
- maxxxxx 10y agoI think if they were better integrated with the IDE and easier to debug then they would probably write better stored procedures.
- ZanyProgrammer 10y agoAs a C# dev myself, fuck stored procedures, to be quite honest.
- 10y ago