Using Glitch and the Astra Data API

In the last blog post, we configured an Express backend server on Node.js, and then connected to our Astra database using the DataStax Node.js driver. We retrieved data from our orders table, only to realize that it’s empty. Fear not! We’re going to remedy that situation in this post and start using Glitch to expedite development of our ordering application for Better Botz.

Goal 1: Configure Glitch with your secure connect bundle

If you don’t already have a Glitch account, head over to Glitch and create an account. After you create an account, open this Better Botz project.

1. In Glitch, open the Better Botz project.

2. Select Remix to Edit in the upper right to make a copy of the project.

   When you remix a project on Glitch, you’re creating a copy of an existing project that you can edit. Think of it like forking a GitHub repository.

3. In your remixed project, drag or upload the secure connect bundle for your DataStax Astra database.

   The secure connect bundle is added to your assets, but we need it in a safe location. Therefore, we’ll move it to a hidden directory.

4. In your remixed project, in the project navigator to the left, select New File.

   1. Enter .data and then select Add This File. The new .data directory will not display in your project navigator, but is created as a hidden directory.

   2. At the bottom of your project navigator, select Tools > Terminal to open the terminal for your project.

   3. In the Glitch terminal for your project, change to your .data directory.

      $ cd ~/.data

   4. In your project navigator, choose your assets folder, right-click your secure connect bundle, and copy the URL of that file.

   5. In the Glitch terminal, in the .data directory, use wget to download your secure connect bundle:

      $ wget secure-connect-bundle-url

      Replace secure-connect-bundle-url with the URL for your secure connect bundle that you copied from the project navigator.

   6. Choose your assets folder, select your secure connect bundle, and delete it. 

   7. Rename your secure connect bundle to secure-connect.zip.

      $ mv secure-connect-bundle-name secure-connect.zip

Your secure connect bundle is now accessible in Glitch. Great job! Going through the extra work of putting your database credentials in a hidden directory is tedious, but ensures your secure connect bundle is actually secure.

Goal 2: Adding order data

If you recall from the last post, we retrieved data from our orders table, only to discover that it was empty. To process customer orders, we need order data!

In previous posts, we created tables and inserted data using the Cassandra Query Language SHell (CQLSH) that is embedded in Astra. This practice is perfectly acceptable, but we’re not CQL experts and would rather use a more programmatic tool. Therefore, we’ll use the Astra Data API.

Creating an authorization token

The Getting Started tutorial in the Astra API documentation illustrates the steps to connect to and interact with your Astra database. Following those instructions, we’ll use cURL commands in this example. Use this cURL command as you work through the following steps to create an authorization token.

Tip: If you want to issue commands from your application, choose the API endpoint you want to work with, select your preferred language, and enter the required data for the endpoint. The example automatically updates with the values you enter, making it easy to copy and paste into your IDE.

curl --request POST \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/auth \
  --header 'accept: */*' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --data '{"username":"{database-user}","password":"{database-password}"}'

1. In a browser, log in to DataStax Astra.

2. Select the database you want to connect to.

   • In the Astra URL, copy the databaseid of your database, which is the last UUID in the path:

     https://astra.datastax.com/org/{org-Id}/database/{databaseid}

   • Select the database you want to connect to and copy the cloud region where your database lives. For example, us-east1.

   • Copy the username and password you entered when creating your database.

3. In the cURL example you copied previously, replace these values with the values for your database:

   • {databaseid} with the UUID of your database, copied from the Astra URL.

   • {region} with the cloud region where your database is located, as listed on the Database Details page in Astra. For example, us-east1.

   • {unique-UUID} with a randomly-generated UUID that is unique for the authorization request.

   • {database-user} is the username entered when you created your database.

   • {database-password} is the password for the specified username.

4. From the command line, run the entire cURL command with the values for your database.

   An authorization token is returned:

   {"authToken": "37396a44-dcb8-4740-a97f-79f0dba47973"}

5. Copy the value of the authorization token, which you'll include when making requests to your database, such as creating tables, adding rows, or modifying columns. In this example, the authorization token value is 37396a44-dcb8-4740-a97f-79f0dba47973.

Inserting data in your orders table

Now that you have an authorization token, use it to connect to your Astra database and submit queries. You pass this token with each query to ensure a secure connection to your Astra database.

