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
  end
end

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"
  end
end

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"
  end
end

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.