Using the Astra DB Stargate GraphQL API (CQL-first)

DataStax Astra DB uses the Stargate GraphQL API to easily modify and query your table data using GraphQL types, queries, and mutations.

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.

Prerequisites

To use Stargate with Astra DB you need to:

  • Register for Astra DB.

  • Create a database after you log into your Astra account.

  • Generate an application token to use the GraphQL Playground and cURL.

  • Run GraphQL Playground 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.

Create or delete schema

Creating a user-defined type (UDT) in your keyspace using GraphQL

User-defined types (UDTs) are custom data types that a user can create and use in table definitions. They are entirely optional, but sometimes useful if you have data that you would like to group together in a single column. UDTs must be added before you define or add a column using the data type in a table schema.

Prerequisites

  1. Get an application token.

  2. Create a keyspace using the DataStax Astra DB dashboard.

This example creates a UDT called address_type that includes a street, city, state, and zip code.

  • graphQL command

  • cURL 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 } }
    ]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createAddressUDT {\n  createType(\n    keyspaceName: \"library\"\n    typeName: \"address_type\"\n    fields: [\n      { name: \"street\", type: { basic: TEXT } }\n      { name: \"city\", type: { basic: TEXT } }\n      { name: \"state\", type: { basic: TEXT } }\n      { name: \"zip\", type: { basic: TEXT } }\n    ]\n  )\n}","variables":{}}'
{
  "data": {
    "createType": true
  }
}

Creating a table in your keyspace using GraphQL

In order to use the GraphQL API, you must create schema that defines the tables that will store the data, in a keyspace that you have defined with the DataStax Astra DB UI. 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.

Prerequisites

  1. Get an application token.

  2. Create a keyspace using the DataStax Astra DB dashboard.

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

Creating a table

You can create a table using a mutation in /graphql-schema. In the following example, products is the table name. The table columns are name, description, price, and created.

+ A special column, partitionKey, defines the outermost grouping of data, similar to a schema in a relational database. In this example, we’ll use the name column to group our data.

In this example, create two tables by executing the following mutation, in either the GraphQL playground, or by executing the cURL command in a terminal. The first table, book includes the table columns title and author. A special column or columns, partitionKeys, defines the outermost grouping of data, similar to a schema in a relational database. For book, the title column groups the data. Another special column or columns, clusteringKeys can sort the data within a table. For book, the author column sorts the data.

  • graphQL command

  • cURL 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 } } ] } } }
    ]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createTables {\n  book: createTable(\n    keyspaceName:\"library\",\n    tableName:\"book\",\n    partitionKeys: [ # The keys required to access your data\n      { name: \"title\", type: {basic: TEXT} }\n    ]\n    clusteringKeys: [\n      { name: \"author\", type: {basic: TEXT} }\n    ]\n  )\n  reader: createTable(\n    keyspaceName:\"library\",\n    tableName:\"reader\",\n    partitionKeys: [\n      { name: \"name\", type: {basic: TEXT} }\n    ]\n    clusteringKeys: [ # Secondary key used to access values within the partition\n      { name: \"user_id\", type: {basic: UUID}, order: \"ASC\" }\n        ]\n    values: [\n      { name: \"birthdate\", type: {basic: DATE} }\n      { name: \"email\", type: {basic: SET, info:{ subTypes: [ { basic: TEXT } ] } } }\n      { name: \"reviews\", type: {basic: TUPLE, info: { subTypes: [ { basic: TEXT }, { basic: INT }, { basic: DATE } ] } } }\n      { name: \"addresses\", type: { basic: LIST, info: { subTypes: [ { basic: UDT, info: { name: \"address_type\", frozen: true } } ] } } }\n    ]\n  )\n}","variables":{}}'
{
  "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 the UDT that was created above.

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

  • cURL command

  • Result

# 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 } ] } } }
    ]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createTableIfNotExists {\n  magazine: createTable(\n    keyspaceName:\"library\",\n    tableName:\"magazine\",\n    partitionKeys: [ # The keys required to access your data\n      { name: \"title\", type: {basic: TEXT} }\n    ]\n    clusteringKeys: [ # Secondary key used to access values within the partition\n      { name: \"pub_yr\", type: {basic: INT}, order: \"ASC\" }\n      { name: \"pub_mon\", type: {basic: INT} }\n      { name: \"mag_id\", type: {basic: INT} }\n    ],\n    ifNotExists: true,\n    values: [ # The values associated with the keys\n      { name: \"editor\", type: {basic: TEXT} }\n    ]\n  )\n  article: createTable(\n    keyspaceName:\"library\",\n    tableName:\"article\",\n    partitionKeys: [ # The keys required to access your data\n      { name: \"title\", type: {basic: TEXT} }\n    ]\n    clusteringKeys: [ # Secondary key used to access values within the partition\n      { name: \"mtitle\", type: {basic: TEXT} }\n    ],\n    ifNotExists: true,\n    values: [ # The values associated with the keys\n      { name: \"authors\", type: {basic:LIST, info:{ subTypes: [ { basic: TEXT } ] } } }\n    ]\n  )\n}","variables":{}}'
{
  "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

  • cURL command

  • Result

# create a table with a MAP
# DATA TYPE: TEXT, INT, MAP(TEXT, DATE)
mutation createMapTable {
  badge: createTable (
    keyspaceName:"library",
    tableName: "badge",
    partitionKeys: [
      { name: "user_id", type: {basic: UUID} },
      { 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}]}} }
    ]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation createMapTable {\n  badge: createTable (\n    keyspaceName:\"library\",\n    tableName: \"badge\",\n    partitionKeys: [\n      { name: \"user_id\", type: {basic: UUID} },\n      { name: \"badge_type\", type: {basic:TEXT} }\n    ]\n    clusteringKeys: [\n      { name: \"badge_id\", type: { basic: INT} }\n    ],\n    ifNotExists:true\n    values: [ \n        { name: \"earned\", type:{basic:MAP, info:{ subTypes: [ { basic: TEXT }, {basic: DATE}]}} }\n    ]\n  )\n}","variables":{}}'
{
  "data": {
    "badge": true
  }
}

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

Adding 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

  • cURL command

  • Result

# 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 } ] } } }
    ]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation alterTableAddCols {\n  alterTableAdd(\n    keyspaceName:\"library\",\n    tableName:\"book\",\n    toAdd:[\n      { name: \"isbn\", type: { basic: TEXT } }\n      { name: \"language\", type: {basic: TEXT} }\n      { name: \"pub_year\", type: {basic: INT} }\n      { name: \"genre\", type: {basic:SET, info:{ subTypes: [ { basic: TEXT } ] } } }\n      { name: \"format\", type: {basic:SET, info:{ subTypes: [ { basic: TEXT } ] } } }\n    ]\n  )\n}","variables":{}}'
{
  "data": {
    "alterTableAdd": true
  }
}

Checking that keyspaces and tables exist

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

For keyspaces and tables:

  • graphQL command

  • cURL command

  • Result

query GetKeyspace {
  keyspace(name: "library") {
      name
      dcs {
          name
          replicas
      }
      tables {
          name
          columns {
              name
              kind
              type {
                  basic
                  info {
                      name
                  }
              }
          }
      }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"query GetKeyspace {\n  keyspace(name: \"library\") {\n      name\n      dcs {\n          name\n          replicas\n      }\n      tables {\n          name\n          columns {\n              name\n              kind\n              type {\n                  basic\n                  info {\n                      name\n                  }\n              }\n          }\n      }\n  }\n}","variables":{}}'
{
  "data": {
    "keyspace": {
      "name": "library",
      "dcs": [
        {
          "name": "us-east-1",
          "replicas": 3
        }
      ],
      "tables": [
        {
          "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
                }
              }
            }
          ]
        },
        {
          "name": "article",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "mtitle",
              "kind": "CLUSTERING",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "authors",
              "kind": "REGULAR",
              "type": {
                "basic": "LIST",
                "info": {
                  "name": null
                }
              }
            }
          ]
        },
        {
          "name": "magazine",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "pub_yr",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "pub_mon",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "mag_id",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "editor",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            }
          ]
        },
        {
          "name": "badge",
          "columns": [
            {
              "name": "user_id",
              "kind": "PARTITION",
              "type": {
                "basic": "UUID",
                "info": null
              }
            },
            {
              "name": "badge_type",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "badge_id",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "earned",
              "kind": "REGULAR",
              "type": {
                "basic": "MAP",
                "info": {
                  "name": null
                }
              }
            }
          ]
        },
        {
          "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
              }
            }
          ]
        }
      ]
    }
  }
}

