Skip to main content

GraphQL Queries and Mutations

With built-in resolver functions that automatically handle filtering and sorting your query and paging through results without requiring custom parameters for each table.

This section will closely follow the Introduction to GraphQL: https://graphql.org/learn/. Devii has a very similar structure, however, there are a few Devii specific methods for GraphQL queries and mutations that will be specifically called out.

When you use the /query or /roles_pbac endpoints.

General information​

The only requirement to form a query are fields from your schema, beginning with the ‘root’ which in this case is a database table name, and fields which are the columns of the table that is being queried. Below is a very simple query, as you can see the query returns the data in the same shape as the query, every data return will look and feel the same as your query so you will always know how your data is returned.

Example Query:

query products_with_lines {
  products {
    productCode
    productName
    productline {
      productLine
      textDescription
    }
  }
}
tip

Use Portal's GraphiQL to try these examples out yourself.

Schema and Types​

  • A GraphQL schema is the core of GraphQL, a schema reflects the data shape from your database and is used to define a collection of types and the relationships between these types that are used to specify the exact queries and mutations which are available to execute against.
type people {
  id: ID!
  name: String
  birthYear: String
  eyeColor: String
  gender: String
  hairColor: String
  height: Int
  mass: Float
  skinColor: String
  created: String
  edited: String
  speciesid: String
  homeworldid: String
  planet: planet
  species: species
  films_collection: [films]
  starships_collection: [starships]
  vehicles_collection: [vehicles]
}

  • GraphQL types: at the top level there is Query (to query your data) and Mutation (to add, update or delete); depending on the policy of your tenant, and the permissions it grants you, you may or may not be allowed to execute mutations on all tables you can query. Permissions are discussed in the Security Engine documents.

  • GraphQL has a set of default scalar types; also, if any of the scalar types are followed by an ‘!’, that object is a non-nullable object in your database and must always return a value.

    • Int: a 32-bit integer (ex: 1)

    • Float: a double precision floating point value (ex: 1.52)

    • String: a UTF-8 character sequence (ex: Charlie)

    • Boolean: True or False

    • ID: The ID scalar type represents a unique identifier, often used to refetch an object or as the key for a cache. The ID type is serialized in the same way as a String; however, defining it as an ID signifies that it is not intended to be human‐readable.

Queries​

Fields / Column​

GraphQL is about asking for specific fields on objects. Let's start by looking at a very simple query and the result we get when we run it:

GraphQLJSON Response
{
  people {
    name
  }
}
{
  "data": {
    "people": [
      {
        "name": "Luke Skywalker"
      }
    ]
  }
}

Object Fields - Sub-Query​

Fields can also refer to Objects. In that case, you can make a sub-selection of fields for that object. GraphQL queries can traverse related objects and their fields, letting clients fetch lots of related data in one request.

GraphQLJSON Response
{
  people(filter: "name='Boba Fett'") {
    name
    birthYear
    eyeColor
    hairColor
    skinColor
    films_collection {
      title
      episodeID
      releaseDate
    }
  }
}
{
  "data": {
    "people": [
      {
        "birthYear": "31.5BBY",
        "eyeColor": "brown",
        "films_collection": [
          {
            "episodeID": 5,
            "releaseDate": "1980-05-17T00:00:00",
            "title": "The Empire Strikes Back"
          },
          {
            "episodeID": 2,
            "releaseDate": "2002-05-16T00:00:00",
            "title": "Attack of the Clones"
          },
          {
            "episodeID": 6,
            "releaseDate": "1983-05-25T00:00:00",
            "title": "Return of the Jedi"
          }
        ],
        "hairColor": "black",
        "name": "Boba Fett",
        "skinColor": "fair"
      }
    ]
  }
}

Query Parameters​

  • Filter please see our Filter Expressions document for full details on the expressions that are available in Devii.
GraphQLJSON Response
{
  people(filter: "id = 'cGVvcGxlOjE='") {
    id
    name
  }
}
{
  "data": {
    "people": [
      {
        "id": "cGVvcGxlOjE=",
        "name": "Luke Skywalker"
      }
    ]
  }
}
  • Ordering - is an array of column names to order by, with each name optionally followed by the word "desc" to sort in reverse order The default for ordering is ascending order To make the query in descending order you need to add ‘desc’ to the query
GraphQLJSON Response
{
  people(ordering: ["name desc"]) {
    id
    name
  }
}
{
  "data": {
    "people": [
      {
        "id": "cGVvcGxlOjcw",
        "name": "Zam Wesell"
      },
      {
        "id": "cGVvcGxlOjIw",
        "name": "Yoda"
      },
      {
        "id": "cGVvcGxlOjU3",
        "name": "Yarael Poof"
      }
    ]
  }
}

