OpenAPI

1
create extension if not exists wrappers with schema extensions;

1
create foreign data wrapper wasm_wrapper
2
  handler wasm_fdw_handler
3
  validator wasm_fdw_validator;

1
-- Save your API key in Vault and retrieve the created `key_id`
2
select vault.create_secret(
3
  'your-api-key',
4
  'my_api',
5
  'API key for My API'
6
);

1
create schema if not exists openapi;

1
create foreign table openapi.users (
2
  id text,
3
  name text,
4
  email text,
5
  created_at timestamptz,
6
  attrs jsonb
7
)
8
server my_api_server
9
options (
10
  endpoint '/users',
11
  rowid_column 'id'
12
);

1
-- Import all endpoints
2
import foreign schema openapi
3
  from server my_api_server
4
  into api;
5
6
-- Import specific endpoints only
7
import foreign schema openapi
8
  limit to ("users", "orders")
9
  from server my_api_server
10
  into api;
11
12
-- Import all except specific endpoints
13
import foreign schema openapi
14
  except ("internal_endpoint")
15
  from server my_api_server
16
  into api;

1
-- Endpoint template with path parameter
2
create foreign table openapi.user_posts (
3
  user_id text,
4
  id text,
5
  title text,
6
  body text,
7
  attrs jsonb
8
)
9
server my_api_server
10
options (
11
  endpoint '/users/{user_id}/posts',
12
  rowid_column 'id'
13
);
14
15
-- Query with path parameter - generates GET /users/123/posts
16
select * from openapi.user_posts where user_id = '123';

1
create foreign table openapi.project_issues (
2
  org text,
3
  repo text,
4
  id text,
5
  title text,
6
  status text,
7
  attrs jsonb
8
)
9
server my_api_server
10
options (
11
  endpoint '/projects/{org}/{repo}/issues',
12
  rowid_column 'id'
13
);
14
15
-- Generates GET /projects/acme/widgets/issues
16
select * from openapi.project_issues where org = 'acme' and repo = 'widgets';

1
-- Generates GET /users/user-123
2
select * from openapi.users where id = 'user-123';

1
-- Generates GET /users?status=active
2
select * from openapi.users where status = 'active';

1
-- Sends GET /users?limit=5 (uses LIMIT as page_size)
2
select * from openapi.users limit 5;

1
create foreign table openapi.search_results (
2
  id text,
3
  title text,
4
  score real,
5
  attrs jsonb
6
)
7
server my_api_server
8
options (
9
  endpoint '/search',
10
  method 'POST',
11
  request_body '{"query": "openapi", "limit": 50}'
12
);
13
14
select id, title, score from openapi.search_results;

1
create server debug_api
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
5
    fdw_package_name 'supabase:openapi-fdw',
6
    fdw_package_version '0.2.0',
7
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
8
    base_url 'https://api.example.com',
9
    debug 'true'
10
  );

1
create server paginated_api
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
5
    fdw_package_name 'supabase:openapi-fdw',
6
    fdw_package_version '0.2.0',
7
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
8
    base_url 'https://openapi.example.com',
9
    page_size '100',
10
    page_size_param 'limit',
11
    cursor_param 'cursor'
12
  );

1
create foreign table openapi.items (
2
  id text,
3
  name text,
4
  attrs jsonb
5
)
6
server paginated_api
7
options (
8
  endpoint '/items',
9
  cursor_path '/meta/next_cursor'
10
);

1
create foreign table openapi.locations (
2
  id text,
3
  name text,
4
  category text,
5
  attrs jsonb
6
)
7
server my_api_server
8
options (
9
  endpoint '/locations',
10
  response_path '/features',
11
  object_path '/properties'
12
);

1
create foreign table openapi.users (
2
  id text,
3
  name text,
4
  attrs jsonb  -- Contains full JSON object
5
)
6
server my_api_server
7
options (endpoint '/users');

