Database changes versioning

Question

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

Answer 1

Some insight might be gained here in this similar question.

Answer 2

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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