PostgREST turns your PostgreSQL database automatically into a RESTful API. Today, PostgREST 9 was released. Let's take a look at some of the new features.
PostgREST 9 brings a much-requested feature: the ability to do inner joins when embedding a table.
Here's an example with supabase-js:
_10
const { data, error } = await supabase
_10
.from('messages')
_10
.select('*, users!inner(*)')
_10
.eq('users.username', 'Jane')
With the new !inner keyword, you can now filter rows of the top-level table (messages) based on a filter (eq) of the embedded table (users).
This works across all Supabase client libraries and you can use it with any of the available operators (gt, in, etc.)
You can now make POST requests to functions with a single unnamed parameter. This is particularly useful for webhooks that send JSON payloads.
For example, imagine you were using Postmark as an email provider and you wanted to save email bounces using their bounce webhook. Previously this wouldn't be possible with PostgREST, as every function required a named parameter.
As of PostgREST 9, this is possible. Simply create a function inside your PostgreSQL database to receive the raw JSON:
_12
create function store_bounces(json)
_12
returns json
_12
language sql
_12
as $$
_12
insert into bounces (webhook_id, email)
_12
values (
_12
($1->>'ID')::bigint,
_12
($1->>'Email')::text
_12
);
_12
_12
select '{ "status": 200 }'::json;
_12
$$;
And the webhook can send data directly to your database via an rpc call:
_15
POST https://<PROJECT_REF>.supabase.co/rest/v1/rpc/store_bounces HTTP/1.1
_15
Content-Type: application/json
_15
_15
{
_15
"RecordType": "Bounce",
_15
"MessageStream": "outbound",
_15
"ID": 4323372036854775807,
_15
"Type": "HardBounce",
_15
"MessageID": "883953f4-6105-42a2-a16a-77a8eac79483",
_15
"Description": "The server was unable to deliver your message (ex: unknown user, mailbox not found).",
_15
"Details": "Test bounce details",
_15
"Email": "john@example.com",
_15
"From": "sender@example.com",
_15
"BouncedAt": "2019-11-05T16:33:54.9070259Z"
_15
}
PostgreSQL 14 compatibility#
If you've ever done your own custom auth functions using PostgREST HTTP Context,
note that a breaking change was necessary for PostgreSQL 14 Compatibility. You'll need to update them:
From | To |
|---|---|
current_setting('request.jwt.claim.custom-claim', true) | current_setting('request.jwt.claims', true)::json->>'custom-claim' |
current_setting('request.header.custom-header', true) | current_setting('request.headers', true)::json->>'custom-header' |
If you only use Supabase default auth functions(auth.email(), auth.uid(), auth.role()), then no action is required because we have updated the functions to handle these changes transparently.
There are a lot more improvements released in PostgREST 9, including support for Partitioned Tables, improved doc, and bug fixes.
You can see the full updates on the PostgREST 9 release notes.