Using Glitch and the Astra Data API
Tags: database, beginners, cloud, tutorial
Published: 2020-04-16 19:24:39

**This post is a part of a tutorial series for learning how to build an app with [DataStax's Astra](https://astra.datastax.com/register?utm_medium=referral&utm_source=devto&utm_campaign=using-glitch-and-the-astra-data-api) database. See all of the posts in this [Better Botz series](https://dev.to/betterbotz) to start using Astra now.**

In the [last blog post](https://dev.to/betterbotz/creating-an-express-server-for-better-botz-4b00), 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 also 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](https://glitch.com/~better-botz-glitchy-fluffy-barnacle).



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 in the previous step.

    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 that your secure connect bundle is _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](https://astra.readme.io/reference). 

#### Creating an authorization token

The [Getting Started tutorial](https://astra.readme.io/docs/getting-started-with-datastax-astra) 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](https://astra.readme.io/reference) 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](https://astra.datastax.com/register?utm_medium=referral&utm_source=devto&utm_campaign=using-glitch-and-the-astra-data-api).
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](https://astra.readme.io/reference#addcolumn-1) 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](https://astra.readme.io/reference#updaterow-1) 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](https://astra.readme.io/reference#addtable-1) 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](https://astra.readme.io/reference#gettable-1) 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/](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](https://your-project-name.glitch.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](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. 

![Customer orders data](https://dev-to-uploads.s3.amazonaws.com/i/rtnbxm2j8m7uqow2c98i.png)

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.