"limit" and "offset" are integers, which you can use to paginate results.

  • Limit will limit the number of records returned
GraphQLJSON Response
{
  people(limit: 2) {
    id
    name
  }
}
{
  "data": {
    "people": [
      {
        "id": "cGVvcGxlOjE=",
        "name": "Luke Skywalker"
      },
      {
        "id": "cGVvcGxlOjI=",
        "name": "C-3PO"
      }
    ]
  }
}
  • Offset skip that many rows before beginning to return rows
GraphQLJSON Response
{
  people(limit: 2, offset: 4) {
    id
    name
  }
}
{
  "data": {
    "people": [
      {
        "id": "cGVvcGxlOjU=",
        "name": "Leia Organa"
      },
      {
        "id": "cGVvcGxlOjY=",
        "name": "Owen Lars"
      }
    ]
  }
}

Aliases​

GraphQLJSON Response
{
  empireCharacters: people(
    filter: "films_collection.episodeID = 5"
    ordering: ["name desc"]
    limit: 3
  ) {
    PersonName: name
  }
}
{
  "data": {
    "empireCharacters": [
      {
        "PersonName": "Yoda"
      },
      {
        "PersonName": "Wedge Antilles"
      },
      {
        "PersonName": "R2-D2"
      }
    ]
  }
}

Fragments​

GraphQLJSON Response
{
  empireCharacters: people(
    filter: "films_collection.episodeID = 5"
    ordering: ["name asc"]
    limit: 3
  ) {
    ...peoplefields
  }
  jediCharacters: people(
    filter: "films_collection.episodeID = 6"
    ordering: ["name"]
    limit: 3
  ) {
    ...peoplefields
  }
}

fragment peoplefields on people {
  name
  eyeColor
  hairColor
}
{
  "data": {
    "empireCharacters": [
      {
        "eyeColor": "brown",
        "hairColor": "black",
        "name": "Boba Fett"
      },
      {
        "eyeColor": "red",
        "hairColor": "none",
        "name": "Bossk"
      },
      {
        "eyeColor": "yellow",
        "hairColor": "n/a",
        "name": "C-3PO"
      }
    ],
    "jediCharacters": [
      {
        "eyeColor": "orange",
        "hairColor": "none",
        "name": "Ackbar"
      },
      {
        "eyeColor": "brown",
        "hairColor": "brown",
        "name": "Arvel Crynyd"
      },
      {
        "eyeColor": "pink",
        "hairColor": "none",
        "name": "Bib Fortuna"
      }
    ]
  }
}

Operation name​

EpisodeCharacters as operation name.

GraphQLJSON Response
query EpisodeCharacters {
  people(limit: 3) {
    name
  }
}
{
  "data": {
    "people": [
      {
        "name": "Luke Skywalker"
      },
      {
        "name": "C-3PO"
      },
      {
        "name": "R2-D2"
      }
    ]
  }
}

Variables​

GraphQLJSON Response
query EpisodeCharacters($filter: String, $limit: Int) {
  people(filter: $filter, limit: $limit) {
    name
  }
}
variables
{
  "filter": "films_collection.episodeID = 6",
  "limit": 3
}
{
  "data": {
    "people": [
      {
        "name": "Luke Skywalker"
      },
      {
        "name": "C-3PO"
      },
      {
        "name": "R2-D2"
      }
    ]
  }
}

Directives​

Devii's GraphQL implementation provides the default directives: @skip, @include, and @deprecated.

Directives are preceded by the @ character, like so:

type ExampleType {

  oldField: String @deprecated(reason: "Use `newField`.")

  newField: String

}

Directives can take arguments of their own (reason in this case).

Directives appear after the declaration of what they decorate (the oldField field in this case)

Schema Directive:

The @deprecated directive is used within the type system definition language to indicate deprecated portions of a GraphQL service's schema, such as deprecated fields on a type or deprecated enum values.

Operation directives: The @skip directive is provided for fields, fragment spreads, and inline fragments, and allows for conditional exclusion during execution as described by the if argument. @skip(if: Boolean!) If true, the decorated field or fragment in an operation is not resolved by the GraphQL server.

The @include directive provided for fields, fragment spreads, and inline fragments, and allows for conditional inclusion during execution as described by the if argument. @include(if: Boolean!) If false, the decorated field or fragment in an operation is not resolved by the GraphQL server.

More information about directives can be found here: https://graphql.org/learn/queries/#directives

In this example films_collection will only be queried if the variable $inMovie has the value true

