Handling migration of database views in a multi-developer environment

Question

Are there any established practises for how the migration of database views can be successfully handled in a multi-developer / multi-branch (VCS) environment?

We've been using a database migration library for all our schema changes, but have run into problems when different developers in different branches of code alter the same view, but their point of origin was the same.

Each developer has their own copy of the database, but as views typically require the whole definition to be specified in the migration, this means that when we come to run the migrations against the staging or production database, whichever view migration gets run last overwrites any changes made in any previous view migrations.

Example:

1. View currently looks like: SELECT 'x'.
2. Developer 1 starts branch A and adds a new column. Their 'up' migration looks like: SELECT 'x', 'y'.
3. Developer 2 starts branch B and adds a new column. Their 'up' migration looks like: SELECT 'x', 'z'.
4. Developer 2 finishes her branch first and runs the migrations. The view now looks like SELECT 'x', 'z'.
5. Developer 1 now finishes his branch and runs the migrations. The view now looks like SELECT 'x', 'y' and developer 2's changes have been lost.

Answer 1

For views, or any database object that can be redefined at any time (e.g. functions), the best practice that I've found is to store the current definition of the function in its own file, e.g., db/views/your_stuff.view.sql; then, whenever a developer wants to change that view, they change that file, then add a boilerplate migration which simply redefines the view from the latest version (I don't know if you're in Rails or not, but the idea here should be pretty clear):

class UpdateYourStuffView < ActiveRecord::Migration
  def up
    execute File.read("#{Rails.root}/db/views/your_stuff.view.sql")
  end

  def down
    # You could expand this to actually track older versions, 
    # but that's generally not worth it.
    raise ActiveRecord::IrreversibleMigration
  end
end

Note that the actual view file should look like:

CREATE OR REPLACE VIEW your_stuff AS (SELECT 'x' FROM foos);

This solves your problem, because the workflow is now:

1. View currently looks like: SELECT 'x' FROM foos.
2. Developer 1 starts branch A and adds a new column. They modify db/views/your_stuff.view.sql to reflect this change; their migration simply runs the new view.
3. Developer 2 starts branch B and adds a new column. They modify the same file, and add a new migration just as above.
4. Developer 2 finishes her branch first and runs the migrations. The view now looks like SELECT 'x', 'z'.
5. Developer 1 now finishes his branch. However, to merge into master, he must resolve the conflict in the view file. Once he does, and runs the migrations, the view now includes all three columns.

Answer 2

If they are working in different code branches, they should be using different databases; and when the branches are merged the differences should be resolved.

That said, I am of the opinion a schema should be treated as it's own "project". You mention multiple developers changing a shared VIEW, when it is no less significant a change than someone changing the signature of a commonly used function in a shared dll.

My answer is to (if it is not too late into development) have standard client code connect under a MySQL user that does not have permission to alter the database anymore than necessary; and have a "migration" application/script/whatever that runs with a connection under a user account with the needed permissions to alter tables, views, procedures, etc...