Postgresql FDW with ActiveRecord and Rails

postgresql_fdw_with_activerecord_and_rails.md

Source Article (See for latest changes): https://westonganger.com/posts/using-postgresql-fdw-with-activerecord-and-rails

If you want to do some cross database joins or includes across postgresql databases regardless of local or remote databases you can use Postgres FDW. Its an awesome tool for the job when you want to avoid data replication.

Base Postgresql FDW Model:

class PostgresFdwBase < ApplicationRecord

  ### Article to familiarize with concepts in this models methods - https://thoughtbot.com/blog/postgres-foreign-data-wrapper

  self.abstract_class = true

  # self.primary_key = :id ### would be nice if this could be added here however this must be specified within each model individually

  after_initialize do
    ### OPTIONAL
    @readonly = true ### safeguard to disallow writes by default, as requirements change this may not be desirable
  end

  def self.fdw_db_config
    # base_config = Rails.configuration.database_configuration["my_second_database_#{Rails.env}"]
    # ### OR custom base config, example below expects normal database.yml keys update as required
    # # base_config = { ... }.with

    # base_config = base_config.merge({
    #   fdw_server_name: "global_data_admin_fdw_server", 
    #   local_fdw_schema_name: "global_data_admin_fdw_schema", ### make sure to use a seperate schema for fdw tables schema otherwise data loss can occur
    #   remote_schema_name: "public",
    # }).merge(Rails.configuration.database_configuration[Rails.env]["global_data_admin"])

    # return base_config.with_indifferent_access

    raise "Override this method in sub-model, Ie. GlobalDataRecord or AuthAppRecord"
  end

  def self.sync_all_fdw_tables!
    config = self.fdw_db_config.with_indifferent_access

    the_sql = <<~SQL
      BEGIN;

      CREATE EXTENSION IF NOT EXISTS postgres_fdw;

      DROP SERVER IF EXISTS #{config.fetch(:fdw_server_name)} CASCADE;

      CREATE SERVER #{config.fetch(:fdw_server_name)}
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '#{config.fetch(:host)}', port '#{config.fetch(:port)}', dbname '#{config.fetch(:database)}');

      CREATE USER MAPPING FOR CURRENT_USER
        SERVER #{config.fetch(:fdw_server_name)}
        #{ config.fetch(:username).presence ? ";" : "OPTIONS (user '#{config.fetch(:username)}', password '#{config.fetch(:password)}');" }

      DROP SCHEMA IF EXISTS #{config.fetch(:local_fdw_schema_name)};

      CREATE SCHEMA #{config.fetch(:local_fdw_schema_name)};

      IMPORT FOREIGN SCHEMA #{config.fetch(:remote_schema_name, "public")}
        FROM SERVER #{config.fetch(:fdw_server_name)}
        INTO #{config.fetch(:local_fdw_schema_name)};

      COMMIT;
    SQL

    self.connection.execute(the_sql)
  end

  ### RESET FDW TABLE FOR JUST THIS MODEL, NOT NEEDED JUST FOR EXAMPLE
  def self.sync_this_fdw_table!
    config = self.fdw_db_config.with_indifferent_access

    the_sql = <<~SQL
      DROP FOREIGN TABLE #{table_name} IF EXISTS;

      IMPORT FOREIGN SCHEMA #{config.fetch(:remote_schema_name, "public")}
        LIMIT TO #{table_name}
        FROM SERVER #{config.fetch(:fdw_server_name)}
        INTO #{config.fetch(:local_fdw_schema_name)};
    SQL

    self.connection.execute(the_sql)
  end

end

Example Second Database Base Model:

class MySecondDatabaseBaseRecord < PostgresFdwBase
  self.abstract_class = true

  def self.fdw_db_config
    ### IF YOU ARE USING RAILS 6+ MULTI-DB config/database.yml
    base_config = Rails.configuration.database_configuration["my_second_database_#{Rails.env}"]
    ### OR custom base config, example below expects normal database.yml keys update as required
    # base_config = { ... }

    base_config = base_config.merge({
      fdw_server_name: "global_data_admin_fdw_server", 
      local_fdw_schema_name: "global_data_admin_fdw_schema", ### make sure to use a seperate schema for fdw tables schema otherwise data loss can occur
      remote_schema_name: "public",
    })

    return base_config.with_indifferent_access
  end

end

Example Model:

class RemotePosts < MySecondDatabaseBaseRecord
  self.primary_key = :id ### must be specified for each model
end

Supporting Code:

# config/database.yml.erb

development:
  schema_search_path: "public,<%= MySecondDatabaseBaseRecord.fdw_db_config[:local_fdw_schema_name] %>"

# config/initializers/postgresql_fdw.rb

if Rails.env.development? || Rails.env.test?
  ### Ensure always in sync when dev or test server starts
  
  MySecondDatabaseBaseRecord.sync_all_fdw_tables!
else
  ### Will be run automatically after db:migrate as per lib/tasks/enhancements.rb
end

# lib/tasks/enhancements.rb

Rake::Task["db:migrate"].enhance do
  ### (ENHANCEMENTS RUN AFTER TASK COMPLETED)

  if !!ENV["SKIP_UPDATE_POSTGRES_FDW_TABLES_AFTER_MIGRATE"]
    # Do nothing, skip
  else
    ### SETUP/RESET ALL FDW TABLE DEFINITIONS HERE
    MySecondDatabaseBaseRecord.sync_all_fdw_tables!
  end
end

Related External Links:

• https://thoughtbot.com/blog/postgres-foreign-data-wrapper