Set up Ruby on Rails to Handle Multiple Schemas in a PostgreSQL Database

The default schema for a Postgres database is the 'public' schema.
When using the default schema, the schema does not have to be specified in SQL statements.
However, when there are more than one schema in the database that needs to be handled by Rails, then schema specific coding is required.

How does one create the non 'public' schema when setting up the database through the db:create rake task? Adding the schema to the template1 database is a possibility. But is there a way to do it as part of the normal rails/rake development process?

One way to add new schemas to the database is to modify the template from which the database is created, e.g.


psql> \c template1
psql> create schema globe_data;


The command "\c template1" connects to the default template database 'template1'

It would be nice to have the Rails rake tasks create required schemas without modifying the template database,
one way to do this is by redefining the db:create task:


module RakeDb
   def RakeDb.connection_params
     database = ActiveRecord::Base.configurations[Rails.env]['database']
     username = ActiveRecord::Base.configurations[Rails.env]['username']
     hostname = ActiveRecord::Base.configurations[Rails.env]['host']
     password = ActiveRecord::Base.configurations[Rails.env]['password']
     connection_params = hostname ? "-h #{hostname} " : ""
     connection_params += "-U #{username} -d #{database}"
   end
end

namespace :db do
   task :create do
      %x{ psql #{RakeDb.connection_params} -c 'create schema science' }
   end
end



Using the non-default schema requires the following setup steps.

1. The database.yml file needs to be modified to specify the schema search path, e.g.

schema_search_path: "science,public"

This section describes one approach for using ActiveRecord with more than one PostgreSQL schema in the database.

Setup database.yml to add another connection configuration for the specific schema, setting schema_search_path to the relevant schema,
so if adding a schema named 'deathray', modify database.yml like:


development: &development
   adapter: postgresql
   encoding: utf8
   database: globe_town_development
   username:
   password:
   schema_search_path: "science,public"

development_deathray:
   <<: *development
   schema_search_path: "deathray,public"


Note that the **public** schema must be in the **schema_search_path** for the development_deathray connection configuration,
this is because the Rails postgres adapter uses the public schema to look for meta data about model tables.

Deathray::Base should then be used as the parent class for models in the deathray schema, e.g.


module Deathray
   class Organization < Deathray::Base
   end
end


This approach handles name collisions across schemas,
e.g. given a schools table exists in both the science schema and deathray schema, this
approach will allow the right table to be addressed.


module Deathray
   class Base < ActiveRecord::Base
     self.abstract_class = true
     establish_connection (Rails.env + '_deathray').to_sym
   end
end