title: Managing database changes
body: <p>I'm starting to move more logic into the database, using triggers, views, functions, CTEs, etc. When plv8/json comes out for postgres, I can see myself putting lots of logic in there.</p>
  
  <p>I'm having problems with the "standard" way of doing database migrations in sequel and activerecord.  Both sequel and activerecord let you put arbitrary sql code into timestamped files. When each file is ran, a schema_versions table is updated with the filename (or timestamp in the filename), which keeps record of which migrations have been applied to the current database.</p>
  
  <p>If a lot of coding is being done at the database level, that means that modifications to existing views, functions, etc follow the below pattern:</p>
  
  <p><strong>Migration 1</strong> defines a function and a view that uses that function.                                                </p>
  
  <pre><code>-- Migration 1
  create function calculate(x int) returns int as $$                              
    return x + 1;                                                                 
  $$ language sql;                                                                
  
  create view foos as (                                                           
    select something, calculate(something) from a_table                           
  );  
  </code></pre>
  
  <p>Requirements change, and I need to change a function type. In <strong>Migration 2</strong> I have to drop all objects that depend on foo, and recreate them by copying their entire body -- even if there weren't any changes in most of the other code!</p>
  
  <pre><code>-- Migration 2                                                              
  
  -- Have to drop all views and functions that depend on the                  
  -- `calculate(int)` function.                                               
  drop view foos;                                                             
  create or replace calculate(x bigint) returns bigint as $$                  
    return x + 1;                                                             
  $$ language sql;                                                            
  
  -- I could do `drop function calculate(int) cascade`,                       
  -- but I might accidentally drop some objects that wouldn't get recreated below.
  
  -- Now I have to recreate foo.                                              
  create view foos as (                                                       
    select something, calculate(something) from a_table                       
  );
  </code></pre>
  
  <p>If I'm building a system based on views and functions and triggers, my migrations would be filled with duplicated code, and it's difficult to find the latest version of the code.  You might say "don't do that!", but for my purposes (e-commerce, shipping, transactions), I'm finding it's a lot easier and faster to have the database ensure the integrity of the data by doing the logic inside the database.  </p>
  
  <p>You can (of course) dump the current database schema (which includes all the code definitions), but I think you lose comments. And you wouldn't generally want to edit a giant file that contains the whole schema. </p>
  
  <p>Any ideas on how to solve this problem? </p>
  
  <p>My best idea is to how the sql code contained in their own canonical files (app/sql/orders/shipping.sql, app/sql/orders/creation.sql, etc).  Everyone develops directly on these. Whenever it's time for a release, then you'd want to make a new migration file, look at all the changed code since the previous release, figure out the dependency chain of the database objects that need to be dropped and recreated, and then copy the sql from the canonical sql files into a new sequel/activerecord migration file.  But it's a pain. :/</p>
  
  <p>Thoughts are very welcome. I hope I explained this well enough, I'm cutting back on my caffeine intake and I'm a little groggy atm.</p>
  
  <p>Oh, I asked a similar question on Stack Overflow: <a href="https://stackoverflow.com/q/9711405/17076">Changing the type of a column used in other views</a> The answer was a function that let me pass in:</p>
  
  <ul>
  <li>sql code to run</li>
  <li>database views to drop and recreate</li>
  </ul>
  
  <p>The function would retrieve the view definition, drop the views, run the sql code, then recreate the view definition (in reverse order of dropping). Perhaps a system of functions like this would help solve the problem of having to copy/paste sql code into the migration files.</p>