GraphQL API

Understanding the core concepts of the GraphQL API.

In our API, each SQL table is reflected as a set of GraphQL types. At a high level, tables become types and columns/foreign keys become fields on those types.

By default, PostgreSQL table and column names are not inflected when reflecting GraphQL names. For example, an account_holder table has GraphQL type name account_holder. In cases where SQL entities are named using snake_case, enable inflection to match GraphQL/Javascript conventions e.g. account_holder -> AccountHolder.

Individual table, column, and relationship names may also be manually overridden.

Primary Keys (Required)#

Every table must have a primary key for it to be exposed in the GraphQL schema. For example, the following Blog table will be available in the GraphQL schema as blogCollection since it has a primary key named id:

But the following table will not be exposed because it doesn't have a primary key:

QueryType#

The Query type is the entrypoint for all read access into the graph.

Node#

The node interface allows for retrieving records that are uniquely identifiable by a globally unique nodeId: ID! field. For more information about nodeId, see nodeId.

SQL Setup

GraphQL Types

To query the node interface effectively, use inline fragments to specify which fields to return for each type.

Example

Collections#

Each table has top level entry in the Query type for selecting records from that table. Collections return a connection type and can be paginated, filtered, and sorted using the available arguments.

SQL Setup

GraphQL Types

Connection types are the primary interface to returning records from a collection.

Connections wrap a result set with some additional metadata.

Pagination#

Keyset Pagination

Paginating forwards and backwards through collections is handled using the first, last, before, and after parameters, following the relay spec.

Metadata relating to the current page of a result set is available on the pageInfo field of the connection type returned from a collection.

To paginate forward in the collection, use the first and after arguments. To retrieve the first page, the after argument should be null or absent.

Example

To retrieve the next page, provide the cursor value from data.blogCollection.pageInfo.endCursor to the after argument of another query.

once the collection has been fully enumerated, data.blogConnection.pageInfo.hasNextPage returns false.

To paginate backwards through a collection, repeat the process substituting first -> last, after -> before, hasNextPage -> hasPreviousPage

Offset Pagination

In addition to keyset pagination, collections may also be paged using first and offset, which operates like SQL's limit and offset to skip offset number of records in the results.

Filtering#

To filter the result set, use the filter argument.

Where the <Table>Filter type enumerates filterable fields and their associated <Type>Filter.

The following list shows the operators that may be available on <Type>Filter types.

Not all operators are available on every <Type>Filter type. For example, UUIDFilter only supports eq and neq because UUIDs are not ordered.

Example: simple

Example: array column

The contains filter is used to return results where all the elements in the input array appear in the array column.

The contains filter can also accept a single scalar.

The containedBy filter is used to return results where every element of the array column appears in the input array.

The containedBy filter can also accept a single scalar. In this case, only results where the only element in the array column is the input scalar are returned.

The overlaps filter is used to return results where the array column and the input array have at least one element in common.

Example: and/or

Multiple filters can be combined with and, or and not operators. The and and or operators accept a list of <Type>Filter.

Example: not

not accepts a single <Type>Filter.

Example: nested composition

The and, or and not operators can be arbitrarily nested inside each other.

Example: empty

Empty filters are ignored, i.e. they behave as if the operator was not specified at all.

Example: implicit and

Multiple column filters at the same level will be implicitly combined with boolean and. In the following example the id: {eq: 1} and name: {eq: "A: Blog 1"} will be anded.

This means that an and filter can be often be simplified. In the following example all queries are equivalent and produce the same result.

Be aware that the above simplification only works for the and operator. If you try it with an or operator it will behave like an and.

