Foreign Data Wrappers

Foreign Data Wrappers (FDW) let a Postgres or Supabase database query another system as if it were a local table. SQLAnvil’s wrapper() action declares the whole bridge — extension, wrapper, server, and ref()-able foreign tables — in a single call, so you can join data that lives in BigQuery (or any FDW-backed source) with your operational tables and never leave SQL.

Note

The FDW mechanism is core PostgreSQL, so wrapper() works on both the postgres and supabase warehouses. The BigQuery wrapper specifically relies on Supabase’s wrappers extension — available on Supabase, or on self-managed Postgres where you’ve installed it. Credentials use Supabase Vault on supabase.

Named connections (recommended)

The cleanest way to read a foreign source is to define a named connection in workflow_settings.yaml and tag a declaration with it. SQLAnvil generates the FDW bridge for you, and the declared table becomes ref()-able like any other source.

# workflow_settings.yaml (committed)
warehouse: my_supabase
connections:
  my_supabase:
    platform: supabase
    defaultSchema: public
  bigquery_public:
    platform: bigquery
    project: bigquery-public-data      # where the source dataset lives
    dataset: geo_us_boundaries
    billingProject: your-gcp-project    # bills FDW jobs to your project (see below)
    saKeyId: <vault-secret-id>          # non-secret Vault pointer

-- definitions/sources/zip_codes.sqlx
config {
  type: "declaration",
  connection: "bigquery_public",
  name: "zip_codes",
  columnTypes: { zip_code: "text", internal_point_lat: "float8", internal_point_lon: "float8" }
}

Now ${ref("zip_codes")} resolves to a foreign table SQLAnvil created behind the scenes (in a bigquery_public_ext schema), and you join it with your Supabase tables in plain SQL. Connection definitions (non-secret: platform, project, dataset, saKeyId, host/port/db) live in workflow_settings.yaml; the actual secrets stay in the gitignored .df-credentials.json, keyed by connection name. connection: is valid only on declarations — tables and views always build into your one read/write warehouse.

runner-extract mode — materialize instead of a live foreign table (1.15.0)

By default a connection builds a live FDW foreign table (mode: fdw), which needs the wrappers (and often postgis) extensions plus a Vault secret on the warehouse. Set mode: runner-extract on a BigQuery connection to instead have SQLAnvil read the source at run time and materialize the rows into a plain table — ref()-able exactly like the FDW bridge, but with no Vault secret and no wrappers/postgis, so it works on bare or ephemeral databases where an FDW can’t be provisioned:

  bigquery_public:
    platform: bigquery
    project: bigquery-public-data
    dataset: geo_us_boundaries
    billingProject: your-gcp-project
    mode: runner-extract          # read + materialize, no FDW/Vault

Source auth goes in .df-credentials.json under connections.bigquery_public — a short-lived accessToken (keyless), a service-account credentials key, or Application Default Credentials. Keep the default fdw mode when you want a live foreign table on a persistent warehouse; runner-extract is materialize-then-use (a snapshot per run), which is the right default for ephemeral/branch runs.

Datasets and naming (1.22.0): a declaration’s schema: overrides the connection’s dataset, so one connection per source GCP project serves declarations across many datasets — and the extracted table keeps that name as its Postgres schema (schema: "ods", name: "zip_code" materializes ods.zip_code), which keeps schema-qualified ref("ods", "zip_code") calls working (e.g. after a Dataform migration). Declarations without a schema: use the connection’s dataset and land in <connection>_ext as before. Also since 1.22.0, empty columnTypes compiles — the extract fails at run time (before touching or billing the source) with the exact sqlanvil introspect command to scaffold it; only FDW-mode declarations still require columnTypes at compile.

Declarations are inert until referenced (1.27.0): an extract only enters the compiled graph when some model actually ref()s its declaration — Dataform semantics, where an unreferenced declaration does nothing. So you can safely pre-declare a source system’s whole catalog (even tables that don’t exist in the source yet) and pull each one over only when a model starts reading it; a full sqlanvil run never materializes — or bills — a source no model consumes. A referenced declaration whose source table is missing fails at run time, exactly like hand-written SQL against a missing table. Selective runs scope the same way as everything else: run --actions my_model alone reads the previously-extracted tables, and --include-deps re-extracts the sources that model consumes.

MySQL/MariaDB sources (1.18.0)

