Using the Stargate GraphQL API (CQL-first)

Stargate is a data gateway deployed between client applications and a database. The GraphQL API plugin that exposes CRUD access to data stored in Cassandra tables.

The CQL-first approach directly translates CQL tables into GraphQL types, mutations, and queries. The GraphQL schema is automatically generated from the keyspace, tables, and columns defined, but no customization is allowed. A standard set of mutations and queries are produced for searching and modifying the table data. If you are familiar with Cassandra, you might prefer this approach.

For more information about the GraphQL API, see the blog post on the GraphQL API.

Prerequisites

To use Stargate you need:

Docker installed and running, if using Docker
GraphQL Playground running to deploy schema, and execute mutations and queries
Optional: cURL to run GraphQL queries from command-line
Optional: Postman collections if you wish to execute mutations and queries in Postman

If you are looking to just get started, DataStax Astra Database-as-a-Service can get you started with no install steps.

This image contains the Cassandra Query Language (CQL), REST, Document, GraphQL APIs, and GraphQL Playground, along with an Apache Cassandra^™ 3.11 backend.

docker pull stargateio/stargate-3_11:v1.0.29

Start the Stargate container in developer mode. Developer mode removes the need to set up a separate Cassandra instance and is meant for development and testing only.

docker run --name stargate \
  -p 8080:8080 \
  -p 8081:8081 \
  -p 8082:8082 \
  -p 127.0.0.1:9042:9042 \
  -d \
  -e CLUSTER_NAME=stargate \
  -e CLUSTER_VERSION=3.11 \
  -e DEVELOPER_MODE=true \
  stargateio/stargate-3_11:v1.0.29

Use the Auth API to generate an auth token

In order to use the Stargate Document API, an authorization token must be generated to access the interface. A REST API token is used for this purpose.

The step below uses cURL to access the REST interface to generate the needed token.

Generate an auth token

First generate an auth token that is required in each subsequent request in the X-Cassandra-Token header. Note the port for the auth service is 8081.

curl -L -X POST 'http://localhost:8081/v1/auth' \
  -H 'Content-Type: application/json' \
  --data-raw '{
    "username": "cassandra",
    "password": "cassandra"
}'

You should receive a token in the response.

{"authToken":"{auth-token}"}

You will need to add this token to the GraphQL Playground in order to authorize your GraphQL requests. Copy the value after "authToken" to use later.

Create or delete schema

In order to use the GraphQL API, you must create schema that defines the keyspace and tables that will store the data. A keyspace is a container for which a replication factor defines the number of data replicas the database will store. Tables consist of columns that have a defined data type. Multiple tables are contained in a keyspace, but a table cannot be contained in multiple keyspaces.

Create a keyspace

Before you can start using the GraphQL API, you must first create a Cassandra keyspace and at least one table in your database. If you are connecting to a Cassandra database with existing schema, you can skip this step.

Inside the GraphQL playground, navigate to http://localhost:8080/graphql-schema and create a keyspace by executing the following mutation:

# create a keyspace called library
mutation createKsLibrary {
  createKeyspace(name:"library", replicas: 1)
}

For each keyspace created in your Cassandra schema, a new path is created under the graphql-path root (default is: /graphql). For example, the mutation just executed creates a path /graphql/library for the library keyspace when Cassandra creates the keyspace.

Add the auth token to the HTTP Headers box in the lower lefthand corner:

{
  "X-Cassandra-Token":"bff43799-4682-4375-99e8-23c8a9d0f304"
}

Notice that the key for this JSON token is different than the value that the generate token has. It is X-Cassandra-Token, not auth-token.

Now run the mutation to create the keyspace. You should see a return value of:

{
  "data": {
    "createKeyspace": true
  }
}

Check keyspace existence

To check if a keyspace exists, execute a GraphQL query:

graphQL command
Result

query GetKeyspace {
  keyspace(name: "library") {
      name
      dcs {
          name
          replicas
      }
      tables {
          name
          columns {
              name
              kind
              type {
                  basic
                  info {
                      name
                  }
              }
          }
      }
  }
}

