PostgREST

postgrest.md

PostRest

PostgREST is a standalone web server which turns a PostgreSQL database into a RESTful API. It serves an API that is customized based on the structure of the underlying database.

Official documentation at http://postgrest.org/en/v5.2/index.html

There's a docker image in case of testing :-)

To configure your PostREST instance: http://postgrest.org/en/v5.2/install.html#configuration

Instalation

TODO

Running the Server

Docker

API

Tables and Views

Table people is returned at GET /people HTTP/1.1

There are no deeply/nested/routes.

Each route provides OPTIONS, GET, POST, PATCH and DELETE verbs.

Horizontal Filtering (Rows)

Examples:

GET /people?age=lt.13 HTTP/1.1

GET /people?age=gte.18&student=is.true HTTP/1.1

GET /people?or=(age.gte.14,age.lte.18) HTTP/1.1

GET /people?and=(grade.gte.90,student.is.true,or(age.gte.14,age.is.null)) HTTP/1.1

Operators: eq, gt, gte, lt, lte, neq, like, ilike, in, is ... more

To negate any operator, prefix it with not like:

• ?a=not.eq.2
• ?not.and=(a.gte.0,a.lte.100).

Full-Text Search (todo)

Vertical Filtering (Columns)

GET /people?select=first_name,age HTTP/1.1

[
  {"first_name": "John", "age": 30},
  {"first_name": "Jane", "age": 20}
]

The default is *, meaning all columns.

Rename:

GET /people?select=fullName:full_name,birthDate:birth_date HTTP/1.1

[
  {"fullName": "John Doe", "birthDate": "04/25/1988"},
  {"fullName": "Jane Doe", "birthDate": "01/12/1998"}
]

Casting:

GET /people?select=full_name,salary::text HTTP/1.1

[
  {"full_name": "John Doe", "salary": "90000.00"},
  {"full_name": "Jane Doe", "salary": "120000.00"}
]

JSON Columns (operators -> and ->>):

GET /people?select=id,json_data->>blood_type,json_data->phones HTTP/1.1

[
  { "id": 1, "blood_type": "A+", "phones": [{"country_code": "61", "number": "917-929-5745"}] },
  { "id": 2, "blood_type": "O+", "phones": [{"country_code": "43", "number": "512-446-4988"}, {"country_code": "43", "number": "213-891-5979"}] }
]

Ordering

GET /people?order=age.desc,height.asc HTTP/1.1

GET /people?order=age HTTP/1.1

GET /people?order=age.nullsfirst HTTP/1.1

GET /people?order=age.desc.nullslast HTTP/1.1

Limits and Pagination

Every response contains the current range and, if requested, the total number of results:

GET /people?limit=15&offset=30 HTTP/1.1

Response Format

Use the Accept request header to specify the acceptable format (or formats) for the response:

GET /people HTTP/1.1
Accept: application/json

The current possibilities:

• */*
• text/csv
• application/json
• application/openapi+json
• application/octet-stream

Singular or Plural

By default PostgREST returns all JSON results in an array, even when there is only one item. For example, requesting /items?id=eq.1 returns

[
  { "id": 1 }
]

Resource Embedding (a.k.a joins)

![class diagram][http://postgrest.org/en/v5.2/_images/film.png]

In this example, since the relationship is a forward relationship, there is only one director associated with a film.

GET /films?select=title,directors(id,last_name) HTTP/1.1

Which returns:

[
  { "title": "Workers Leaving The Lumière Factory In Lyon",
    "directors": {
      "id": 2,
      "last_name": "Lumière"
    }
  },
  ...
]

You can also reverse the direction of inclusion, asking for all Directors with each including the list of their Films:

GET /directors?select=films(title,year) HTTP/1.1

Embedded Filters

To order the actors in each film:

GET /films?select=*,actors(\*)&actors.order=last_name,first_name HTTP/1.1

This sorts the list of actors in each film but does not change the order of the films themselves. To filter the roles returned with each film:

GET /films?select=*,roles(\*)&roles.character=in.(Chico,Harpo,Groucho) HTTP/1.1

Once again, this restricts the roles included to certain characters but does not filter the films in any way. Films without any of those characters would be included along with empty character lists.

An or filter can be used for a similar operation:

GET /films?select=*,roles(\*)&roles.or=(character.eq.Gummo,character.eq.Zeppo) HTTP/1.1

Limit and offset operations are possible:

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

Embedded resources can be aliased and filters can be applied on these aliases:

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

Stored Procedures

Every stored procedure in the API-exposed database schema is accessible under the /rpc prefix. The API endpoint supports POST (and in some cases GET) to execute the function.

POST /rpc/function_name HTTP/1.1

Such functions can perform any operations allowed by PostgreSQL (read data, modify data, and even DDL operations).

Example:

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

The client call it by posting an object like

POST /rpc/add_them HTTP/1.1

{ "a": 1, "b": 2 }

3

Procedures must be declared with named parameters, procedures declared like: CREATE FUNCTION non_named_args(integer, text, integer) ... Can not be called with PostgREST, since we use named notation internally.

PostgreSQL has four procedural languages that are part of the core distribution: PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python.

For versions prior to PostgreSQL 10, to pass a PostgreSQL native array you need to quote it as a string: POST /rpc/native_array_func HTTP/1.1 { "arg": "{1,2,3}" }

PostgREST will detect if the function is scalar or table-valued and will shape the response format accordingly:

GET /rpc/add_them?a=1&b=2 HTTP/1.1
3

GET /rpc/best_films_2017 HTTP/1.1
[
  { "title": "Okja", "rating": 7.4},
  { "title": "Call me by your name", "rating": 8},
  { "title": "Blade Runner 2049", "rating": 8.1}
]

Function filters

A function that returns a table type response can be shaped using the same filters as the ones used for tables and views:

CREATE FUNCTION best_films_2017() RETURNS SETOF films ..

GET /rpc/best_films_2017?select=title,director:directors(\*) HTTP/1.1

GET /rpc/best_films_2017?rating=gt.8&order=title.desc HTTP/1.1

Overloaded functions

You can call overloaded functions with different number of arguments.

CREATE FUNCTION rental_duration(customer_id integer) ..
CREATE FUNCTION rental_duration(customer_id integer, from_date date) ..

GET /rpc/rental_duration?customer_id=232 HTTP/1.1

GET /rpc/rental_duration?customer_id=232&from_date=2018-07-01 HTTP/1.1

Accessing Request Headers, Cookies and JWT claims

-- To read the value of the Origin request header:
SELECT current_setting('request.header.origin', true);
-- To read the value of sessionId in a cookie:
SELECT current_setting('request.cookie.sessionId', true);
-- To read the value of the email claim in a jwt:
SELECT current_setting('request.jwt.claim.email', true);

Errors and HTTP Status Codes

Stored procedures can return non-200 HTTP status codes by raising SQL exceptions. For instance, here's a saucy function that always responds with an error:

CREATE OR REPLACE FUNCTION just_fail() RETURNS void
  LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'I refuse!'
    USING DETAIL = 'Pretty simple',
          HINT = 'There is nothing you can do.';
END
$$;

Calling the function returns HTTP 400 with the body

{
  "message":"I refuse!",
  "details":"Pretty simple",
  "hint":"There is nothing you can do.",
  "code":"P0001"
}

One way to customize the HTTP status code is by raising particular exceptions according to the PostgREST error to status code mapping. For example, RAISE insufficient_privilege will respond with HTTP 401/403 as appropriate.

Insertions / Updates

To create a row in a database table post a JSON object whose keys are the names of the columns you would like to create.

POST /table_name HTTP/1.1
{ "col1": "value1", "col2": "value2" }

Don't "{ "a": 1, "b": 2 }"

you can get the full created object back in the response to your request by including the header Prefer: return=representation.

To update a row or rows in a table, use the PATCH verb. Use Horizontal Filtering (Rows) to specify which record(s) to update. Here is an example query setting the category column to child for all people below a certain age.

PATCH /people?age=lt.13 HTTP/1.1
{ "category": "child" }

Updates also support Prefer: return=representation plus Vertical Filtering (Columns).

Bulk Insert

Bulk insert works exactly like single row insert except that you provide either a JSON array of objects having uniform keys, or lines in CSV (faster) format.

To bulk insert CSV simply post to a table route with Content-Type: text/csv and include the names of the columns as the first row. For instance

POST /people HTTP/1.1
Content-Type: text/csv

name,age,height
J Doe,62,70
Jonas,10,55

An empty field (,,) is coerced to an empty string and the reserved word NULL is mapped to the SQL null value. Note that there should be no spaces between the column names and commas.

Upsert

A single row UPSERT can be done by using PUT and filtering the primary key columns with eq:

PUT /employees?id=eq.4 HTTP/1.1
{ "id": 4, "name": "Sara B.", "salary": 60000 }

You can make an UPSERT with POST and the Prefer: resolution=merge-duplicates header:

POST /employees HTTP/1.1
Prefer: resolution=merge-duplicates
[
  { "id": 1, "name": "Old employee 1", "salary": 30000 },
  { "id": 2, "name": "Old employee 2", "salary": 42000 },
  { "id": 3, "name": "New employee 3", "salary": 50000 }
]

Deletions

To delete rows in a table, use the DELETE verb plus Horizontal Filtering (Rows). For instance deleting inactive users:

DELETE /user?active=is.false HTTP/1.1

Authentication

TODO

Overview of Role System

Client Auth

Schema Isolation

SQL User Management

Administration

TODO

Harding PostgRest

Debugging

Daemonizing

Alternate URL Structure