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 Scalar Function Response Format 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

Horizontal/Vertical Filtering

Insertions/Updates

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.

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

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 Foreign Key Joins on Multiple Foreign Key Relationships 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 Foreign Key Joins on Multiple Foreign Key Relationships.

  • 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!

../_images/cybertec-new.png ../_images/2ndquadrant.png ../_images/retool.png ../_images/gnuhost.png ../_images/supabase.png ../_images/oblivious.jpg
  • 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.