Foreign data wrappers enable PostgreSQL to access remote SQL databases. I use TDS foreign data wrapper (tds_fdw on GitHub) to connect PostgreSQL, serving as the Rails database, to a number of remote Microsoft SQL Server databases.

Requirements

PostgreSQL should be version 9.2 or above, and the FreeTDS library should be installed on the database server. In my environment, the SQL Server database versions were 2012 and above.

Connecting to the remote database

After installing TDS foreign data wrapper, follow these steps to connect to the remote SQL Server.

  1. Create a server that the foreign data wrapper will use to access the remote database:
DROP SERVER IF EXISTS local_name_for_remote_sql_server CASCADE;

CREATE SERVER local_name_for_remote_sql_server
FOREIGN DATA WRAPPER tds_fdw
OPTIONS (servername 'sql.company.com', port '1433', database 'customers');
  1. Map the foreign server to your PostgreSQL user, for example, your Rails user:
CREATE USER MAPPING FOR rails_user          
SERVER local_name_for_remote_sql_server
OPTIONS (username 'logon_username', password 'logon_password')
  1. Create a foreign table. Map the SQL Server data types to their closest PostgreSQL counterparts. Not all fields or attributes from the remote table need to be included.
  CREATE FOREIGN TABLE remote_customer_table (
    "cust_id" varchar(50),
    "sales_id" varchar(50),
    "legacy" int,
    "percent_sales" real,
    "sales_current_year" numeric(12, 2)
  )
  SERVER local_name_for_remote_sql_server
  OPTIONS (table 'customers.major_accounts');
  1. Depending on your PostgreSQL security settings, creation of foreign servers (and therefore foreign tables) might be restricted to the root or postgres user. You will need to grant access to the foreign server directly, or just to a foreign table as shown below:
GRANT ALL PRIVILEGES ON remote_customer_table TO rails_user;

pg_catalog

Head over to the pg_catalog schema of your PostgreSQL database. This schema has information about database extensions, data wrappers, foreign servers and tables.

  • pg_extension contains the installed extensions, and you should find a row for tds_fdw
  • pg_foreign_data_wrapper has an entry for tds_fdw
  • pg_foreign_server and pg_foreign_table have entries for each of the foreign databases and tables you chose to map

The Rails side

Switching from tiny_tds to tds_fdw for my Rails tasks decreased running times from hours to minutes. A drawback is that the code is now written purely in SQL. You might need to write additional tasks to take advantage of Rails features like associations and caching.

You may also want to change your Rails models’ primary and foreign keys to reuse existing table relationships. I plan to discuss this in another post.