-
Notifications
You must be signed in to change notification settings - Fork 16
/
postgrest.cli.txt
717 lines (535 loc) · 36.3 KB
/
postgrest.cli.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
POSTGREST
VERSION ==> #11.2.2
#Proxies postgres server to expose it as a REST API
Server: postgrest/X.Y.Z [S] #
SUMMARY ==> #Config: file, ENVVAR, custom logic, reload
#Connection: status check, pool
#Roles: schemas, authenticator, anonymous, user/JWT
#MIMEs: JSON, urlencoded, CSV, GeoJSON, BYTEA, XML, raw
#Request: UNKNOWN, JSON body, VARR, virtual COL
#Errors: sqlstate|sqlerrm|details|hint, status code
#Routes: GET|HEAD|POST|PATCH|PUT|DELETE, CORS, write return, upsert, RPC
#Filter: VARR, not|or|and, any|all, = <> < <= > >=, in, is distinct from, is, [i]like, ~[*], @> <@ && << >> &< &> -|-, @@
#Sort
#Pagination: offset, limit, total
#Select: *, VARR, alias, cast, write body
#Populate: 1|n-1|n, [M]VIEW|FUNC, deep, spread, virtual COL, inner join
#Transaction: read only, dry run
#Explain
#Custom logic: HTTP request|response params
#OpenAPI
#Logging
#Client
/=+===============================+=\
/ : : \
)==: CONFIG :==(
\ :_______________________________: /
\=+===============================+=/
CONF #Either:
# - postgrest CONF_PATH: INI format
# - ENVVAR PGRST_*
# - TCONF.pgrst.*, but not for:
# - CONF that cannot be reloaded
# - db-config, db-channel*, app.settings.*
postgrest --dump-config #Print CONF
SCONF|PCONF #Postgres's SCONF|PCONF, not postgrest
CONF.db-pre-config #"SCHEMA.FUNC" called as FUNC() on server start
#Meant to set TCONF.pgrst.*
CONF.db-config #BOOL (def: true). Enable CONF.db-pre-config
SIGUSR1
notify "pgrst", 'reload schema' #Reloads DDL, which is cached at server start
SIGUSR2 #Reloads CONF
notify "pgrst", 'reload config' #Cannot reload:
# - db-uri, admin-server-port, db-pool-*, server-*
# - log-level, db-tx-end
CONF.db-channel-enabled #BOOL (def: true). Enable `notify "pgrst"`
CONF.db-channel #"CHANNEL" (def: "pgrst") to override name
/=+===============================+=\
/ : : \
)==: SERVER :==(
\ :_______________________________: /
\=+===============================+=/
postgrest/postgrest #Docker image
#CLI binary also available
postgrest #Start server
CONF.server-host #STR. Host of the postgrest server. Can also be:
# - '!4' (def), '!6': any IPv4|6
# - '*4', '*6': any IP, with IPv4|6 preferred
# - '*': any IP
CONF.server-port #NUM (def: 3000). Port of the postgrest server
CONF.server-unix-socket #'PATH' to Unix socket of the postgrest server
CONF.server-unix-socket-mode #STR (def: '600'). Permissions of Unix socket
CONF.admin-server-port #ADMIN_PORT (def: none)
GET HOST:ADMIN_PORT/live #200, empty response, if postgrest server running. Meant for status check
GET HOST:ADMIN_PORT/ready #Same but also checks connection with Postgres database, and SCHEMA cache
/=+===============================+=\
/ : : \
)==: CLIENT :==(
\ :_______________________________: /
\=+===============================+=/
@supabase/postgrest-js #Client for Postgrest for Node.js, browsers, Deno
#Can also use HTTP requests directly instead, as documented here too
#Version 1.8.5
new PostgrestClient #CLIENT
('URL'[, POPTS]) #URL is postgrest server
CLIENT.*(...)->CLIENT #Most methods return CLIENT
#Except: csv|geojson, single|maybeSingle, throwOnError, explain
#First method must be CLIENT.rpc|from(...)
await CLIENT #RES
POPTS.fetch #Def: cross-fetch
#To support Deno, must pass global `fetch`
CLIENT.abortSignal(ABORT_SIGNAL) #
POPTS.headers #OBJ. HTTP request headers
X-Client-Info:
postgres-js/X.Y.Z [C] #
SUCCESS_RES.data #OBJ[_ARR]
#STR with non-JSON MIME
CLIENT.returns<TYPE>() #Overrides return TypeScript TYPE
/=+===============================+=\
/ : : \
)==: DATABASE CONNECTION :==(
\ :_______________________________: /
\=+===============================+=/
CONF.db-uri #'LIBPQ' connection (def: 'postgresql://') to the postgres server
#Also indicates DATABASE to use
#Can be '@PATH' to file
LIBPQ.keepalives_interval #Uses 75s by default
LIBPQ.keepalives_count #Uses 9 by default
LIBPQ.keepalives_idle #Uses 2h by default
Retry-After: NUM [S] #Set if disconnected from postgres server, with 503
#Also tries to reconnect every 30s
CONF.db-pool #Max NUM (def: 10) of sockets to keep in pool
#Must be <= PCONF.max_connections
CONF.db-pool-acquisition-timeout #NUM (def: 10) of secs to wait for a socket in pool
CONF.db-pool-max-idletime #NUM (def: 30s) of secs to keep unused sockets in pool
CONF.db-pool-max-lifetime #NUM (def: 30min) of secs to keep sockets in pool (used or not)
CONF.db-prepared-statements #BOOL (def: true). Create PREPs under-the-hood, for performance
#Must disable if using an external connection pooler (like pgBouncer)
# - can only pool sessions, not statements
/=+===============================+=\
/ : : \
)==: SCHEMA :==(
\ :_______________________________: /
\=+===============================+=/
CONF.db-schemas #"SCHEMA,..." (def: "public") available in URL routes
Accept-Profile: SCHEMA [C] #Specifies SCHEMA, when CONF.db-schemas has multiple ones
POPTS.schema #Def: CONF.db-schemas[0]
CLIENT.schema('SCHEMA') #Always added to SCONF.search_path
#For GET|HEAD
Content-Profile: SCHEMA [C]
POPTS.schema
CLIENT.schema('SCHEMA') #Same but for POST|PATCH|PUT|DELETE
CONF.db-extra-search-path #"SCHEMA" (def: "public") to prepend to SCONF.search_path
/=+===============================+=\
/ : : \
)==: ROLE :==(
\ :_______________________________: /
\=+===============================+=/
ROLE #Clients use (in order):
# - user ROLE, if set by Authorization [C]
# - anonymous ROLE, if set in CONF
# - authenticator ROLE
AUTHENTICATOR ROLE ==> #ROLE set by CONF.db-uri, i.e. initial session ROLE
#Switches to user|anonymous ROLE using `set role 'ROLE'`
# - i.e. must be a member of it
# - also means other ROLEs can be `nologin`
#Should itself have low PRIVILEGEs
# - e.g. noinherit, nocreatedb, nocreaterole, nosuperuser
#But should have `login` PRIVILEGE
CONF.db-anon-role #"ROLE". "Anonymous ROLE" to use for unauthenticated clients
Authorization: Bearer JWT [C]
WWW-Authenticate: Bearer [S] #Authentication as a user ROLE
JWT.ENVLOP.alg #Must be 'HS256'
JWT.PAYLOAD.role #"ROLE" to authenticate with
#Def: CONF.db-anon-role
JWT.PAYLOAD.exp #DATE_NUM
CONF.jwt-secret #STR (def: ''). JWT private key
#Can be '@PATH' to a file
#Min 32 chars
#Can also be 'JSON' of JWK|JWKS
CONF.jwt-secret-is-base64 #BOOL (def: false)
CONF.jwt-aud #STR (def: ''). Require JWT.PAYLOAD.aud to match
CONF.jwt-role-claim-key #'.VARR' (def: '.role'). Customize attribute name of JWT.PAYLOAD.role
TCONF.request.jwt.claims #'OBJ_JSON'|null of JWT.PAYLOAD.*
current_role
TCONF.role #'ROLE'
@dthyresson/
prisma-extension-supabase-rls ##Version 2023-02-14
useSupabaseRowLevelSecurity ##Runs `select set_config(request.jwt.claims, OBJ_JSON, true)` before each CMODEL.METHOD(...)
([OPTS])->EXTENSION ##I.e. sets JWT.PAYLOAD.* for current query only
OPTS.claimsFn()->OBJ ##OBJ to set (def: {})
OPTS.logging ##BOOL (def: false). On exception thrown by either set_config() or CMODEL.METHOD(...), print it
OPTS.policyError ##ERROR (def: new Error('Not authorized.')) to throw on such exception
/=+===============================+=\
/ : : \
)==: MIMES :==(
\ :_______________________________: /
\=+===============================+=/
Accept|Content-Type:
application/json [C] #JSON
Accept|Content-Type: application/
x-www-form-urlencoded [C] #urlencoded
Accept|Content-Type: text/csv [C] #CSV
CLIENT.csv() #Empty fields are null
Accept: application/geo+json [C]
CLIENT.geojson() #GeoJSON for geometry TYPEs
Accept: application/
vnd.pgrst.object+json [C] #Return response as OBJ (using ARR[0]) instead of OBJ_ARR
CLIENT.single() #406 if OBJ_ARR.length 0
CLIENT.maybeSingle() #Same but, if empty response, sets SUCCESS_RES.data null instead of failing
Content-Type: text/plain [C] #Can be used to call /rpc/FUNC with a single positional STR argument, i.e. FUNC(STR)
Content-Type:
application/octet-stream [C] #Same with BYTEA
Content-Type: application/json [C]#Same with JSON[B]
Content-Type: text/xml [C] #Same with XML
Prefer: params=single-object [C] #Can be used to call /rpc/FUNC with a single JSON argument, i.e. FUNC(JSON)
CONF.raw-media-types #'MIME,...' (def: '').
#Prevents converting to JSON when using `Accept: MIME [C]` with either:
# - /rpc/FUNC
# - ?select on a BYTEA COL
Transfer-Encoding: chunked [S] #Can be used
/=+===============================+=\
/ : : \
)==: REQUEST :==(
\ :_______________________________: /
\=+===============================+=/
URL ENCODING ==> #Must be used like any URL
?VAR=VAL #VAL is interpreted as UNKNOWN
#E.g. {VAL,...} for ARR, etc.
JSON REQUEST BODY ==> #Can contain:
# - NUM|BOOL|null: TYPE as is
# - ARR: TYPE[]
# - OBJ: JSON or ROW
# - STR: UNKNOWN, like ?VAR=VAL
VARR #Can use following
JSON[B]->[>]KEY #
VAL->[>]KEY #to_jsonb(VAL)->[>]KEY
#E.g. with ROW|ARR|HJSON
FUNC #"virtual COLs", i.e. SQL FUNC(ROW)
#Can be indexed too, as any other EXPR
#If `create function ... returns setof TYPE rows 1`, VAL not ARR
/=+===============================+=\
/ : : \
)==: ERROR :==(
\ :_______________________________: /
\=+===============================+=/
ERROR_RESP #Error response
ERROR_RESP.code #STR. sqlstate
#Errors from Postgrest (not Postgres) start with 'PGRST'
ERROR_RESP.message #STR. sqlerrm
ERROR_RESP.details #STR|null. pg_exception_detail
ERROR_RESP.hint #STR|null. pg_exception_hint
ERROR_RES.error #ERROR_RESP OBJ
CLIENT.throwOnError() #Throw ERROR_RESP OBJ instead of returning it as RES.error
ERROR STATUS CODE ==> #Set based on sqlstate. Main ones:
# - 300: ambiguous overloaded FUNC
# - 400: `raise` in FUNC (unless overridden), invalid query parameters
# - 401: authentication, or missing PRIVILEGE + anonymous ROLE
# - 403: authorization
# - 404: unknown TABLE|FUNC
# - 405: `read only` transaction
# - 406: unknown SCHEMA
# - 409: foreign key, unique CONSTRAINT
# - 413: too many COLs, ARGs, statements
# - 415: invalid Content-Type [C]
# - 416: invalid Range [C]
# - 500: invalid TCONF.response.headers
# - 503: connection issue, resources limit
# - 504: CONF.db-pool-acquisition-timeout
# - 400|500: others
#Can also set status code XXX with sqlstate 'PTXXX'
RES.status #NUM. HTTP status
RES.statusText #STR
/=+===============================+=\
/ : : \
)==: REST :==(
\ :_______________________________: /
\=+===============================+=/
CLIENT.from('TABLE') #GET|POST|PATCH|DELETE /TABLE
GET /TABLE #select [COL,...] from TABLE [where ...]
#Request body: empty
#Response: ROW_ARR
HEAD /TABLE #Same as GET, but empty response
OPTS.head true #As last OPTS of CLIENT.rpc|select()
POST /TABLE #insert into TABLE
CLIENT.insert(OBJ[_ARR]) #Request: ROW[_ARR]
#Response: empty
PATCH /TABLE #update TABLE [where ...]
CLIENT.update(OBJ) #Request: ROW
#Response: empty
PUT /TABLE #Like PATCH but forces single ROW update
DELETE /TABLE #delete from TABLE [where ...]
CLIENT.delete() #Request body: empty
#Response: empty
OPTIONS ... #Can be used for CORS
Access-Control-Allow-Origin: * [S]
Allow: OPTIONS,GET,HEAD,POST,
PATCH,DELETE [S] #
Prefer: return=STR [C] #Whether to return:
Preference-Applied: return=STR [S]# - 'none': nothing
# - 'minimal': only updated|deleted ROWs
# - 'headers-only': primary key in Location [S]
# - 'representation': updated|deleted ROW_ARR in body
# - can use ?select
#With POST|PATCH|DELETE
CLIENT.select('...') #With POST|PATCH|DELETE, also does: Prefer: return=representation [C]
Accept: application/
vnd.pgrst.array|object+json
;nulls=stripped [C] #Omit `null` values in response
Prefer: #BOOL (def: false). insert on conflict do nothing
resolution=ignore-duplicates [C] #With POST
OPTS.ignoreDuplicates #As last OPTS to CLIENT.upsert()
Prefer:
resolution=merge-duplicates [C] #insert on conflict do update set, i.e. upserts
CLIENT.upsert(OBJ[_ARR]) #With POST
?on_conflict=VARR #'VARR'. Allow Prefer: resolution=merge-duplicates to work on COLs with `unique` CONSTRAINT, not only primary key
OPTS.onConflict #As last OPTS to CLIENT.upsert()
Prefer: missing=default [C] #BOOL (def: false). Missing values are `default` instead of null
OPTS.defaultToNull #With POST|PATCH
#As last OPTS of CLIENT.insert|upsert()
/=+===============================+=\
/ : : \
)==: RPC :==(
\ :_______________________________: /
\=+===============================+=/
POST /rpc/FUNC #Call SQL FUNC(...)
CLIENT.rpc('FUNC'[, OBJ]) #Request: OBJ
#Response: return value
#Only if FUNC has named parameters
#Variadic ARGs can be called by their name
#If FUNC()->ROW_SET, can use same query variables as GET /TABLE (?COL, etc.)
#Overloaded FUNCs are supported if their arguments differ by name or arity (not only by TYPE)
GET|HEAD /rpc/FUNC #Same using query variables ?VAR=VAL,... and with a `read only` transaction
#Variadic ARGs must be repeated
/=+===============================+=\
/ : : \
)==: FILTER :==(
\ :_______________________________: /
\=+===============================+=/
?COL=OP.VAL
CLIENT.OP('COL', VAL) #where COL OP VAL [and ...]
CLIENT.filter #With GET|PATCH|DELETE
('COL', '[not.]OP', VAL) #VAL can be ARR
?VARR=... #where VARR ...
?COL=not.OP.VAL
CLIENT.not('COL', 'OP', VAL) #not
?or=(COL.OP.VAL,...)
CLIENT.or('...')
?and=(COL.OP.VAL,...) #or|and. Can be nested
?COL=OP(any).ARR #OP any (ARR)
?COL=OP(all).ARR #OP all (ARR)
CLIENT.OPAnyOf('COL', ARR)
CLIENT.OPAllOf('COL', ARR) #Same but only for [i]like
?COL=eq.VAL #= VAL
CLIENT.match({ COL: VAL, ... }) #COL = VAL and ...
?COL=neq.VAL #<> VAL
?COL=gt.VAL #> VAL
?COL=gte.VAL #>= VAL
?COL=lt.VAL #< VAL
?COL=lte.VAL #<= VAL
?COL=in.(VAL,...) #in (VAL,...)
?COL=isdistinct.VAL #is distinct from VAL
?COL=is.null|true|false|unknown #is null|true|false|unknown
?COL=like.GLOB #like GLOB. Can use * instead of % (to make it URL-friendlier)
?COL=ilike.GLOB #ilike GLOB. Can also use *
?COL=match.REGEXP #~ REGEXP
?COL=imatch.REGEXP #~* REGEXP
?COL=cs.VAL
CLIENT.contains('COL', VAL) #@> VAL
?COL=cd.VAL
CLIENT.containedBy('COL', VAL) #<@ VAL
?COL=ov.VAL
CLIENT.overlaps('COL', VAL) #&& VAL
?COL=sl.VAL
CLIENT.rangeLt('COL', VAL) #<< VAL
?COL=sr.VAL
CLIENT.rangeGt('COL', VAL) #>> VAL
?COL=nxr.VAL
CLIENT.rangeLte('COL', VAL) #&< VAL
?COL=nxl.VAL
CLIENT.rangeGte('COL', VAL) #&> VAL
?COL=adj.VAL
CLIENT.rangeAdjacent('COL', VAL) #-|- VAL
?COL=fts.STR #@@ to_tsquery(STR)
?COL=plfts.STR #@@ plainto_tsquery(STR)
?COL=phfts.STR #@@ phraseto_tsquery(STR)
?COL=wfts.STR #@@ websearch_to_tsquery(STR)
?COL=*fts(REGCONFIG).STR #@@ *to_tsquery(REGCONFIG, STR)
CLIENT.textSearch #Like ?COL=*fts.VAL
('COL', VAL[, OPTS]) #OPTS: type 'plain|phrase|webseearch', config 'REGCONFIG'
/=+===============================+=\
/ : : \
)==: SORT :==(
\ :_______________________________: /
\=+===============================+=/
?order=COL,... #order by COL,...
CLIENT.order(STR[, OPTS]) #With GET
?order=VARR #order by VARR
?order=COL.asc|desc
OPTS.ascending #BOOL (def: false). order by COL asc|desc
?order=COL.nullsfirst|nullslast
OPTS.nullsFirst #BOOL (def: false). order by COL nullsfirst|nullslast
/=+===============================+=\
/ : : \
)==: PAGINATION :==(
\ :_______________________________: /
\=+===============================+=/
PAGINATION METHODS ==> #Either:
# - GET
# - PATCH|DELETE:
# - limits the number of updated|deleted ROWs
# - uses: with (select ... offset|limit) update|delete ...
?offset=NUM #SQL `offset`
?limit=NUM
CLIENT.limit(NUM) #SQL `limit`
Range: NUM-[NUM2] [C]
CLIENT.range(NUM, NUM2) #SQL `offset` + `limit`
Range-Unit: STR [C] #Def: 'items'
CONF.db-max-rows #Max NUM (def: unlimited) for `limit`
Content-Range: NUM-NUM2/NUM3 [S] #
SUCCESS_RES.count #NUM3|null
Prefer: count=STR [C] #STR. Set NUM3 total amount of ROWs
OPTS.count #As last OPTS of CLIENT.rpc|select|insert|upsert|update|delete()
#Can be:
# - 'none' (def): always '*'
# - 'exact': use count(*)
# - 'planned': use `explain`
# - 'estimated': 'exact' if < CONF.db-max-rows, 'planned' if >=
/=+===============================+=\
/ : : \
)==: SELECT :==(
\ :_______________________________: /
\=+===============================+=/
?select=COL,... #select COL,...
#With GET
CLIENT.select('COL,...') #Same. Must be after any CLIENT.OP(...)
#Ignores whitespaces|newlines except "-quoted
?select=* #select *
?select=VARR #select VARR
?select=COL_ALIAS:COL #select COL as "COL_ALIAS"
?select=COL::TYPE #select COL::TYPE (explicit cast)
JSON CAST ==> #json implicit cast used in request|response body
#text implicit cast used in query variables
?columns=COL,... #Like ?select but for POST, filtering request body
/=+===============================+=\
/ : : \
)==: POPULATE :==(
\ :_______________________________: /
\=+===============================+=/
?select=TABLE2(COL,...) #select TABLE2.COL,... from TABLE left outer join ...
# - n-1 (OBJ):
# - TABLE2 on TABLE2.PRIMARY_KEY = TABLE.FOREIGN_KEY
# - i.e. current TABLE has foreign key
# - 1-n (OBJ_ARR):
# - TABLE2 on TABLE2.FOREIGN_KEY = TABLE.PRIMARY_KEY
# - i.e. other TABLE2 has foreign key to current TABLE
# - 1-1 (OBJ):
# - same as 1-n but FOREIGN_KEY has a unique or primary key CONSTRAINT
# - n-n (OBJ_ARR):
# - TABLE3 on TABLE3.FOREIGN_KEY = TABLE.PRIMARY_KEY join TABLE2 on TABLE3.FOREIGN_KEY2 = TABLE2.PRIMARY_KEY
# - i.e. TABLE3 has foreign key to both TABLE[2]
#Works also on [M]VIEWs or FUNCs when they return the foreign keys as is
# - e.g. `create view as select FOREIGN_KEY from TABLE`
?select=CONSTRAINT(...) #Same but using the foreign key "CONSTRAINT" name instead of the referenced "TABLE2" name
?select=TABLE2!COL(...) #Same but specifies the referenced COL. Only needed to avoid ambiguity in some edge cases
?select=TABLE2(TABLE3(...),...) #Can be deep
?select=...TABLE2(...) #`...` spreads to the parent OBJ, i.e. like { ...OBJ } in JavaScript
?select=FUNC(COL,...) #Virtual COL, i.e. FUNC(ROW)->ROW2_SET
?select=TABLE2!inner(...) #select ... inner join ...
#I.e. exclude TABLE ROWs where TABLE.TABLE2 is empty ARR
# - i.e. did not join to anything
# - including to a ?TABLE2.COL filter
?TABLE2=[not.]is.null #Similar result than ?select=TABLE2!inner, since an empty ARR `is null`
?TABLE2.COL=OP.VAL
?TABLE2.and|or=(COL.OP.VAL,...)
?TABLE2.order=...
?order=TABLE2(COL)
?TABLE2.offset|limit=NUM #Same, but for a joined TABLE2. Can be deep
OPTS.foreignTable #Same, when using CLIENT.or|order|limit|range()
/=+===============================+=\
/ : : \
)==: TRANSACTION :==(
\ :_______________________________: /
\=+===============================+=/
TRANSACTION ==> #Each request runs in a transaction
begin readonly #Used when either:
# - GET|HEAD
# - POST /rpc/FUNC on a stable|immutable FUNC
Prefer: tx=commit|rollback [C] #Make the request's transaction:
CLIENT.rollback() # - commit at the end (def)
# - rollback: for a dry run
# - including with `explain`
CONF.db-tx-end #Whether to allow using Prefer: tx=commit|rollback [C], among:
# - 'commit' (def): does not allow
# - 'commit-allow-override': allows
# - 'rollback[-allow-override]': same but defaults to always rolling back
/=+===============================+=\
/ : : \
)==: EXPLAIN :==(
\ :_______________________________: /
\=+===============================+=/
Accept: application/
vnd.pgrst.plan[+text] [C]
CLIENT.explain([OPTS]) #Runs `explain` on GET
Accept: application/
vnd.pgrst.plan+json [C]
OPTS.format #'json|text'. `explain` format
Accept: application/
vnd.pgrst.plan; for="MIME" [C] #Specifies Content-Type: MIME [C] used by `explain`
Accept: application/
vnd.pgrst.plan;
options=OPT|... [C] #BOOL (def: false)
OPTS.OPT #`explain` OPTs: analyze, verbose, settings, buffers, wal
CONF.db-plan-enabled #BOOL (def: false). Allows application/vnd.pgrst.plan[+json]
/=+===============================+=\
/ : : \
)==: CUSTOM LOGIC :==(
\ :_______________________________: /
\=+===============================+=/
CONF.db-pre-request #"SCHEMA.FUNC" called after any authentication, but before request
#No arguments nor return value
#Meant to throw on authorization error, set TCONF.response.*, etc.
TCONF.* #Those are Postgres SCONF.* available inside FUNCs like /rpc/FUNC, CONF.db-pre-request, etc.
TCONF.app.settings.* #Set using CONF.app.settings.*
TCONF.request.path #'/PATH' of HTTP request
TCONF.request.method #'GET|...' HTTP method
TCONF.request.headers #'OBJ_JSON' of HTTP headers
TCONF.request.cookies #'OBJ_JSON' of Cookie [C]
TCONF.response.status #'NUM' of HTTP response, to set
TCONF.response.headers #'OBJ_ARR' of HTTP headers, to set. Each OBJ has a single property
/=+===============================+=\
/ : : \
)==: OPENAPI :==(
\ :_______________________________: /
\=+===============================+=/
CONF.openapi-mode #Generates a URL which responds with an OpenAPI 2.0 JSON definition.
#Can be:
# - 'disabled'
# - 'follow-provileges' (def): enabled
# - 'ignore-privileges': same but does not take into account ROLE PRIVILEGEs
#Includes COMMENTs on ENTITYs (e.g. SCHEMA, TABLE, COL)
# - can include newlines
# - first line on SCHEMA is used as API title
CONF.openapi-server-proxy-uri #External 'URL' (def: '', i.e. root) of the API
CONF.openapi-security-active #BOOL (def: false). Include `security[Definitions]` in response
CONF.db-root-spec #'FUNC' (def: '') to manually set OpenAPI response
#Must be a PGSQL FUNC()->JSON calling: openapi json = '{...}'
/=+===============================+=\
/ : : \
)==: LOG/DEBUG :==(
\ :_______________________________: /
\=+===============================+=/
CONF.log-level #For Postgrest messages, among:
# - 'crit': connection errors
# - 'error' (def): also 5**
# - 'warn': also 4**
# - 'info': also 2**|3**
#Logs to stdout requests info: IP, ROLE, DATE, METHOD, PATH, STATUS, USER_AGENT
#Logs to stderr server info
#Also sets SCONF.client_min_messages
CONF.server-trace-header #'HEADER' (def: ''). Request header to repeat in response, e.g. 'X-Request-Id'
Content-Location:
/TABLE[?VAR=VAL,...] [S] #
Preference-Applied: ... [S] #Set when Prefer: ... [C] was used