PostgREST

https://docs.postgrest.org/en/v14/
Admin
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful...
Tokens:272,270
Snippets:2,119
Trust Score:9.5
Update:1 week ago
Show doc for...
Tokens:
Raw
### PostgREST startup output

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Example output when PostgREST starts successfully, showing version and listening port.

```text
Starting PostgREST 12.0.2...
Successfully connected to PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
API server listening on port 3000
```

--------------------------------

### Setup PostGIS and sample table for TWKB example

Source: https://docs.postgrest.org/en/v14/_sources/references/api/media_type_handlers.rst.txt

Creates the PostGIS extension and a table 'lines' with a geometry column. This setup is required for the TWKB handler examples that follow.

```postgres
create extension postgis;

create table lines (
  id   int primary key
, name text
, geom geometry(LINESTRING, 4326)
);

insert into lines values (1, 'line-1', 'LINESTRING(1 1,5 5)'::geometry), (2, 'line-2', 'LINESTRING(2 2,6 6)'::geometry);
```

--------------------------------

### Show example configuration with postgrest --example

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Shows example configuration settings.

```bash
$ postgrest --example
```

--------------------------------

### postgrest --example

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Shows example configuration settings.

```APIDOC
## postgrest --example

### Description
Shows example configuration settings.

### Method
CLI

### Endpoint
postgrest --example

### Parameters
None

### Request Example
```bash
$ postgrest --example
```

### Response
#### Success Response
Displays an example configuration file.

#### Response Example
```text
# Example configuration
...
```
```

--------------------------------

### Function inlining example with explain plan

Source: https://docs.postgrest.org/en/v14/_sources/references/api/functions.rst.txt

Create an inlinable function and get its explain plan when called with filters.

```postgres
create function getallprojects() returns setof projects
language sql stable
as $$
  select * from projects;
$$;
```

```bash
curl "http://localhost:3000/rpc/getallprojects?id=eq.1" \
  -H "Accept: application/vnd.pgrst.plan"
```

```postgres
Aggregate  (cost=8.18..8.20 rows=1 width=112)
  ->  Index Scan using projects_pkey on projects  (cost=0.15..8.17 rows=1 width=40)
        Index Cond: (id = 1)
```

--------------------------------

### Install PostgREST with nix-env from nixpkgs

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Installs PostgREST from nixpkgs using nix-env.

```bash
nix-env -i postgrest

```

--------------------------------

### Check PostgREST installation with postgrest --help

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Verifies that the PostgREST server is installed by displaying its help information.

```bash
postgrest --help
```

--------------------------------

### Install libpq on Ubuntu or Debian

Source: https://docs.postgrest.org/en/v14/tutorials/tut0.html

Installs libpq-dev to resolve the missing libpq.so.5 error on Ubuntu/Debian.

```bash
sudo apt-get install libpq-dev

```

--------------------------------

### Install PostgREST with FreeBSD ports

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Installs PostgREST from the official FreeBSD ports.

```bash
pkg install hs-postgrest

```

--------------------------------

### Build and install PostgREST binary with Stack

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Clones the PostgREST repository and builds the binary, installing it to /usr/local/bin. The --install-ghc flag is only needed for the first build and can be omitted in subsequent builds. If the build fails on systems with less than 1GB of memory, adding a swap file may help.

```bash
git clone https://github.com/PostgREST/postgrest.git
cd postgrest

# adjust local-bin-path to taste
stack build --install-ghc --copy-bins --local-bin-path /usr/local/bin

```

--------------------------------

### Install PostgreSQL on macOS with Homebrew

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Install PostgreSQL using Homebrew on macOS.

```bash
brew install postgresql
```

--------------------------------

### Enable and start PostgREST service

Source: https://docs.postgrest.org/en/v14/_sources/integrations/systemd.rst.txt

Commands to enable the service at boot and start it. Includes commented reload command.

```bash
systemctl enable postgrest
systemctl start postgrest

## For reloading the service
## systemctl restart postgrest
```

--------------------------------

### Install PostgREST with pacman on Arch Linux

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Installs PostgREST from the Arch Linux community repository.

```bash
pacman -S postgrest

```