And for tables:

  • graphQL command

  • cURL command

  • Result

query GetTables {
  keyspace(name: "library") {
      name
      tables {
          name
          columns {
              name
              kind
              type {
                  basic
                  info {
                      name
                  }
              }
          }
      }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"query GetTables {\n  keyspace(name: \"library\") {\n      name\n      tables {\n          name\n          columns {\n              name\n              kind\n              type {\n                  basic\n                  info {\n                      name\n                  }\n              }\n          }\n      }\n  }\n}","variables":{}}'
{
  "data": {
    "keyspace": {
      "name": "library",
      "tables": [
        {
          "name": "magazine",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "pub_yr",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "pub_mon",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "mag_id",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "editor",
              "kind": "REGULAR",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            }
          ]
        },
        {
          "name": "article",
          "columns": [
            {
              "name": "title",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "mtitle",
              "kind": "CLUSTERING",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "authors",
              "kind": "REGULAR",
              "type": {
                "basic": "LIST",
                "info": {
                  "name": null
                }
              }
            }
          ]
        },
        {
          "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": "badge",
          "columns": [
            {
              "name": "user_id",
              "kind": "PARTITION",
              "type": {
                "basic": "UUID",
                "info": null
              }
            },
            {
              "name": "badge_type",
              "kind": "PARTITION",
              "type": {
                "basic": "VARCHAR",
                "info": null
              }
            },
            {
              "name": "badge_id",
              "kind": "CLUSTERING",
              "type": {
                "basic": "INT",
                "info": null
              }
            },
            {
              "name": "earned",
              "kind": "REGULAR",
              "type": {
                "basic": "MAP",
                "info": {
                  "name": 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
                }
              }
            }
          ]
        }
      ]
    }
  }
}

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.