A platform: mysql connection makes a MySQL or MariaDB database a read-only source for your Postgres/Supabase warehouse. There is no Postgres FDW for MySQL, so MySQL sources are runner-extract only — it’s the default (you can omit mode:), and an explicit mode: fdw is a compile-time error:

  shop_mysql:
    platform: mysql
    host: mysql.internal.example.com
    port: 3306                    # optional, default 3306
    database: shop                # source database (a declaration's `schema:` overrides it)

Source credentials go in .df-credentials.json under connections.shop_mysql: { "host": "…", "port": 3306, "user": "…", "password": "…", "sslMode": "require" }. Each run reads database.table over the wire (capped at 1M rows / 512 MB, truncation logged) and materializes it as a plain shop_mysql_ext.<name> table. Validated against MySQL 8 and MariaDB 11.

Which non-secret fields a connection needs depends on its platform:

Platform	Fields
bigquery	platform, project, dataset, saKeyId (fdw mode), billingProject (optional), mode (optional: fdw | runner-extract)
postgres / supabase	platform, host, port, database, defaultSchema
mysql	platform, host, port, database (runner-extract only)

billingProject (BigQuery, optional; added in 1.13.0). BigQuery bills query jobs to the FDW server’s project. By default that’s project — fine when you own it. But to read a dataset you can read but not bill — e.g. bigquery-public-data — set billingProject to your own GCP project: SQLAnvil then bills that project and reads the source via a full-FQN subquery. The service account needs bigquery.jobUser on the billing project.

columnTypes is required on a connection-tagged declaration (the FDW foreign table needs column types) — but you rarely hand-write it; see below.

How the bridge maps

When you compile, SQLAnvil turns each named connection into the FDW objects below (it adds them as extra actions alongside your own models). Multiple declarations on the same connection share one server:

You write	SQLAnvil generates
connection bigquery_public	server bigquery_public_srv + schema bigquery_public_ext
declaration zip_codes	foreign table bigquery_public_ext.zip_codes
${ref("zip_codes")}	a query against the live foreign table

Introspecting source schemas

sqlanvil introspect reads a source table’s columns and writes the declaration for you, with columnTypes (and any column descriptions) filled in:

sqlanvil introspect bigquery_public geo_us_boundaries.zip_codes \
  --output definitions/sources/zip_codes.sqlx

It connects directly to the source with that connection’s credentials, maps the source types to your warehouse dialect (BigQuery → Postgres; Postgres → Postgres; MySQL → Postgres since 1.18.0 — e.g. datetime → timestamp, json → jsonb), and prints the declaration (or writes it with --output). It’s a dev-time command — compile and run never touch the network for schema.

Source credentials: build-time vs run-time

A named connection touches credentials at two distinct moments, and they live in different places:

• Run-time — what the FDW server uses to read the source during run.
  • BigQuery sources read a service-account key from Supabase Vault by id (saKeyId) — see Credentials below.
  • Postgres/Supabase sources read user/password from .df-credentials.json under connections.<name>; SQLAnvil injects them into the generated CREATE USER MAPPING at run time. The non-secret host/port/database from workflow_settings.yaml feed the foreign server at compile time.
• Build-time — what sqlanvil introspect uses to read the source schema from your machine. These also live in .df-credentials.json, under a connections map keyed by connection name, alongside (not mixed into) your flat write-warehouse credentials:

{
  "host": "aws-1-us-east-1.pooler.supabase.com",
  "port": 5432,
  "database": "postgres",
  "user": "postgres.<project-ref>",
  "password": "<warehouse-password>",
  "sslMode": "require",
  "defaultSchema": "public",

  "connections": {
    // BigQuery source: the service-account key JSON
    "bigquery_public": { "credentials": { "type": "service_account", "...": "service-account JSON" } }
    // Postgres/Supabase source: { "host", "port", "database", "user", "password", "sslMode" }
  }
}

The connections map is read only by introspect; run reads the flat warehouse credentials and ignores it. This build-time credential (for reading the schema) is distinct from the run-time Vault saKeyId the FDW server uses inside Supabase.

Note

Postgres/Supabase sources require @sqlanvil/core 1.2.0 or newer (the postgres_fdw user mapping + run-time credential injection landed in 1.2.0). BigQuery sources work from 1.1.1. Pin it with sqlanvilCoreVersion in workflow_settings.yaml.

The wrapper() action

For full control (or sources without a connection preset), wrapper() is the lower-level JavaScript-API action that emits the FDW setup directly — put it in a .js file under definitions/:

definitions/sources/bigquery_zip_codes.js

