I find myself surprised that so many people resist keeping their code in version control. I think this is less people all the time, but I don’t have good percentages because at this point I think a fair number of people embarassed enough to not answer the questions. However, in quick conversations, I still find people that don’t think a VCS is necessary. I can’t conceive of this for application code, but more and more I think this also makes sense for database code.
I ran across a blog that asks you to find a way to keep your database code in a VCS. While I’m glad that the author provides some ideas on how you might script this yourself in a pinch, I think that’s silly. There are tools to help, whether paid for ones, like SQL Source Control or free utilities. In either case, it makes sense to work with something that has been tested a bit rather than inventing your own. It seems that running scripts is simple and easy. In practice, these tools add value because of their maturity. Some of the mistakes you’ll make with your own software have already been made, and solved, with many of these frameworks and paid for tools. Don’t spend your time maintaining software scripts when you need to be developing other software.
I also think the author makes the process sound a bit simpler and easier than it is. In practice, moving between schema versions in a database is typically a one way process, and rollback scripts are of dubious benefit. A discussion of this post on Hacker News shows some of the shortcomings in trying to version database schemas and easily move between versions. In practice many database schema changes can be rolled forward and back, and the corner cases that are very difficult for tools to solve, aren’t items that occur too often. Really, it’s table changes that are hard, and having a good process helps here, but these are usually one way changes.
However, the reason I think version control is important is that database development is often disconnected between the database and application. Changes might occur in the database long before they are used in code, or they may need to be undone in development, perhaps in a destructive way that isn’t possible in production. The history of changes, and understanding of what needs to be changed, is much easier with a record in a VCS.
These days there are a variety of ways to capture your schema changes and record them. I would encourage you to investigate some method and give it a try. Even building the habit of manually recording changes by all developers will help you to move faster and faster in your development process without worrying about losing track of what’s happening.
The Voice of the DBA Podcast