Fixed

• #3149, Misleading "Starting PostgREST.." logs on schema cache reloading - @steve-chavez
• #3205, Fix wrong subquery error returning a status of 400 Bad Request - @steve-chavez
• #3224, Return status code 406 for non-accepted media type instead of code 415 - @wolfgangwalther
• #3160, Fix using select= query parameter for custom media type handlers - @wolfgangwalther
• #3361, Clarify PGRST204(column not found) error message - @steve-chavez
• #3373, Remove rejected mediatype application/vnd.pgrst.object+json from response - @taimoorzaeem
• #3418, Fix OpenAPI not tagging a FK column correctly on O2O relationships - @laurenceisla
• #3256, Fix wrong http status for pg error 42P17 infinite recursion - @taimoorzaeem

Added

• #2887, Add Preference max-affected to limit affected resources - @taimoorzaeem
• #3171, Add an ability to dump config via admin API - @SkyWriter
• #3061, Apply all function settings as transaction-scoped settings - @taimoorzaeem
• #3171, #3046, Log schema cache stats to stderr - @steve-chavez
• #3210, Dump schema cache through admin API - @taimoorzaeem
• #2676, Performance improvement on bulk json inserts, around 10% increase on requests per second by removing json_typeof from write queries - @steve-chavez
• #3214, Log connection pool events on log-level=info - @steve-chavez
• #3435, Add log-level=debug, for development purposes - @steve-chavez
• #1526, Add /metrics endpoint on admin server - @steve-chavez
  • Exposes connection pool metrics, schema cache metrics
• #3404, Show the failed MESSAGE or DETAIL in the details field of the PGRST121 (could not parse RAISE 'PGRST') error - @laurenceisla
• #3404, Show extra information in the PGRST121 (could not parse RAISE 'PGRST') error - @laurenceisla
  • Shows the failed MESSAGE or DETAIL in the details field
  • Shows the correct JSON format in the hints field
• #3340, Log when the LISTEN channel gets a notification - @steve-chavez
• #3184, Log full pg version to stderr on connection - @steve-chavez

Fixed

• #3149, Misleading "Starting PostgREST.." logs on schema cache reloading - @steve-chavez
• #2815, Build static executable with GSSAPI support - @wolfgangwalther
• #3205, Fix wrong subquery error returning a status of 400 Bad Request - @steve-chavez
• #3224, Return status code 406 for non-accepted media type instead of code 415 - @wolfgangwalther
• #3160, Fix using select= query parameter for custom media type handlers - @wolfgangwalther
• #3237, Dump media handlers and timezones with --dump-schema - @wolfgangwalther
• #3323, #3324, Don't hide error on LISTEN channel failure - @steve-chavez
• #3330, Incorrect admin server /ready response on slow schema cache loads - @steve-chavez
• #3345, Fix in-database configuration values not loading for pgrst.server_trace_header and pgrst.server_cors_allowed_origins - @laurenceisla
• #3361, Clarify the PGRST204 (column not found) error message - @steve-chavez
• #3373, Remove rejected mediatype application/vnd.pgrst.object+json from response - @taimoorzaeem
• #3418, Fix OpenAPI not tagging a FK column correctly on O2O relationships - @laurenceisla
• #3256, Fix wrong http status for pg error 42P17 infinite recursion - @taimoorzaeem
• #3404, Clarify the PGRST121 (could not parse RAISE 'PGRST') error message - @laurenceisla
• #3267, Fix wrong 503 Service Unavailable on pg error 53400 - @taimoorzaeem
• #2985, Fix not adding application_name on all connection strings - @steve-chavez
• #3424, Admin /live and /ready now differentiates a failure as 500 status - @steve-chavez
  • 503 status is still given when postgREST is in a recovering state
• #3478, Media Types are parsed case insensitively - @develop7

Deprecated

• Prefer: params=single-object is deprecated. Use a function with a single unnamed JSON parameter instead. - @steve-chavez

Documentation

• #3289, Add dark mode. Can be toggled by a button in the bottom right corner. - @laurenceisla

Fixed

• #3124, Fix table's media type handlers not working for all schemas - @steve-chavez
• #3126, Fix empty row on media type handler function - @steve-chavez

Fixed

• #3054, Fix not allowing special characters in JSON keys - @laurenceisla
• #2344, Replace JSON parser error with a clearer generic message - @develop7
• #3100, Add missing in-database configuration option for jwt-cache-max-lifetime - @laurenceisla
• #3089, The any media type handler now sets Content-Type: application/octet-stream by default instead of Content-Type: application/json - @steve-chavez

Features

API

• Aggregate Functions by @timabdulla in #2925

• Media Type Handlers by @steve-chavez in #2825, #3076

  • New how-to for Providing HTML Content Using Htmx by @laurenceisla

• Strict/Lenient handling for Prefer header by @taimoorzaeem in #2969

• Timezone for Prefer header by @taimoorzaeem in #3024

• Config to specify CORS origins by @taimoorzaeem in #2986