Before we can insert data, there’s something missing from our table structure that we discovered when reviewing the order data. Each order has a quantity, but our orders table doesn’t have a column for that data.

We’ll use the add column endpoint of the DataStax Astra API to add a quantity column. 

1. Copy the following request, which you use to add a quantity column to the orders table in your betterbotz keyspace.

   curl --request POST \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/keyspaces/{my_keyspace}/tables/orders/columns \
  --header 'accept: application/json' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'x-cassandra-token: {auth-token}' \
  --data '{"static":false,"name":"quantity","typeDefinition":"int"}'

2. Replace the following values with the values for your database:

   • Replace {databaseid} with the UUID of your database, copied from the Astra URL.

   • Replace {region} with the cloud region where your database is located, as listed on the Database Details page in Astra. For example, us-east1.

   • Modify {my_keyspace} to match the name of your keyspace, which is betterbotz if you followed the previous blog entries.

   • Enter a {unique-UUID} for the request and the {auth-token} you created earlier.

3. Run the cURL call to add a quantity column to your orders table in the keyspace that you specified. If the call is successful, a message returns indicating: {"success":true}

Now that our table columns are updated, we’ll use the update rows endpoint to add data to our orders table. Copy the following cURL command and replace the same variables as in the previous steps.

curl --request PUT \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/keyspaces/{my_keyspace}/tables/{tableName}/rows/Otto%20Octavius \
  --header 'accept: application/json' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'x-cassandra-token: {auth-token}' \
  --data '{"changeset":[{"column":"id","value":"e65063a7-fba3-41ba-84bb-740a01cacf5e"},{"column":"address","value":"2475 Shadow Ln. Stow, Ohio(OH), 44224"},{"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb0"},{"column":"prod_name","value":"Heavy Lift Arms"},{"column":"description","value":"Heavy lift arms capable of lifting 1,250 lbs of weight per arm. Sold as a set."},{"column":"price","value":4199.99},{"column":"quantity","value":5},{"column":"sell_price","value":21000.96}]}'

In the --data option of the call, we’ll use the changeset parameter to define the data that we want to update in the orders table. This example adds a single row to the orders table.

Notice that Otto%20Octavius is used in the --url option, but customer_name is not included in the changeset. That’s because customer_name is the primary key, which identifies the location and order of stored data. The first column declared in the primary key definition is the partition key, which we’ll get to in the next section when creating a shipping table.

To add more rows:

1. Copy the changeset parameter for each of the following examples and replace the changeset defined by the --data option of the call.

2. Replace the primary key in the the --url option to use the value indicated before the example.

3. Run the cURL command with the updated data.

For example, here’s an updated changeset parameter for a customer named Desmond Blackwell. Because customer_name is the primary key, the --url changes to https://databaseid-region.apps.astra.datastax.com/api/rest/v1/keyspaces/keyspaceName/tables/tableName/rows/Desmond%20Blackwell.

Primary key = Desmond%20Blackwell

'{"changeset":[{"column":"id","value":"e5a768fc-ffdd-49e9-a179-b491e024088a"},{"column":"address","value":"91 Dogwood Dr. Bridgeport, Connecticut (CT) 06606"},{"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb8"},{"column":"prod_name","value":"Precision Torso"},{"column":"description","value":"Robot torso built to handle precision jobs with extra stability and accuracy reinforcement."},{"column":"price","value":8199.99},{"column":"quantity","value":3},{"column":"sell_price","value":24599.97}]}'

Use the following examples to make additional update row calls by modifying the primary key, replacing the changeset, and running the cURL command again.

Primary key = Loretta%20Stillwell

'{"changeset":[{"column":"id","value":"fae7c26c-7bc4-41e0-9e9f-63905cc38944"},{"column":"address","value":"1314 Lindwood Dr. Carter Lake, Iowa (IA) 51510"},
{"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb7"},{"column":"prod_name","value":"Medium Strength Torso"},{"column":"description","value":"Robot body to handle general jobs."},{"column":"price","value":1999.99},{"column":"quantity","value":2},{"column":"sell_price","value":3999.98}]}'

Primary key = Matt%20Williamson