Create an index

Prerequisites

  1. Get an application token.

  2. Create a keyspace using the DataStax Astra DB dashboard.

  3. Create a table.

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

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

  • cURL command

  • Result

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 --location --request POST http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_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 TBD

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

  • cURL command

  • CQL command

  • Result

curl --location --request POST 'http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema' \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_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":{}}'
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 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.

Dropping user-defined types (UDTs), tables, columns or indexes

Prerequisites

  1. Get an application token.

  2. Create a keyspace using the DataStax Astra DB dashboard.

  3. Optional Creating UDTs.

  4. Creating a table.

  5. Optional Creating an index.

Dropping a type

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

  • graphQL command

  • Result

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

Dropping a table

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

  • graphQL command

  • cURL command

  • Result

# drop a table
mutation dropTableBook {
  dropTable(keyspaceName:"library",
    tableName:"article")
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation dropTableArticle {\n  dropTable(keyspaceName:\"library\",\n    tableName:\"article\")\n}","variables":{}}'
{
  "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

  • cURL command

  • Result

# drop a table if it exists
mutation dropTableIfExists {
  dropTable(keyspaceName:"library",
    tableName:"magazine",
  ifExists: true)
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation dropTableIfExists {\n  dropTable(keyspaceName:\"library\",\n    tableName:\"magazine\",\n  ifExists: true)\n}","variables":{}}'
{
  "data": {
    "dropTable": true
  }
}

Dropping 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

  • cURL command

  • Result

# drop a column format from a table
mutation dropColumnFormat {
    alterTableDrop(
    keyspaceName:"library",
    tableName:"book",
    toDrop:["format"]
  )
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql-schema \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation dropColumnFormat {\n    alterTableDrop(\n    keyspaceName:\"library\",\n    tableName:\"book\",\n    toDrop:[\"format\"]\n  )\n}","variables":{}}'
{
  "data": {
    "alterTableDrop": true
  }
}

Dropping an index from table schema

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

  • graphQL command

  • cURL command

  • Result

mutation dropIndexBdate {

  reader: dropIndex(
      keyspaceName:"library",
      indexName:"reader_bdate_idx"
  )
}
curl --location --request POST 'https://8319febd-e7cf-4595-81e3-34f45d332d2a-us-east1.apps.astra.datastax.com/api/graphql-schema' \
--header 'X-Cassandra-Token: AstraCS:txZMMdsloiGKPZhosGAnMsHY:0d54c888f49f4c5f3201a057c5e7124c30fa573a687fd16f23cb9c98167c26c8' \
--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":{}}'
{
  "data": {
    "reader": true
  }
}

Interact with data stored in tables

Adding rows in your table using GraphQL

Insert data in your tables using Astra DB’s GraphQL API.

After creating a table in your keyspace using GraphQL, you can add rows of data.

Prerequisites

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. Switch to the graphql tab in the GraphQL playground, and change the location to https://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library The main importance is changing the keyspace name, as the default is system.

First, let’s add a couple of books to the book table:

  • graphQL command

  • cURL command

  • Result

# 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
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation insert2Books {\n  moby: insertbook(value: {title:\"Moby Dick\", author:\"Herman Melville\"}) {\n    value {\n      title\n    }\n  }\n  catch22: insertbook(value: {title:\"Catch-22\", author:\"Joseph Heller\"}) {\n    value {\n      title\n    }\n  }\n}","variables":{}}'
{
  "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:

Note that in Astra DB, the consistency levels ANY, ONE, or LOCAL_ONE cannot be used. The default is LOCAL_QUORUM.

An example insertion that sets the consistency level and TTL:

  • graphQL command

  • cURL command

  • Result

# 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
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation insertBookWithOption {\n  nativeson: insertbook(value: {title:\"Native Son\", author:\"Richard Wright\"}, options: {consistency: LOCAL_QUORUM, ttl:86400}) {\n    value {\n      title\n    }\n  }\n}","variables":{}}'
{
  "data": {
    "nativeson": {
      "value": {
        "title": "Native Son"
      }
    }
  }
}

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

  • cURL command

  • Result

# 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
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation insertArticle {\n  magarticle: insertarticle(value: {title:\"How to use GraphQL\", authors: [\"First author\", \"Second author\"], mtitle:\"Database Magazine\"}) {\n    value {\n      title\n      mtitle\n      authors\n    }\n  }\n}","variables":{}}'
{
  "data": {
    "magarticle": {
      "value": {
        "title": "How to use GraphQL",
        "mtitle": "Database Magazine",
        "authors": [
          "First author",
          "Second author"
        ]
      }
    }
  }
}

A map is slightly more complex:

  • graphQL command

  • cURL command

  • Result

mutation insertOneBadge {
  editor1: insertbadge(value: { user_id:100, badge_type: "Editor", badge_id: 100, earned: {key:"Gold", value:"2020-11-20"} } ) {
    value {
      user_id
      badge_type
      badge_id
      earned
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation insertOneBadge {\n  editor1: insertbadge(value: { user_id: \"b5b5666b-2a37-4d0b-a5eb-053e54fc242b\", badge_type: \"Editor\", badge_id: 100, earned: {key:\"Gold\", value:\"2020-11-20\"} } ) {\n    value {\n      user_id\n      badge_type\n      badge_id\n      earned {\n        key\n        value\n      }\n    }\n  }\n}","variables":{}}'
{
  "data": {
    "editor1": {
      "value": {
        "user_id": "b5b5666b-2a37-4d0b-a5eb-053e54fc242b",
        "badge_type": "Editor",
        "badge_id": 100,
        "earned": [
          {
            "key": "Gold",
            "value": "2020-11-20"
          }
        ]
      }
    }
  }
}
Insert a tuple

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

  • graphQL command

  • cURL command

  • Result

# 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
      }
     }
   }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# insert a reader record that uses a TUPLE\nmutation insertJaneWithTuple{\n   jane: insertreader(\n     value: {\n       user_id: \"b5b5666b-2a37-4d0b-a5eb-053e54fc242b\"\n       name: \"Jane Doe\"\n       birthdate: \"2000-01-01\"\n       email: [\"janedoe@gmail.com\", \"janedoe@yahoo.com\"]\n       reviews: { item0: \"Moby Dick\", item1: 5, item2: \"2020-12-01\" }\n     }\n   ) {\n     value {\n       user_id\n       name\n       birthdate\n       reviews {\n        item0\n        item1\n        item2\n      }\n     }\n   }\n}","variables":{}}'
{
  "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

  • cURL command

  • Result

# 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
      }
    }
  }
 }
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"mutation insertReaderWithUDT{\n  ag: insertreader(\n    value: {\n      user_id: \"e0ed81c3-0826-473e-be05-7de4b4592f64\"\n      name: \"Allen Ginsberg\"\n      birthdate: \"1926-06-03\"\n      addresses: [{ street: \"Haight St\", city: \"San Francisco\", zip: \"94016\" }]\n    }\n  ) {\n    value {\n      user_id\n      name\n      birthdate\n      addresses {\n        street\n        city\n        zip\n      }\n    }\n  }\n }","variables":{}}'
{
  "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"
          }
        ]
      }
    }
  }
}

Retrieving rows in your table using GraphQL

Retrieve data from your tables using Astra’s GraphQL API.

After creating a table in your keyspace using GraphQL and adding rows using GraphQL, you can retrieve data.

Prerequisites

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

  • cURL command

  • Result

# get one book using the primary key title with a value
query oneBook {
  book (value: {title:"Moby Dick"}) {
    values {
      title
      author
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# get one book using the primary key title with a value\nquery oneBook {\n    book (value: {title:\"Moby Dick\"}) {\n      values {\n        title\n        author\n      }\n    }\n}","variables":{}}'
{
  "data": {
    "book": {
      "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

  • cURL command

  • Result

# 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
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"query ThreeBooks {\n  book(filter: { title: { in: [\"Native Son\", \"Moby Dick\", \"Catch-22\"] } } ) {\n      values {\n        title\n        author\n     }\n   }\n}","variables":{}}'
{
  "data": {
    "book": {
      "values": [
        {
          "title": "Catch-22",
          "author": "Joseph Heller"
        },
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        },
        {
          "title": "Native Son",
          "author": "Richard Wright"
        }
      ]
    }
  }
}

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

  • graphQL command

  • cURL command

  • Result

# 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
      }
    }
  }
}
curl -sL --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query the author to see the UDT\nquery getReaderWithUDT{\n  reader(value: { name:\"Allen Ginsberg\" user_id: \"e0ed81c3-0826-473e-be05-7de4b4592f64\" }) {\n    values {\n      name\n      birthdate\n      addresses {\n        street\n        city\n        zip\n      }\n    }\n  }\n}","variables":{}}'
{
  "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

  • cURL command

  • Result

# 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
      }
     }
  }
}
curl --location --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query a badge record that has a MAP (earned) with only the partition key\nquery oneGoldBadge {\n  badge(value: { badge_type: \"Gold\" } ) {\n      values {\n        badge_type\n        badge_id\n        earned {\n        key\n        value\n      }\n     }\n  }\n","variables":{}}'
{
  "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) - limited use in conditional statements

  • 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

  • cURL command

  • Result

# 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
        }
     }
   }
}
curl --location --request POST --url http://$ASTRA_CLUSTER_ID-$ASTRA_REGION.apps.astra.datastax.com/api/graphql/library \
--header "X-Cassandra-Token: $ASTRA_DB_APPLICATION_TOKEN" \
--header 'Content-Type: application/json' \
--data-raw '{"query":"# query a badge record that has a MAP (earned) with the partition key and the clustering key\nquery oneGold100Badge {\n  badge(filter: { badge_type: {eq:\"Gold\"} badge_id: {eq:100}} ) {\n      values {\n        badge_type\n        badge_id\n        earned {\n          key\n          value\n        }\n     }\n   }\n}","variables":{}}'
{
  "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

  • Result

# 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
        }
     }
   }
}
{
  "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

  • Result

# 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
        }
     }
   }
}
{
  "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

  • Result

# 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
        }
     }
   }
}
{
  "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

  • Result

# 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
        }
     }
   }
}
{
  "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

  • Result

# 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
        }
     }
   }
}
{
  "data": {
    "badge": {
      "values": [
        {
          "badge_type": "Gold",
          "badge_id": 100,
          "earned": [
            {
              "key": "Writer",
              "value": "2020-11-20"
            }
          ]
        }
      ]
    }
  }
}
Limiting and paging results using query options

