REST API for any Postgres database
jwt-cache-max-lifetime
- @laurenceislaContent-Type: application/octet-stream
by default instead of Content-Type: application/json
- @steve-chavezAggregate Functions by @timabdulla in https://github.com/PostgREST/postgrest/pull/2925
Media Type Handlers by @steve-chavez in https://github.com/PostgREST/postgrest/pull/2825, https://github.com/PostgREST/postgrest/pull/3076
Strict/Lenient handling for Prefer header by @taimoorzaeem in https://github.com/PostgREST/postgrest/pull/2969
Timezone for Prefer header by @taimoorzaeem in https://github.com/PostgREST/postgrest/pull/3024
Config to specify CORS origins by @taimoorzaeem in https://github.com/PostgREST/postgrest/pull/2986
Server-Timing response header by @taimoorzaeem, @develop7, @laurenceisla in #2937, #2983, #3064, #3068
Log all internal database errors to stderr by @laurenceisla in https://github.com/PostgREST/postgrest/pull/3067
Function Settings by @taimoorzaeem in https://github.com/PostgREST/postgrest/pull/3056
Settings with privileged context by @steve-chavez in https://github.com/PostgREST/postgrest/pull/3058
Remove unnecessary count on RPC returning single row by @steve-chavez in https://github.com/PostgREST/postgrest/pull/3015
HTTP status responses for upserts by @taimoorzaeem in https://github.com/PostgREST/postgrest/pull/2926
PUT
returns 201
instead of 200
when rows are insertedPOST
with Prefer: resolution=merge-duplicates
returns 200
instead of 201
when no rows are insertedTransaction-Scoped Settings are now shown clearly in the Postgres logs by @laurenceisla in https://github.com/PostgREST/postgrest/pull/3032
set_config('pgrst.setting_name', $1)
instead of setconfig($1, $2)
app.settings.*
Display an actual TCP port app is bound to by @develop7 in https://github.com/PostgREST/postgrest/pull/3034
Fix Acquision Timeout errors logging to stderr when log-level=crit
by @laurenceisla in https://github.com/PostgREST/postgrest/pull/3067
application/octet-stream
, text/plain
, text/xml
builtin support for scalar results - @steve-chavez
application/openapi+json
media type for db-root-spec by @steve-chavez in #2825
application/openapi+json
and a Handler Function now.Full Changelog: https://github.com/PostgREST/postgrest/compare/v11.2.2...v12.0.0
v11.2.1 addresses bugs introduced in v11.2.0. Also PostgreSQL 16 is now tested and confirmed to work.
application/vnd.pgrst.array
not accepted as a valid mediatype - @taimoorzaeemPreference-Applied
with Prefer: tx=commit
when transaction is rollbacked - @steve-chavezcount=exact
not being included in Preference-Applied
- @steve-chavezNOTIFY
not working on Windows - @diogob, @laurenceislaNULL
value in any of the selected fields when doing null filtering on them - @laurenceislaPrefer: count=<type>
and doing null filtering on embedded resources - @laurenceisladefault_transaction_isolation
unnecessarily - @steve-chavezFull Changelog: https://github.com/PostgREST/postgrest/compare/v11.2.0...v11.2.1
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.
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 precisionPOST/PUT/PATCH
by configuring the reverse transformation(s)select
parameter to select only a subset of columns, embedding through complex joins, renaming fields, with views and computed columnsRETURNING
operations, such as requesting the full body in a POST/PUT/PATCH with Prefer: return=representation
GENERATED ALWAYS
columns)IN
predicates); data representations are not implemented for RPC--version
option which prints the version information by @laurenceisla in #2856details
field of the singular error response by @taimoorzaeem in #1655Prefer: return=*
now reply with Preference-Applied headers by @taimoorzaeem in #740Prefer: missing=default
with DOMAIN default values by @steve-chavez in #2840/bets?data->>contractId=eq.1
and /bets?order=data->>contractId
) by @steve-chavez in #2594RECORD
or SET OF RECORD
by @laurenceisla in #2881/table?select=*,other!fk(*)
must be used to disambiguate!fk
by sending a hint
on the error whenever an ambiguous request happens.Full Changelog: https://github.com/PostgREST/postgrest/compare/v11.1.0...v11.2.0
db-pool-max-idletime
(default 30s).db-pool-timeout
of PostgREST 10.0.0.db-pool-timeout
is included.db-pre-config
(empty by default)db-channel-enabled
is false, start automatic connection recovery on a new request when pool connections are closed with pg_terminate_backend
- @steve-chavez
Prefer: missing=default
- @steve-chavez/projects?select=*,clients(*)&order=clients(name).desc.nullsfirst
/projects?select=*,...clients(client_name:name)
is.null
or not.is.null
on an embedded resource - @steve-chavez
!inner
, e.g. /projects?select=*,clients(*)&clients=not.is.null
/projects?select=*,clients(*)&clients=is.null
server-trace-header
for tracing HTTP requests. - @steve-chavez
db-root-spec
stable. - @steve-chavez
Prefer: missing=default
header - @steve-chavezIS DISTINCT FROM
using the isdistinct
operator, e.g. /people?alias=isdistinct.foo
any/all
modifiers on the eq,like,ilike,gt,gte,lt,lte,match,imatch
operators, e.g. /tbl?id=eq(any).{1,2,3}
- @steve-chavez
ALTER ROLE anon SET statement_timeout TO '5s'
will result in that statement_timeout
getting applied for that role.NOTIFY pgrst, 'reload config'
.default_transaction_isolation
- @steve-chavez
create function .. set default_transaction_isolation = 'repeatable read'
alter role .. set default_transaction_isolation = 'serializable'
get
path item for RPCs to the OpenAPI output - @laurenceislaPGRST204
: Column is not foundPGRST003
: Timed out when acquiring connection to dbRange
header on PATCH/DELETE
- @laurenceisla
"message": "syntax error at or near \"RETURNING\""
errororder
query parameter was presentPATCH
requests not recognizing embedded filters and using the top-level resource instead - @steve-chavezRange
header is now only considered on GET
requests and is ignored for any other method - @laurenceisla
limit/offset
query parameters for sub-rangesPUT
requests no longer return an error when this header is present (using limit/offset
still triggers the error)Prefer: params=multiple-objects
header. A function with a JSON array or object parameter should be used instead.db-pool-max-lifetime
(default 30m)db-pool-acquisition-timeout
is no longer optional and defaults to 10sdb-pool-acquisition-timeout
not logging to stderr when the timeout is reached - @steve-chavez