{
  "data": {
    "keyspace": {
      "name": "library",
      "dcs": [],
      "tables": [
        {
          "name": "book",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "author",
              "kind": "CLUSTERING",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "format",
              "kind": "REGULAR",
              "type": {
                "basic": "SET",
                "info": {
                  "name": null
                }
              }
            },
            {
              "name": "genre",
              "kind": "REGULAR",
              "type": {
                "basic": "SET",
                "info": {
                  "name": null
                }
              }
            },
            {
              "name": "isbn",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "language",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "pub_year",
              "kind": "REGULAR",
              "type": {
                "basic": "INT",
                "info": null
              }
            }
          ]
        },
        {
          "name": "reader",
          "columns": [
            {
              "name": "name",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "user_id",
              "kind": "CLUSTERING",
              "type": {
                "basic": "UUID",
                "info": null
              }
            },
            {
              "name": "addresses",
              "kind": "REGULAR",
              "type": {
                "basic": "LIST",
                "info": {
                  "name": null
                }
              }
            },
            {
              "name": "birthdate",
              "kind": "REGULAR",
              "type": {
                "basic": "DATE",
                "info": null
              }
            },
            {
              "name": "email",
              "kind": "REGULAR",
              "type": {
                "basic": "SET",
                "info": {
                  "name": null
                }
              }
            },
            {
              "name": "reviews",
              "kind": "REGULAR",
              "type": {
                "basic": "TUPLE",
                "info": {
                  "name": null
                }
              }
            }
          ]
        }
      }
    }
}

Delete a keyspace

You can delete a keyspace. All tables and table data will be deleted along with the keyspace schema.

mutation dropKsLibrary {
  dropKeyspace(name:"library", ifExists: true)
}

Create a user-defined type (UDT)

Optional user-defined types (UDTs) can be created and used in table definitions. This example creates a UDT called address_type that includes a street, city, state, and zipcode. If you plan to use UDTs as a data type for columns in your table, create the UDT first.

graphQL command
Result

# create a user-defined type (UDT)
mutation createAddressUDT {
  createType(
    keyspaceName: "library"
    typeName: "address_type"
    fields: [
      { name: "street", type: { basic: TEXT } }
      { name: "city", type: { basic: TEXT } }
      { name: "state", type: { basic: TEXT } }
      { name: "zip", type: { basic: TEXT } }
    ]
  )
}

{
  "data": {
    "createType": true
  }
}

Delete a user-defined type (UDT)

You can delete a UDT. All tables that use the UDT must first be deleted.

# drop a UDT
mutation dropType {
  dropType(keyspaceName:"library", typeName:"address_type", ifExists:true)
}

Create a table

After the keyspace exists, you can create two tables by executing the following mutation:

graphQL command
Result

# create two tables (book, reader) in library with a single mutation
# DATA TYPES: TEXT, UUID, SET(TEXT), TUPLE(TEXT, INT, DATE), LIST(UDT)
mutation createTables {
  book: createTable(
    keyspaceName:"library",
    tableName:"book",
    partitionKeys: [ # The keys required to access your data
      { name: "title", type: {basic: TEXT} }
    ]
    clusteringKeys: [
      { name: "author", type: {basic: TEXT} }
    ]
  )
  reader: createTable(
    keyspaceName:"library",
    tableName:"reader",
    partitionKeys: [
      { name: "name", type: {basic: TEXT} }
    ]
    clusteringKeys: [ # Secondary key used to access values within the partition
      { name: "user_id", type: {basic: UUID}, order: "ASC" }
  	]
    values: [
      { name: "birthdate", type: {basic: DATE} }
      { name: "email", type: {basic: SET, info:{ subTypes: [ { basic: TEXT } ] } } }
      { name: "reviews", type: {basic: TUPLE, info: { subTypes: [ { basic: TEXT }, { basic: INT }, { basic: DATE } ] } } }
      { name: "addresses", type: { basic: LIST, info: { subTypes: [ { basic: UDT, info: { name: "address_type", frozen: true } } ] } } }
    ]
  )
}

  "data": {
    "book": true,
    "reader": true
  }
}