--------------------------------

### Install libpq-dev on Ubuntu or Debian

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Install the PostgreSQL client library development package required for building PostgREST on Ubuntu or Debian.

```bash
sudo apt-get install libpq-dev
```

--------------------------------

### Install PostgREST with Homebrew on macOS

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Installs PostgREST from the Homebrew official repository on macOS.

```bash
brew install postgrest

```

--------------------------------

### Install postgresql-libs on Fedora, CentOS, or Red Hat

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Install the PostgreSQL client library package for Fedora, CentOS, or Red Hat.

```bash
sudo yum install postgresql-libs
```

--------------------------------

### Install libpq on Fedora, CentOS, or Red Hat

Source: https://docs.postgrest.org/en/v14/tutorials/tut0.html

Installs postgresql-libs to resolve the missing libpq.so.5 error on Fedora/CentOS/Red Hat.

```bash
sudo yum install postgresql-libs

```

--------------------------------

### Example postgrest.conf configuration file

Source: https://docs.postgrest.org/en/v14/_sources/references/configuration.rst.txt

Shows key-value pairs for db-uri, db-anon-role, jwt-secret, and server-port. The jwt-secret must be at least 32 characters. Use `postgrest --example` to see all parameters.

```text
# postgrest.conf

# The standard connection URI format, documented at
# https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING
db-uri       = "postgres://user:pass@host:5432/dbname"

# The database role to use when no client authentication is provided.
# Should differ from authenticator
db-anon-role = "anon"

# The secret to verify the JWT for authenticated requests with.
# Needs to be 32 characters minimum.
jwt-secret           = "reallyreallyreallyreallyverysafe"
jwt-secret-is-base64 = false

# Port the postgrest process is listening on for http requests
server-port = 3000
```

--------------------------------

### Install PostgREST with Chocolatey or Scoop on Windows

Source: https://docs.postgrest.org/en/v14/explanations/install.html

Installs PostgREST using Chocolatey or Scoop package managers.

```bash
choco install postgrest
scoop install postgrest

```

--------------------------------

### Example URL for non-guaranteed column order

Source: https://docs.postgrest.org/en/v14/_sources/references/api/resource_representation.rst.txt

Example URL demonstrating that column ordering in the response is not guaranteed to align with the select clause order, especially with resource embedding.

```bash
http://localhost:3000/films?select=directors(last_name,id),title
```

--------------------------------

### Metrics output example

Source: https://docs.postgrest.org/en/v14/_sources/references/observability.rst.txt

Example output of PostgREST metrics, showing gauge and counter types for schema cache query time and loads.

```text
# HELP pgrst_schema_cache_query_time_seconds The query time in seconds of the last schema cache load
# TYPE pgrst_schema_cache_query_time_seconds gauge
pgrst_schema_cache_query_time_seconds 1.5937927e-2
# HELP pgrst_schema_cache_loads_total The total number of times the schema cache was loaded
# TYPE pgrst_schema_cache_loads_total counter
pgrst_schema_cache_loads_total 1.0
...
```

--------------------------------

### Install pg-safeupdate via PGXN

Source: https://docs.postgrest.org/en/v14/_sources/integrations/pg-safeupdate.rst.txt

Commands to install the pg-safeupdate extension using PGXN and then add the shared_preload_libraries configuration to postgresql.conf. The extension raises an error if UPDATE or DELETE are executed without conditions.

```bash
sudo -E pgxn install safeupdate

# then add this to postgresql.conf:
# shared_preload_libraries='safeupdate';
```

--------------------------------

### GET todos and response showing all completed

Source: https://docs.postgrest.org/en/v14/tutorials/tut1.html

Fetches the list of todos with a GET request and shows the JSON response containing three todos, all with 'done' set to true.

```bash
curl http://localhost:3000/todos

```

```json
[
  {
    "id": 1,
    "done": true,
    "task": "finish tutorial 0",
    "due": null
  },
  {
    "id": 2,
    "done": true,
    "task": "pat self on back",
    "due": null
  },
  {
    "id": 3,
    "done": true,
    "task": "learn how to auth",
    "due": null
  }
]

```

--------------------------------

