Rails migrations for postgreSQL schemas

Question

I'm working on a multi-tenant rails application using PostgreSQL schemas for different clients. Rails migrations don't work with multiple schemas out of the box, so I made the following rake task to migrate all schemas and it seems to work. My question is if others have implemented better and more elegant solutions. I would also be really happy with a good tutorial including rails code examples for PostgreSQL using multiple schemas. So far I have only found a good presentation on the subject http://aac2009.confreaks.com/06-feb-2009-14-30-writing-multi-tenant-applications-in-rails-guy-naor.html and an example of what I'm aiming for tomayko.com/writings/rails-multiple-connections

desc 'Migrates all postgres schemas'
task :schemas do
  # get all schemas
  env = "#{RAILS_ENV}"
  config = YAML::load(File.open('config/database.yml'))
  ActiveRecord::Base.establish_connection(config[env])
  schemas = ActiveRecord::Base.connection.select_values("select * from pg_namespace where nspname != 'information_schema' AND nspname NOT LIKE 'pg%'")
  puts "Migrate schemas: #{schemas.inspect}"
  # migrate each schema
  schemas.each do |schema|
    puts "Migrate schema: #{schema}"
    config = YAML::load(File.open('config/database.yml'))
    config[env]["schema_search_path"] = schema
    ActiveRecord::Base.establish_connection(config[env])
    ActiveRecord::Base.logger = Logger.new(STDOUT)
    ActiveRecord::Migrator.migrate('db/migrate', ENV["VERSION"] ? ENV["VERSION"].to_i : nil)
  end
end

Toussaint

Liquibase does work with schemas as far as i know – Widera

@Janning Liquibase is not a solution that works with the ActiveRecord modle that rails uses. – Perri

Answer 1

I have a schema_utils library which I use and has the following method for handling migrations:

  def self.with_schema(schema_name, &block)
    conn = ActiveRecord::Base.connection
    old_schema_search_path = conn.schema_search_path
    conn.schema_search_path = schema_name
    begin
      yield
    ensure
      conn.schema_search_path = old_schema_search_path
    end
  end

I then use migrations as normal so I can continue to call rake:migrate Now, in your migrations you can use:

...
schemas.each do |schema|
  SchemaUtils.with_schema(schema) do
    #Put migration code here
    #e.g. add_column :xyz, ...
  end
end

Because I tend to be mapping schemas to account codes I do the following:

Account.for_each do |account|
  SchemaUtils.with_schema(account.code) do
    #Put migration code here
  end
end

Witmer

Answer 2

Check the apartment gem that's been built just for that purpose. It's brilliant.

Slovene

Answer 3

I'm not sure if I got the question right but don't you just need to declare a few more environments in your database.yml with different "database" specified in each?

Rett

Schemas in postgres are within a database. I.E one database can have many schemas. – Perri

Answer 4

I wrote pg_migrate because of these scenarios, i.e., situations in which multiple applications share the same database. There is probably a Rails way to handle this (engines?) but I too often have another app that's not Rails that needs the database too... then what?

In this case, the key feature of pg_migrate is it can generate a ruby gem; so it becomes possible to maintain your database schema separately from all the downstream applications, but all can reference it.

In your Rails Gemfile, after you've built the ruby gem using pg_migrate's 'package' command, you can do:

gem 'my_db', gem 'jam_db', :path=> "../my_db/gem_package"

Oporto