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.

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.