### Create tutorial.conf configuration file

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Configuration file for PostgREST specifying database URI, schemas, and anonymous role.

```ini
db-uri = "postgres://authenticator:mysecretpassword@localhost:5432/postgres"
db-schemas = "api"
db-anon-role = "web_anon"
```

--------------------------------

### GET /people (with logical operators)

Source: https://docs.postgrest.org/en/v14/_sources/references/api/tables_views.rst.txt

Example of using logical operators (OR, NOT, AND) in query parameters to filter records with complex conditions.

```APIDOC
## GET /people

### Description
Example of using logical operators (OR, NOT, AND) in query parameters to filter records with complex conditions.

### Method
GET

### Endpoint
/people

### Query Parameters
- `or` (string) - Required - Logical OR expression, e.g., `(age.lt.18,age.gt.21)`
- `grade` (string) - Optional - Filter condition, e.g., `gte.90`
- `student` (string) - Optional - Filter condition, e.g., `is.true`
- `age` (string) - Optional - Filter condition, e.g., `eq.14`

### Request Example
```bash
curl "http://localhost:3000/people?or=(age.lt.18,age.gt.21)"
```

```bash
curl --get "http://localhost:3000/people" \
  -d "grade=gte.90" \
  -d "student=is.true" \
  -d "or=(age.eq.14,not.and(age.gte.11,age.lte.17))"
```

### Response
No response schema is documented in the source.
```

--------------------------------

### Run PostgREST server

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Start the PostgREST server using the configuration file, either from a package manager or binary.

```bash
# Running postgrest installed from a package manager
postgrest tutorial.conf

# Running postgrest binary
./postgrest tutorial.conf
```

--------------------------------

### GET /projects?id=gt.10

Source: https://docs.postgrest.org/en/v14/_sources/references/api/resource_representation.rst.txt

Example of querying the projects table with a filter and requesting an array JSON response with nulls stripped.

```APIDOC
## GET /projects?id=gt.10

### Description
Example of querying the projects table with a filter and requesting an array JSON response with nulls stripped.

### Method
GET

### Endpoint
/projects?id=gt.10

### Parameters
#### Query Parameters
- **id** (string) - Required - Filter condition: greater than 10 (gt.10)

### Request Example
```
curl "http://localhost:3000/projects?id=gt.10" \
  -H "Accept: application/vnd.pgrst.array+json;nulls=stripped"
```

### Response
#### Success Response (200)
- **id** (integer) - Project ID
- **name** (string) - Project name
- **client_id** (integer) - Client ID (may be null and stripped)
- **another_col** (string) - Another column (may be null and stripped)

#### Response Example
```json
[
  { "id": 11, "name": "OSX", "client_id": 1, "another_col": "val" },
  { "id": 12, "name": "ProjectX" },
  { "id": 13, "name": "Y"}
]
```
```

--------------------------------

### any modifier for OR logic

Source: https://docs.postgrest.org/en/v14/references/api/tables_views.html

Use the any modifier to avoid repeating the same column for OR conditions. This example matches last names starting with O or P.

```bash
curl -g "http://localhost:3000/people?last_name=like(any).{O*,P*}"

```

--------------------------------

### Show help with postgrest --help

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Displays all available options for the postgrest command.

```bash
$ postgrest --help
```

--------------------------------

### Create add_them function

Source: https://docs.postgrest.org/en/v14/_sources/references/api/functions.rst.txt

Creates a PostgreSQL function that adds two integers. This function is used in the examples for calling with POST and GET.

```postgres
CREATE FUNCTION add_them(a integer, b integer)
RETURNS integer AS $$
 SELECT a + b;
$$ LANGUAGE SQL IMMUTABLE;
```

--------------------------------

### all modifier for AND logic

Source: https://docs.postgrest.org/en/v14/references/api/tables_views.html

Use the all modifier to avoid repeating the same column for AND conditions. This example matches last names starting with O and ending with n.

```bash
curl -g "http://localhost:3000/people?last_name=like(all).{O*,*n}"

```

--------------------------------

### Configure db-pre-request in tutorial.conf

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut1.rst.txt

