PostgreSQL schemas and ruby on rails

Christian Kruse

For a new project I'm working on I wanted to use PostgreSQL schemas for a cleaner database structure. Schemas in PostgreSQL are asically a kind of namespace. You can store data types, tables, function, etc in a schema and names across schemas do not clash.

Just a word before: schemas in Rails are not supported very well. Therefore you cannot use the model generator, you have to write model and migration by yourself. First, create a file db/migrations/0001_create_my_relation.rb. In this file, we write the code to generate the database schema. It needs a class inheriting from ActiveRecord::Migration with the two methods up and down. The class name has to be the camel cased version of the file name, e.g. for 0001_create_my_relation.rb the class name would be CreateMyRelation:

class CreateMyRelation < ActiveRecord::Migration  def up  end  def down  endend

The method up creates the database structure, the method down reverts the changes.

As I said, support for schemas is poor. So we have to create it directly using SQL:

class CreateMyRelation < ActiveRecord::Migration  def up    execute "CREATE SCHEMA myschema"  end  def down    execute "DROP SCHEMA myschema"  endend

For the table generation itself we can use ruby, again:

class CreateMyRelation < ActiveRecord::Migration  def up    execute "CREATE SCHEMA myschema"    create_table 'myschema.my_relations' do |t|      t.string :test_field      t.timestamps    end  end  def down    drop_table 'myschema.my_relations'    execute "DROP SCHEMA myschema"  endend

If we run rails db:migrate, we have a working database migration creating a table myschema.my_relations. Caution: note the quotes around 'myschema.my_relations', using a symbol (:name) does not work in this case since the dot is an operator in Ruby.

Now we have to create the model. This is very easy, just create a file app/models/my_relations.rb containing a class MyRelations inheriting from ActiveRecord::Base:

class MyRelation < ActiveRecord::Base  set_table_name 'myschema.my_relation'end

Note the set_table_name line. We have to set the table name manually because Rails is not able to handle schemas automatically.