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

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

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

What is the source of the wariness?

Don'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).

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

Should they do that through a single service api instead of directly hitting the database?

Yeah, the proper way to be doing that is via a middleware api that can be maintained and versioned by it's own team, rather than coupling all these applications to a very specific database. If you have 4 apps talking to the same oracle database, and you decide to ditch the license, that's going to be way harder to deal with than if you just had one api being maintained by a team who's job is to know databases.

Midleware is the normal term

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

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

It's fat models vs thin models, just with fat database vs thin database. You have to be careful.

I 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?

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

Yes, 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).

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

I have found Sqitch (http://sqitch.org) to be a useful solution.