Skip to content

Read

Info

For simplicity, all the examples on this page, assume the HTTP client is already initialized and the requests are unauthenticated.

For an example of how to initialize the HTTP clients and make authenticated requests see authentication section

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

Note

since the name of the tables and views from the api schema are used as the names for the REST endpoints, sometimes we may use endpoint and table/view to mean the same thing.

curl http://localhost:8080/rest/todos
const response = await fetch(endpoint + '/rest/todos');
const response = await client.get('/rest/todos');
const response = await client.query({ 
    query: gql`
        query {
            todos {
                id todo
            }
        }
    `
});

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:8080/rest/todos?id=eq.1"
const options = {headers: new Headers({'Accept': 'application/vnd.pgrst.object+json'})}
const response = await fetch(endpoint + '/rest/todos?id=eq.1', options);
const options = {headers: {'Accept': 'application/vnd.pgrst.object+json'}}
const response = await client.get('/rest/todos?id=eq.1', options);
const response = await client.query({ 
    query: gql`
        query {
            todo(id: 1) {
                id
            }
        }
    `
});

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:8080/rest/todos?select=id,todo
const response = await fetch(endpoint + '/rest/todos?select=id,todo');
const response = await client.get('/rest/todos?select=id,todo');
const response = await client.query({ 
    query: gql`
        query {
            todos {
                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:8080/rest/todos?select=id::text
const response = await fetch(endpoint + '/rest/todos?select=id::text');
const response = await client.get('/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:8080/rest/users?select=firstName:first_name,lastName:last_name
const response = await fetch(endpoint + '/rest/users?select=firstName:first_name,lastName:last_name');
const response = await client.get('/rest/users?select=firstName:first_name,lastName:last_name');
const response = await client.query({ 
    query: gql`
        query {
            users {
                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:8080/rest/customers?select=name,addresses->billing,phones->0->>number
const response = await fetch(endpoint + '/rest/customers?select=name,addresses->billing,phones->0->>number');
const response = await client.get('/rest/customers?select=name,addresses->billing,phones->0->>number');

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 Schema 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:8080/rest/projects?select=id,name,client:clients(id,name),tasks(id,name,users(id,name))
const response = await fetch(endpoint + '/rest/projects?select=id,name,client:clients(id,name),tasks(id,name,users(id,name))');
const response = await client.get('/rest/projects?select=id,name,client:clients(id,name),tasks(id,name,users(id,name))');
const response = await client.query({ 
    query: gql`
        query {
            projects {
                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:8080/rest/projects?select=name:\$upper(name)"
curl "http://localhost:8080/rest/projects?select=name:\$concat('X-',name)"
await fetch(endpoint + "/rest/projects?select=name:$upper(name)");
await fetch(endpoint + "/rest/projects?select=name:$concat('X-',name)");
await client.get("/rest/projects?select=name:$upper(name)");
await client.get("/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.

Note

for security reasons, not every function is available for calling with the select parameter. The list of safe functions is configured using the db-safe-functions configuration parameter.

The default list of safe functions is: avg, count, every, max, min, sum, array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg, corr, covar_pop, covar_samp, regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy, mode, percentile_cont, percentile_cont, percentile_disc, percentile_disc, row_number, rank, dense_rank, cume_dist, percent_rank, first_value, last_value, nth_value, lower, trim, upper, concat, concat_ws, format, substr

Limits and pagination

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

curl "http://localhost:8080/rest/todos?select=id,todo&limit=10&offset=20"
await fetch(endpoint + '/rest/todos?select=id,todo&limit=10&offset=20');
await client.get('/rest/todos?select=id,todo&limit=10&offset=20');
await client.query({ 
    query: gql`
        query {
            todos(limit: 10, offset: 20) {
                id
                todo
            }
        }
    `
});

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:8080/rest/projects?select=name,tasks(name)&tasks.limit=50"
await fetch(endpoint + '/rest/projects?select=name,tasks(name)&tasks.limit=50');
await client.get('/rest/projects?select=name,tasks(name)&tasks.limit=50');
await client.query({ 
    query: gql`
        query {
            projects{
                name
                tasks(limit: 50){ name }
            }
        }
    `
});

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:8080/rest/todos?select=id,todo&order=name.asc"
await fetch(endpoint + '/rest/todos?select=id,todo&order=name.asc');
await client.get('/rest/todos?select=id,todo&order=name.asc');
await client.query({ 
    query: gql`
        query {
            todos(order: {column: "name", direction: asc}) {
                id
                todo
            }
        }
    `
});

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:8080/rest/projects?select=name,tasks(name)&tasks.order=name.asc"
await fetch(endpoint + '/rest/projects?select=name,tasks(name)&tasks.order=name.asc');
await client.get('/rest/projects?select=name,tasks(name)&tasks.order=name.asc');
await client.query({ 
    query: gql`
        query {
            projects{
                name
                tasks(order: {column: "name", direction: asc}){ name }
            }
        }
    `
});