Add the 'db-pre-request' line to tutorial.conf to specify the function to run during authentication.

```ini
# add this line to tutorial.conf

db-pre-request = "auth.check_token"
```

--------------------------------

### GET /survey (with reserved characters)

Source: https://docs.postgrest.org/en/v14/_sources/references/api/tables_views.rst.txt

Example of using logical operators with filter values containing reserved characters, which must be wrapped in double quotes.

```APIDOC
## GET /survey

### Description
Example of using logical operators with filter values containing reserved characters, which must be wrapped in double quotes.

### Method
GET

### Endpoint
/survey

### Query Parameters
- `or` (string) - Required - Logical OR expression with quoted values, e.g., `(age_range.adj."[18,21)",age_range.cs."[30,35]")`

### Request Example
```bash
curl -g 'http://localhost:3000/survey?or=(age_range.adj."[18,21)",age_range.cs."[30,35]")'
```

### Response
No response schema is documented in the source.
```

--------------------------------

### GET /projects - Spread with Aliasing to Avoid Duplicate Keys

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Aliasing spreaded columns is recommended to avoid duplicate keys in JSON. This example shows the problem and the solution.

```APIDOC
## GET /projects

### Description
Spread a to-one relationship and alias the embedded columns to avoid duplicate keys in the JSON response. Duplicate keys can cause issues in JavaScript objects.

### Method
GET

### Endpoint
/projects

### Parameters
#### Query Parameters
- **select** (string) - Required - The columns to select, including spread syntax with aliases. Example: `id,name,...clients(client_id:id,client_name:name)`

### Request Example
```
curl --get "localhost:3000/projects" \
  -d "select=id,name,...clients(client_id:id,client_name:name)"
```

### Response
#### Success Response (200)
- **id** (integer) - The project ID.
- **name** (string) - The project name.
- **client_id** (integer) - The client ID (aliased).
- **client_name** (string) - The client name (aliased).

#### Response Example
```
[{"id":1,"name":"Windows 7","client_id":1,"client_name":"Microsoft"},
 {"id":2,"name":"Windows 10","client_id":1,"client_name":"Microsoft"},
 {"id":3,"name":"IOS","client_id":2,"client_name":"Apple"},
 {"id":4,"name":"OSX","client_id":2,"client_name":"Apple"},
 {"id":5,"name":"Orphan","client_id":null,"client_name":null}]
```
```

--------------------------------

### Build PostgREST from source with Stack

Source: https://docs.postgrest.org/en/v14/_sources/explanations/install.rst.txt

Clones the PostgREST repository and builds the binary using Stack. The --install-ghc flag is only needed for the first build. Adjust --local-bin-path to taste. If building fails on systems with less than 1GB of memory, add a swap file.

```bash
git clone https://github.com/PostgREST/postgrest.git
cd postgrest

# adjust local-bin-path to taste
stack build --install-ghc --copy-bins --local-bin-path /usr/local/bin
```

--------------------------------

### GET /films?select=*,actors(*)&actors.limit=10&actors.offset=2

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Applies limit and offset to the embedded actors resource, returning only 10 actors starting from offset 2 for each film.

```APIDOC
## GET /films?select=*,actors(*)&actors.limit=10&actors.offset=2

### Description
Applies limit and offset to the embedded actors resource, returning only 10 actors starting from offset 2 for each film.

### Method
GET

### Endpoint
/films

### Parameters
#### Query Parameters
- **select** (string) - Required - Specifies the columns and embedded resources to return. Example: `*,actors(*)`
- **actors.limit** (integer) - Required - Maximum number of embedded actors to return. Example: `10`
- **actors.offset** (integer) - Required - Number of embedded actors to skip before starting to return. Example: `2`

### Request Example
```
curl --get "http://localhost:3000/films" \
  -d "select=*,actors(*)" \
  -d "actors.limit=10" \
  -d "actors.offset=2"
```

### Response
#### Success Response (200)
Returns a JSON array of films, each with an `actors` array limited to 10 items starting from offset 2.

#### Response Example
```json
[
  {
    "id": 1,
    "title": "Film Title",
    "actors": [
      {"last_name": "Smith", "first_name": "John"},
      {"last_name": "Doe", "first_name": "Jane"}
    ]
  }
]
```
```

