Database changes versioning

What are the recommended strategies to do versioning of database changes to tables, views, functions, stored procedures, etc.?

From stackoverflow Krishna Kumar

• Some insight might be gained here in this similar question.

  From Craig

• Export your schema as a sql script and maintain that script in a version control system.

  From Ed Guiness

• I am not sure what is the best approach, but I always keep an updated SQL script into my SVN repository, so, any version of the app matches with the right SQL structure.

  From Fernando Barrocal

• Keep creation scripts for all of your database objects under source control. Whenever you change a table, trigger, etc., check out that file, make the necessary change, execute the script, and check it back in.

  From JosephStyons

• I like Rails' method.

  It preserves in an executable script (with a version number in the file name) the sql (*) sentences needed to both apply and revert changes to the database. It also adds a table in the database to know which one's the current version.

  Result: You can actually move between versions automatically by having some extra scripts that go and call the update or downgrade parts of each script.

  (*) Note: Rails doesn't actually store sql, but ActiveRecord code, but that's not relevant.

  Example: That's version 10 of the db schema. Calling the up() method will move the db to version 10, calling down() will leave the db in version 9. In this case, by creating a table and dropping it respectively. You can use this same method in any other database/environment. At most you'd have to hack up a few supporting scripts which'll do this for you as rails does.

  $ more 010_programs_users.rb
  class ProgramsUsers < ActiveRecord::Migration
    def self.up
      create_table :programs_users , :id => false do |t|
        t.integer :user_id
        t.integer :program_id
      end
    end
  
    def self.down
      drop_table :programs_users
    end
  end
  

  This does not eliminate the need of having these files under source control, of course.

  From Vinko Vrsalovic

• As Jeff Atwood posted about database control, on his blog.

  Scott Allen just wrote a brilliant five part series on the philosophy and practice of database version control:

  1. Three rules for database work
  2. The Baseline
  3. Change Scripts
  4. Views, Stored Procedures and the Like
  5. Branching and Merging

  Joe Philllips : If you'd also like an explanation of the versioning, visit http://commons.apache.org/releases/versioning.html
  From stukelly