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.
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
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null,_10 "updatedAt" timestamp not null_10);
GraphQL Types
_10"""The root type for querying data"""_10type Query {_10_10 """Retrieve a record by its `ID`"""_10 node(nodeId: ID!): Node_10_10}
To query the node interface effectively, use inline fragments to specify which fields to return for each type.
Example
_12{_12 node(_12 nodeId: "WyJwdWJsaWMiLCAiYmxvZyIsIDFd"_12 ) {_12 nodeId_12 # Inline fragment for `Blog` type_12 ... on Blog {_12 name_12 description_12 }_12 }_12}
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
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null,_10 "updatedAt" timestamp not null_10);
GraphQL Types
_25"""The root type for querying data"""_25type Query {_25_25 """A pagable collection of type `Blog`"""_25 blogCollection(_25_25 """Query the first `n` records in the collection"""_25 first: Int_25_25 """Query the last `n` records in the collection"""_25 last: Int_25_25 """Query values in the collection before the provided cursor"""_25 before: Cursor_25_25 """Query values in the collection after the provided cursor"""_25 after: Cursor_25_25 """Filters to apply to the results set when querying from the collection"""_25 filter: BlogFilter_25_25 """Sort order to apply to the collection"""_25 orderBy: [BlogOrderBy!]_25 ): BlogConnection_25}
Connection types are the primary interface to returning records from a collection.
Connections wrap a result set with some additional metadata.
_12type BlogConnection {_12_12 # Count of all records matching the *filter* criteria_12 totalCount: Int!_12_12 # Pagination metadata_12 pageInfo: PageInfo!_12_12 # Result set_12 edges: [BlogEdge!]!_12_12}
note
The totalCount field is disabled by default because it can be expensive on large tables. To enable it use a comment directive
Pagination
Paginating forwards and backwards through collections is handled using the first, last, before, and after parameters, following the relay spec.
_20type Query {_20_20 blogCollection(_20_20 """Query the first `n` records in the collection"""_20 first: Int_20_20 """Query the last `n` records in the collection"""_20 last: Int_20_20 """Query values in the collection before the provided cursor"""_20 before: Cursor_20_20 """Query values in the collection after the provided cursor"""_20 after: Cursor_20_20 ...truncated..._20_20 ): BlogConnection_20}
Metadata relating to the current page of a result set is available on the pageInfo field of the connection type returned from a collection.
_14type PageInfo {_14_14 # unique identifier of the first record within the query_14 startCursor: String_14_14 # unique identifier of the last record within the query_14 endCursor: String_14_14 # is another page of content available_14 hasNextPage: Boolean!_14_14 # is another page of content available_14 hasPreviousPage: Boolean!_14}
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
_19{_19 blogCollection(_19 first: 2,_19 after: null_19 ) {_19 pageInfo {_19 startCursor_19 endCursor_19 hasPreviousPage_19 hasNextPage_19 }_19 edges {_19 cursor_19 node {_19 id_19 }_19 }_19 }_19}
To retrieve the next page, provide the cursor value from data.blogCollection.pageInfo.endCursor to the after argument of another query.
_10{_10 blogCollection(_10 first: 2,_10 after: "WzJd"_10 ) {_10 ...truncated..._10}
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
Filtering
To filter the result set, use the filter argument.
_11type Query {_11_11 blogCollection(_11_11 """Filters to apply to the results set when querying from the collection"""_11 filter: BlogFilter_11_11 ...truncated..._11_11 ): BlogConnection_11}
Where the <Table>Filter type enumerates filterable fields and their associated <Type>Filter.
_11input BlogFilter {_11 nodeId: IDFilter_11 id: IntFilter_11 name: StringFilter_11 description: StringFilter_11 createdAt: DatetimeFilter_11 updatedAt: DatetimeFilter_11 and: [BlogFilter!]_11 or: [BlogFilter!]_11 not: BlogFilter_11}
The following list shows the operators that may be available on <Type>Filter types.
| Operator | Description |
|---|---|
| eq | Equal To |
| neq | Not Equal To |
| gt | Greater Than |
| gte | Greater Than Or Equal To |
| in | Contained by Value List |
| lt | Less Than |
| lte | Less Than Or Equal To |
| is | Null or Not Null |
| startsWith | Starts with prefix |
| like | Pattern Match. '%' as wildcard |
| ilike | Pattern Match. '%' as wildcard. Case Insensitive |
| regex | POSIX Regular Expression Match |
| iregex | POSIX Regular Expression Match. Case Insensitive |
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
_12{_12 blogCollection(_12 filter: {id: {lt: 3}},_12 ) {_12 edges {_12 cursor_12 node {_12 id_12 }_12 }_12 }_12}
** 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.
and Filter Query"
_20{_20 blogCollection(_20 filter: {_20 and: [_20 {id: {eq: 1}}_20 {name: {eq: "A: Blog 1"}}_20 ]_20 }_20 ) {_20 edges {_20 cursor_20 node {_20 id_20 name_20 description_20 createdAt_20 }_20 }_20 }_20}
and Filter Result"
_17{_17 "data": {_17 "blogCollection": {_17 "edges": [_17 {_17 "node": {_17 "id": 1,_17 "name": "A: Blog 1",_17 "createdAt": "2023-07-24T04:01:09.882781",_17 "description": "a desc1"_17 },_17 "cursor": "WzFd"_17 }_17 ]_17 }_17 }_17}
or Filter Query"
_20{_20 blogCollection(_20 filter: {_20 or: [_20 {id: {eq: 1}}_20 {name: {eq: "A: Blog 2"}}_20 ]_20 }_20 ) {_20 edges {_20 cursor_20 node {_20 id_20 name_20 description_20 createdAt_20 }_20 }_20 }_20}
or Filter Result"
_26{_26 "data": {_26 "blogCollection": {_26 "edges": [_26 {_26 "node": {_26 "id": 1,_26 "name": "A: Blog 1",_26 "createdAt": "2023-07-24T04:01:09.882781",_26 "description": "a desc1"_26 },_26 "cursor": "WzFd"_26 },_26 {_26 "node": {_26 "id": 2,_26 "name": "A: Blog 2",_26 "createdAt": "2023-07-24T04:01:09.882781",_26 "description": "a desc2"_26 },_26 "cursor": "WzJd"_26 }_26 ]_26 }_26 }_26}
** Example: not **
not accepts a single <Type>Filter.
not Filter Query"
_17{_17 blogCollection(_17 filter: {_17 not: {id: {eq: 1}}_17 }_17 ) {_17 edges {_17 cursor_17 node {_17 id_17 name_17 description_17 createdAt_17 }_17 }_17 }_17}
not Filter Result"
_35{_35 "data": {_35 "blogCollection": {_35 "edges": [_35 {_35 "node": {_35 "id": 2,_35 "name": "A: Blog 2",_35 "createdAt": "2023-07-24T04:01:09.882781",_35 "description": "a desc2"_35 },_35 "cursor": "WzJd"_35 },_35 {_35 "node": {_35 "id": 3,_35 "name": "A: Blog 3",_35 "createdAt": "2023-07-24T04:01:09.882781",_35 "description": "a desc3"_35 },_35 "cursor": "WzNd"_35 },_35 {_35 "node": {_35 "id": 4,_35 "name": "B: Blog 3",_35 "createdAt": "2023-07-24T04:01:09.882781",_35 "description": "b desc1"_35 },_35 "cursor": "WzRd"_35 }_35 ]_35 }_35 }_35}
** Example: nested composition **
The and, or and not operators can be arbitrarily nested inside each other.
_21{_21 blogCollection(_21 filter: {_21 or: [_21 { id: { eq: 1 } }_21 { id: { eq: 2 } }_21 { and: [{ id: { eq: 3 }, not: { name: { eq: "A: Blog 2" } } }] }_21 ]_21 }_21 ) {_21 edges {_21 cursor_21 node {_21 id_21 name_21 description_21 createdAt_21 }_21 }_21 }_21}
** Example: empty **
Empty filters are ignored, i.e. they behave as if the operator was not specified at all.
_17{_17 blogCollection(_17 filter: {_17 and: [], or: [], not: {}_17 }_17 ) {_17 edges {_17 cursor_17 node {_17 id_17 name_17 description_17 createdAt_17 }_17 }_17 }_17}
** 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.
_21{_21 blogCollection(_21 filter: {_21 # Equivalent to not: { and: [{id: {eq: 1}}, {name: {eq: "A: Blog 1"}}]}_21 not: {_21 id: {eq: 1}_21 name: {eq: "A: Blog 1"}_21 }_21 }_21 ) {_21 edges {_21 cursor_21 node {_21 id_21 name_21 description_21 createdAt_21 }_21 }_21 }_21}
This means that an and filter can be often be simplified. In the following example all queries are equivalent and produce the same result.
and Query"
_21{_21 blogCollection(_21 filter: {_21 and: [_21 {id: {gt: 0}}_21 {id: {lt: 2}}_21 {name: {eq: "A: Blog 1"}}_21 ]_21 }_21 ) {_21 edges {_21 cursor_21 node {_21 id_21 name_21 description_21 createdAt_21 }_21 }_21 }_21}
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.
_21{_21 blogCollection(_21 filter: {_21 # This is really an `and` in `or`'s clothing_21 or: {_21 id: {eq: 1}_21 name: {eq: "A: Blog 2"}_21 }_21 }_21 ) {_21 edges {_21 cursor_21 node {_21 id_21 name_21 description_21 createdAt_21 }_21 }_21 }_21}
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.
note
Avoid naming your columns and, or or not. If you do, the corresponding filter operator will not be available for use.
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.
_11type Query {_11_11 blogCollection(_11_11 """Sort order to apply to the collection"""_11 orderBy: [BlogOrderBy!]_11_11 ...truncated..._11_11 ): BlogConnection_11}
Example
_11{_11 blogCollection(_11 orderBy: [{id: DescNullsLast}]_11 ) {_11 edges {_11 node {_11 id_11 }_11 }_11 }_11}
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
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null default now(),_10 "updatedAt" timestamp_10);
_41"""The root type for creating and mutating data"""_41type Mutation {_41_41 """Adds one or more `BlogInsertResponse` records to the collection"""_41 insertIntoBlogCollection(_41_41 """Records to add to the Blog collection"""_41 objects: [BlogInsertInput!]!_41_41 ): BlogInsertResponse_41_41 """Updates zero or more records in the collection"""_41 updateBlogCollection(_41 """_41 Fields that are set will be updated for all records matching the `filter`_41 """_41 set: BlogUpdateInput!_41_41 """Restricts the mutation's impact to records matching the critera"""_41 filter: BlogFilter_41_41 """_41 The maximum number of records in the collection permitted to be affected_41 """_41 atMost: Int! = 1_41_41 ): BlogUpdateResponse!_41_41 """Deletes zero or more records from the collection"""_41 deleteFromBlogCollection(_41 """Restricts the mutation's impact to records matching the critera"""_41 filter: BlogFilter_41_41 """_41 The maximum number of records in the collection permitted to be affected_41 """_41 atMost: Int! = 1_41_41 ): BlogDeleteResponse!_41_41}
Insert#
To add records to a collection, use the insertInto<Table>Collection field on the Mutation type.
SQL Setup
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null default now(),_10 "updatedAt" timestamp_10);
GraphQL Types
_12"""The root type for creating and mutating data"""_12type Mutation {_12_12 """Adds one or more `BlogInsertResponse` records to the collection"""_12 insertIntoBlogCollection(_12_12 """Records to add to the Blog collection"""_12 objects: [BlogInsertInput!]!_12_12 ): BlogInsertResponse_12_12}
Where elements in the objects array are inserted into the underlying table.
Example
_14mutation {_14 insertIntoBlogCollection(_14 objects: [_14 {name: "foo"},_14 {name: "bar"},_14 ]_14 ) {_14 affectedCount_14 records {_14 id_14 name_14 }_14 }_14}
Update#
To update records in a collection, use the update<Table>Collection field on the Mutation type.
SQL Setup
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null default now(),_10 "updatedAt" timestamp_10);
GraphQL Types
_21"""The root type for creating and mutating data"""_21type Mutation {_21_21 """Updates zero or more records in the collection"""_21 updateBlogCollection(_21 """_21 Fields that are set will be updated for all records matching the `filter`_21 """_21 set: BlogUpdateInput!_21_21 """Restricts the mutation's impact to records matching the critera"""_21 filter: BlogFilter_21_21 """_21 The maximum number of records in the collection permitted to be affected_21 """_21 atMost: Int! = 1_21_21 ): BlogUpdateResponse!_21_21}
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
_12mutation {_12 updateBlogCollection(_12 set: {name: "baz"}_12 filter: {id: {eq: 1}}_12 ) {_12 affectedCount_12 records {_12 id_12 name_12 }_12 }_12}
Delete#
To remove records from a collection, use the deleteFrom<Table>Collection field on the Mutation type.
SQL Setup
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null,_10 description varchar(255),_10 "createdAt" timestamp not null default now(),_10 "updatedAt" timestamp_10);
GraphQL Types
_16"""The root type for creating and mutating data"""_16type Mutation {_16_16 """Deletes zero or more records from the collection"""_16 deleteFromBlogCollection(_16 """Restricts the mutation's impact to records matching the critera"""_16 filter: BlogFilter_16_16 """_16 The maximum number of records in the collection permitted to be affected_16 """_16 atMost: Int! = 1_16_16 ): BlogDeleteResponse!_16_16}
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
_11mutation {_11 deleteFromBlogCollection(_11 filter: {id: {eq: 1}}_11 ) {_11 affectedCount_11 records {_11 id_11 name_11 }_11 }_11}
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.
relay support
ID field for types to have the name id. pg_graphql uses nodeId by default to avoid conflicting with user defined id columns. You can configure relay to work with pg_graphql's nodeId field with relay's nodeInterfaceIdField option. More info available here.SQL Setup
_10create table "Blog"(_10 id serial primary key,_10 name varchar(255) not null_10);
GraphQL Types
_10type Blog {_10 nodeId: ID! # this field_10 id: Int!_10 name: String!_10}
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
_11create table "Blog"(_11 id serial primary key,_11 name varchar(255) not null_11);_11_11create table "BlogPost"(_11 id serial primary key,_11 "blogId" integer not null references "Blog"(id),_11 title varchar(255) not null,_11 body varchar(10000)_11);
GraphQL Types
_30type Blog {_30_30 # globally unique identifier_30 nodeId: ID!_30_30 id: Int!_30 name: String!_30 description: String_30_30 blogPostCollection(_30 """Query the first `n` records in the collection"""_30 first: Int_30_30 """Query the last `n` records in the collection"""_30 last: Int_30_30 """Query values in the collection before the provided cursor"""_30 before: Cursor_30_30 """Query values in the collection after the provided cursor"""_30 after: Cursor_30_30 """Filters to apply to the results set when querying from the collection"""_30 filter: BlogPostFilter_30_30 """Sort order to apply to the collection"""_30 orderBy: [BlogPostOrderBy!]_30 ): BlogPostConnection_30_30}
Where blogPostCollection exposes the full Query interface to BlogPosts.
Example
_17{_17 blogCollection {_17 edges {_17 node {_17 name_17 blogPostCollection {_17 edges {_17 node {_17 id_17 title_17 }_17 }_17 }_17 }_17 }_17 }_17}
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
_11create table "Blog"(_11 id serial primary key,_11 name varchar(255) not null_11);_11_11create table "BlogPost"(_11 id serial primary key,_11 "blogId" integer not null references "Blog"(id),_11 title varchar(255) not null,_11 body varchar(10000)_11);
GraphQL Types
_10type BlogPost {_10 nodeId: ID!_10 id: Int!_10 blogId: Int!_10 title: String!_10 body: String_10_10 blog: Blog_10}
Where blog exposes the Blog record associated with the BlogPost.
_12{_12 blogPostCollection {_12 edges {_12 node {_12 title_12 blog {_12 name_12 }_12 }_12 }_12 }_12}
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
_10create table "EmailAddress"(_10 id serial primary key,_10 address text unique not null_10);_10_10create table "Employee"(_10 id serial primary key,_10 name text not null,_10 email_address_id int unique references "EmailAddress"(id)_10);
GraphQL Types
_10type Employee {_10 nodeId: ID!_10 id: Int!_10 name: String!_10 emailAddressId: Int_10 emailAddress: EmailAddress_10}
Example
_19{_19 "data": {_19 "employeeCollection": {_19 "edges": [_19 {_19 "node": {_19 "name": "Foo Barington",_19 "emailAddress": {_19 "address": "foo@bar.com",_19 "employee": {_19 "name": "Foo Barington"_19 }_19 }_19 }_19 }_19 ]_19 }_19 }_19}
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.
_10scalar JSON
Example
Given the setup
_10create table "User"(_10 id bigserial primary key,_10 config jsonb_10);_10_10insert into "User"(config)_10values (jsonb_build_object('palette', 'dark-mode'));
The query
_10{_10 userCollection {_10 edges {_10 node {_10 config_10 }_10 }_10 }_10}
The returns the following data. Note that config is serialized as a string
_13{_13 "data": {_13 "userCollection": {_13 "edges": [_13 {_13 "node": {_13 "config": "{\"palette\": \"dark-mode\"}"_13 }_13 }_13 ]_13 }_13 }_13}
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.
_12scalar BigInt_12_12input BigIntFilter {_12 eq: BigInt_12 gt: BigInt_12 gte: BigInt_12 in: [BigInt!]_12 lt: BigInt_12 lte: BigInt_12 neq: BigInt_12 is: FilterIs_12}
Example
Given the setup
_10create table "Person"(_10 id bigserial primary key,_10 name text_10);_10_10insert into "Person"(name)_10values ('J. Bazworth');
The query
_10{_10 personCollection {_10 edges {_10 node {_10 id_10 name_10 }_10 }_10 }_10}
The returns the following data. Note that id is serialized as a string
_14{_14 "data": {_14 "personCollection": {_14 "edges": [_14 {_14 "node": {_14 "id": "1",_14 "name": "Foo Barington",_14 }_14 }_14 ]_14 }_14 }_14}
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.
_12scalar BigFloat_12_12input BigFloatFilter {_12 eq: BigFloat_12 gt: BigFloat_12 gte: BigFloat_12 in: [BigFloat!]_12 lt: BigFloat_12 lte: BigFloat_12 neq: BigFloat_12 is: FilterIs_12}
Example
Given the SQL setup
_10create table "GeneralLedger"(_10 id serial primary key,_10 amount numeric(10,2)_10);_10_10insert into "GeneralLedger"(amount)_10values (22.15);
The query
_10{_10 generalLedgerCollection {_10 edges {_10 node {_10 id_10 amount_10 }_10 }_10 }_10}
The returns the following data. Note that amount is serialized as a string
_14{_14 "data": {_14 "generalLedgerCollection": {_14 "edges": [_14 {_14 "node": {_14 "id": 1,_14 "amount": "22.15",_14 }_14 }_14 ]_14 }_14 }_14}
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.
_10scalar Opaque_10_10input OpaqueFilter {_10 eq: Opaque_10 is: FilterIs_10}