--------------------------------

### postgrest --help

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Shows all the options available for the PostgREST CLI.

```APIDOC
## postgrest --help

### Description
Shows all the options available for the PostgREST CLI.

### Method
CLI

### Endpoint
postgrest --help

### Parameters
None

### Request Example
```bash
$ postgrest --help
```

### Response
#### Success Response
Displays the help text listing all available options.

#### Response Example
```text
Usage: postgrest [-v|--version] [-e|--example] [--dump-config | --dump-schema | --ready]
               [FILENAME]

  PostgREST / create a REST API to an existing Postgres
  database

Available options:
  -h,--help                Show this help text
  -v,--version             Show the version information
  -e,--example             Show an example configuration file
  --dump-config            Dump loaded configuration and exit
  --dump-schema            Dump loaded schema as JSON and exit (for debugging,
                           output structure is unstable)
  --ready                  Checks the health of PostgREST by doing a request on
                           the admin server /ready endpoint
  FILENAME                 Path to configuration file
```
```

--------------------------------

### GET /films - Spread a Join Table

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Spread a join table in a many-to-many relationship to move its columns to the top object. This example spreads the roles join table into actors.

```APIDOC
## GET /films

### Description
Spread a join table in a many-to-many relationship to move its columns to the top object. This example spreads the roles join table into actors.

### Method
GET

### Endpoint
/films

### Parameters
#### Query Parameters
- **select** (string) - Required - The columns to select, including spread syntax for the join table. Example: `title,actors:roles(character,...actors(first_name,last_name))`
- **title** (string) - Optional - Filter by title. Example: `like.*Lighthouse*`

### Request Example
```
curl --get "http://localhost:3000/films" \
  -d "select=title,actors:roles(character,...actors(first_name,last_name))" \
  -d "title=like.*Lighthouse*"
```

### Response
#### Success Response (200)
- **title** (string) - The film title.
- **actors** (array of objects) - The actors with the join table column and spread actor columns.
  - **character** (string) - The character played by the actor.
  - **first_name** (string) - The actor's first name.
  - **last_name** (string) - The actor's last name.

#### Response Example
```
[
  {
    "title": "The Lighthouse",
    "actors": [
       {
         "character": "Thomas Wake",
         "first_name": "Willem",
         "last_name": "Dafoe"
       }
    ]
  }
]
```
```

--------------------------------

### PostgREST CLI usage and options

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Shows the general usage syntax and all available options for the postgrest command.

```text
Usage: postgrest [-v|--version] [-e|--example] [--dump-config | --dump-schema | --ready]
               [FILENAME]

  PostgREST / create a REST API to an existing Postgres
  database

  Available options:
    -h,--help                Show this help text
    -v,--version             Show the version information
    -e,--example             Show an example configuration file
    --dump-config            Dump loaded configuration and exit
    --dump-schema            Dump loaded schema as JSON and exit (for debugging,
                             output structure is unstable)
    --ready                  Checks the health of PostgREST by doing a request on
                             the admin server /ready endpoint
    FILENAME                 Path to configuration file
```

--------------------------------

### GET /directors - Multiple Spreads

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Use multiple spreads at any level to embed and spread multiple resources. This example spreads technical_specs and roles into films, then spreads films into directors.

```APIDOC
## GET /directors

### Description
Use multiple spreads at any level to embed and spread multiple resources. This example spreads technical_specs and roles into films, then spreads films into directors.

### Method
GET

### Endpoint
/directors

### Parameters
#### Query Parameters
- **select** (string) - Required - The columns to select, including multiple spread syntax. Example: `first_name,...films(film_titles:title,film_years:year,...technical_specs(film_runtimes:runtime),...roles(film_characters:character))`
- **first_name** (string) - Optional - Filter by first name. Example: `like.Quentin*`
- **films.order** (string) - Optional - Order the embedded films. Example: `year`
- **films.roles.order** (string) - Optional - Order the embedded roles. Example: `character`

### Request Example
```
curl --get "http://localhost:3000/directors" \
  -d "select=first_name,...films(film_titles:title,film_years:year,...technical_specs(film_runtimes:runtime),...roles(film_characters:character))" \
  -d "first_name=like.Quentin*" \
  -d "films.order=year" \
  -d "films.roles.order=character"