1
-- Create a foreign server connecting to the Weather.gov API
2
create server openapi_server
3
  foreign data wrapper wasm_wrapper
4
  options (
5
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
6
    fdw_package_name 'supabase:openapi-fdw',
7
    fdw_package_version '0.2.0',
8
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
9
    base_url 'https://api.weather.gov',
10
    spec_url 'https://api.weather.gov/openapi.json'
11
  );
12
13
-- Create a schema to hold the imported foreign tables
14
create schema if not exists openapi;
15
16
-- Auto-import all API endpoints as foreign tables based on the OpenAPI spec
17
import foreign schema openapi from server openapi_server into openapi;
18
19
-- Query the stations endpoint to get weather station data
20
select * from openapi.stations limit 5;

1
-- Create a foreign table for a parameterized endpoint with {zone_id} path parameter
2
create foreign table openapi.zone_stations (
3
  zone_id text,
4
  id text,
5
  type text,
6
  attrs jsonb
7
) server openapi_server
8
options (
9
  endpoint '/zones/forecast/{zone_id}/stations',
10
  rowid_column 'id'
11
);
12
13
-- Query stations for Alaska zone AKZ317 - generates GET /zones/forecast/AKZ317/stations
14
select id, type from openapi.zone_stations where zone_id = 'AKZ317';

1
-- Query a search API that uses POST for read operations
2
create foreign table openapi.search_results (
3
  id text,
4
  title text,
5
  score real,
6
  attrs jsonb
7
)
8
server my_api_server
9
options (
10
  endpoint '/search',
11
  method 'POST',
12
  request_body '{"query": "postgresql", "limit": 25}'
13
);
14
15
select id, title, score from openapi.search_results;

1
create server custom_api
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
5
    fdw_package_name 'supabase:openapi-fdw',
6
    fdw_package_version '0.2.0',
7
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
8
    base_url 'https://openapi.example.com',
9
    api_key 'your-key',
10
    user_agent 'MyApp/1.0',
11
    accept 'application/json',
12
    headers '{"X-Request-ID": "postgres-fdw", "X-Feature-Flag": "beta"}'
13
  );

1
create server query_auth_api
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
5
    fdw_package_name 'supabase:openapi-fdw',
6
    fdw_package_version '0.2.0',
7
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
8
    base_url 'https://api.example.com',
9
    api_key 'sk-your-api-key',
10
    api_key_location 'query'  -- sends as ?api_key=sk-... (uses api_key_header as param name)
11
  );

1
create server per_user_api
2
  foreign data wrapper wasm_wrapper
3
  options (
4
    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_openapi_fdw_v0.2.0/openapi_fdw.wasm',
5
    fdw_package_name 'supabase:openapi-fdw',
6
    fdw_package_version '0.2.0',
7
    fdw_package_checksum 'f0d4d6e50f7c519a66363bd8bdbe1ea8086ca810ca14b43fb0ed18b64acdf6aa',
8
    base_url 'https://api.example.com',
9
    auth_token_setting 'app.api_token'  -- read the token from this session variable each request
10
  );
11
12
-- Resolve the calling user's token (e.g. from an RLS-protected table keyed to
13
-- auth.uid()) and pin it for the life of the transaction:
14
create function set_api_token() returns void
15
  language sql security definer set search_path = '' as $$
16
  select set_config('app.api_token',
17
    (select access_token from public.user_tokens where user_id = auth.uid()),
18
    true);
19
$$;
20
21
select set_api_token();
22
select * from some_foreign_table;

1
-- API returns: {"data": [...], "meta": {...}}
2
create foreign table openapi.items (
3
  id text,
4
  name text,
5
  attrs jsonb
6
)
7
server my_api_server
8
options (
9
  endpoint '/items',
10
  response_path '/data'
11
);

1
create materialized view api_users_cache as
2
select * from openapi.users;
3
4
-- Query the cache
5
select * from api_users_cache;
6
7
-- Refresh when needed
8
refresh materialized view api_users_cache;