Historyically, I've been against putting logic inside (MySQL) database since it's a bitch to version and maintain (especially cross-database-user)
Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
Maybe Postgres is a lot better, but for MySQL I'm staying the hell away from it
My short checklist for doing database centric development (postgresql centered):
* Database schema is kept under version control ( essentially all the DDL for a given database is kept in one place and managed as a unit ). You can use `pg_dump -s` to extract the schema from an extant database.
* data types and functions are kept in separate files under version control
* the database is built and filled automatically for tests, for migrations and for upgrades. Shell script, Makefile, Chef; the tool doesn't matter so long as the process is repeatable and automatic.
* data fixtures for testing are kept separate from the code ( own repo usually, since you want to be able to add edge cases and errors to the test database ).
* migrations on the production database are run immediately after a backup.
Nothing too fancy, just basic hygiene. But staying on top of that means you're ready to do things like build your application onto a new set of machines; or make drastic changes to how your data is structured.
There are a number of tools that do much of the scutwork for you ( South for Django, ActiveRecord::Migration for Rails, etc.) but the tools are not a substitute for understanding what your database is doing.
I wholehartedly agree that the schema should be versioned. But the schema dump is imho unreadable. I edit it manually instead, which has its own drawbacks.
Since I referr to the schema a lot, I think it is worth optimizing for readability.
Of course nowadays with easy cloud provisioning and tools like Docker, you can version server configurations, so you don't have to put up with it if you don't want to. But databases, I think are at that same in that you should really understand the one you're working with such that you could replicate your current usage from scratch if you really had to, but honestly the chances of you being called upon to do it are minuscule, and the costs of such are going to be borne by the organization you work for anyway.