Notion
Notion provides a versatile, ready-to-use solution for managing your data.
The Notion Wrapper is a WebAssembly(Wasm) foreign data wrapper which allows you to read data from your Notion workspace for use within your Postgres database.
Available Versions
| Version | Wasm Package URL | Checksum |
|---|---|---|
| 0.1.1 | https://github.com/supabase/wrappers/releases/download/wasm_notion_fdw_v0.1.1/notion_fdw.wasm | 6dea3014f462aafd0c051c37d163fe326e7650c26a7eb5d8017a30634b5a46de |
| 0.1.0 | https://github.com/supabase/wrappers/releases/download/wasm_notion_fdw_v0.1.0/notion_fdw.wasm | e017263d1fc3427cc1df8071d1182cdc9e2f00363344dddb8c195c5d398a2099 |
Preparation
Before you can query Notion, you need to enable the Wrappers extension and store your credentials in Postgres.
Enable Wrappers
Make sure the wrappers extension is installed on your database:
_10create extension if not exists wrappers with schema extensions;
Enable the Notion Wrapper
Enable the Wasm foreign data wrapper:
_10create foreign data wrapper wasm_wrapper_10 handler wasm_fdw_handler_10 validator wasm_fdw_validator;
Store your credentials (optional)
By default, Postgres stores FDW credentials inside pg_catalog.pg_foreign_server in plain text. Anyone with access to this table will be able to view these credentials. Wrappers is designed to work with Vault, which provides an additional level of security for storing credentials. We recommend using Vault to store your credentials.
_10-- Save your Notion API key in Vault and retrieve the `key_id`_10insert into vault.secrets (name, secret)_10values (_10 'notion',_10 '<Notion API key>' -- Notion API key_10)_10returning key_id;
Connecting to Notion
We need to provide Postgres with the credentials to access Notion and any additional options. We can do this using the create server command:
_10create server notion_server_10 foreign data wrapper wasm_wrapper_10 options (_10 fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_notion_fdw_v0.1.1/notion_fdw.wasm',_10 fdw_package_name 'supabase:notion-fdw',_10 fdw_package_version '0.1.1',_10 fdw_package_checksum '6dea3014f462aafd0c051c37d163fe326e7650c26a7eb5d8017a30634b5a46de',_10 api_url 'https://api.notion.com/v1', -- optional_10 api_key_id '<key_ID>' -- The Key ID from above._10 );
Note the fdw_package_* options are required, which specify the Wasm package metadata. You can get the available package version list from above.
Create a schema
We recommend creating a schema to hold all the foreign tables:
_10create schema if not exists notion;
Options
The full list of foreign table options are below:
object- Object name in Notion, required.
Supported objects are listed below:
| Object name |
|---|
| block |
| page |
| database |
| user |
Entities
Block
This is an object representing Notion Block content.
Ref: Notion API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Block | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_13create foreign table notion.blocks (_13 id text,_13 page_id text,_13 type text,_13 created_time timestamp,_13 last_edited_time timestamp,_13 archived boolean,_13 attrs jsonb_13)_13 server notion_server_13 options (_13 object 'block'_13 );
Notes
- The
attrscolumn contains all user attributes in JSON format - The
page_idfield is added by the FDW for development convenience - All blocks, including nested children blocks, belong to one page will have the same
page_id - Query pushdown supported for both
idandpage_idcolumns - Use
page_idfilter to fetch all blocks of a specific page recursively - Querying all blocks without filters may take a long time due to recursive data requests
Page
This is an object representing Notion Pages.
Ref: Notion API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Page | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_12create foreign table notion.pages (_12 id text,_12 url text,_12 created_time timestamp,_12 last_edited_time timestamp,_12 archived boolean,_12 attrs jsonb_12)_12 server notion_server_12 options (_12 object 'page'_12 );
Notes
- The
attrscolumn contains all page attributes in JSON format - Query pushdown supported for
idcolumn
Database
This is an object representing Notion Databases.
Ref: Notion API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| Database | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_12create foreign table notion.databases (_12 id text,_12 url text,_12 created_time timestamp,_12 last_edited_time timestamp,_12 archived boolean,_12 attrs jsonb_12)_12 server notion_server_12 options (_12 object 'database'_12 );
Notes
- The
attrscolumn contains all database attributes in JSON format - Query pushdown supported for
idcolumn
User
This is an object representing Notion Users.
Ref: Notion API docs
Operations
| Object | Select | Insert | Update | Delete | Truncate |
|---|---|---|---|---|---|
| User | ✅ | ❌ | ❌ | ❌ | ❌ |
Usage
_11create foreign table notion.users (_11 id text,_11 name text,_11 type text,_11 avatar_url text,_11 attrs jsonb_11)_11 server notion_server_11 options (_11 object 'user'_11 );
Notes
- The
attrscolumn contains all user attributes in JSON format - Query pushdown supported for
idcolumn - User email can be extracted using:
attrs->'person'->>'email'
Query Pushdown Support
This FDW supports where clause pushdown with id as the filter. For example,
_10select * from notion.pages_10where id = '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247';
will be translated to a Notion API call: https://api.notion.com/v1/pages/5a67c86f-d0da-4d0a-9dd7-f4cf164e6247.
In addition to id column pushdown, page_id column pushdown is also supported for Block object. For example,
_10select * from notion.blocks_10where page_id = '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247';
will recursively fetch all children blocks of the Page with id '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247'. This can dramatically reduce number of API calls and improve query performance.
Below query will request ALL the blocks of ALL pages recursively, it may take very long time to run if there are many pages in Notion. So it is recommended to always query Block object with an id or page_id filter like above.
_10select * from notion.blocks;
Supported Data Types
| Postgres Data Type | Notion Data Type |
|---|---|
| boolean | Boolean |
| text | String |
| timestamp | Time |
| timestamptz | Time |
| jsonb | Json |
The Notion API uses JSON formatted data, please refer to Notion API docs for more details.
Limitations
This section describes important limitations and considerations when using this FDW:
- Large result sets may experience slower performance due to full data transfer requirement
- Query pushdown support limited to 'id' and 'page_id' columns only
- Recursive block fetching can be extremely slow for large page hierarchies
- Materialized views using these foreign tables may fail during logical backups
Examples
Basic Example
This example will create a "foreign table" inside your Postgres database and query its data.
_19create foreign table notion.pages (_19 id text,_19 url text,_19 created_time timestamp,_19 last_edited_time timestamp,_19 archived boolean,_19 attrs jsonb_19)_19 server notion_server_19 options (_19 object 'page'_19 );_19_19-- query all pages_19select * from notion.pages;_19_19-- query one page_19select * from notion.pages_19where id = '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247';
attrs is a special column which stores all the object attributes in JSON format, you can extract any attributes needed from it. See more examples below.
Query JSON Attributes
_16create foreign table notion.users (_16 id text,_16 name text,_16 type text,_16 avatar_url text,_16 attrs jsonb_16)_16 server notion_server_16 options (_16 object 'user'_16 );_16_16-- extract user's email address_16select id, attrs->'person'->>'email' as email_16from notion.users_16where id = 'fd0ed76c-44bd-413a-9448-18ff4b1d6a5e';
Query Blocks
_10-- query ALL blocks of ALL pages recursively, may take long time!_10select * from notion.blocks;_10_10-- query a single block by block id_10select * from notion.blocks_10where id = 'fc248547-83ef-4069-b7c9-18897edb7150';_10_10-- query all block of a page by page id_10select * from notion.blocks_10where page_id = '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247';