WWWTech

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.