It is worth noting that one mutation is used to create two tables. Information about partition keys and clustering keys can be found in the CQL reference.

The second table, reader, also defines a column using a user-defined type (UDT).

Naming conventions for GraphQL

The GraphQL API uses specific naming conventions to preserve capitalization and special characters. Note that if typical GraphQL naming conventions are used, such as camelCase, that the underlying Cassandra storage tables will use double quoting to preserve the capitalization. If a naming conflict occurs, an error results that the table already exists.

Table 1. GraphQL naming convention
GraphQL table name	CQL table name	GraphQL mutation format
foo	foo	insertfoo
Foo	"Foo"	insertFoo
foo_bar	foo_bar	insertfoo_bar
FooBar	"FooBar"	insertFooBar
Hellox21_	"Hello!"	insertHellox21_

==== Data types

Tables can be created with an option ifNotExists. They can also be created with collections (set, list, map), tuples, and UDTs.

==== IF NOT EXISTS option

A table can be created with an option ifNotExists that will only create the table if it does not already exist:

graphQL command

# create two tables, magazine and article, IF THEY DON'T EXIST
# DATA TYPES: TEXT, INT, LIST(TEXT)

mutation createTableIfNotExists {
  magazine: createTable(
    keyspaceName:"library",
    tableName:"magazine",
    partitionKeys: [ # The keys required to access your data
      { name: "title", type: {basic: TEXT} }
    ]
    clusteringKeys: [ # Secondary key used to access values within the partition
      { name: "pub_yr", type: {basic: INT}, order: "ASC" }
      { name: "pub_mon", type: {basic: INT} }
      { name: "mag_id", type: {basic: INT} }
    ],
    ifNotExists: true,
    values: [ # The values associated with the keys
      { name: "editor", type: {basic: TEXT} }
    ]
  )
  article: createTable(
    keyspaceName:"library",
    tableName:"article",
    partitionKeys: [ # The keys required to access your data
      { name: "title", type: {basic: TEXT} }
    ]
    clusteringKeys: [ # Secondary key used to access values within the partition
      { name: "mtitle", type: {basic: TEXT} }
    ],
    ifNotExists: true,
    values: [ # The values associated with the keys
      { name: "authors", type: {basic:LIST, info:{ subTypes: [ { basic: TEXT } ] } } }
    ]
  )
}

Result

{
  "data": {
    "magazine": true,
    "article": true
  }
}

One of these tables includes creating a column with the data type LIST, an ordered collection of text values.

==== Collection (set, list, map) columns

Including a collection in a table has a couple of extra parts:

graphQL command

# create a table with a MAP
# DATA TYPE: TEXT, INT, MAP(TEXT, DATE)
mutation createMapTable {
  badge: createTable (
    keyspaceName:"library",
    tableName: "badge",
    partitionKeys: [
      {name: "badge_type", type: {basic:TEXT}}
    ]
    clusteringKeys: [
      { name: "badge_id", type: { basic: INT} }
    ],
    ifNotExists:true,
    values: [
      {name: "earned", type:{basic:MAP, info:{ subTypes: [ { basic: TEXT }, {basic: DATE}]}}}
    ]
  )
}

Result

{
  "data": {
    "badge": true
  }
}

This example shows a map. A previous example shows a list. In the next example, a set will be used.

=== Add columns to table schema

If you need to add more attributes to something you are storing in a table, you can add one or more columns:

graphQL command

# alter a table and add columns
# DATA TYPES: TEXT, INT, SET(TEXT)
mutation alterTableAddCols {
  alterTableAdd(
    keyspaceName:"library",
    tableName:"book",
    toAdd:[
      { name: "isbn", type: { basic: TEXT } }
      { name: "language", type: {basic: TEXT} }
      { name: "pub_year", type: {basic: INT} }
      { name: "genre", type: {basic:SET, info:{ subTypes: [ { basic: TEXT } ] } } }
      { name: "format", type: {basic:SET, info:{ subTypes: [ { basic: TEXT } ] } } }
    ]
  )
}

