Rails Postgresql Multiple Schemas and the Same Table Name

Using multiple PostgreSQL schemas with Rails models

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

I hope it helps.

Is it OK to specify a schema in `table_name_prefix`?

To address your concerns in reverse order:

• Is putting a schema name in table_name_prefix okay?

There are no problems with this just as long as the names are unique(internal and external).

• Is there a concern of having too many schemas listed in schema_search_path?

The answer is maybe, any non-fully qualified request(asking for a table by name only) will have to search each of the schemas in the order listed in schema_search_path If it is cached in memory there is little penalty; an on-disk search of all schemas will be slow(proportional to its location in the list.)
Be sure to list the most active schemas first.

A fully qualified request should take no longer than the separated database solution.

Assuming all of your calls are fully qualified, this technique should provide the full advantages of connection pooling, when possible.

Remember that connection pooling only minimizes the overhead of the setup and tear-down times of the connections, by taking advantage of "gaps" during communication.

For example:

• You have four clients and three of them are making near constant requests, you will still have four connections to the server, even with pooling.

• The advantage comes when you have four clients each utilizing a quarter of the resources, pooled over a single connection.

The underlying(excluding connection overhead) database utilization will remain the same, whether pooling with a single database or separate connections to separate databases.

The drawback/advantage to combining the databases into a single one is this: it is not possible to move individual databases to another server for load balancing purposes outside of PostgreSQLs methods for load balancing.

Rails with DB2 and multiple schemas

Schemas in DB2 are a very handy way to provide separate namespace to different applications. For example, you can separate all database objects for an application called "recruiting" from say application called "payroll". You can have objects (tables, views, procedures etc.) with the same name reside in multiple schemas and not colide with one another. Having your application set a schema is a handy way for it to say "hey, I am a payroll and I only want to work with my objects". So, what happens when you want to work with objects owned by another application? Well, in traditional procedural programming languages your application code would explicitly specify the schema when referencing an object in another schema or you would just do a SET CURRENT SCHEMA to switch to another schema. The problem with ORMs like ActiveRecord in Ruby on Rails is that you are not supposed to use SQL i.e. you don't have a good "Rails way" to specify schema when referencing an object. You can use find_by_sql and qualify your objects in the SQL statement but this is not what RoR people will consider to be good Rails.
You can fix things on the DB2 side. You can define a view per table in the "foreign" schema but you will have to take care to name the view so that it does not colide with what you already have in your primary schema. And, when you do that, you will undoubtedly create names that are not true Rails names.
Rails people are very proud of the "Rails way". It makes it very easy to create new applications. Rails is really awesome in this space. However, when it comes to integration with what is already out there Rails ... how do we say it ... sucks. I suggest you will have to accept to do things that are not the best examples of the Rails Way if you want to work with existing database structures.

Query different schemas data in a rails application for read+write

As per discussions in comments.

def self.with_schema(schema_name)
    class_name = self.name + schema_name.camelize
    table_name = self.table_name
    if !Object.const_defined?(class_name)
      Object.const_set(
        class_name, Class.new(self) do
          self.table_name = "#{schema_name}.#{table_name}"
        end
      )
    end
    class_name.constantize
  end

Add this to your application record and you can do things like: Data.schema('schema_name').all

Using Postgres Schema with Ruby on Rails

This can be easily implemented with Rails, you will have to override the default table name expected by Rails to point to a specific Schema though:

class YourSchemaRecord < ApplicationRecord
  self.table_name_prefix = 'name_of_your_schema.'
end

class SomeRecord < ApplicationRecord
end

class YourCommonSchemaRecord < ApplicationRecord
  self.table_name_prefix = 'public.'
end

class SomeCommonRecord < YourCommonSchemaRecord
end

About Rails migrations, you can either use plain SQL (my favorite option) or use Rails' built-in method but give the "full path" of the table you want to update the structure:

add_column 'name_of_your_schema.some_records', :some_column, :string

Loop through pattern matching schemas and tables in PostgreSQL

You can create sql's by querying information_schema.table like this:

select 'select <column_list> from '||table_name||' where ... <conditions> ;' from information_schema.tables where table_name='logistics' and table_schema like '%\_finance';

Example: To query a table tbl in a schema having name starting with demo

postgres=#  select table_schema, 'select <column_list> from '||table_name||' where ... <conditions> ;' from information_schema.tables where table_name ='tbl' and table_schema like 'demo\_%';
 table_schema |                        ?column?
--------------+--------------------------------------------------------
 demo_schema  | select <column_list> from tbl where ... <conditions> ;
(1 row)

---

Related Topics