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#

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:

1
create extension if not exists wrappers with schema extensions;

Enable the Notion Wrapper#

Enable the Wasm foreign data wrapper:

1
2
3
create foreign data wrapper wasm_wrapper  handler wasm_fdw_handler  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.

1
2
3
4
5
6
7
-- Save your Notion API key in Vault and retrieve the `key_id`insert into vault.secrets (name, secret)values (  'notion',  '<Notion API key>' -- should look like ntn_589513........)returning key_id; -- copy this Vault key id for the next step

⚠️ ** Getting a Notion API key**

1. Visit Notion > Profile > Integrations
2. Click New integration
3. Add an integration name, select your workspace, then select Internal as the Type
4. This will give you an Internal Integration Secret that will look like ntn_589513........
5. Use this as your Notion API key

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:

1
2
3
4
5
6
7
8
9
10
create server notion_server  foreign data wrapper wasm_wrapper  options (    fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_notion_fdw_v0.1.1/notion_fdw.wasm',    fdw_package_name 'supabase:notion-fdw',    fdw_package_version '0.1.1',    fdw_package_checksum '6dea3014f462aafd0c051c37d163fe326e7650c26a7eb5d8017a30634b5a46de',    api_url 'https://api.notion.com/v1',  -- optional    api_key_id '<vault key_ID>' -- the Vault key id from the previous step, not the Notion API key itself  );

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:

1
create 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:

Entities#

Block#

This is an object representing Notion Block content.

Ref: Notion API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
13
create foreign table notion.blocks (  id text,  page_id text,  type text,  created_time timestamp,  last_edited_time timestamp,  archived boolean,  attrs jsonb)  server notion_server  options (    object 'block'  );

Notes#

• The attrs column contains all user attributes in JSON format
• The page_id field 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 id and page_id columns
• Use page_id filter 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#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table notion.pages (  id text,  url text,  created_time timestamp,  last_edited_time timestamp,  archived boolean,  attrs jsonb)  server notion_server  options (    object 'page'  );

Notes#

• The attrs column contains all page attributes in JSON format
• Query pushdown supported for id column

Database#

This is an object representing Notion Databases.

Ref: Notion API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
12
create foreign table notion.databases (  id text,  url text,  created_time timestamp,  last_edited_time timestamp,  archived boolean,  attrs jsonb)  server notion_server  options (    object 'database'  );

Notes#

• The attrs column contains all database attributes in JSON format
• Query pushdown supported for id column

User#

This is an object representing Notion Users.

Ref: Notion API docs

Operations#

Usage#

1
2
3
4
5
6
7
8
9
10
11
create foreign table notion.users (  id text,  name text,  type text,  avatar_url text,  attrs jsonb)  server notion_server  options (    object 'user'  );

Notes#

• The attrs column contains all user attributes in JSON format
• Query pushdown supported for id column
• 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,

1
2
select * from notion.pageswhere 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,

1
2
select * from notion.blockswhere 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.

1
select * from notion.blocks;

Supported Data Types#

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create foreign table notion.pages (  id text,  url text,  created_time timestamp,  last_edited_time timestamp,  archived boolean,  attrs jsonb)  server notion_server  options (    object 'page'  );-- query all pagesselect * from notion.pages;-- query one pageselect * from notion.pageswhere 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#

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create foreign table notion.users (  id text,  name text,  type text,  avatar_url text,  attrs jsonb)  server notion_server  options (    object 'user'  );-- extract user's email addressselect id, attrs->'person'->>'email' as emailfrom notion.userswhere id = 'fd0ed76c-44bd-413a-9448-18ff4b1d6a5e';

Query Blocks#

1
2
3
4
5
6
7
8
9
10
-- query ALL blocks of ALL pages recursively, may take long time!select * from notion.blocks;-- query a single block by block idselect * from notion.blockswhere id = 'fc248547-83ef-4069-b7c9-18897edb7150';-- query all block of a page by page idselect * from notion.blockswhere page_id = '5a67c86f-d0da-4d0a-9dd7-f4cf164e6247';