Overview

PostgREST serves a RESTful API from any existing PostgreSQL database.

v11.2.0 brings you the possibility of using PostgreSQL domains and casts to change how your data is presented to web users. We call this feature "domain representations" and it holds some advantages over views.

-- having a uuid domain and a table
create domain app_uuid as uuid;

create table profiles(
  id   app_uuid
, name text
);

-- we define a function to convert the uuid domain to base64
create or replace function json(app_uuid) returns json as $$
  select to_json(encode(uuid_send($1),'base64'));
$$ language sql immutable;

-- and use the function for an implicit cast from domain to json 
create cast (app_uuid as json) with function json(app_uuid) as implicit;

-- the format changes when requesting JSON at the API level
curl "http://localhost:3000/profiles" \
  -H "Accept: application/json"

[{"id":"hGxP/ZLOTeeNEY4pkp9OxA==","name":"John Doe"}]

-- while the data is kept as is at the database level
select * from profiles;

                  id                  |   name
--------------------------------------+----------
 846c4ffd-92ce-4de7-8d11-8e29929f4ec4 | John Doe

Domain representations also allow you to change the format of the request payload and the format of the filters values. Check the Domain Representations docs for more details.

What is new

API

• Domain representations by @aljungberg in #2523
  • Allows for flexible API output formatting and input parsing on a per-column type basis using regular SQL functions configured in the database
  • Enables greater flexibility in the form and shape of your APIs, both for output and input, making PostgREST a more versatile general-purpose API server
  • Examples include base64 encode/decode your binary data (like a bytea column containing an image), choose whether to present a timestamp column as seconds since the Unix epoch or as an ISO 8601 string, or represent fixed precision decimals as strings, not doubles, to preserve precision
  • ...and accept the same in POST/PUT/PATCH by configuring the reverse transformation(s)
  • Other use-cases include custom representation of enums, arrays, nested objects, CSS hex colour strings, gzip compressed fields, metric to imperial conversions, and much more
  • Works when using the select parameter to select only a subset of columns, embedding through complex joins, renaming fields, with views and computed columns
  • Works when filtering on a formatted column without extra indexes by parsing to the canonical representation
  • Works for data RETURNING operations, such as requesting the full body in a POST/PUT/PATCH with Prefer: return=representation
  • Works for batch updates and inserts
  • Completely optional, define the functions in the database and they will be used automatically everywhere
  • Data representations preserve the ability to write to the original column and require no extra storage or complex triggers (compared to using GENERATED ALWAYS columns)
  • Note: data representations require Postgres 10 (Postgres 11 if using IN predicates); data representations are not implemented for RPC

Admin

• Query the PostgREST version in SQL by @laurenceisla in #2647
• The CLI now has the --version option which prints the version information by @laurenceisla in #2856

Resource Embedding

• The documentation now explicitly mentions Foreign Key Joins and includes more complex relationships.

Resource Representation

• nulls=stripped can remove JSON keys which have null values by @taimoorzaeem in #1601
• Improve details field of the singular error response by @taimoorzaeem in #1655

Tables and Views

• Prefer: return=* now reply with Preference-Applied headers by @taimoorzaeem in #740

What is fixed

Auth

• Fix applying superuser settings for impersonated role by @steve-chavez in #2896

API

• Fix OPTIONS not accepting all available media types by @steve-chavez in #2821
• Fix Prefer: missing=default with DOMAIN default values by @steve-chavez in #2840
• Fix HEAD unnecessarily executing aggregates by @steve-chavez in #2849
• Fix unused index on jsonb/jsonb arrow filter and order (/bets?data->>contractId=eq.1 and /bets?order=data->>contractId) by @steve-chavez in #2594
• Fix character and bit columns with fixed length not inserting/updating properly by @laurenceisla in #2861
  • Fixes the error "value too long for type character(1)" when the char length of the column was bigger than one.
• Fix null filtering on embedded resource when using a column name equal to the relation name by @steve-chavez in #2862
• Fix function parameters of type character and bit not ignoring length by @laurenceisla in #1586
  • Fixes the error "value too long for type character(1)" when the char length of the parameter was bigger than one.
• Fix error when a function returns RECORD or SET OF RECORD by @laurenceisla in #2881

Misc

• Fix compilation on Ubuntu by being compatible with GHC 9.0.2 by @steve-chavez in #2834

Deprecated

Resource Embedding

• Deprecate resource embedding target disambiguation by @steve-chavez in #2863
  • The /table?select=*,other!fk(*) must be used to disambiguate
  • The server aids in choosing the !fk by sending a hint on the error whenever an ambiguous request happens.

New Contributors

• @taimoorzaeem made their first contribution in https://github.com/PostgREST/postgrest/pull/2873

Full Changelog: https://github.com/PostgREST/postgrest/compare/v11.1.0...v11.2.0