PostgREST 13 is out! It comes with API and Observabilty improvements. In this post, we'll see what's new.
This new feature allows you to represent one-to-many and many-to-many relationships as flat JSON arrays.
For example, if you have database similar to IMDB and you’d like to represent it as a hierarchical JSON structure for your frontend, like so:
_17
[
_17
{
_17
"title": "The Shawshank Redemption",
_17
"actors": ["Tim Robbins", "Morgan Freeman"],
_17
"genres": ["Drama"]
_17
},
_17
{
_17
"title": "The Godfather",
_17
"actors": ["Marlon Brando", "Al Pacino"],
_17
"genres": ["Drama", "Crime"]
_17
},
_17
{
_17
"title": "The Dark Knight",
_17
"actors": ["Christian Bale", "Heath Ledger"],
_17
"genres": ["Drama", "Crime", "Action"]
_17
}
_17
]
You can now do it this way:
The above ...people is “spreading” the many-to-many relationship between titles and people, forming a flat array only consisting of the primary_name column. This flat array is then renamed to actors. We do a similar process for genres , which also forms a many-to-many relationship with people.
You can see the data model used for this example on this gist. There are more details about this feature on the official docs.
Previously you could only use the full text search operator on tsvector columns, now you can do it on text and json/jsonb columns too:
This works because text and json/jsonb columns will be automatically converted with to_tsvector.
To ensure this operation is fast, add an index:
_10
create index idx_titles on people
_10
using gin (to_tsvector('english', primary_name));
You can now limit to the amount of rows affected by an update or delete operation with maxAffected:
If the rows affected by the operation surpass the limit in maxAffected, an error will be thrown.
This also works with rpc(), given that it modifies rows and returns the affected rows. More on details on the official docs.
Content-Length header#
For observability, you can now verify the response body size in bytes in the Content-Length header.
_10
HTTP/1.1 200 OK
_10
Content-Length: 104
_10
Content-Location: /items
This helps in cases where you want to know which requests consume the most traffic to avoid exceeding egress limits.
The PostgREST error code is now present in the Proxy-Status header.
_10
HTTP/1.1 406 Not Acceptable
_10
Proxy-Status: PostgREST; error=PGRST116
You can check the Proxy-Status and Content-Length headers in the Supabase Logs Explorer.
JWT kid validation#
PostgREST now validates the JWT kid claim. If your JWT contains a Key ID (kid), it will try to match this with one of the kid's in the configured JSON Web Key Set. Check the official docs for more details.
If you use Supabase Auth or the CLI to create JSON Web Keys, you shouldn’t worry about this change as both systems will ensure kid's are present in the JSON Web Key Set.
For users that integrate with other Auth systems, make sure that both your JWT and JWKS follow the above rules.
Schema validation in PostgREST search path#
The schemas inside db-schemas and db-extra-search-path are now validated. This means you cannot put a nonexistent schema there, if you do PostgREST will fail with an error message.
If you drop a schema during a migration, you should make sure this is synced with the PostgREST search path, which is possible thanks to postgres transactional DDL:
_10
begin;
_10
drop schema old_schema;
_10
alter role authenticator set pgrst.db_schemas = 'public, pg_graphql, others'; -- make sure old_schema is not present here
_10
commit;
PostgREST v13 is now available for all new projects on the Supabase platform, old projects can upgrade to get this new version.
You can look at the full changelog on the release notes.