Authentication

• JWT Caching by @taimoorzaeem in #2928

Connection Pool

• Configuration for disabling Automatic Recovery by @taimoorzaeem in #2900.

Errors

• Add HTTP Headers with RAISE by @taimoorzaeem in #2911

Observability

• Server-Timing response header by @taimoorzaeem, @develop7, @laurenceisla in #2937, #2983, #3064, #3068

• Log all internal database errors to stderr by @laurenceisla in #3067

Transactions

• Function Settings by @taimoorzaeem in #3056

• Settings with privileged context by @steve-chavez in #3058

Fixes

API

• Remove unnecessary count on RPC returning single row by @steve-chavez in #3015

• HTTP status responses for upserts by @taimoorzaeem in #2926

  • PUT returns 201 instead of 200 when rows are inserted
  • POST with Prefer: resolution=merge-duplicates returns 200 instead of 201 when no rows are inserted

Observability

• Transaction-Scoped Settings are now shown clearly in the Postgres logs by @laurenceisla in #3032

  • Shows set_config('pgrst.setting_name', $1) instead of setconfig($1, $2)
  • Does not apply to role settings and app.settings.*

• Display an actual TCP port app is bound to by @develop7 in #3034

• Fix Acquision Timeout errors logging to stderr when log-level=crit by @laurenceisla in #3067

Breaking changes

API

• Removed raw-media-types config by @steve-chavez in #2825
  • Can be done more granularly with Media Type Handlers now.
• Removed application/octet-stream, text/plain, text/xml builtin support for scalar results - @steve-chavez
  • Can be done with more flexibility with a Handler Function now.
• Removed default application/openapi+json media type for db-root-spec by @steve-chavez in #2825
  • Can be done by adding a media type for application/openapi+json and a Handler Function now.

Transactions

• Removed db-use-legacy-gucs by @laurenceisla in #3032
  • All PostgreSQL versions now use GUCs in JSON format for Headers, Cookies and JWT claims.

New Contributors

• @develop7 made their first contribution in #2946
• @omahs made their first contribution in #3044
• @timabdulla made their first contribution in #2925

Full Changelog: v11.2.2...v12.0.0

Fixed

• #2824, Fix regression by reverting fix that returned 206 when first position = length in a Range header - @laurenceisla, @Strengthless

Maintenance Release

v11.2.1 addresses bugs introduced in v11.2.0. Also PostgreSQL 16 is now tested and confirmed to work.

What is fixed

API

• #2899, Fix application/vnd.pgrst.array not accepted as a valid mediatype - @taimoorzaeem
• #2915, Fix duplicate headers in response - @taimoorzaeem
• #2824, Fix range request with first position same as length return status 206 - @taimoorzaeem
• #2939, Fix wrong Preference-Applied with Prefer: tx=commit when transaction is rollbacked - @steve-chavez
• #2939, Fix count=exact not being included in Preference-Applied - @steve-chavez
• #2929, Fix arrow filtering on RPC returning dynamic TABLE with composite type - @steve-chavez

Schema Cache

• #2524, Fix schema cache and configuration reloading with NOTIFY not working on Windows - @diogob, @laurenceisla

Resource Embedding

• #2800, Fix not including to-one embed resources that had a NULL value in any of the selected fields when doing null filtering on them - @laurenceisla
• #2846, Fix error when requesting Prefer: count=<type> and doing null filtering on embedded resources - @laurenceisla
• #2963, Fix RPCs not embedding correctly when using overloaded functions for computed relationships - @laurenceisla

Authentication

• #2959, Fix setting default_transaction_isolation unnecessarily - @steve-chavez

Connection Pool

• #2970, Fix regression that rejects URI connection strings with certain unescaped characters in the password - @laurenceisla, @steve-chavez

Full Changelog: v11.2.0...v11.2.1

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 #2873

Full Changelog: v11.1.0...v11.2.0

Added

• #2786, Limit idle postgresql connection lifetime - @robx
  • New option db-pool-max-idletime (default 30s).
  • This is equivalent to the old option db-pool-timeout of PostgREST 10.0.0.
  • A config alias for db-pool-timeout is included.
• #2703, Add pre-config function - @steve-chavez
  • New config option db-pre-config(empty by default)
  • Allows using the in-database configuration without SUPERUSER
• #2781, When db-channel-enabled is false, start automatic connection recovery on a new request when pool connections are closed with pg_terminate_backend - @steve-chavez
  • Mitigates the lack of LISTEN/NOTIFY for schema cache reloading on read replicas.

Fixed

• #2791, Fix dropping schema cache reload notifications - @steve-chavez
• #2801, Stop retrying connection when "no password supplied" - @steve-chavez

Fixed

• #2762, Fixes "permission denied for schema" error during schema cache load - @steve-chavez
• #2756, Fix bad error message on generated columns when using Prefer: missing=default - @steve-chavez
• #1139, Allow a 30 second skew for JWT validation - @steve-chavez
  • It used to be 1 second, which was too strict