[Talking-Ruby] Use Foreign Data Wrappers in Rails

Use Foreign Data Wrappers in Rails

Foreign Data Wrappers is a fantastic feature of PostgreSQL that allows you to query against external data sources. The external data source is not just a different Postgres database - it could be anything as long as the appropriate extension is available for that particular data source. You can make it work with MySQL, Redis, MongoDB, and even Kafka, so the flexibility is quite impressive. Nevertheless, let’s focus on Postgres-to-Postgres integration, which is available out of the box.

The idea behind FDWs is quite simple:

• Enabling the extension

• Define an external server

• Define the user mapping of how to access that server

• Create or import foreign tables, which are adapter/proxy-like tables to an external data source.

• Run queries against just yet another table - it will just be a table with some extras compared to a standard one.

Now that we know the basics let’s see how we could use it in a Rails application.

Usages

Imagine that we have some OrderStat model in our current app, and we need some data from the Order model represented by the “orders” table from a different database.

We will need fews migrations to make it work.

Enabling the extension

First, let’s create the extension:

class CreateFdwExtension < ActiveRecord::Migration[6.1]
  def up
    execute "CREATE EXTENSION postgres_fdw;"
  end

  def down
    execute "DROP EXTENSION postgres_fdw;"
  end
end

Define an external server

Next, let’s create a server:

class CreateFdwServer < ActiveRecord::Migration[6.1]
  def up
    execute "CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'name_of_external_db'); "
  end

  def down
    execute "DROP SERVER server_name"
  end
end

Define the user mapping of how to access that server

we will need to provide the user and password to access that foreign DB:

class CreateFdwMapping < ActiveRecord::Migration[6.1]
  def up
    execute "CREATE USER MAPPING FOR CURRENT_USER SERVER name_of_external_db OPTIONS (user '', password '');"
  end

  def down
    execute "DROP USER MAPPING FOR CURRENT_USER SERVER name_of_external_db"
  end
end

Create or import foreign tables

One way of doing this is via CREATE FOREIGN TABLE orders where you provide the exact schema for this table, but this is not efficient for a large number of columns. It’s way more convenient to use IMPORT FOREIGN SCHEMA where you can provide the schema name (unless you went with some custom solution, just use “public” here), name of the table(s), and name of the server, and that’s it! You don’t need to bother with the exact columns and their types and constraints.

class CreateForeignAccountsTable < ActiveRecord::Migration[6.1]
  def up
    execute "IMPORT FOREIGN SCHEMA public LIMIT TO (orders) FROM SERVER server_name INTO public;"

    # Alternatively:
    # execute "CREATE FOREIGN TABLE orders (
    # id integer NOT NULL
    # )
    # SERVER server_name
    # OPTIONS (schema_name 'public', table_name 'orders');
    # "
  end

  def down
    execute "DROP FOREIGN TABLE orders"
  end
end

And that’s it!

You could test it using joins:

# assuming that OrderStat and Order models exist in the app and OrderStat belongs to Order
OrderStat.joins(:order)

And that’s how you join tables from two different databases :). However, to make it fully work in your Rails app, so that you can, for example, execute simple queries like OrderStat.joins(:order).first.order, you might need one adjustment in the Order model with explicitly specifying the primary key, as otherwise, you might get the following error:

ActiveRecord::UnknownPrimaryKey (Unknown primary key for table orders in model Order.)

So here it is:

class Order < ApplicationRecord
  self.primary_key = "id"
end

And that’s it!

Refreshing schema

It’s quite likely that the schema of the “orders” table will change. In such a case, if you need to refresh the schema, just recreate foreign tables.

References

[1] Multiple databases in a single query in your Rails apps - Postgres Foreign Data Wrappers to the rescue - https://karolgalanciak.com/blog/2021/06/27/using-multiple-databases-in-a-single-query-in-your-rails-app-postgres-foreign-data-wrappers-to-the-rescue/

[2] PostgreSQL: Documentation: 13: F.33. postgres_fdw - https://www.postgresql.org/docs/13/postgres-fdw.html

[3] Foreign data wrappers - PostgreSQL wiki - https://wiki.postgresql.org/wiki/Foreign_data_wrappers