Foreign data wrappers

A foreign data wrapper (FDW) makes a table that lives somewhere else — another
PostgreSQL server, a MySQL database, a CSV file, Redis, S3 — queryable as if it
were local. Once a foreign table exists you can SELECT from it, join it
against local tables, and point a Prorm model at it.

This is PostgreSQL-specific. Every other dialect throws:

Foreign Data Wrappers are not supported by SQLite.
This is a PostgreSQL-specific feature.

The methods live on the connection, and on ForeignDataManager if you would
rather hold the API separately.

The four steps

Setting up a foreign table is always the same sequence: install the wrapper,
define the remote server, map a local user onto remote credentials, then declare
the table. Each object depends on the one before it:

1. The wrapper extension

await prorm.createExtension('postgres_fdw', { ifNotExists: true });

KNOWN_FDWS names the wrappers worth knowing:

Constant	Extension	Reaches
`POSTGRES`	`postgres_fdw`	another PostgreSQL server
`MYSQL`	`mysql_fdw`	MySQL / MariaDB
`SQLITE`	`sqlite_fdw`	a SQLite file
`ORACLE`	`oracle_fdw`	Oracle
`TDS`	`tds_fdw`	SQL Server / Sybase
`ODBC`	`odbc_fdw`	anything with an ODBC driver
`MONGO`	`mongo_fdw`	MongoDB
`REDIS`	`redis_fdw`	Redis
`FILE`	`file_fdw`	CSV and other files (built in)
`S3`	`parquet_s3_fdw`	Parquet on S3
`CSTORE`	`cstore_fdw`	columnar local storage
`MULTICORN`	`multicorn`	Python-authored wrappers

Only file_fdw and postgres_fdw ship with PostgreSQL; the rest have to be
installed on the server first.

2. The server

await prorm.createServer('remote_pg', {
  fdw: KNOWN_FDWS.POSTGRES,
  options: { host: 'other.internal', port: '5432', dbname: 'legacy' },
});

Every options value is a string — that is the FDW OPTIONS grammar, so
port: '5432', not 5432.

await prorm.getServers();      // ServerInfo[]: { name, fdw, options }
await prorm.dropServer('remote_pg', { ifExists: true, cascade: true });

3. The user mapping

Which remote credentials a local role uses:

await prorm.createUserMapping({
  serverName: 'remote_pg',
  user: 'CURRENT_USER',                                  // or a role name, or 'PUBLIC'
  options: { user: 'remote_reader', password: process.env.REMOTE_PW! },
});

await prorm.dropUserMapping('remote_pg', 'CURRENT_USER');

[!WARNING]
The remote password is stored in the PostgreSQL catalogue and appears in the
statement text, so a statement log (log_statement = 'ddl', or prorm's own
logging) records it. Give the remote account the narrowest privileges that
do the job, and turn query logging off around this call.

A mapping for PUBLIC covers every local role that has no mapping of its own;
ifNotExists makes the call safe to repeat in a migration:

await prorm.createUserMapping({
  serverName: 'remote_pg',
  user: 'PUBLIC',
  ifNotExists: true,
  options: { user: 'reader', password: process.env.REMOTE_PW! },
});

4. The foreign table

await prorm.createForeignTable('legacy_users', {
  serverName: 'remote_pg',
  columns: {
    id:    { type: 'INTEGER' },
    email: { type: 'VARCHAR(255)', options: "OPTIONS (column_name 'mail')" },
  },
  options: { schema_name: 'public', table_name: 'users' },
});

columns is keyed by local column name; the per-column options string maps
it onto a differently-named remote column. Types are raw SQL strings here, not
DataTypes — the remote type is what it is.

Importing a whole schema

Rather than declaring each table, let PostgreSQL introspect the remote server:

await prorm.importForeignSchema('public', 'remote_pg', {
  localSchema: 'legacy',
  limitTo: ['users', 'orders'],
});

except: [...] inverts the filter, and options are passed to the wrapper:

await prorm.importForeignSchema('public', 'remote_pg', {
  localSchema: 'legacy',
  except: ['audit_log'],
  options: { import_default: 'true' },   // postgres_fdw: keep column defaults
});

localSchema defaults to public — create
the target schema first (prorm.createSchema('legacy')), and prefer a dedicated
one so remote tables cannot collide with local names.

Querying through a model

defineForeignTable() creates the foreign table and returns a model bound to
it, so the rest of your code is ordinary ORM code:

const LegacyUser = await prorm.defineForeignTable('legacy_users', {
  serverName: 'remote_pg',
  columns: { id: { type: 'INTEGER' }, email: { type: 'VARCHAR(255)' } },
  options: { schema_name: 'public', table_name: 'users' },
});