GraphQLJSON Response
query EpisodeCharacters($limit: Int, $inMovie: Boolean!) {
  people(limit: $limit) {
    name
    films_collection @include(if: $inMovie) {
      title
    }
  }
}
variables
{
  "limit": 2,
  "inMovie": true
}
{
  "data": {
    "people": [
      {
        "films_collection": [
          {
            "title": "Return of the Jedi"
          },
          {
            "title": "Revenge of the Sith"
          },
          {
            "title": "A New Hope"
          },
          {
            "title": "The Empire Strikes Back"
          },
          {
            "title": "The Force Awakens"
          }
        ],
        "name": "Luke Skywalker"
      },
      {
        "films_collection": [
          {
            "title": "Return of the Jedi"
          },
          {
            "title": "Revenge of the Sith"
          },
          {
            "title": "A New Hope"
          },
          {
            "title": "The Empire Strikes Back"
          }
        ],
        "name": "Darth Vader"
      }
    ]
  }
}

Aggregates​

GraphQLJSON Response
{
  Aggregates {
    count(subquery: "query {films{id}}")
  }
}
{
  "data": {
    "Aggregates": {
      "count": [
        {
          "count": 7
        }
      ]
    }
  }
}

Pagination Code Example​

Show Code
import { ApolloClient, InMemoryCache, gql } from "@apollo/client";

const ACCESS_TOKEN = auth_response.access_token;
const QUERY_URL = auth_response.routes.query;

const client = new ApolloClient({
  uri: queryUrl,
  headers: {
    Authorization: ACCESS_TOKEN ? `Bearer ${ACCESS_TOKEN}` : "",
  },
  cache: new InMemoryCache(),
});

client
  .query({
    query: gql`
    query get${reducedResource}($filter: String, $ordering: [String], $limit: Int, $offset: Int) {
      ${reducedResource}(filter: $filter, ordering: $ordering, limit: $limit, offset: $offset) {
        ${fields[resource]}
      }
    Aggregates {
      count(subquery: "query ${reducedResource}{${reducedResource}{${alias_id_fields[reducedResource]}}}", filter: $filter)
      }
    }`,

    variables: {
      limit: perPage === 0 ? null : perPage,
      offset: perPage === 0 ? null : (page - 1) * perPage,
      ordering: [`${[sort_field]} ${order.toLowerCase()}`],
      filter: filterString,
    },
    fetchPolicy: "network-only",
  })
  .then((result) => {
    return {
      data: result.data[reducedResource],
      total: result.data["Aggregates"].count[0].count,
    };
  })
  .catch((error) => {
    throw new Error(error);
  });

Mutations​

Create​

Create mutations allow you to create new records.

mutation {
  create_role(
    input: {
      tenantid: 14
      login: "testlogin"
      name: "test user"
      password: "testpassword"
    }
  ) {
    roleid
    login
    name
    classes {
      classid
      name
    }
    capabilities
  }
}
Show Code
import { ApolloClient, InMemoryCache, gql } from "@apollo/client";

const ACCESS_TOKEN = auth_response.access_token;
const QUERY_URL = auth_response.routes.query;

const client = new ApolloClient({
  uri: queryUrl,
  headers: {
    Authorization: ACCESS_TOKEN ? `Bearer ${ACCESS_TOKEN}` : "",
  },
  cache: new InMemoryCache(),
});

client
  .mutate({
    mutation: gql`
        mutation ($data: ${reducedResource}Input!) {
            create_${reducedResource}(input: $data) {
              ${fields[reducedResource]}
            }
        }`,
    variables: {
      data: omit(params.data, ["__typename", "createtime"]),
    },
  })
  .then((result) => ({
    data: {
      ...result.data[`create_${reducedResource}`],
      id:
        result.data[`create_${reducedResource}`][
          real_id_field[reducedResource]
        ] ?? result.data[`create_${reducedResource}`]["id"],
    },
  }));

Update​

Update mutations let you update existing records.

mutation {
  update_role(
    input: { tenantid: 14, login: "testuser", name: "Test User" }
    roleid: "123"
  ) {
    roleid
    login
    name
    classes {
      classid
      name
    }
    capabilities
  }
}
Show Code
import { ApolloClient, InMemoryCache, gql } from "@apollo/client";

const ACCESS_TOKEN = auth_response.access_token;
const QUERY_URL = auth_response.routes.query;

const client = new ApolloClient({
  uri: queryUrl,
  headers: {
    Authorization: ACCESS_TOKEN ? `Bearer ${ACCESS_TOKEN}` : "",
  },
  cache: new InMemoryCache(),
});

