Foreign data wrappers

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: 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. 1. The wrapper extension KNOWNFDWS names the wrappers worth knowing: Constant | Extension | Reaches | --- | --- | --- | POSTGRES | postgresfdw | another PostgreSQL server | MYSQL | mysqlfdw | MySQL / MariaDB | SQLITE | sqlitefdw | a SQLite file | ORACLE | oraclefdw | Oracle | TDS | tdsfdw | SQL Server / Sybase | ODBC | odbcfdw | anything with an ODBC driver | MONGO | mongofdw | MongoDB | REDIS | redisfdw | Redis | FILE | filefdw | CSV and other files (built in) | S3 | parquets3fdw | Parquet on S3 | CSTORE | cstorefdw | columnar local storage | MULTICORN | multicorn | Python-authored wrappers | Only filefdw and postgresfdw ship with PostgreSQL; the rest have to be installed on the server first. 2. The server Every options value is a string — that is the FDW OPTIONS grammar, so port: '5432', not 5432. 3. The user mapping Which remote credentials a local role uses: The remote password is stored in the PostgreSQL catalogue and appears in the statement — give the remote account the narrowest privileges that do the job. 4. The foreign table 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: except: [...] inverts the filter. 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: 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: What to watch Predicate push-down is not guaranteed. postgresfdw 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 fetchsize on the server options, and consider a materialized view over the foreign table for read-heavy paths. Writes depend on the wrapper. postgresfdw supports them; several read-only wrappers (filefdw, 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. Related reading Multiple databases — separate connections instead Schema objects — schemas, extensions, materialized views PostgreSQL dialect