Using multiple PostgreSQL schemas with Rails models

Question

I have a PostgreSQL database for my Rails application. In the schema named 'public' the main Rails models tables are stored etc. I have created a 'discogs' schema which will have tables with names that are sometimes the same as in the 'public' schema - which is one of the reasons that I'm using schemas to organize this.

How would I setup models from the 'discogs' schema in my app? I will be using Sunspot to let Solr index these models as well. I'm unsure of how you would do this.

Finella

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? – Crony

@Crony There is pg_power gem for that as one option. Its home is here. It hasn't been updated for 2 yrs though. Another option might be to write create rake task in db namespace and add your stuff there. – Calutron

Answer 1

PostgreSQL adapter schema_search_path in database.yml does solve your problem?

development:
  adapter: postgresql
  encoding: utf-8
  database: solidus
  host: 127.0.0.1
  port: 5432
  username: postgres
  password: postgres
  schema_search_path: "discogs,public"

Or, you can to specify different connections for each schema:

public_schema:
  adapter: postgresql
  encoding: utf-8
  database: solidus
  host: 127.0.0.1
  port: 5432
  username: postgres
  password: postgres
  schema_search_path: "public"

discogs_schema:
  adapter: postgresql
  encoding: utf-8
  database: solidus
  host: 127.0.0.1
  port: 5432
  username: postgres
  password: postgres
  schema_search_path: "discogs"

After each connection defined, create two models:

class PublicSchema < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :public_schema
end

class DiscoGsSchema < ActiveRecord::Base
  self.abstract_class = true
  establish_connection :discogs_schema
end

And, all your models inherit from the respective schema:

class MyModelFromPublic < PublicSchema
  set_table_name :my_table_name
end

class MyOtherModelFromDiscoGs < DiscoGsSchema
  set_table_name :disco
end

Rhetorical

Looks like you should add self.abstract_class = true to *Schema classes to avoid non-existing tables troubles. – Steffens

The config schema_search_path: is crucial, if You leave it and use only self.table_name = 'discogs.disco' than all seems to work...except database_cleaner. It uses schema_search_path for to get list of tables from schemas. I ommit this and records keep stocking in ommited schema tables between test runs. – Bedspring

I have tried the same but I'm not able to access the active storage record of DiscoGsSchema Example: User table is present only in PublicSchema and then I'm trying to execute User.first.image and it's still checking in PublicSchema, not in DiscoGsSchema. but however, I'm getting a User record from PublicSchema but not an Active storage image stored in S3. Kindly check the link for a detailed description link – Mock

Answer 2

The correct one for rails 4.2 is as:

class Foo < ActiveRecord::Base
  self.table_name = 'myschema.foo'
end

More info -http://api.rubyonrails.org/classes/ActiveRecord/ModelSchema/ClassMethods.html#method-i-table_name-3D

Prithee

Answer 3

In migrations:

class CreateUsers < ActiveRecord::Migration
  def up
    execute 'CREATE SCHEMA settings'
    create_table 'settings.users' do |t|
      t.string :username
      t.string :email
      t.string :password

      t.timestamps null: false
    end
  end

  def down
    drop_table 'settings.users'
    execute 'DROP SCHEMA settings'
  end

end

Optional in model

class User < ActiveRecord::Base
  self.table_name 'settings.users'
end

Banjo

Answer 4

Just do

class Foo < ActiveRecord::Base
  self.table_name = 'myschema.foo'
end

Rheumatic

Deprecate set_table_name in favour of self.table_name= github.com/rails/rails/commit/0b72a04 – Cropdusting

Answer 5

Because set_table_name was removed, and it was replaced by self.table_name.

I think you should code follow as:

class Foo < ActiveRecord::Base
  self.table_name =  'myschema.foo'
end

Haldes

Answer 6

method set_table_name has been remove. self.table_name works fine.

Sherly

Answer 7

class ApplicationRecord < ActiveRecord::Base
  primary_abstract_class

  # Set schema
  def self.schema(schema)
    self.table_name = "#{schema}.#{self.name.tableize}"
  end
end

class Foo < ApplicationRecord
  schema :myschema
end

Dagny