client
  .mutate({
    mutation: gql`
        mutation ($id: ID!, $data: ${reducedResource}Input!) {
            update_${reducedResource}(${real_id_field[reducedResource]}:$id input: $data) {
                ${fields[reducedResource]}
            }
        }`,
    variables: {
      id: params.id,
      // Need to omit the __typename and createtime as they can"t be updated in the record, and the server will come back with an error.
      data: omit(params.data, ["__typename", "createtime"]),
    },
  })
  .then((result) => ({
    data: {
      ...result.data[`update_${reducedResource}`],
      id:
        result.data[`update_${reducedResource}`][
          real_id_field[reducedResource]
        ] ?? result.data[`update_${reducedResource}`]["id"],
    },
  }));

Upsert​

Upsert mutations allow you to perform create or update operations based on whether a particular ID exists in the database.

The create_ mutations will have an optional argument, onconflict, which can be "fail" (the default), "update", or "skip". If "update", it will search for existing rows using any primary key or unique constraints on the table, with the input values. If it finds a row, it will update it, otherwise it will insert. If "skip", it will do the same as above, except that if it finds an existing row, it will not update, just skip, and "fail" is the default behavior, where it will throw an exception.

caution

Keep in mind, it matches on all columns with pkey and unique contraints. So if you change one of those, it won't find the row. You're better off in that case doing an explicit update.

onconflict: update example - It will insert into the database.
GraphQLJSON Response
mutation {
  create_user(
    input: {
      username: "TestUser"
      comment: "All words are made up!"
      email: ""
    }
    onconflict: update
  ) {
    username
    email
    comment
    create_time
    id
  }
}
{
  "data": {
    "create_user": {
      "comment": "All words are made up!",
      "create_time": "2023-06-02T19:20:13",
      "email": "",
      "id": "14",
      "username": "TestUser"
    }
  }
}
onconflict: update example 2 - Unlike a normal insert a 2nd run of the same create_user mutation will return the same response instead of providing an error message because of the duplicate entry on a unique field.
GraphQLJSON Response
mutation {
  create_user(
    input: {
      username: "TestUser"
      comment: "All words are made up!"
      email: ""
    }
    onconflict: update
  ) {
    username
    email
    comment
    create_time
    id
  }
}
{
  "data": {
    "create_user": {
      "comment": "All words are made up!",
      "create_time": "2023-06-02T19:20:13",
      "email": "",
      "id": "14",
      "username": "TestUser"
    }
  }
}
onconflict: fail example - TestUser already exists in the database.
GraphQLJSON Response
mutation {
  create_user(
    input: {
      username: "TestUser"
      comment: "All words are made up!"
      email: ""
    }
    onconflict: fail
  ) {
    username
    email
    comment
    create_time
    id
  }
}
{
  "error": "1062:Duplicate entry 'TestUser' for key 'user.username_UNIQUE'",
  "status": 400
}
onconflict: update example 3 - If you change the existing comment and run it again it will update the comment.
GraphQLJSON Response
mutation {
  create_user(
    input: {
      username: "TestUser"
      comment: "Nothing is real."
      email: "" 
    }
    onconflict: update
  ) {
    username
    email
    comment
    create_time
    id
  }
}
{
  "data": {
    "create_user": {
      "comment": "Nothing is real.",
      "create_time": "2023-06-02T19:20:13",
      "email": "",
      "id": "14",
      "username": "TestUser"
    }
  }
}
onconflict: skip example - If you change the comment and run it again it will NOT update the comment.
GraphQLJSON Response
mutation {
  create_user(
    input: {
      username: "TestUser"
      comment: "Life is short."
      email: "" 
    }
    onconflict: skip
  ) {
    username
    email
    comment
    create_time
    id
  }
}
{
  "data": {
    "create_user": {
      "comment": "Nothing is real.",
      "create_time": "2023-06-02T19:20:13",
      "email": "",
      "id": "14",
      "username": "TestUser"
    }
  }
}

Delete​

Delete Mutations allow you to delete records.

mutation {
  delete_role(roleid: "123") {
    roleid
  }
}
Show Code
import { ApolloClient, InMemoryCache, gql } from "@apollo/client";

const ACCESS_TOKEN = auth_response.access_token;
const QUERY_URL = auth_response.routes.query;

const client = new ApolloClient({
  uri: queryUrl,
  headers: {
    Authorization: ACCESS_TOKEN ? `Bearer ${ACCESS_TOKEN}` : "",
  },
  cache: new InMemoryCache(),
});

client
  .mutate({
    mutation: gql`
        mutation ($id: ID!) {
            delete_${reducedResource}(${real_id_field[reducedResource]}: $id) {
              ${fields[reducedResource]}
            }
        }`,
    variables: {
      id: params.id,
    },
    update(cache, { data }) {
      cache.evict({
        id: `${reducedResource}:${data[`delete_${reducedResource}`]?.id}`,
      });
    },
  })
  .then((result) => ({
    data: result.data[`delete_${reducedResource}`],
  }));