```

### Response
#### Success Response (200)
- **first_name** (string) - The director's first name.
- **film_titles** (array of strings) - The titles of the films.
- **film_years** (array of integers) - The years of the films.
- **film_runtimes** (array of strings) - The runtimes of the films.
- **film_characters** (array of arrays) - The characters for each film.

#### Response Example
```
[
  {
    "first_name": "Quentin",
    "film_titles": [
      "Reservoir Dogs",
      "Pulp Fiction"
    ],
    "film_years": [
      1992,
      1994
    ],
    "film_runtimes": [
      "01:39:00",
      "02:29:00"
    ],
    "film_characters": [
      [ "Mr. Pink", "Mr. White" ],
      [ "Mia Wallace", "Vincent Vega" ]
    ]
  }
]
```
```

--------------------------------

### Run PostgreSQL in Docker

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Starts a PostgreSQL container named 'tutorial' on port 5432 with a dummy password. Requires Docker and no other PostgreSQL on port 5432.

```bash
sudo docker run --name tutorial -p 5432:5432 \
                -e POSTGRES_PASSWORD=notused \
                -d postgres
```

--------------------------------

### Use any modifier to avoid repeating column for OR

Source: https://docs.postgrest.org/en/v14/_sources/references/api/tables_views.rst.txt

Use the any modifier with like to match any of the patterns in the list, simplifying OR conditions on the same column. The example retrieves people with last names starting with O or P.

```bash
curl -g "http://localhost:3000/people?last_name=like(any).{O*,P*}"
```

--------------------------------

### Use all modifier to avoid repeating column for AND

Source: https://docs.postgrest.org/en/v14/_sources/references/api/tables_views.rst.txt

Use the all modifier with like to match all patterns in the list, simplifying AND conditions on the same column. The example retrieves people with last names starting with O and ending with n.

```bash
curl -g "http://localhost:3000/people?last_name=like(all).{O*,*n}"
```

--------------------------------

### GET /films?select=*,roles(*,actors(*))&roles.actors.order=last_name&roles.actors.first_name=like.*Tom*

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Applies filters and ordering on nested embedded resources. This example orders nested actors by last_name and filters them to those with first_name like 'Tom'.

```APIDOC
## GET /films?select=*,roles(*,actors(*))&roles.actors.order=last_name&roles.actors.first_name=like.*Tom*

### Description
Applies filters and ordering on nested embedded resources. This example orders nested actors by last_name and filters them to those with first_name like 'Tom'.

### Method
GET

### Endpoint
/films

### Parameters
#### Query Parameters
- **select** (string) - Required - Specifies the columns and embedded resources to return, including nested resources. Example: `*,roles(*,actors(*))`
- **roles.actors.order** (string) - Required - Specifies the ordering of the nested actors. Example: `last_name`
- **roles.actors.first_name** (string) - Required - Filter condition for the nested actors. Example: `like.*Tom*`

### Request Example
```
curl --get "http://localhost:3000/films" \
  -d "select=*,roles(*,actors(*))" \
  -d "roles.actors.order=last_name" \
  -d "roles.actors.first_name=like.*Tom*"
```

### Response
#### Success Response (200)
Returns a JSON array of films, each with a `roles` array, and each role has an `actors` array filtered to actors with first_name like 'Tom' and ordered by last_name.

#### Response Example
```json
[
  {
    "id": 1,
    "title": "Film Title",
    "roles": [
      {
        "character": "Lead",
        "actors": [
          {"last_name": "Hanks", "first_name": "Tom"}
        ]
      }
    ]
  }
]
```
```

--------------------------------

### Run PostgREST with --help flag

Source: https://docs.postgrest.org/en/v14/_sources/explanations/install.rst.txt

Run the PostgREST binary with the --help flag to see usage instructions. The command differs depending on whether you run the binary directly or installed via a package manager.

```bash
# Running postgrest binary
./postgrest --help

# Running postgrest installed from a package manager
postgrest --help

# You should see a usage help message
```