Result

{
  "data": {
    "alterTableAdd": true
  }
}

=== Check table and column existence

To check if a table or particular table columns exist, execute a GraphQL query:

graphQL command

query GetTables {
  keyspace(name: "library") {
      name
      tables {
          name
          columns {
              name
              kind
              type {
                  basic
                  info {
                      name
                  }
              }
          }
      }
  }
}

Result

{
  "data": {
    "keyspace": {
      "name": "library",
      "tables": [
        {
          "name": "reader",
          "columns": [
            {
              "name": "name",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
          ]
        },
        {
          "name": "book",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "author",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "isbn",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            }
          ]
        }
      ]
    }
  }
}

Because these queries are named, the GraphQL playground will allow you to select which query to run. The first query will return information about the keyspace library and the tables within it. The second query will return just information about the tables in that keyspace.

=== Delete columns from table schema

If you find an attribute is no longer required in a table, you can remove a column. All column data will be deleted along with the column schema.

graphQL command

# drop a column format from a table
mutation dropColumnFormat {
    alterTableDrop(
    keyspaceName:"library",
    tableName:"book",
    toDrop:["format"]
  )
}

Result

{
  "data": {
    "alterTableDrop": true
  }
}

=== Delete a table

You can delete a table. All data will be deleted along with the table schema.

graphQL command

# drop a table
mutation dropTableBook {
  dropTable(keyspaceName:"library",
    tableName:"article")
}

Result

{
  "data": {
    "dropTable": true
  }
}

==== IF EXISTS option

You can delete a table after checking that it exists with the ifExists option. All data will be deleted along with the table schema.

graphQL command

# drop a table if it exists
mutation dropTableIfExists {
  dropTable(keyspaceName:"library",
    tableName:"magazine",
  ifExists: true)
}

Result

{
  "data": {
    "dropTable": true
  }
}

=== Create an index

Tables can contain partition keys and clustering keys, both of which define the primary key. A table can also include non-primary keys.

If you wish to create a table query that uses anything other than the partition key to define which row or rows are to be retrieved, a column index must be created on each column in order to read the data.

Currently, those indexes can be created with CQL or GraphQL.

Use the application token you generated to create schema in your keyspace using the GraphQL playground.

You can create an index using a mutation in /graphql-schema. In the following example, three indexes are created for the tables book and reader. The table columns for these indexes are created are author, birthdate, and email.

An index name can be defined, such as author_idx in this example. An additional option, indexType can be defined to use SAI indexes if desired.

graphQL mutation

mutation createIndexes {
  book: createIndex(
    keyspaceName:"library",
    tableName:"book",
    columnName:"author",
    indexName:"author_idx"
  )
  reader: createIndex(
      keyspaceName:"library",
      tableName:"reader",
      columnName:"birthdate",
      indexName:"reader_bdate_idx"
  )
  reader2: createIndex(
      keyspaceName:"library",
      tableName:"reader",
      columnName:"email",
      indexName:"reader_email_idx"
  )
}

cURL command

curl --location --request POST http://localhost:8080/graphql-schema \
--header "X-Cassandra-Token: $AUTH_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n  book: createIndex(\n    keyspaceName:\"library\",\n    tableName:\"book\",\n    columnName:\"author\",\n    indexName:\"author_idx\"\n  )\n  reader: createIndex(\n      keyspaceName:\"library\",\n      tableName:\"reader\",\n      columnName:\"birthdate\",\n      indexName:\"reader_bdate_idx\"\n  )\n  reader2: createIndex(\n      keyspaceName:\"library\",\n      tableName:\"reader\",\n      columnName:\"email\",\n      indexName:\"reader_email_idx\"\n  )\n}","variables":{}}'

Result

Result TBD

Here is an additional example, which creates indexes that could be used in the REST API examples:

cURL command

