5 ms·
How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?
by titanomachy 10y ago
How do you manage version-control on stored procedures? Can they be checked in with the rest of the application logic?
- sitharus 10y agoYes, there are tools to do this. It varies by database server and may need custom integration with your deployment system but they definitely exist. The alternative is the old StoredProc_v1, StoredProc_v2 etc in the database and have the application check what's available. This allows you to roll the parts independently. The big advantage with stored procedures is they can benefit from the DBMS caching and applying additional optimisations (depending on the DBMS of course).
- sopooneo 10y agoI myself am wary of stored procedures except in very specific and uncommon circumstances. That said, you could absolutely version control your stored procedures by creating them from within database migration files that are version controlled by default.
- platz 10y agoWhat is the source of the wariness?
- Udik 10y agoDon't know about the parent, but I always felt that stored procedures end up by incorporating a good share of the business logic, extracting it from the main code of the application. This creates a messy situation in which you have your business logic split up between two completely separate and different systems, one of which (the stored procedures) is much harder to read, write, maintain and test. That said, I also deeply hate ORMs, I much prefer to use thinner layer to interface with a database (query builders and row mappers).
- platz 10y agoI also do think putting too much logic in stored procs is a danger, and was/is a scourge for some shops. But was curious if that is the primary objection.
- deleted 10y ago[deleted]
- snuxoll 10y agoI try to avoid anything but mapping or querying data in sprocs, but sometimes they are a better place to put logic that is needed to ensure consistency. Your database will outlive your application, or you will eventually need to integrate data from a third party system - as a result your database engine is always the last line of defense to protect your data, and if that means using a sproc than so be it. I'll admit, I've written some business logic into stored functions in PostgreSQL, but it's mostly a background job to update cached data because it is much faster to do it in the database than pulling a bunch of records down to a service and sending them back up.
- PeCaN 10y agoIt's fat models vs thin models, just with fat database vs thin database. You have to be careful.
- collyw 10y agoI am working on an app where the original developer has followed a "thin models fat controllers philosophy". I have never seen this referred to as a good thing anywhere else (and Django works far better the other way around). Is there any information on why this might be a good idea / benefits to this approach?
- spacecowboy_lon 10y agoThat is normally considered a good thing other wise different applications accessing the same data have to roll there own - do you really want multiple versions of biz logic. eg a large organizations like a bit telco may have multiple applications that update customer records.
- EGreg 10y agoHow about the fact that in mysql you need root access to add / drop stored procedures? So my app's installer requires the regular user to have admin credentials to mysql instead of just creating a database for them? There is hardly any reason to use stored procedures anyway, when your favorite mysql library supports multiquery.
- programmarchy 10y agoThat's the way I've done things in the past -- save stored procs in a file in a project repo, along with deploy and rollback scripts.
- IndianAstronaut 10y agoYes. Data warehousing projects I have worked on have lots of stored procedures in with the code. One way that works is make sure you make a simultaneous tag of your code and stored procedure that works with it. You really want development to run hand in hand with stored procedure development, not as two parallel processes.
- gfody 10y agoIn Microsoft land we have SSDT and stored procedures live in source control right next to the schema and any CLR functions. The whole thing is built, compile-time checked, and deployed. It's all pretty wonderful.
- bigpeopleareold 10y agoI have found Sqitch (http://sqitch.org http://sqitch.org) to be a useful solution.