Use of Migrations in Ruby on Rails

Question

I would like to confirm that the following analysis is correct:

I am building a web app in RoR. I have a data structure for my postgres db designed (around 70 tables; this design may need changes and additions during development to reflect Rails ways of doing things. EG, I designed some user and role tables - but if it makes sense to use Restful Authentication, I will scrub them and replace with whatever RA requires. ).

I have a shellscript which calls a series of .sql files to populate the empty database with tables and initial data (eg, Towns gets pre-filled with post towns) as well as test data (eg, Companies gets a few dummy companies so I have data to play with).

for example:

CREATE TABLE towns (
  id         integer PRIMARY KEY DEFAULT nextval ('towns_seq'),
  county_id  integer REFERENCES counties ON DELETE RESTRICT ON UPDATE CASCADE,
  country_id integer REFERENCES countries ON DELETE RESTRICT ON UPDATE CASCADE NOT NULL,
  name       text    NOT NULL UNIQUE
);

Proposition 0: Data lasts longer than apps, so I am convinced that I want referential integrity enforced at the DB level as well as validations in my RoR models, despite the lack of DRYNESS.

Proposition 1: If I replace the script and sql files with Migrations, it is currently impossible to tell my Postgres database about the Foreign Key and other constraints I currently set in SQL DDL files within the migration code.

Proposition 2: The touted benefit of migrations is that changes to the schema are versioned along with the RoR model code. But if I keep my scripts and .sql files in railsapp/db, I can version them just as easily.

Proposition 3: Given that migrations lack functionality I want, and provide benefits I can replicate, there is little reason for me to consider using them. So I should --skipmigrations at script/generate model time.

My question: If Proposition 0 is accepted, are Propositions 1,2,3 true or false, and why?

Thanks!

Answer 1

Proposition 1 is mistaken : you can definitely define referential integrity using migrations if only by using direct SQL inside the migration, see this post for more details.

Proposition 2: The touted interest of migrations is to be able to define your database model incrementally while keeping track of what each change added and be able to easily rollback any such change at a later time.

You have to be careful with the order you create/modify things in but you can do it.

One thing to keep in mind : rails is better suited for application-centri design. in the Rails Way(tm) the database is only ever accessed through the application active record layer and exposes data to the outside using webservices

Answer 2

Proposition 1 is false in at least two situations - you can use plugins like foreign_key_migrations to do the following:

def self.up
  create_table :users do |t|
    t.column :department_id, :integer, :references => :departments
  end
end

which creates the appropriate foreign key constraint in your DB.

Of course, you might have other things that you want to do in your DDL, in which case the second situation becomes more compelling: you're not forced to use the Ruby DSL in migrations. Try the execute method, instead:

def self.up
  execute 'YOUR SQL HERE'
end

With that, you can keep the contents of your SQL scripts in migrations, gaining the benefits of the latter (most prominently the down methods, which you didn't address in your original question) and retaining the lower-level control you prefer.

Answer 3

1: You may want to try out this plugin. I didn't try it myself though, but it seems to be able to add foreign key constraints through migrations.

2: The real benefit of migration is the ability to go back and forth in the history of your database. That's not as easy with your .sql files.

3: See if the above-mentioned plugin works for you, then decide :) At any rate, it's not a capital sin if you don't use them!

Answer 4

Since you are using Postgres and may not want to install the foreign_key_migrations plugin, here is what I do when I want to use both migrations and foreign key constraints.

I add a SchemaStatements method to ActiveRecord::SchemaStatements called "add_fk_constraint". This could go in some centralized file, but in the example migration file below, I have just put it inline.

---

module ActiveRecord
  module ConnectionAdapters # :nodoc:
    module SchemaStatements
      # Example call:
      # add_fk_constraint 'orders','advertiser_id','advertisers','id'
      # "If you want add/alter a 'orders' record, then its 'advertiser_id' had
      #   better point to an existing 'advertisers' record with corresponsding 'id'"
      def add_fk_constraint(table_name, referencing_col, referenced_table, referenced_col)
        fk_name = "#{table_name}_#{referencing_col}"
        sql = <<-ENDSQL
          ALTER TABLE #{table_name}
            ADD CONSTRAINT #{fk_name}
            FOREIGN KEY (#{referencing_col}) REFERENCES #{referenced_table} (#{referenced_col})
            ON UPDATE NO ACTION ON DELETE CASCADE; 
          CREATE INDEX fki_#{fk_name} ON #{table_name}(#{referencing_col});
        ENDSQL
        execute sql
      end
    end
  end
end

class AdvertisersOrders < ActiveRecord::Migration
  def self.up
    create_table :advertisers do |t|
      t.column :name,               :string,  :null => false
      t.column :net_id,             :integer, :null => false
      t.column :source_service_id,  :integer, :null => false, :default => 1
      t.column :source_id,          :integer, :null => false 
    end

    create_table :orders do |t|
      t.column :name,               :string,  :null => false
      t.column :advertiser_id,      :integer, :null => false
      t.column :source_id,          :integer, :null => false
    end
    add_fk_constraint 'orders','advertiser_id','advertisers','id'
  end

  def self.down
    drop_table :orders
    drop_table :advertisers
  end
end

---

I hopes this helps someone. It has been very useful to me since I need to load a lot of externally supplied data with SQL "COPY" calls, yet I find the migrations system very convenient.