--------------------------------

### GET /films?select=*,90_comps:competitions(name),91_comps:competitions(name)&90_comps.year=eq.1990&91_comps.year=eq.1991

Source: https://docs.postgrest.org/en/v14/references/api/resource_embedding.html

Aliases embedded resources and applies filters on the aliases. This example retrieves competitions from 1990 and 1991 separately.

```APIDOC
## GET /films?select=*,90_comps:competitions(name),91_comps:competitions(name)&90_comps.year=eq.1990&91_comps.year=eq.1991

### Description
Aliases embedded resources and applies filters on the aliases. This example retrieves competitions from 1990 and 1991 separately.

### Method
GET

### Endpoint
/films

### Parameters
#### Query Parameters
- **select** (string) - Required - Specifies the columns and embedded resources to return, including aliases. Example: `*,90_comps:competitions(name),91_comps:competitions(name)`
- **90_comps.year** (string) - Required - Filter condition for the aliased embedded resource. Example: `eq.1990`
- **91_comps.year** (string) - Required - Filter condition for the aliased embedded resource. Example: `eq.1991`

### Request Example
```
curl --get "http://localhost:3000/films" \
  -d "select=*,90_comps:competitions(name),91_comps:competitions(name)" \
  -d "90_comps.year=eq.1990" \
  -d "91_comps.year=eq.1991"
```

### Response
#### Success Response (200)
Returns a JSON array of films, each with `90_comps` and `91_comps` arrays containing competitions from the respective years.

#### Response Example
```json
[
  {
    "id": 1,
    "title": "Film Title",
    "90_comps": [{"name": "Competition 1990"}],
    "91_comps": [{"name": "Competition 1991"}]
  }
]
```
```

--------------------------------

### Create sample user role foo

Source: https://docs.postgrest.org/en/v14/_sources/how-tos/sql-user-management-using-postgres-users-and-passwords.rst.txt

Creates a sample user role with password 'bar' for testing the login function.

```postgres
CREATE ROLE foo PASSWORD 'bar';
```

--------------------------------

### Check PostgREST help via package manager

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut0.rst.txt

Runs 'postgrest -h' to print the help page with version and options.

```bash
postgrest -h
```

--------------------------------

### jwt-role-claim-key with JSPath String Comparisons

Source: https://docs.postgrest.org/en/v14/_sources/references/auth.rst.txt

Examples of using `jwt-role-claim-key` with the JSPath extension. The DSL accepts alphanumeric characters and `_$@` as keys, while non-alphanumerical characters require quoted and escaped config keys. The `@` symbol represents the current array element, enabling string comparison operators `==`, `!=`, `^==`, `==^`, and `*==` to select array elements that match, differ, start with, end with, or contain the right operand.

```bash
# {"postgrest":{"roles": ["other", "author"]}}
# the DSL accepts characters that are alphanumerical or one of "_$@" as keys
jwt-role-claim-key = ".postgrest.roles[1]"
```

```bash
# {"https://www.example.com/role": { "key": "author" }}
# non-alphanumerical characters can go inside quotes(escaped in the config value)
jwt-role-claim-key = ".\"https://www.example.com/role\".key"
```

```bash
# {"postgrest":{"roles": ["other", "author"]}}
# `@` represents the current element in the array
# all the these match the string "author"
jwt-role-claim-key = ".postgrest.roles[?(@ == \"author\")]"
jwt-role-claim-key = ".postgrest.roles[?(@ != \"other\")]"
jwt-role-claim-key = ".postgrest.roles[?(@ ^== \"aut\")]"
jwt-role-claim-key = ".postgrest.roles[?(@ ==^ \"hor\")]"
jwt-role-claim-key = ".postgrest.roles[?(@ *== \"utho\")]"
```

--------------------------------

### Show version with postgrest --version

Source: https://docs.postgrest.org/en/v14/_sources/references/cli.rst.txt

Prints the PostgREST version.

```bash
$ postgrest --version
```

--------------------------------

### Run PostgREST with a configuration file

Source: https://docs.postgrest.org/en/v14/_sources/explanations/install.rst.txt

