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.

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? — TsenYing, Apr 04 '12 at 22:40
@TsenYing [There is pg_power gem](http://stackoverflow.com/a/18227887/673826) for that as one option. Its [home is here](https://github.com/TMXCredit/pg_power). 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. — mlt, Oct 03 '16 at 21:49

score 122 · Accepted Answer · edited Dec 26 '22 at 03:35

122

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

edited Dec 26 '22 at 03:35

starball

20,030
7
43
238

answered Jan 12 '12 at 16:50

Felsangom

1,522
1
10
12

11

Looks like you should add self.abstract_class = true to *Schema classes to avoid non-existing tables troubles. – Ivan Velichko Nov 03 '13 at 17:57
1

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. – Foton Mar 25 '20 at 11:04
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](https://stackoverflow.com/questions/74919527/how-to-support-active-storage-in-multi-schema) – praveen ak Jan 09 '23 at 10:40

score 19 · Answer 2 · answered Mar 11 '16 at 16:53

19

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

answered Mar 11 '16 at 16:53

Lalu

732
3
8
20

Jaime Castrillon · Answer 3 · 2016-05-06T18:15:24.087

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

maniek · Answer 4 · 2019-09-05T20:28:07.907

13

Just do

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

edited Sep 05 '19 at 20:28

answered Jan 15 '12 at 15:09

maniek

7,087
2
20
43

Deprecate `set_table_name` in favour of `self.table_name=` https://github.com/rails/rails/commit/0b72a04 – Edgar Ortega Sep 04 '19 at 16:07

score 12 · Answer 5 · edited Mar 30 '18 at 07:09

12

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

edited Mar 30 '18 at 07:09

uzaif

3,511
2
21
33

answered Jan 20 '16 at 02:59

Khanh Pham

2,848
2
28
36

score 5 · Answer 6 · edited Jul 08 '19 at 11:56

5

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

edited Jul 08 '19 at 11:56

dfens

5,413
4
35
50

answered Aug 05 '15 at 10:07

zhulinpinyu

599
7
12

score 1 · Answer 7 · answered Nov 25 '22 at 16:21

1

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

answered Nov 25 '22 at 16:21

Kochka

113
6

Using multiple PostgreSQL schemas with Rails models

7 Answers7

Linked