curl --location --request POST 'http://localhost:8080/api/graphql-schema' \
--header "X-Cassandra-Token: $AUTH_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createIndexes {\n  user1: createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"favorite_books\",\n    indexName:\"fav_books_idx\",\n    indexKind: VALUES\n  )\n  user2:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"top_three_tv_shows\",\n    indexName:\"tv_idx\",\n    indexKind: VALUES\n  )\n  user3:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evalv_idx\",\n    indexKind: VALUES\n  )\n   user4:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evalk_idx\",\n    indexKind: KEYS\n  )\n   user5:createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"evaluations\",\n    indexName:\"evale_idx\",\n    indexKind: ENTRIES\n  )\n    users6: createIndex(\n    keyspaceName:\"'$KEYSPACE_NAME'\",\n    tableName:\"'$TABLE_NAME'\",\n    columnName:\"current_country\",\n    indexName:\"country_idx\"\n  )\n}","variables":{}}'

CQL command

CREATE INDEX books_idx ON users_keyspace.users (VALUES(favorite_books));
CREATE INDEX tv_idx ON users_keyspace.users (VALUES (top_three_tv_shows));
CREATE INDEX evalk_idx ON users_keyspace.users (KEYS (evaluations));
CREATE INDEX evalv_idx ON users_keyspace.users (VALUES (evaluations));
CREATE INDEX evale_idx ON users_keyspace.users (ENTRIES (evaluations));
CREATE INDEX country_idx ON users_keyspace.users (VALUES (current_country));

Result

Result TBD

The CQL commands for creating these indexes is included here for reference. The cqlsh tool can be used to create the indexes if desired.

=== Delete an index If you find an index is no longer required on a table column, or you need to change the index, you can delete it. All index data will be deleted along with the index schema.

graphQL command

mutation dropIndexBdate {

  reader: dropIndex(
      keyspaceName:"library",
      indexName:"reader_bdate_idx"
  )
}

cURL command

curl --location --request POST http://localhost:8080/graphql-schema \
--header "X-Cassandra-Token: $AUTH_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation dropIndexBdate {\n\n  reader: dropIndex(\n      keyspaceName:\"library\",\n      indexName:\"reader_bdate_idx\"\n  )\n}","variables":{}}'

Result

{
  "data": {
    "reader": true
  }
}

== Interact with data stored in tables

=== API generation

Once schema is created, the GraphQL API generates mutations and queries can be used. In the GraphQL playground, expand the tabs on the righthand side labelled "DOCS" or "SCHEMA", to discover the items available and the syntax to use.

For each table in the Cassandra schema that we just created, several GraphQL fields are created for handling queries and mutations. For example, the GraphQL API generated for the books table is:

schema {
  query: Query
  mutation: Mutation
}

type Query {
  book(value: bookInput, filter: bookFilterInput, orderBy: [bookOrder], options: QueryOptions): bookResult
  bookFilter(filter: bookFilterInput!, orderBy: [bookOrder], options: QueryOptions): bookResult
}

type Mutation {
  insertbook(value: bookInput!, ifNotExists: Boolean, options: UpdateOptions): bookMutationResult
  updatebook(value: bookInput!, ifExists: Boolean, ifCondition: bookFilterInput, options: UpdateOptions): bookMutationResult
  deletebook(value: bookInput!, ifExists: Boolean, ifCondition: bookFilterInput, options: UpdateOptions): bookMutationResult
}

The query books() can query book values by equality. If no value argument is provided, then the first hundred (default pagesize) values are returned.

Several mutations are created that you can use to insert, update, or delete books. Some important facts about these mutations are:

insertBooks() is an upsert operation if a book with the same information exist, unless the ifNotExists is set to true.
updateBooks() is also an upsert operation, and will create a new book if it doesn’t exist, unless ifNotExists is set to true.
Using the ifNotExists or ifCondition options affects the performance of operations because of the compare-and-set execution path in Cassandra. Under the hood these operations are using a feature in Cassandra called lightweight transactions (LWTs).

As more tables are added to a keyspace, additional GraphQL fields will add query and mutation types that can be used to interact with the table data.

=== Write data

Any of the created APIs can be used to interact with the GraphQL data, to write or read data.

