Reading data

Basic url structure

All views and tables in the exposed schema are available for querying provided the authenticated user is authorized to do so. They are exposed in one-level deep routes.

The full contents of a table or view from the api schema can be accessed using a GET request

curl http://localhost:3000/rest/todos

Requesting a single row

When requesting a single row, by specifying it's id, in order to receive it as a json object, as opposed to an array with a single item, specify the following http header Accept: application/vnd.pgrst.object+json. The GraphQL interface has a distinct type for selecting a single row.

curl -H "Accept: application/vnd.pgrst.object+json" \
"http://localhost:3000/rest/todos?id=eq.1"

select= query parameter

The select= query parameter provides a way for the api client to decide which specific columns from the api endpoint (table/view) are needed.

A simplified (partial) bnf for the format is (we will further define each item below)

<select> ::= <field> | <field> "," <select>
<field> ::= [alias] <column> [cast] | [alias] <relation> | [alias] <function_call>

Which means the select parameter, in it's simplest form, looks like this:

select=col1,col2,...

The absence of this parameters is equivalent to select=* (select all columns). It is never a good idea to omit this parameter or specify it as ?select=*

Basic usage example

curl http://localhost:3000/rest/todos?select=id,todo

Casting response column types

The columns can be cast to a specific type by suffixing them with the desired type column::another_type

    <cast> ::= "::" <cast_type>

curl http://localhost:3000/rest/todos?select=id::text

Renaming columns

You can rename the columns in the response by prefixing them with an alias followed by the colon : operator to suit the need of api client.

<alias> ::= <alias_name> ":"

curl http://localhost:3000/rest/users?select=firstName:first_name,lastName:last_name

Accessing JSON columns

When requesting a column of type json or jsonb using the select parameter the response will contain the entire json value of that column. It's possible select a subset of that data if desired by using the -> and ->> operators. The operators have the equivalent behavior as their SQL counterparts

<column> :: <column_name> | <column_name> <json_path>
<json_path> :: = <json_operator> <identifier> | <json_operator> <identifier> <json_path>
<json_operator> ::= "->" | "->>"

curl http://localhost:3000/rest/customers?select=name,addresses->billing,phones->0->>number

Embedding related data

In addition to providing a way to specify the desired columns from the current endpoint (table/view), the select parameter provides as way to request data (rows) from a different endpoint that has a foreign key relation to the current one.

<relation> ::= <relation_reference> "(" <select> ")"
<relation_reference> ::= [ <junction_endpoint> "!" ] <endpoint> | [ <endpoint> "!" ] <fk_column_name> | [ <endpoint> "!" ] <fk_constraint_name>
<endpoint> ::= <table_name> | <view_name>

Provided the database schema has the following tables defined with foreign key relations between them it is possible to construct this select parameter value that will return a list of projects and for each project, we'll get it's related client and tasks and the users associated with each task

select=id,name,client:clients(id,name),tasks(id,name,users(id,name))

curl http://localhost:3000/rest/projects?select=id,name,client:clients(id,name),tasks(id,name,users(id,name))

[
    ...
    {
        "id":1, "name":"My project",
        "client": {"id":1, "name":"My client"},
        "tasks":[
            {"id":1, "name":"Task 1", "users":[{"id": 1, "name":"Alice"}]},
            {"id":2, "name":"Task 2", "users":[{"id": 2, "name":"Bob"}]}
        ]
    }
    ...
]

Note that we used the rename column feature to have the json field as client instead of clients

Calling functions

When desired, it's possible to include in the response the result of a function call for each row.

<function_call> ::= "$" <function_name> "(" <parameters> ")" [ <f_partition> ] [ <f_order> ]
<parameters> ::= <parameter> | <parameter> "," <parameters>
<parameter> ::= <column> | <value>
<value> ::= "'" <any_character> "'"
<f_partition> ::= "-p" "(" <column_list> ")"
<f_order> ::= "-o" "(" <order> ")"

curl "http://localhost:3000/rest/projects?select=name:\$upper(name)"
curl "http://localhost:3000/rest/projects?select=name:\$concat('X-',name)"

For more details of how this feature is leveraged in the context of aggregate and window functions see Aggregate section.

Limits and pagination

Limiting the number of returned rows is done using limit and offset query parameters.

curl "http://localhost:3000/rest/todos?select=id,todo&limit=10&offset=20"

It is also possible to limit the number of rows in the embedded relations by prefixing the parameters with the relation name

curl "http://localhost:3000/rest/projects?select=name,tasks(name)&tasks.limit=50"

Ordering

Ordering of the response rows is done using the order query parameter

<order> ::= <order_term> | <order_term> "," <order>
<order_term> ::= <column> [ "." <direction> ] [ "." <nullposition>] 
<direction> ::= <asc> | <desc>
<nullposition> ::= <nullsfirst> | <nullslast>

curl "http://localhost:3000/rest/todos?select=id,todo&order=name.asc"

Just like with limiting, it's possible to order the rows within embedded relations by prefixing the parameter order with the relation name

curl "http://localhost:3000/rest/projects?select=name,tasks(name)&tasks.order=name.asc"