You can add options to the query to limit and add paging to the results.

Add the limit option to set the maximum number of results returned by the query.

  • graphQL command

  • Result

# get books using a limit of 10
query readBooksWithLimit {
  book(options: {limit:10}) {
    values {
      title
	    author
    }
  }
}
{
  "data": {
    "book": {
      "values": [
        {
          "title": "Catch-22",
          "author": "Joseph Heller"
        },
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        }
      ]
    }
  }
}

The query results can be paged by using the pageSize and pageState options.

Set pageSize to the number of results returned at each step. The default value of pageSize is 100.

  • graphQL command

  • Result

# get books using a pageSize of 2
query readBooksWithPageSize {
  book( options: { pageSize: 2 } ) {
    values {
      title
	    author
    }
   pageState
  }
}
{
  "data": {
    "book": {
      "values": [
        {
          "title": "Catch-22",
          "author": "Joseph Heller"
        },
        {
          "title": "Moby Dick",
          "author": "Herman Melville"
        }
      ],
      "pageState": "CU1vYnkgRGljaxEAD0hlcm1hbiBNZWx2aWxsZfB////98H////4="
    }
  }
}

The pageState value is returned in the data of the last query. Pass the value of pageState from the previous query to retrieve the next page in the results.

  • graphQL command

  • Result