First, let’s navigate to your new keyspace library inside the playground. Change the location to http://localhost:8080/graphql/library and add a couple of books to the book table:

graphQL command

# insert 2 books in one mutation
mutation insert2Books {
  moby: insertbook(value: {title:"Moby Dick", author:"Herman Melville"}) {
    value {
      title
    }
  }
  catch22: insertbook(value: {title:"Catch-22", author:"Joseph Heller"}) {
    value {
      title
    }
  }
}

Result

{
  "data": {
    "moby": {
      "value": {
        "title": "Moby Dick"
      }
    },
    "catch22": {
      "value": {
        "title": "Catch-22"
      }
    }
  }
}

Note that the keyword value is used twice in the mutation. The first use defines the value that the record is set to, for instance, the title to Moby Dick and the author to Herman Melville. The second use defines the values that will be displayed after the success of the mutation, so that proper insertion can be verified. This same method is valid for updates and read queries.

==== Insertion options

Three insertion options are configurable during data insertion or updating:

An example insertion that sets the consistency level and TTL:

graphQL command

# insert a book and set the option for consistency level
mutation insertBookWithOption {
  nativeson: insertbook(value: {title:"Native Son", author:"Richard Wright"}, options: {consistency: LOCAL_QUORUM, ttl:86400}) {
    value {
      title
    }
  }
}

Result

{
  "data": {
    "moby": {
      "value": {
        "title": "Moby Dick"
      }
    }
  }
}

The serial consistency can also be set with serialConsistency in the options, if needed.

==== Insert collections (set, list, map)

Inserting a collection is simple. An example of inserting a list:

graphQL command

# insert an article USING A LIST (authors)
mutation insertArticle {
  magarticle: insertarticle(value: {title:"How to use GraphQL", authors: ["First author", "Second author"], mtitle:"Database Magazine"}) {
    value {
      title
      mtitle
      authors
    }
  }
}

Result

{
  "data": {
    "magarticle": {
      "value": {
        "title": "How to use GraphQL",
        "mtitle": "Database Magazine",
        "authors": [
          "First author",
          "Second author"
        ]
      }
    }
  }
}

A map is slightly more complex:

graphQL command

mutation insertOneBadge {
  gold: insertBadges(value: { btype:"Gold", earned: "2020-11-20", category: ["Editor", "Writer"] } ) {
    value {
      btype
      earned
      category
    }
  }
}

Result

{
  "data": {
    "gold": {
      "value": {
        "badge_type": "Gold",
        "badge_id": 100,
        "earned": [
          {
            "key": "Writer",
            "value": "2020-11-20"
          }
        ]
      }
    }
  }
}

==== Insert a tuple