const rows = await LegacyUser.findAll({ where: { email: { [Op.like]: '%@old.example' } } });

The returned model is read-only in practice: create / update / destroy
are not blocked by the ORM, but PostgreSQL foreign tables reject writes unless
the wrapper supports them, so those calls fail at the database. Treat it as a
query surface.

You can also join a foreign table against local tables in one statement, which
is the real reason to use an FDW rather than two connections:

const rows = await prorm.query(`
  SELECT u.id, u.email, o.total
  FROM legacy_users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.created_at >= :since
`, { replacements: { since } });

Reading a CSV file with `file_fdw`

file_fdw ships with PostgreSQL and needs no remote credentials, so there is
no user mapping: a server with no options, then a table whose options name the
file. The path is on the database server's filesystem, not your
application's.

await prorm.createExtension('file_fdw', { ifNotExists: true });
await prorm.createSchema('staging');

await prorm.createServer('csv_files', { fdw: KNOWN_FDWS.FILE, ifNotExists: true });

await prorm.createForeignTable('ext_rates', {
  serverName: 'csv_files',
  schema: 'staging',
  ifNotExists: true,
  columns: {
    currency: { type: 'CHAR(3)' },
    rate: { type: 'NUMERIC(12,6)' },
    as_of: { type: 'DATE' },
  },
  options: { filename: '/var/lib/postgresql/import/rates.csv', format: 'csv', header: 'true' },
});

Every SELECT re-reads the file, so replacing rates.csv on the server changes
what the next query sees. Copy it into a real table when you need a snapshot:

await prorm.query(`
  INSERT INTO rates (currency, rate, as_of)
  SELECT currency, rate, as_of FROM staging.ext_rates
  ON CONFLICT (currency, as_of) DO UPDATE SET rate = excluded.rate
`);

[!IMPORTANT]
Reading server files through file_fdw needs superuser or the
pg_read_server_files role, because the file is read by the server process.
Managed PostgreSQL services usually refuse it; use COPY … FROM STDIN from
the client there.

Changing and removing things

ForeignDataManager holds the same API apart from the connection, and adds the
calls the connection does not have: alterServer, serverExists,
dropForeignTable and alterUserMapping.

import { ForeignDataManager } from 'ts-prorm-orm';

const fdw = new ForeignDataManager(prorm);

if (await fdw.serverExists('remote_pg')) {
  // Point at a replica and fetch in larger pages.
  await fdw.alterServer('remote_pg', {
    setOptions: { host: 'replica.internal' },
    addOptions: { fetch_size: '1000' },
  });
}

ADD fails for an option that is already set and SET for one that is not,
which is why the two are separate keys; dropOptions: ['fetch_size'] renders
OPTIONS (DROP fetch_size). Plain options renders the pairs with no
keyword, which PostgreSQL treats as ADD.

Teardown runs in reverse order of creation. cascade on the server removes its
mappings and tables in one go; without it, drop them first:

await fdw.dropForeignTable('legacy_users', { ifExists: true });
await fdw.dropUserMapping('remote_pg', 'CURRENT_USER', { ifExists: true });
await fdw.dropServer('remote_pg', { ifExists: true });

To change the remote password, alterUserMapping() sets the options you pass
(options), and addOptions / dropOptions add and remove others:

await fdw.alterUserMapping({ serverName: 'remote', user: 'CURRENT_USER', options: { password: "it's-new" } });

Before 4.9.2 it rendered the options without SET, which PostgreSQL reads as
ADD and refuses for an option the mapping already has.

What to watch

Predicate push-down is not guaranteed. postgres_fdw pushes many WHERE
clauses to the remote server, but a function or type it cannot translate means
the whole remote table is fetched and filtered locally. Check with
EXPLAIN — see Query optimization.

A foreign table is a network call. Latency and remote availability are now
part of every query that touches it. Set fetch_size on the server options,
and consider a materialized view over the foreign table for read-heavy paths.

Writes depend on the wrapper. postgres_fdw supports them; several
read-only wrappers (file_fdw, most multicorn wrappers) do not.

No transactions across servers. A local transaction does not make the
remote write atomic with it.

buildOptionsClause(options) is exported if you need to render an OPTIONS
clause yourself for a wrapper Prorm has no helper for.

Multiple databases — separate connections instead

Schema objects — schemas, extensions, materialized views

PostgreSQL dialect

Foreign data wrappers

The four steps#

1. The wrapper extension#

2. The server#

3. The user mapping#

4. The foreign table#

Importing a whole schema#

Querying through a model#

Reading a CSV file with file_fdw#

Changing and removing things#

What to watch#

Related reading#