'{"changeset":[{"column":"id","value":"02668188-7b74-4ac6-bb4b-273b14bbda7e"},{"column":"address","value":"15900 Wilcox Ln. Marion, Michigan (MI) 49665"},
{"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb6"},
    {"column":"prod_name","value":"High Strength Torso"},
    {"column":"description","value":"Robot body with reinforced plate to handle heavy workload and weight during jobs."},
    {"column":"price","value":2999.99},
    {"column":"quantity","value":6},
    {"column":"sell_price","value":13199.94}]}'

Primary key = Jayashree%20Marshall

'{"changeset":[{"column":"id","value":"295c362d-2eaf-43c0-bd68-63efc2cd1767"},
    {"column":"address","value":"107 Trulson St. Oakland, Nebraska (NE), 68045"},
    {"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb5"},
    {"column":"prod_name","value":"Basic Task CPU"},
    {"column":"description","value":"Head processor unit for robot with basic process tasks."},
    {"column":"price","value":899.99},
    {"column":"quantity","value":5},
    {"column":"sell_price","value":4499.95}]}'

Primary key = Evelyn%20Davis

'{"changeset":[{"column":"id","value":"5305ff90-e838-46ea-860f-69e831d28146"},
    {"column":"address","value":"36 Jasmine Ln. Valley Stream, New York(NY),
     11581"},
    {"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb4"},
    {"column":"prod_name","value":"High Process AI CPU"},
    {"column":"description","value":"Head processor unit for robot with basic process tasks."},
    {"column":"price","value":2199.99},
    {"column":"quantity","value":12},
    {"column":"sell_price","value":26399.88}]}'

Primary key = Wyatt%20Devonshire

'{"changeset":[{"column":"id","value":"bccaed73-e7fb-4f16-8799-206e08905161"},
    {"column":"address","value":"2770 Raymond St. Forest Grove, Oregon(OR), 97116"},
    {"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb3"},
    {"column":"prod_name","value":"Drill Arms"},
    {"column":"description","value":"Arms for drilling into surface material. Sold as a set. Does not include drill bits."},
    {"column":"price","value":2199.99},
    {"column":"quantity","value":3},
    {"column":"sell_price","value":6599.97}]}'

Primary key = Lavender%20Chesterfield

'{"changeset":[{"column":"id","value":"ccc5bc2d-d166-471b-ad44-68be45663545"},
    {"column":"address","value":"250 Holmes Blvd #1A Gretna, Louisiana(LA), 70056"},
    {"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb1"},
    {"column":"prod_name","value":"Precision Action Arms"},
    {"column":"description","value":"Arms for precision activities in manufacturing or repair. Sold as a set."},
    {"column":"price","value":12199.99},
    {"column":"quantity","value":1},
    {"column":"sell_price","value":12199.99}]}’

Primary key = Darius%20Smith

'{"changeset":[{"column":"id","value":"3c371be4-203c-497f-a1eb-79769d3526a8"},
    {"column":"address","value":"199 State U Rd. Macks Creek, Missouri (MO), 65786"},
    {"column":"prod_id","value":"31047029-2175-43ce-9fdd-b3d568b19bb2"},
    {"column":"prod_name","value":"Medium Lift Arms"},
    {"column":"description","value":"Medium lift arms capable of lifting 850 lbs of weight per arm. Sold as a set."},
    {"column":"price","value":3199.99},
    {"column":"quantity","value":1},
    {"column":"sell_price","value":3199.99}]}’

Goal 3: Tracking orders being shipped

As orders propagate through the system, we’ll need to track ones that are shipping so that customers can easily check their order status. To that end, we’ll use the add table endpoint to create a shipping table in your Astra database. 