# get booksi using pageState
query readBooksNextPage {
  book( options: {
    pageSize: 2,
	  pageState: "CU1vYnkgRGljaxEAD0hlcm1hbiBNZWx2aWxsZfB//98H////4="
    }
  ) {
    values {
      title
	     author
    }
    pageState
  }
}
{
  "data": {
    "book": {
      "values": [
        {
          "title": "Pride and Prejudice",
          "author": "Jane Austen"
        },
      ],
      "pageState": null
    }
  }
}

Updating rows in your table using GraphQL

Update data in your tables using Astra’s GraphQL API.

After creating a table in your keyspace and adding rows, you can update data.

Updates are upserts

Running an update mutation in Astra is an upsert. This means that if the row you are altering doesn’t exist it will be created.

Prerequisites

Using the column that we added earlier, the data for a book is updated with the ISBN value. If the call is successful, a message returns with the newly inserted row data:

  • graphQL command

  • Result

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

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

  • graphQL command

  • Result

# 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
   }
 }
}
{
  "data": {
    "moby": {
      "value": {
        "title": "Moby Dick",
        "author": "Herman Melville",
        "genre": [
          "Drama",
          "Classic lit"
        ]
      }
    }
  }
}

Deleting rows in your table using GraphQL

Delete data in your tables using the DataStax Astra DB GraphQL API.

As needed, you can delete rows from tables. Use the delete object method to remove the rows in the table you created previously.

Prerequisites

Delete data

After adding the book "Pride and Prejudice" with an insertbook(), you can delete the book using deletebook() to illustrate deleting data. If the call is successful there won’t be a return message.

  • graphQL command

  • Result

mutation deleteOneBook {
  PaP: deletebook(value: {title:"Pride and Prejudice", author: "Jane Austen"}, ifExists: true ) {
    value {
      title
    }
  }
}
{
  "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

  • Result

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