Common practice for propagating database changes through the SDLC?

Question

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!

In addition to Paul's seach-string, here [my](http://www.google.ru/search?client=safari&rls=en&q=database+versioning&ie=UTF-8&oe=UTF-8&gfe_rd=cr&ei=ipS7U5eBE-u-wAPUyYDoAg), slightly diferent — Lazy Badger, Jul 08 '14 at 06:52
possible duplicate of [Database Schema Versioning Strategies](http://stackoverflow.com/questions/2950344/database-schema-versioning-strategies) — Stew, Jul 08 '14 at 19:26

score 3 · Accepted Answer · edited May 23 '17 at 11:46

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

Here is a blog post that may prove quite informative and useful since it discusses several different methods of revision control of SQL objects with pros and cons to each approach.
Check out this SO Post and answers. It may actually be a dupe of this OP.
Here is another SO post that may help, and here is yet another.
Here is a related post, answers and conversation about the same topic.
Here is an M$DN view of things and another.

I think my question is a duplicate of the one you indicate, though with less precise language. I've voted to close it as a duplicate of that post. Thanks for the very helpful answer! — Stew, Jul 08 '14 at 19:28

Common practice for propagating database changes through the SDLC?

1 Answers1