Create Sequence In Migration Not Reflected In Schema

Question

I have an application that requires a sequence to be present in the database. I have a migration that does the following:

class CreateSequence < ActiveRecord::Migration
  def self.up
    execute "CREATE SEQUENCE sequence"
  end

  def self.down
    execute "DROP SEQUENCE sequence"
  end
end

This does not modify the schema.rb and thus breaks rake db:setup. How can I force the schema to include the sequence?

Note: The sequence exists after running rake db:migrate.

Answer 1

Rails Migrations because they aim toward a schema of tables and fields, instead of a complete database representation including stored procedures, functions, seed data.

When you run rake db:setup, this will create the db, load the schema and then load the seed data.

A few solutions for you to consider:

Choice 1: create your own rake task that does these migrations independent of the Rails Migration up/down. Rails Migrations are just normal classes, and you can make use of them however you like. For example:

rake db:create_sequence

Choice 2: run your specific migration after you load the schema like this:

rake db:setup
rake db:migrate:up VERSION=20080906120000

Choice 3: create your sequence as seed data, because it's essentially providing data (rather than altering the schema).

db/seeds.rb

Choice 4 and my personal preference: run the migrations up to a known good point, including your sequence, and save that blank database. Change rake db:setup to clone that blank database. This is a bit trickier and it sacrifices some capabilities - having all migrations be reversible, having migrations work on top of multiple database vendors, etc. In my experience these are fine tradeoffs. For example:

rake db:fresh  #=> clones the blank database, which you store in version control

Answer 2

All the above suggestions are good. however, I think I found a better solution. basically in your development.rb put

    config.active_record.schema_format = :sql 

For more info see my answer to this issue - rake test not copying development postgres db with sequences