Run the PostgREST server by passing a configuration file as its only argument. You can generate a sample config file with postgrest -e > postgrest.conf, but you'll need to edit it and remove the usage parts for postgrest to read it.

```bash
postgrest /path/to/postgrest.conf

# You can also generate a sample config file with
# postgrest -e > postgrest.conf
# You'll need to edit this file and remove the usage parts for postgrest to read it
```

--------------------------------

### GET /rpc/add_them

Source: https://docs.postgrest.org/en/v14/_sources/references/api/functions.rst.txt

Calls a function that does not modify the database using GET, with arguments as query parameters.

```APIDOC
## GET /rpc/add_them

### Description
Calls the `add_them` function using GET, with arguments passed as query parameters. Only functions that do not modify the database are allowed.

### Method
GET

### Endpoint
/rpc/add_them

### Parameters
#### Query Parameters
- **a** (integer) - Required - First argument
- **b** (integer) - Required - Second argument

### Request Example
```
/rpc/add_them?a=1&b=2
```

### Response
#### Success Response (200)
Returns the result of the function as a JSON value.

#### Response Example
```json
3
```
```

--------------------------------

### Docker Compose setup for PostgREST and PostgreSQL

Source: https://docs.postgrest.org/en/v14/_sources/explanations/install.rst.txt

Defines a docker-compose.yml with a PostgREST server and a PostgreSQL database. The server exposes port 3000 and requires the PGRST_SERVER_HOST, PGRST_DB_URI, and PGRST_OPENAPI_SERVER_PROXY_URI environment variables. The database uses the postgres image with credentials app_user/password. Uncomment the volumes section to persist data. Run with 'docker-compose up'.

```yaml
version: '3'
services:
  server:
    image: postgrest/postgrest
    ports:
      - "3000:3000"
    environment:
      PGRST_SERVER_HOST: 0.0.0.0 # necessary for `postgrest --ready` flag to work
      PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
      PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
    depends_on:
      - db
  db:
    image: postgres
    ports:
      - "5432:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: password
  # Uncomment this if you want to persist the data.
  # volumes:
  #   - "./pgdata:/var/lib/postgresql/data"
```

--------------------------------

### Call variadic plus_one via GET (repeated parameters)

Source: https://docs.postgrest.org/en/v14/references/api/functions.html

GET request repeating the parameter name for each value.

```bash
curl "http://localhost:3000/rpc/plus_one?v=1&v=2&v=3&v=4"

```

--------------------------------

### GET /rpc/add_them

Source: https://docs.postgrest.org/en/v14/references/api/functions.html

Calls a Postgres function using the GET method. Function parameters are passed as query parameters.

```APIDOC
## GET /rpc/add_them

### Description
Calls the `add_them` function using the GET method. The function parameters are passed as query parameters.

### Method
GET

### Endpoint
/rpc/add_them

### Parameters
#### Query Parameters
- **a** (integer) - Required - First parameter for the function.
- **b** (integer) - Required - Second parameter for the function.

### Request Example
```
curl "http://localhost:3000/rpc/add_them?a=1&b=2"
```

### Response
#### Success Response (200)
Returns the result of the function call.

```

--------------------------------

### Check tutorial.conf for jwt-secret

Source: https://docs.postgrest.org/en/v14/_sources/tutorials/tut1.rst.txt

Displays the contents of tutorial.conf to verify the jwt-secret is set. The secret must be at least 32 characters long.

```bash
# THE SECRET MUST BE AT LEAST 32 CHARS LONG
cat tutorial.conf
```

--------------------------------

### Example request logs with log-level info

Source: https://docs.postgrest.org/en/v14/_sources/references/observability.rst.txt

Shows the format of request logs output to stdout when log-level is set to info. Includes authenticated user, IP, user agent, URL, status, and response size.

```text
127.0.0.1 - user [26/Jul/2021:01:56:38 -0500] "GET /clients HTTP/1.1" 200 56 "" "curl/7.64.0"
127.0.0.1 - anonymous [26/Jul/2021:01:56:48 -0500] "GET /unexistent HTTP/1.1" 404 162 "" "curl/7.64.0"
```
Tokens:
Raw