Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Supabase Wrappers: A framework for building Postgres foreign data wrappers

supabase.com

Post reply on HN
Print view View original

1–10 of 47 posts

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#2

Post by kiwicopple »

hey hn, supabase ceo here

this one might be more in of a “Show HN” because it’s a pre-release - something that you might want to try yourself or contribute to. The GitHub repo is here: https://github.com/supabase/wrappers

For context, Postgres has Foreign Data Wrappers (FDW) [0]. These allow you to connect one Postgres database to another, and then query the secondary database directly in the first.

    CREATE FOREIGN TABLE other_database_table ( 
      id integer, 
      title text 
    )
    SERVER other_database;
The data does not need to be “moved” to the primary database - it stays in the secondary and is fetched when you run a select query:

    select * from other_database_table;
Our release today is a framework which extends this functionality to other databases/systems. If you’re familiar with Multicorn[1] or Steampipe[2], then it’s very similar. The framework is written in Rust, using the excellent pgx[3].

We have developed FDWs for Stripe, Firebase, BigQuery, Clickhouse, and Airtable - all in various pre-release states. We'll focus on the systems we’re using internally while we stabalize the framework.

There’s a lot in the blog post into our goals for this release. It’s early, but one of the things I’m most excited about.

[0] Postgres FDW: https://www.postgresql.org/docs/current/sql-createforeigndat...

[1] Multicorn: https://multicorn.org/

[2] Steampipe: https://steampipe.io/

[2] pgx: https://github.com/tcdi/pgx

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#3

Post by kiwicopple »

kiwicopple wrote: post #2

hey hn, supabase ceo here this one might be more in of a “Show HN” because it’s a pre-release - something that you might want to try yourself or contribute to. The GitHub repo is here: https://github.com/supabase/wrappers For context, Postgres has Foreign Data Wrappers (FDW) [0]. These allow you to connect one Postgres database to another, and then query the secondary database directly in the first. CREATE FOREIGN TA…

Full documentation is here: https://supabase.github.io/wrappers/

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#4

Post by atonse »

This is wonderful!

I love the pragmatism behind tools like osquery and steampipe that expose a lot of APIs as database tables. It makes these datasets available to non-programmers that are more comfortable with a database/tabular format.

Is it fair to say though, that FDWs have to run as compiled shared libs? I've always wondered if there can be (like with VS code and language servers) a protocol where we can run a generic FDW that speaks a particular API over the network, and then you can build out-of-process connectors to it and just have to know the usual tools (HTTP, JSON, etc).

Thoughts? Maybe this already exists.

Then anyone could potentially build a bespoke API/dataset and just point an FDW to it.

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#6

Post by kiwicopple »

atonse wrote: post #4

This is wonderful! I love the pragmatism behind tools like osquery and steampipe that expose a lot of APIs as database tables. It makes these datasets available to non-programmers that are more comfortable with a database/tabular format. Is it fair to say though, that FDWs have to run as compiled shared libs? I've always wondered if there can be (like with VS code and language servers) a protocol where we can run a g…

> speaks a particular API over the network

it's a interesting idea, and one of the things that we were toying with in our pg_net extension (https://github.com/supabase/pg_net). This is a "generic" async network extension, so you can fetch/put/post. It works well for APIs.

I think the generic approach works for some things where the data is less "fixed" - for example, an OpenAI API endpoint.

But for "fixed" data (data warehouses), the wrapper usually needs some custom work for security, protocols, and "push down". I'll be interested to get HN's take on this - they might have some suggestions for us for this framework

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#7

Post by kiwicopple »

thedangler wrote: post #5

So you're telling me I can use this to create my own wrapper to get data directly from another API? I can now use this instead of creating my own workflow to get the data via an api which will be stored in the DB anyways?

yes, that's correct. we've built a read-only version for Stripe (which is API-based), and we aim to have the read/write implementation done soon.

You will be able to do something like this:

    insert into stripe_products (name)
    values ('Pizza'), ('Pasta');
This will insert a value into Stripe via the API. Then you can query your stripe products like this:

    select *
    from stripe_products
    limit 10;

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#8

Post by jzelinskie »

I really want to love foreign data wrappers for Postgres and this seems like a big improvement over existing Python library, but the lack of support for them in managed databases services makes them a non-starter for so many use-cases.

Because RDS, for example, will only support the foreign data wrapper for reading from another "Postgres", what we really need is a server that supports the Postgres wire protocol (easier said than done) and you implement your drivers as a handler to that server.

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#9

Post by thedangler »

kiwicopple wrote: post #7
thedangler wrote: post #5

So you're telling me I can use this to create my own wrapper to get data directly from another API? I can now use this instead of creating my own workflow to get the data via an api which will be stored in the DB anyways?

yes, that's correct. we've built a read-only version for Stripe (which is API-based), and we aim to have the read/write implementation done soon. You will be able to do something like this: insert into stripe_products (name) values ('Pizza'), ('Pasta'); This will insert a value into Stripe via the API. Then you can query your stripe products like this: select * from stripe_products limit 10;

Is there documentation on how to interact with a API for the wrapper? The API I'm probably going to try this with needs some crazy logic to parse the responses and does everything through query parameters.

Can't wait until this is ready.

Re: Supabase Wrappers: A framework for building Postgres foreign data wrappers

#10

Post by kiwicopple »

thedangler wrote: post #9
kiwicopple wrote: post #7

Earlier quoted context omitted.

yes, that's correct. we've built a read-only version for Stripe (which is API-based), and we aim to have the read/write implementation done soon. You will be able to do something like this: insert into stripe_products (name) values ('Pizza'), ('Pasta'); This will insert a value into Stripe via the API. Then you can query your stripe products like this: select * from stripe_products limit 10;

Is there documentation on how to interact with a API for the wrapper? The API I'm probably going to try this with needs some crazy logic to parse the responses and does everything through query parameters. Can't wait until this is ready.

You'd need to build a specific wrapper for that API. For example, here[0] is the wrapper for Stripe (docs[1])

I think you're looking for something more generic, like pg_net[2]. This would allow you to do your crazy logic by parsing your API response:

    select net.http_get('https://news.ycombinator.com') as request_id;

    select body, status_code from net.http_collect_response(1, async:=false);
[0] Stripe src: https://github.com/supabase/wrappers/tree/main/wrappers/src/...

[1] Stripe docs: https://supabase.github.io/wrappers/stripe/

[2] pg_net: https://supabase.github.io/pg_net/api/

Post reply on HN