1. Copy the following request, which you use to create a shipping table in your betterbotz keyspace.

   On the last line of the following example, shipping is the table name. The table columns are defined by columnDefinitions, such as id, prod_id, cust_id, and address_id.

   Remember the primary key we used earlier when updating the orders table? We also mentioned a partition key (partitionKey), which is a special column that defines the outermost grouping of data, similar to a schema in a relational database. For the shipping table, our partition key uses two columns: prod_name and customer_name.

   curl --request POST \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/keyspaces/{my_keyspace}/tables \
  --header 'accept: */*' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'x-cassandra-token: {auth-token}' \
  --data '{"ifNotExists":true,"columnDefinitions":[
  {"static":false,"name":"id","typeDefinition":"uuid"},
  {"static":false,"name":"prod_id","typeDefinition":"uuid"},
  {"static":false,"name":"cust_id","typeDefinition":"uuid"},
  {"static":false,"name":"address_id","typeDefinition":"uuid"},
  {"static":false,"name":"prod_name","typeDefinition":"text"},
  {"static":false,"name":"customer_name","typeDefinition":"text"},
  {"static":false,"name":"street","typeDefinition":"text"},
  {"static":false,"name":"city","typeDefinition":"text"},
  {"static":false,"name":"state","typeDefinition":"text"},
  {"static":false,"name":"country","typeDefinition":"text"},
  {"static":false,"name":"code","typeDefinition":"text"}],
  "primaryKey":{"partitionKey":["prod_name","customer_name"]},
  "tableOptions":{"defaultTimeToLive":0},"name":"shipping"}'

2. Replace the following values with the values for your database:

   • Replace {databaseid} with the UUID of your database, copied from the Astra URL.

   • Replace {region} with the cloud region where your database is located, as listed on the Database Details page in Astra. For example, us-east1.

   • Modify {my_keyspace} to match the name of your keyspace, which is betterbotz if you followed the previous blog entries.

   • Enter a {unique-UUID} for the request and the {auth-token} you created earlier.

   • In the --data option of the call, we’ll define the table columns using the columnDefinitions parameter.

3. Run the cURL call to create a shipping table in the keyspace that you specified. If the call is successful, a message returns indicating: {"success":true}

Let’s use the get table endpoint to retrieve the table you just created. Replace the same variables as in previous steps, and then replace {tableName} with the name of your table (which is shipping).

curl --request GET \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/keyspaces/{keyspaceName}/tables/{tableName} \
  --header 'accept: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'x-cassandra-token: {auth-token}'
 
Running this cURL command with the variables you entered returns data for your shipping table, but it’s not exactly human readable. You can add a pipe character and json_pp at the end of your cURL command to provide a formatted JSON response, but customers need an easier way to access this shipping data.

{"name":"shipping","keyspace":"betterbotz","columnDefinitions":[{"Name":"customer_name","TypeDefinition":"text"},{"Name":"id","TypeDefinition":"uuid"},{"Name":"prod_name","TypeDefinition":"text"},{"Name":"city","TypeDefinition":"text"},{"Name":"cust_id","TypeDefinition":"uuid"},{"Name":"country","TypeDefinition":"text"},{"Name":"prod_id","TypeDefinition":"uuid"},{"Name":"state","TypeDefinition":"text"},{"Name":"street","TypeDefinition":"text"},{"Name":"address_id","TypeDefinition":"uuid"},{"Name":"code","TypeDefinition":"text"}],"primaryKey":{"PartitionKey":["prod_name"],"clusteringKey":["customer_name"]},"tableOptions":{"DefaultTimeToLive":null,"clusteringExpression":[{"Column":"customer_name","Order":"asc"}]}}

What about your application in Glitch? Glad you asked! Now that there’s data in your orders table, you can actually see it being returned by your application. Open your Glitch project and complete the following steps:

1. Edit the sample-run.sh script to include your Astra database username and password.

   ASTRAUSER=username ASTRAPASSWORD=password

2. Select Tools > Terminal to open a terminal connected to your project.

3. In the terminal, from your home directory (~), run the ./sample-run.sh script to start the server using your Astra credentials:

   cd ~ &&
./sample-run.sh

4. With the server running, open a browser and navigate to the following paths to view examples of data retrieval. In each URL, change your-project-name to the name of your Glitch project:

   1. This URL verifies that the Express server is running with the included Jade templates.
      https://your-project-name.glitch.me/

   2. The /data endpoint includes the raw data that is a direct response of the JSON response that is retrieved using the API GET call.
      https://your-project-name.me/data

   3. The /datareport endpoint is a response that includes the data results generated from the Jade template. As configured, customer orders will display at this endpoint.
      https://your-project-name.glitch.me/datareport

Your data is being returned at the /data endpoint, and is formatted in a human-readable form at the /datareport endpoint. You did it! This step is a huge milestone achievement for building an online ordering system for Better Botz. 

In the next post, we’ll develop a means to quickly and easily manage CRUD (Create, Read, Update, Delete) operations, which we’ll build with Glitch and Express.js to get data in and out of your tables.