I completed a feature request that required a change to our website's database.
What is a standard way of propagating that database change to the testing and production phases of the software development life cycle. Folks at my work suggested we don't have a procedure in place for this, but that we should.
What is a common way of doing this within a version controlled context?
I was thinking of including an instruction to testers/deployers in the README to execute a SQL script contained within the project via psql before attempting to launch the site. But I don't want that script to persist in a descendant branch, right? Or do I? Is the answer that all changes to the database should somehow be centralized?
Part of the problem here may be that I don't know a more precise way to ask this question, so it's hard to Google. Any advice would be very welcome, thanks!
Source/version control and propagation of SQL DDLs is a difficult problem to solve. There are 3rd party tools such as Red Gate's SQL Source Control that help but can get expensive. In my experience different development shops tend to use manual processes developed in-house. This could mean DDL export and saving of scripts to shoe-horn the SQL DDL into whatever revision control system is used. (Git, SVN, CVS etc.) I've also seen some shops simply zip up and store an entire (but empty) database. It's a rather ham-fisted approach but it does work.
The best bet is to use a tool to help you, like the one mentioned above, but exporting and versioning DDL scripts works too. And yes, you do want the DDLs to be centralized in some revision control system. If you use an automated build system you can also include the execution of version controlled DDL scripts against your database(s). Whatever approach you choose the most important thing is to stay consistent.
Some resources to help you get started: (This is my Google search that yielded the links below.)