Inserting a tuple involves inserting an object; note the use of item0, item`1, and so on, to insert the parts of the tuple

graphQL command

# insert a reader record that uses a TUPLE
mutation insertJaneWithTuple{
   jane: insertreader(
     value: {
       user_id: "b5b5666b-2a37-4d0b-a5eb-053e54fc242b"
       name: "Jane Doe"
       birthdate: "2000-01-01"
       email: ["janedoe@gmail.com", "janedoe@yahoo.com"]
       reviews: { item0: "Moby Dick", item1: 5, item2: "2020-12-01" }
     }
   ) {
     value {
       user_id
       name
       birthdate
       reviews {
        item0
        item1
        item2
      }
     }
   }
}

Result

{
  "data": {
    "jane": {
      "value": {
        "user_id": "b5b5666b-2a37-4d0b-a5eb-053e54fc242b",
        "name": "Jane Doe",
        "birthdate": "2000-01-01",
        "reviews": {
          "item0": "Moby Dick",
          "item1": 5,
          "item2": "2020-12-01"
        }
      }
    }
  }
}

==== Insert a user-defined type (UDT)

Inserting a UDT requires taking careful note of the brackets used:

graphQL command

# insert a reader record that uses a UDT
mutation insertReaderWithUDT{
  ag: insertreader(
    value: {
      user_id: "e0ed81c3-0826-473e-be05-7de4b4592f64"
      name: "Allen Ginsberg"
      birthdate: "1926-06-03"
      addresses: [{ street: "Haight St", city: "San Francisco", zip: "94016" }]
    }
  ) {
    value {
      user_id
      name
      birthdate
      addresses {
        street
        city
        zip
      }
    }
  }
 }

Result

{
  "data": {
    "ag": {
      "value": {
        "user_id": "e0ed81c3-0826-473e-be05-7de4b4592f64",
        "name": "Allen Ginsberg",
        "birthdate": "1926-06-03",
        "addresses": [
          {
            "street": "Haight St",
            "city": "San Francisco",
            "zip": "94016"
          }
        ]
      }
    }
  }
}

=== Read data

Let’s check that the data was inserted.

Now let’s search for a particular record using a WHERE clause. The primary key of the table can be used in the WHERE clause, but non-primary key columns cannot be used. The following query, looking at the location http://localhost:8080/graphql/library will get both the title and the author for the specified book WHERE title:"Moby Dick":

graphQL command

# get one book using the primary key title with a value
query oneBook {
    book (value: {title:"Moby Dick"}) {
      values {
      	title
      	author
      }
    }
}

Result

{
  "data": {
    "books": {
      "values": [
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        }
      ]
    }
  }
}

To find multiple books, an addition to the WHERE clause is required, to denote that the list of titles desired is IN a group:

graphQL command

# get 3 books using the primary keys with an "in" filter clause of the primary key title
query ThreeBooks {
  book(filter: { title: { in: ["Native Son", "Moby Dick", "Catch-22"] } } ) {
      values {
      	title
	author
     }
   }
}

Result

{
  "data": {
    "book": {
      "values": [
        {
          "title": "Catch-22",
          "author": "Joseph Heller"
        },
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        }
      ]
    }
  }
}

To display the contents of a UDT, notice the inclusion of addresses in the values displayed for this read query:

graphQL command

# query the author to see the UDT
query getReaderWithUDT{
  reader(value: { name:"Allen Ginsberg" user_id: "e0ed81c3-0826-473e-be05-7de4b4592f64" }) {
    values {
      name
      birthdate
      addresses {
        street
        city
        zip
      }
    }
  }
}

Result

{
  "data": {
    "reader": {
      "values": [
        {
          "name": "Allen Ginsberg",
          "birthdate": "1926-06-03",
          "addresses": [
            {
              "street": "Haight St",
              "city": "San Francisco",
              "zip": "94016"
            }
          ]
        }
      ]
    }
  }
}

To display the contents of a map collection, notice the inclusion of earned in the values displayed for this read query:

graphQL command

# query a badge record that has a MAP (earned) with only the partition key
query oneGoldBadge {
  badge(value: { badge_type: "Gold" } ) {
      values {
      	badge_type
        badge_id
        earned {
        key
        value
      }
     }
  }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

==== Filter options for reading

The filters available are:

eq (equal)
notEq (not equal)
gt (greater than)
gte (greater than or equal to)
lt (less than)
lte (less than or equal to)
in (within)
contains (a map contains the specified value)
containsKey (a map contains the specified key)
containsEntry (a map contains the specified key:value pair)

Note that these can only be used with primary key columns, just like in Cassandra, unless indexing is created.

The next examples will query the same table, badge, using a variety of filters to illustrate the versatility of such filters. The first example finds the record that has the partition key badge_type equal to Gold, and the badge_id equal to 100:

graphQL command

# query a badge record that has a MAP (earned) with the partition key and the clustering key
query oneGold100Badge {
  badge(filter: { badge_type: {eq:"Gold"} badge_id: {eq:100}} ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

Now if we use a different operator gt with the same query, notice that the query will fail, because no badge_id greater than a value of 100 is found:

graphQL command

# query a badge record that has a MAP (earned) with the partition key and the clustering key
# filter badge_id: {gt:100 will fail}
query oneGold100BadgeFail {
  badge(filter: { badge_type: {eq:"Gold"} badge_id: {gt:100}} ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": []
    }
  }
}

In order to use filters for any columns that are not part of the primary key, currently you need to use CQL to create a secondary index using the CQL shell. The next three examples show the CQL creation of an index in order to query a column that is a map collection.

In this example, an index is created on the keys of the map earned, so the containsKey filter can be used to query in GraphQL.

graphQL command

# query a badge record that has a MAP (earned) with the partition key, clustering key, and a MAP key
# Requires: CREATE INDEX badge_idx ON library.badge(KEYS(earned));
query oneWriterBadge {
  badge(filter: { badge_type: {eq:"Gold"} badge_id: {eq:100} earned: { containsKey: "Writer"} } ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

Because the index now exists, it is also possible to just filter based on the map key itself:

graphQL command

# query a badge record that has a MAP (earned) with only a MAP key
# CREATE INDEX badge_idx ON library.badge(KEYS(earned));
query oneWriterKeyBadge {
  badge(filter: { earned: { containsKey: "Writer"} } ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

In this next example, an index is created on the values of the map earned, so the contains filter can be used to query in GraphQL.

graphQL command

# query a badge record that has a MAP (earned) with only a MAP value
# Requires: CREATE INDEX badge2_idx ON library.badge(VALUES(earned));
query oneWriterValueBadge {
  badge(filter: { earned: { contains: "2020-11-20"} } ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

To make a complete set of filters, an index is created on the entries of the map earned, so the containsEntry filter can be used to query in GraphQL.

graphQL command

# query a badge record that has a MAP (earned) with only a MAP entry
# Requires: CREATE INDEX badge3_idx ON library.badge(ENTRIES(earned));
query oneWriterEntryBadge {
  badge(filter: { earned: { containsEntry: {key:"Writer", value:"2020-11-20"}} } ) {
      values {
      	badge_type
        badge_id
      	earned {
          key
          value
        }
     }
   }
}

Result

{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}

=== Update data

Using the column that we added earlier, the data for a book is updated with the ISBN value:

graphQL command

mutation updateOneBook {
  moby: updatebook(value: {title:"Moby Dick", author:"Herman Melville", isbn: "9780140861723"}, ifExists: true ) {
    value {
      title
      author
      isbn
    }
  }
}

Result

{
  "data": {
    "moby": {
      "value": {
        "title": "Moby Dick",
        "author": "Herman Melville",
        "isbn": "9780140861723"
      }
    }
  }
}

Updates are upserts. If the row doesn’t exist, it will be created. If it does exist, it will be updated with the new row data.

It is also possible to update other types of data, such as a set:

graphQL command

# update one book, adding a SET (genre)
mutation updateOneBookAgain {
 moby: updatebook(value: {title:"Moby Dick", author:"Herman Melville", genre: ["Drama", "Classic lit"]}, ifExists: true ) {
   value {
     title
     author
     genre
   }
 }
}

Result

{
  "data": {
    "moby": {
      "value": {
        "title": "Moby Dick",
        "author": "Herman Melville",
        "genre": [
          "Drama",
          "Classic lit"
        ]
      }
    }
  }
}

=== Delete data

After adding the book "Pride and Prejudice" with an insertBooks(), you can delete the book using deleteBooks() to illustrate deleting data:

graphQL command

mutation deleteOneBook {
  PaP: deletebook(value: {title:"Pride and Prejudice", author: "Jane Austen"}, ifExists: true ) {
    value {
      title
    }
  }
}

Result

{
  "data": {
    "PaP": {
      "value": {
        "title": "Pride and Prejudice"
      }
    }
  }
}

Note the use of ifExists to validate that the book exists before deleting it.

==== Deletion options

Similar to the option ifExists, you can delete a book using consistency, serialConsistency, or ttl, similar to insertions:

graphQL command

mutation deleteOneBookCL {
  PaP: deletebook(value: {title:"Pride and Prejudice", author: "Jane Austen"}, ifExists: true, options: {consistency: LOCAL_ONE }) {
    value {
      title
    }
  }
}

Result

{
  "data": {
    "PaP": {
      "value": {
        "title": "Pride and Prejudice"
      }
    }
  }
}