wrapper({
  name: "bq_setup",
  provider: "bigquery",                 // preset: wrappers ext + bigquery handler/validator
  server: "bq_geo_server",
  serverOptions: {
    project_id: "bigquery-public-data",
    dataset_id: "geo_us_boundaries"
  },
  credential: {
    // A Vault secret id — a non-secret pointer. The service-account key JSON
    // is stored in Vault, never in your repo. (See "Credentials" below.)
    saKeyId: sqlanvil.projectConfig.vars.bq_sa_key_id
  },
  foreignTables: [
    {
      name: "zip_codes",                // → ref("zip_codes") works downstream
      schema: "bq_ext",
      options: { table: "zip_codes", location: "US" },
      columns: {
        zip_code: "text",
        internal_point_lat: "float8",
        internal_point_lon: "float8"
      }
    }
  ]
});

This compiles to the full setup, in dependency order:

create extension if not exists "wrappers" cascade;
-- idempotent foreign data wrapper creation
do $$ begin
  if not exists (select 1 from pg_foreign_data_wrapper where fdwname = 'bigquery_wrapper') then
    create foreign data wrapper bigquery_wrapper
      handler big_query_fdw_handler validator big_query_fdw_validator;
  end if;
end $$;
drop server if exists "bq_geo_server" cascade;
create server "bq_geo_server" foreign data wrapper "bigquery_wrapper"
  options (project_id 'bigquery-public-data', dataset_id 'geo_us_boundaries', sa_key_id '<vault-id>');
-- one per foreignTables[] entry:
drop foreign table if exists "bq_ext"."zip_codes";
create foreign table "bq_ext"."zip_codes" ("zip_code" text, "internal_point_lat" float8, "internal_point_lon" float8)
  server "bq_geo_server" options (table 'zip_codes', location 'US');

Referencing foreign tables

Each entry in foreignTables[] becomes a ref()-able target that depends on the server setup, so downstream models consume it like any other source:

-- definitions/staging/stg_zip_codes.sqlx
config { type: "view", schema: "bq_ext" }

SELECT zip_code, internal_point_lat AS lat, internal_point_lon AS lon
FROM ${ref("zip_codes")}

A common pattern is to materialize the foreign data into a real table once, so downstream joins don’t re-hit the remote warehouse on every query:

-- definitions/staging/zip_codes_cache.sqlx
config { type: "table", schema: "public" }

SELECT * FROM ${ref("stg_zip_codes")}

Credentials

wrapper() never handles your service-account key. Store it once in Supabase Vault and reference its id:

-- Run once in the Supabase SQL editor:
select vault.create_secret('<paste service-account JSON>', 'bigquery_sa');
select id from vault.secrets where name = 'bigquery_sa';

Pass the returned id as credential.saKeyId (e.g. via a var so it stays out of committed config).

Config reference

wrapper() config:

Field	Required	Description
name	✓	Name of the server-setup action
provider		Preset that infers the extension + handler/validator (e.g. "bigquery")
wrapper / handler / validator		Explicit FDW for generic Postgres FDWs (required when provider is omitted)
server	✓	Foreign server name
serverOptions		Map of server options (e.g. project_id, dataset_id)
credential.saKeyId		Vault secret id passed to the server as sa_key_id (Supabase)
foreignTables[]		Foreign tables to expose (name, schema, options, columns) — each is ref()-able

Caution

The BigQuery wrapper queries are billed to the GCP project behind your service-account key. Reading bigquery-public-data is free up to the monthly tier, but you still need a billing-enabled project.

Troubleshooting

Symptom	Likely cause	Fix
Unknown connection "X" on declaration "Y" at compile	the declaration’s connection: doesn’t match a key under connections:	Check the name. If it does match, ensure @sqlanvil/core is ≥ 1.1.1 — 1.1.0 dropped connections in the published package.
Declaration "X" on connection "Y" requires columnTypes	a connection-tagged declaration with no columnTypes	Add them, or run sqlanvil introspect <conn> <schema.table> --output <file>.
Reading connection "X" from a bigquery warehouse is not yet supported	your warehouse: is bigquery	The read side must be postgres/supabase — the FDW bridge is a Postgres feature.
Wrapper/extension errors on run	the wrappers extension isn’t enabled on the database	Enable it (Supabase Dashboard → Database → Extensions).

Full example

The supabase_bigquery_mailing_list example builds a proximity mailing list — customers who purchased recently and live within a radius of a target ZIP — by joining Supabase operational data with Google’s public ZIP geo data over a live BigQuery FDW, with PostGIS distance math.