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;
select * from other_database_table;
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
So this "wrappers" project is just a framework that simplifies the development and manages the curation and distribution of Postgres Foreign Data Wrappers? At the end of the day, are these just FDWs that run on top of any Postgres instance? Do I have to run Supabase to take advantage of the FDWs created through this framework?
Additionally one of your examples is Snowflake, but I don't see a matching SF FDW in your repo? Is there a generic JDBC/SQL/etc FDW for any SQL based database like Snowflake, Oracle, etc? Or is this just to create a spark in someone's mind (guilty!) about what is possible and yet to be implemented?
Despite my confusion, this sounds like a very exciting project to follow.
Yes, the FDWs developed by Wrappers can be used on any Postgres db.
| Do I have to run Supabase to take advantage of the FDWs created through this framework?
No, you don't need to. Those are just normal FDWs can be used on any Postgres db.
| Is there a generic JDBC/SQL/etc FDW for any SQL based database like Snowflake, Oracle, etc?
The Snowflake FDW isn't developed yet, the example is just for concept demo. It has ClickHouse and BigQuery FDWs although they are not JDBC based. Generic JDBC FDW will need JVM embedded so it is not in plan at this moment.
Disclaimer: I am Supabase developer.
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.
On a sidenote, is not the wrappers for Airtable, BigQuery and ClickHouse opensourced? Or why did they skip that column in the second table?
Is all of supabase opensourced now? I'm meaning I heard something about function not being opensourced but I can be remembering wrong. Most stuff is on GitHub I see
All of the wrappers are open source. You can see the source for the Airtable, BigQuery, and ClickHouse wrappers here https://github.com/supabase/wrappers/tree/5fac8afb62e6e8362b...
The `self-hosted` column is only missing from the "under development" wrappers in the blog post's table because those are not production ready and shouldn't be self hosted (yet).
> Is all of supabase opensourced now?
Yep! The whole stack is open source and can be self hosted
disclaimer: I am a Supabase employee, but these views are my own.
Stripe and Firebase both offer test endpoints for their services. The foreign data wrappers for each allow subbing in a user defined endpoint in their `options` so thats how I'd recommend testing the two that have released. Some of the pre-release integrations can be spun up locally in docker e.g. clickhouse. The FDWs for those similarly take an arbitrary connection string making it pretty straightforward to write tests. Here's an example from the repo https://github.com/supabase/wrappers/blob/5fac8afb62e6e8362b...
> Add custom logic?
Views!
> tasks that the database is not designed to do
The C API for foreign data wrappers is baked right into Postgres proper. They've been around since 2013 and are pretty battle hardened at this point. Supabase Wrappers exposes that API in rust so users can write FDWs without worrying about accidentally tanking the Postgres process. Its more about making a great Postgres feature more accessible than tricking the database into doing anything new.
I'm not sure about this one though - rust is a great systems language, but it wouldn't be my first choice for bridging the db <-> api gap.
I wonder why this wasn't built on top of, or an enhancement to, the existing (excellent) multicorn[1] project. Python seems like a better choice of language for dealing with io bound problems like talking to remote APIs.
Multicorn is mature, stable, well tested and has a ton of FDW implementations already.
The dynamic nature of python simplifies the development/debug cycle, and serialization to/from JSON is easier than in any mainstream language except for javascript.
I'd love to understand more about the technical rationale that drove this.
> unexpected N API calls when joining across my domain
I'm not sure why they would be unexpected (because it should displace some other API calls). I'll hazard a guess that you're worried about fetching the same data multiple times? If that's the case, then yes, you should materialize the data into your database.
The Wrapper itself handles pagination, but you'd also want to make sure you're using filters & limits in your SQL statements.
You're right -- it's not unexpected -- maybe more like a leaky abstraction.
Is there a way to propagate changes from the foreign data source through the FDW to Postgres?
Or would it just be some kind of task polls the foreign data source pulling a delta?
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?
You will be able to do something like this:
insert into stripe_products (name)
values ('Pizza'), ('Pasta');
select *
from stripe_products
limit 10;I can't wait for somebody offering a generator, where you plug in your API, it pulls and parses the JSON, then you can select the fields you want and it generates the wrapper. (Alternatively, for put/post you could supply your own JSON).
Practically like some low/no code tools like Appsmith/Budibase and the likes already do today.
Sadly I lack the necessary skills and more important the time to dive into that.
Can't wait until this is ready.
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.
I'm doing this right now because I have a postgres installation that it's not yet convenient to upgrade beyond v12, but where I'd really like the benefit of the recently-improved JDBC FDW, which requires at least v13.
Also, is the Firebase connector for Realtime Database or Firestore?
You can provide table size estimation to Postgres's query planner in wrappers using `get_rel_size()` function, here is doc:
https://docs.rs/supabase-wrappers/latest/supabase_wrappers/i...
| Also, is the Firebase connector for Realtime Database or Firestore?
It is for Firestore, Realtime Database is not supported yet.
But what is the point? Why is this better than making the API call?
SQL is for queries, why would I want to do networking with it?
I'm not sure on the value of this for customer facing production systems, but for internal reporting / product analytics this should make it really easy to pull in disparate datasets w/o having to spend eng time to keep each one running.
Could you point me to any tools or resources on achieving that (pulling data from several external databases into a single one for the purpose of analytics/aggregation)?
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
So more like, (this is a bit contrived but bear with me):
Let's say I wanted to make a table that lists all my keybase proofs (so sad Keybase is basically dead, but I digress).
I'd build a graphql endpoint where you can query those proofs.
Then I'd have to define a metadata endpoint that maybe returns a JSON schema?
Then you can tell the FDW to define a virtual table that (as much as possible) uses the JSON schema to generate columns in this virtual table.
You'd define the FDW to say, here's the virtual table (like you'd do now), and then put in an option to say "you'll get the mappings for this table with the JSON schema returned by this metadata endpoint" and then tell the FDW "send select queries to this REST/graphql endpoint" which would be something in our example that returns keybase proofs).
I wouldn't need to build a whole new keybase FDW. I'd just use the generic one to hit that existing endpoint.
Is that making sense? It's kind of a dynamic FDW that can be configured for the long tail of API endpoints.
Have to agree with you there, multicorn is extremely cool. I'm a big sqlalchemy fan so their default SQLA wrapper was a killer feature to give up (although maybe we could do something similar with launchbadge/sqlx[1]). We investigated using multicorn early this year and had a few hiccups. Activity on the original repo[2] quieted way down ~3 years ago. For example, pg14 support hasn't landed and the newest supported python version is EOL in 2022. There is new fork[3] with pg14 support (15 in the pipe) that might pick up in adoption but thats still TBD.
Supabase aims to support new major Postgres versions within 2-3 months so we have to be very careful taking on dependencies that might slow that process.
> I'd love to understand more about the technical rationale that drove this.
Architecturally, multicorn has postgres communicate with a separate python process on the host that does all the hard work. That's convenient, but it can bloat over time and/or be memory hungry for larger result sets. The rust implementation runs in-process and is generally a lot lighter.
Currently I'd say supabase/wrappers is a safer/easier version of the C API vs a direct analog to multicorn. Over time I think we'll see that comparison become more appropriate. There's a lot of excitement around the concept internally and we've already been floating some ideas wrt `auto-mapping` tables for common SQL dialects, a generic JSON HTTP API wrapper, etc. Stay tuned!
[1]https://github.com/launchbadge/sqlx [2]https://github.com/Segfault-Inc/Multicorn [3]https://github.com/pgsql-io/multicorn2
I'm not clear how mocking in the middleware is materially different from this approach (except perhaps you are testing network connectivity too).
> generator, where you plug in your API, it pulls and parses the JSON, then you can select the fields you want and it generates the wrapper
Probably on this one the wrapper could use an OpenAPI spec
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);
[1] Stripe docs: https://supabase.github.io/wrappers/stripe/
[2] pg_net: https://supabase.github.io/pg_net/api/
I looked into using a postgres FDW years ago when I was trying to come up with an easy way to ELT data out of Mongo into a DB. Effectively using Postgres as the ETL tool. Understanding where to find FDWs, who maintains them, etc... was overwhelming. I can see the merits of this framework that you're building, and def going to keep an eye on it.
Also always excited to hear about Rust being used.
I imagine you want to do something like:
select
*
from
public.users join stripe.customers
on
public.users.stripe_id = stripe.customers.id
limit 100;
1. Materialize your customers into your database (like I mention in the previous comment)
2. We build a "smart" FDW, so that it parses your query and fetches the Stripe data first, then performs the join.
3. Use a CTE:
with
customers as (
select * from stripe.customers
),
users as (
select * from public.users
)
select
*
from
users join customers
on
users.stripe_id = customers.id> define a metadata endpoint that maybe returns a JSON schema
We could use an OpenAPI spec[0], which many REST services expose as a JSON endpoint. I think that could work: develop a generic "OpenAPI" FDW, which you can connect like this:
create server example_api
foreign data wrapper example_api_wrapper
options (
open_api_url 'https://api.example.com/'
);
GET -> SELECT (which would map selected columns into a table)
POST -> INSERT
PATCH -> UPDATE
DELETE -> DELETE
I’m wondering if this is how multicorn works even now. It seems to be an out of process thing. I wasn’t aware of multicorn before.
But python… wish it was elixir :-)
There's no facility for deltas, or any other way of propagating changes.
Edit: And to answer your original question, yes you can absolutely base the materialized view on data obtained from a foreign table.
Once I heard those words mentioned, I began to imagine what sort of interaction with a foreign data source's operations log FDW might have.
Of course, and depending on the foreign data source, you could probably even expose an operations log to Postgres via a FDW by defining it as a foreign table... Effectively opening up the possibility of eventually consistent replication. Lots of data stores' operations logs are accessible data collections/tables anyways.
All of this is exciting stuff!