分类

Posted in

PostgreSQL 外部数据包装器与 WebAssembly_AI阅读总结 — 包阅AI

by baoyueai

包阅导读总结

1.

“`

Postgres、Foreign Data Wrappers、Wasm、Supabase、Data Integration

“`

2.

Postgres 的 Foreign Data Wrappers（FDWs）可与外部数据交互，现支持 Wasm 封装器。Wasm FDW 具有诸多优势，如提高安全性、简化开发和分布等。文中介绍了其架构、工作流程、支持的数据源及使用步骤，并感谢了相关开发者的贡献。

3.

– Postgres Foreign Data Wrappers（FDWs）

– 允许 Postgres 与外部数据交互，用户创建外部表进行查询操作。

– Wrappers 框架支持 Wasm

– 任何人可创建 Wasm 封装器，通过示例展示如何从 GitHub 运行。

– Wasm FDW 的优势

– 包括提高安全性、简化开发和分布等。

– Wasm FDW 架构和工作流程

– 核心组件、外部存储、数据源及动态加载和交互流程。

– 使用 Wasm FDW

– 目前仅支持特定数据源，可自行构建，介绍了 Supabase 上使用的步骤。

– 以 Paddle 为例展示了具体操作。

– 社区贡献

– 感谢了部分开发者的贡献，鼓励更多人参与。

思维导图：

文章地址：https://supabase.com/blog/postgres-foreign-data-wrappers-with-wasm

文章来源：supabase.com

作者：Supabase Blog

发布时间：2024/8/16 0:00

语言：英文

总字数：1181字

预计阅读时间：5分钟

评分：91分

标签：PostgreSQL,外部数据包装器,WebAssembly,Rust,Supabase

以下为原文内容

本内容来源于用户推荐转载，旨在分享知识与观点，如有侵权请联系删除 联系邮箱 media@ilingban.com

Foreign Data Wrappers (FDWs) allow Postgres to interact with externally hosted data. To operate a FDW, the user creates a foreign table. When queried, the foreign table reaches out to the 3rd party service, collects the requested data, and returns it to the query in the shape defined by the foreign table. This allows seamless querying and data manipulation across different tools as if they were local tables from within Postgres.

Wrappers is a Rust framework for creating Postgres Foreign Data Wrappers. Today we’re releasing support for Wasm (WebAssembly) wrappers.

With this feature, anyone can create a Wasm wrapper to an external service and run it directly from e.g. GitHub:

_14

-- An Example Google Sheets Wasm Wrapper:

_14

create server google_sheets

_14

foreign data wrapper wasm_wrapper

_14

-- Install from GitHub

_14

fdw_package_url 'https://github.com/<ORG>/<REPO>/releases/download/v0.2.0/google_sheets_fdw.wasm',

_14

fdw_package_name 'my-company:google-sheets-fdw',

_14

fdw_package_version '0.2.0',

_14

fdw_package_checksum '338674c4c983aa6dbc2b6e63659076fe86d847ca0da6d57a61372b44e0fe4ac9',

_14

-- Provide custom options

_14

base_url 'https://docs.google.com/spreadsheets/d'

This feature is available today in public alpha for all new projects.

Foreign Data Wrappers (FDW) are a powerful feature of Postgres that allows you to connect to and query external data sources as if they were regular tables.

Wrappers is an open source project that simplifies the creation of Postgres Foreign Data Wrappers using Rust.

WebAssembly (Wasm) is a binary instruction format that enables secure and high-performance execution of code on the web. It is originally designed for web browsers, but now can also be used in server-side environments like Postgres.

Here’s how the Wasm FDW benefits us:

  • Improved Security: Wasm’s sandboxed execution runtime with minimum interfaces enhances the security of FDW.
  • Simplified Development: Developers can use Rust to create complex FDWs without diving deep into Postgres internal API.
  • Simplified Distribution: Easily distribute your Wasm FDW through any URL-accessible storage (such as GitHub or S3).
  • Enhanced Performance: Wasm’s near-native speed ensures FDWs operate with minimal overhead.
  • Increased Modularity: Each Wasm FDW is an isolated package which is dynamically loaded and executed by Wrappers individually.

To better understand how the Wasm FDW works, let’s take a look at the architecture:

The above diagram illustrates the key components and how they interact:

  1. Supabase Wrappers Extension (Host): This is the core component that runs within Postgres. It includes below modules:
    • Wasm Runtime: Provides runtime environment to executes the Wasm FDW package.
    • HTTP Interface: Manages communication with external data sources through HTTP.
    • Utilities: Helper tools and functions to support FDW operations.
    • Other modules providing specific functionalities, such like JWT, stats and etc.
  2. Wasm FDWs (Guests): Isolated, dynamically-loaded Wasm packages that perform data fetching and processing. They execute in a sandboxed environment to ensure security and performance. For example:
    • Snowflake Wasm FDW: A foreign data wrapper specifically designed to interact with Snowflake.
    • Paddle Wasm FDW: Another FDW example, tailored for Paddle integration.
  3. Web Storage: Represents external storage services like GitHub or S3, where Wasm packages can be publicly stored and downloaded from.
  4. External Data Source: Various external systems which data is fetched from or pushed to, such as Snowflake and Paddle. Data is accessed using RESTful APIs.

Wasm FDWs are loaded dynamically when the first request is made. The interaction flow is:

  1. Wasm download: The Wasm FDWs are dynamically downloaded from web storage services, like GitHub or S3, and cached locally. This happens the first time the SELECT statement is initiated.
  2. Initialization and Execution: Once downloaded, the Wasm FDWs are initialized and executed within the embedded Wasm runtime environment. This provides a secure, sandboxed execution environment that isolates the packages from the main Postgres system.
  3. Data Fetching via RESTful API: The Wasm FDWs interact with their respective external data sources via RESTful APIs.
  4. Query Handling and Data Integration: When a query is executed against a foreign table in Postgres, the Supabase Wrappers extension invokes the appropriate Wasm FDW, fetches data from the external source, processes it, and returns it to the Supabase Wrappers, which integrates it back into the Postgres query execution pipeline.

The Wasm FDW currently only supports data sources which have HTTP(s) based JSON API, other sources such like TCP/IP based DBMS or local files are not supported yet.

A major benefit of Wasm FDW is that you can build your own FDW and use it on Supabase. To get started, clone the Postgres Wasm FDW [Template]. Building your own Wasm FDWs opens up a world of possibilities for integrating diverse data sources into Postgres.

Visit Wrappers docs and guides to learn more about how to develop a Wasm FDW.

As the Wasm FDW can access external data sources, you should never install Wasm Wrappers from untrusted source. Always use official Supabase FDWs, or use sources which you have full visibility and control.

The Wasm FDW feature is available today on the Supabase platform. We have 2 new built-in Wasm FDWs: Snowflake and Paddle.

To get started, follow below steps:

  1. Create a new Supabase project: database.new
  2. Navigate to the Database -> Wrappers section and enable Wrappers.
  3. Add Snowflake or Paddle wrapper, follow the instructions and create foreign tables.

We can also use SQL. Let’s try, using the Paddle FDW as an example.

Inside the SQL editor, enable the Wasm Wrapper feature:

_10

-- install Wrappers extension

_10

create extension if not exists wrappers with schema extensions;

_10

-- create Wasm foreign data wrapper

_10

create foreign data wrapper wasm_wrapper

_10

handler wasm_fdw_handler

_10

validator wasm_fdw_validator;

Sign up for a sandbox account and get API key with Paddle.

Create a Paddle server in Postgres using the Wasm FDW created above:

_15

-- create Paddle foreign server

_15

create server paddle_server

_15

foreign data wrapper wasm_wrapper

_15

-- check all available versions at

_15

-- https://fdw.dev/catalog/paddle/#available-versions

_15

fdw_package_url 'https://github.com/supabase/wrappers/releases/download/wasm_paddle_fdw_v0.1.1/paddle_fdw.wasm',

_15

fdw_package_name 'supabase:paddle-fdw',

_15

fdw_package_version '0.1.1',

_15

fdw_package_checksum 'c5ac70bb2eef33693787b7d4efce9a83cde8d4fa40889d2037403a51263ba657',

_15

-- save your Paddle credentials

_15

api_url 'https://sandbox-api.paddle.com',

_15

api_key '<your Paddle sandbox API key>'

Create a table for Paddle data:

_19

-- create dedicated schema for Paddle foreign tables

_19

create schema if not exists paddle;

_19

-- create foreign table

_19

create foreign table paddle.customers (

_19

created_at timestamp,

_19

updated_at timestamp,

Query Paddle from Postgres#

Now let’s query the foreign table and check the result:

_10

select id, name, email, status

_10

from paddle.customers;

That’s it. Head over to the Supabase Wrappers documentation to find more detailed guides on setting up and using Wasm FDWs.

None of this innovation would have been possible without the relentless efforts and contributions of our vibrant community. We’d like to thank all the following developers for their contributions:

Aayushya Vajpayee, Romain Graux

Want to join the Supabase Wrappers community contributors? Check out our contribution docs.