This is because according to the rules of GraphQL list input coercion, if a value passed to an input of list type is not a list, then it is coerced to a list of a single item. So in the above example or: {id: {eq: 1}, name: {eq: "A: Blog 2}} will be coerced into or: [{id: {eq: 1}, name: {eq: "A: Blog 2}}] which is equivalent to or: [and: [{id: {eq: 1}}, {name: {eq: "A: Blog 2}}}] due to implicit anding.

The and, or and not operators also work with update and delete mutations.

Ordering#

The default order of results is defined by the underlying table's primary key column in ascending order. That default can be overridden by passing an array of <Table>OrderBy to the collection's orderBy argument.

Example

Note, only one key value pair may be provided to each element of the input array. For example, [{name: AscNullsLast}, {id: AscNullFirst}] is valid. Passing multiple key value pairs in a single element of the input array e.g. [{name: AscNullsLast, id: AscNullFirst}], is invalid.

MutationType#

The Mutation type is the entrypoint for mutations/edits.

Each table has top level entry in the Mutation type for inserting insertInto<Table>Collection, updating update<Table>Collection and deleting deleteFrom<Table>Collection.

SQL Setup

Insert#

To add records to a collection, use the insertInto<Table>Collection field on the Mutation type.

SQL Setup

GraphQL Types

Where elements in the objects array are inserted into the underlying table.

Example

Update#

To update records in a collection, use the update<Table>Collection field on the Mutation type.

SQL Setup

GraphQL Types

Where the set argument is a key value pair describing the values to update, filter controls which records should be updated, and atMost restricts the maximum number of records that may be impacted. If the number of records impacted by the mutation exceeds the atMost parameter the operation will return an error.

Example

Delete#

To remove records from a collection, use the deleteFrom<Table>Collection field on the Mutation type.

SQL Setup

GraphQL Types

Where filter controls which records should be deleted and atMost restricts the maximum number of records that may be deleted. If the number of records impacted by the mutation exceeds the atMost parameter the operation will return an error.

Example

Concepts#

nodeId#

The base GraphQL type for every table with a primary key is automatically assigned a nodeId: ID! field. That value, can be passed to the node entrypoint of the Query type to retrieve its other fields. nodeId may also be used as a caching key.

SQL Setup

GraphQL Types

Relationships#

Relationships between collections in the Graph are derived from foreign keys.

One-to-Many#

A foreign key on table A referencing table B defines a one-to-many relationship from table A to table B.

SQL Setup

GraphQL Types

Where blogPostCollection exposes the full Query interface to BlogPosts.

Example

Many-to-One#

A foreign key on table A referencing table B defines a many-to-one relationship from table B to table A.

SQL Setup

GraphQL Types

Where blog exposes the Blog record associated with the BlogPost.

One-to-One#

A one-to-one relationship is defined by a foreign key on table A referencing table B where the columns making up the foreign key on table A are unique.

SQL Setup

GraphQL Types

Example

Custom Scalars#

Due to differences among the types supported by PostgreSQL, JSON, and GraphQL, pg_graphql adds several new Scalar types to handle PostgreSQL builtins that require special handling.

JSON#

pg_graphql serializes json and jsonb data types as String under the custom scalar name JSON.

Example

Given the setup

The query

The returns the following data. Note that config is serialized as a string

Use serialized JSON strings when updating or inserting JSON fields via the GraphQL API.

JSON does not currently support filtering.

BigInt#

PostgreSQL bigint and bigserial types are 64 bit integers. In contrast, JSON supports 32 bit integers.

Since PostgreSQL bigint values may be outside the min/max range allowed by JSON, they are represented in the GraphQL schema as BigInts and values are serialized as strings.

Example

Given the setup

The query

The returns the following data. Note that id is serialized as a string

BigFloat#

PostgreSQL's numeric type supports arbitrary precision floating point values. JSON's float is limited to 64-bit precision.

Since a PostgreSQL numeric may require more precision than can be handled by JSON, numeric types are represented in the GraphQL schema as BigFloat and values are serialized as strings.

Example

Given the SQL setup

The query

The returns the following data. Note that amount is serialized as a string

Opaque#

PostgreSQL's type system is extensible and not all types handle all operations e.g. filtering with like. To account for these, pg_graphql introduces a scalar Opaque type. The Opaque type uses PostgreSQL's to_json method to serialize values. That allows complex or unknown types to be included in the schema by delegating handling to the client.