PostgREST 10.0.0

Features

XML/SOAP support for RPC

RPC now understands the text/xml media type, allowing SQL functions to send XML output(Accept: text/xml) and receive XML input(Content-Type: text/xml). This makes SOAP endpoints possible, check the Create a SOAP endpoint how-to and the Response Formats For Scalar Responses reference for more details.

GeoJSON support

GeoJSON is supported across the board(reads, writes, RPC) with the Accept: application/geo+json header, this depends on PostGIS from the versions 3.0.0 and up. The working with PostGIS section has an example to get you started.

Execution Plan

The execution plan of a request is now obtainable with the Accept: application/vnd.pgrst.plan header. The result can be in text or json formats and is compatible with EXPLAIN vizualizers like explain.depesz.com or explain.dalibo.com.

Resource Embedding

• A one-to-one relationship is now detected when a foreign key is unique.

• Using Computed relationships, you can add custom relationships or override automatically detected ones. This makes Resource Embedding possible on Foreign Data Wrappers and complex SQL views.

Horizontal/Vertical Filtering

• Accessing fields of a Composite type or elements of an Array type is now possible with the arrow operators(->, ->>) in the same way you would access a JSON type fields.

• Pattern Matching operators for POSIX regular expressions are now available: match and imatch, equivalent in PostgreSQL to ~ and ~* respectively.

Insertions/Updates

• limit can now affect the number of updated/deleted rows. See Limited Updates/Deletions.

OpenAPI

You can now activate the “Authorize” button in SwaggerUI by enabling the openapi-security-active configuration. Add your JWT token prepending Bearer to it and you’ll be able to request protected resources.

Administration

• Two health check endpoints are now exposed in a secondary port.

• Logging now shows the database user.

• It is now possible to execute PostgREST without specifying any configuration variable. The three that were mandatory on the previous versions, are no longer so.

  • If db-uri is not set, PostgREST will use the libpq environment variables for the database connection.

  • If db-schemas is not set, it will use the database public schema.

  • If db-anon-role is not set, it will not allow anonymous requests.

Error messages

• To increase consistency, all the errors messages are now normalized. The hint, details, code and message fields will always be present in the body, each one defaulting to a null value. In the same way, the errors that were raised with SQLSTATE now include the message and code in the body.

• To further clarify the source of an error, we now add a PGRST prefix to the error code of all the errors that are PostgREST-specific and don’t come from the database. These errors have unique codes that identify them and are documented in the PostgREST Error Codes section.

Documentation improvements

• Added a Working with PostgreSQL data types how-to, which contains explanations and examples on how to work with different PostgreSQL data types such as timestamps, ranges or PostGIS types, among others.

• Added in-database and environment variable settings for each configuration variable.

• Added the File Descriptors subsection.

• Added a reference page for Error documentation.

• Moved the Error Source and the HTTP Status Codes sections to the errors reference page.

• Moved the Casting type to custom JSON how-to to the Casting a Range to a JSON Object subsection.

• Removed direct links for PostgREST versions older than 8.0 from the versions menu.

• Removed the Embedding table from another schema how-to.

• Restructured the Resource Embedding section:

  • Added a One-to-many relationships and Many-to-one relationships subsections.

  • Renamed the Embedding through join tables subsection to Many-to-many relationships.

• Split up the Insertions/Updates section into Insertions and Updates.

Breaking changes

• Many-to-many relationships now require that foreign key columns be part of the join table composite key

  • This was needed to reduce Embedding Disambiguation errors in complex schemas(#2070).

  • For migrating to this version, the less invasive method is to use Computed relationships to replace the previous many-to-many relationships.

  • Otherwise you can change your join table primary key. For example with alter table permission_user drop constraint permission_user_pkey, add primary key (id, user_id, permission_id);

• Views now are not detected when embedding using Target Disambiguation.

  • This embedding form was easily made ambiguous whenever a new view was added(#2277).

  • For migrating to this version, you can use Computed relationships to replace the previous view relationships.

  • Hint Disambiguation works as usual on views.

• limit/offset now limits the affected rows on UPDATE/DELETE

  • Previously, limit/offset only limited the returned rows but not the actual updated rows(#2156)

• max-rows is no longer applied on POST, PATCH, PUT and DELETE returned rows

  • This was misleading because the affected rows were not really affected by max-rows, only the returned rows were limited(#2155)

• Return 204 No Content without Content-Type for RPCs returning VOID

  • Previously, those RPCs would return null as a body with Content-Type: application/json (#2001).

• Using Prefer: return=representation no longer returns a Location header

  • This reduces unnecessary computing for all insertions (#2312)

Bug fixes

• Return 204 No Content without Content-Type for PUT (#2058)

• Clarify error for failed schema cache load. (#2107)

  • From Database connection lost. Retrying the connection to Could not query the database for the schema cache. Retrying.

• Fix silently ignoring filter on a non-existent embedded resource (#1771)

• Remove functions, which are not callable due to unnamed arguments, from schema cache and OpenAPI output. (#2152)

• Fix accessing JSON array fields with -> and ->> in ?select= and ?order=. (#2145)

• Ignore max-rows on POST, PATCH, PUT and DELETE (#2155)

• Fix inferring a foreign key column as a primary key column on views (#2254)

• Restrict generated many-to-many relationships (#2070)

  • Only adds many-to-many relationships when a table has foreign keys to two other tables and these foreign key columns are part of the table’s primary key columns.

• Allow casting to types with underscores and numbers (e.g. select=oid_array::_int4) (#2278)

• Prevent views from breaking one-to-many/many-to-one embeds when using column or foreign key as target (#2277, #2238, #1643)

  • When using a column or foreign key as target for embedding (/tbl?select=*,col-or-fk(*)), only tables are now detected and views are not.

  • You can still use a column or an inferred foreign key on a view to embed a table (/view?select=*,col-or-fk(*))

• Increase the db-pool-timeout to 1 hour to prevent frequent high connection latency (#2317)

• The search path now correctly identifies schemas with uppercase and special characters in their names (regression) (#2341)

• “404 Not Found” on nested routes and “405 Method Not Allowed” errors no longer start an empty database transaction (#2364)

• Fix inaccurate result count when an inner embed was selected after a normal embed in the query string (#2342)

• OPTIONS requests no longer start an empty database transaction (#2376)

• Allow using columns with dollar sign ($) without double quoting in filters and select (#2395)

• Fix loop crash error on startup in PostgreSQL 15 beta 3. Log: "UNION types \"char\" and text cannot be matched." (#2410)

• Fix race conditions managing database connection helper (#2397)

• Allow limit=0 in the request query to return an empty array (#2269)

Thanks

Big thanks from the PostgREST team to our sponsors!

• Evans Fernandes

• Jan Sommer

• Franz Gusenbauer

• Daniel Babiak

• Tsingson Qin

• Michel Pelletier

• Jay Hannah

• Robert Stolarz

• Nicholas DiBiase

• Christopher Reid

• Nathan Bouscal

• Daniel Rafaj

• David Fenko

• Remo Rechkemmer

• Severin Ibarluzea

• Tom Saleeba

• Pawel Tyll

If you like to join them please consider supporting PostgREST development.