DEV Community: BetterBotz

Adding and removing data in Astra tables

BetterBotz — Mon, 01 Jun 2020 14:20:59 +0000

This post is a part of a tutorial series for learning how to build an app with DataStax's Astra database. See all of the posts in this Better Botz series to start using Astra now.

In the last post, you used the DataStax Astra API to generate data for our Better Botz database. When DataStax Astra was made generally available, GraphQL was added as a feature for creating, reading, updating, and deleting data.

GraphQL uses queries and mutations to retrieve and change data.

Goal 1: See the status of current products

Better Botz has created a new and improved Heavy Lift Arms. While planning when to add Heavy Lift Arms II, Better Botz needs to know if the existing Heavy Lift Arms are already listed.

If necessary, get a fresh authorization token using the DataStax Astra API. Replace {auth-token} with the value for your database:

```
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: {auth-token}'
```

Using GraphQL, run a query to see if you already have the Heavy Lift Arms II.

```
curl --location --request POST 'https://{databaseid}-{region}.apps.astra.datastax.com/api/graphql' \
  --header 'accept: */*' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'X-Cassandra-Token: {auth-token}' \
  --header 'Content-Type: application/json' \
  --data-raw '{"query":"query {products(value: {id:\"Heavy Lift Arms\"}) {values {id name price description created}}}"}'
```

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.
- Enter a {unique-UUID} for the request and the {auth-token} you created earlier.
Check for an existing “Heavy Lift Arms II”. This should verify that you do not have an existing “Heavy Lift Arms II” product.

Goal 2: Add a new product

Now that you know there aren’t any Heavy Lift Arms in the products table, you want to add the new and improved product to our table for customers to be able to order.

Use the following cURL command:

```
curl --request POST \
  --url https://{databaseid}-{region}.apps.astra.datastax.com/api/rest/v1/keyspaces/{my_keyspace}/tables/{table_name}/rows \
  --header 'accept: application/json' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'x-cassandra-token: {auth-token}' \
  --data-raw '{"query":"mutation {superarms: insertProducts(value:{id:\"65cad0df-4fc8-42df-90e5-4effcd221ef7\"\n name:\"Arm Spec A2\" description:\"Heavy Lift Arms II\"price: \"9999.99\" created: \"2012-04-23T18:25:43.511Z\"}){value {name description price created}}}","variables":{}}'
```

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.
- Replace {table_name} with the name of the table you want to create. In this example, enter products as the table name.
- Enter a {unique-UUID} for the request and the {auth-token} you created earlier.
- The --data-raw option defines the new product, including a description, price, and created timestamp.

Using a GraphQL query, confirm the new product is available in the database:

```
curl --location --request POST 'https://{databaseid}-{region}.apps.astra.datastax.com/api/graphql' \
  --header 'accept: */*' \
  --header 'content-type: application/json' \
  --header 'x-cassandra-request-id: {unique-UUID}' \
  --header 'X-Cassandra-Token: {auth-token}' \
  --header 'Content-Type: application/json' \
  --data-raw '{"query":"query {products(value: {id:\"Heavy Lift Arms II\"}) {values {id name price description created}}}"}'
```

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.
- Enter a {unique-UUID} for the request and the {auth-token} you created earlier.

In the next post, Better Botz you will learn to create a new table for orders that have shipped and explore further data modeling!

Using Glitch and the Astra Data API

BetterBotz — Thu, 16 Apr 2020 19:24:39 +0000

This post is a part of a tutorial series for learning how to build an app with DataStax's Astra database. See all of the posts in this Better Botz series to start using Astra now.

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 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.

In Glitch, open the Better Botz project.
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.
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.
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.

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}"}'

In a browser, log in to DataStax Astra.
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.
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.
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"}
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.

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"}'

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.
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:

Copy the changeset parameter for each of the following examples and replace the changeset defined by the --data option of the call.
Replace the primary key in the the --url option to use the value indicated before the example.
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.

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"}'

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.
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:

Edit the sample-run.sh script to include your Astra database username and password.
```
ASTRAUSER=username ASTRAPASSWORD=password
```
Select Tools > Terminal to open a terminal connected to your project.
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
```
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.

Creating an Express server for Better Botz

BetterBotz — Mon, 16 Mar 2020 20:40:23 +0000

This post is a part of a tutorial series for learning how to build an app with DataStax's Astra database. See all of the posts in this Better Botz series to start using Astra now.

In the last blog post, we learned about Better Botz and their need for a new online ordering application. We created an Astra database, generated two tables, inserted some data, and learned how to retrieve data.

Using that database, we’re going to configure an Express backend server on Node.js. This server is the backbone of the React application that will become the online ordering and reporting platform for Better Botz. If that goal sounds ambitious, don’t worry; we’re going to guide you through each part of the process. While experience with Node.js is helpful, it is not required.

We’ll install the DataStax Node.js driver, download the secure connect bundle for your Astra database, and then configure the driver to use that bundle when connecting to your Astra database. After establishing that connection, we can use the Node.js driver as our conduit for getting data in and out of the database.

Goal 1: Laying the Foundation

Before you dive into developing the ordering application, lay the necessary groundwork. You’ll install Node.js and the DataStax Node.js driver, and then download the secure connect bundle for your Astra database.

First, download and install Node.js. Follow the instructions on the Node.js website for your preferred method of installing software:

With Node.js installed, install the DataStax Node.js driver:
```
$ npm install cassandra-driver
```
Create a new directory for your Node.js application, and then go to that directory:
```
$ mkdir betterbotzapp && cd betterbotzapp
```

We’ll be creating a set of files to work from. Choose one of the following options:

Prebuilt: Clone this BetterBotz GitHub repository. This option provides all of the files you need with the main structure already in place.
Manual: Use express generator to create a skeleton Node.js application. With this option, you’ll do more of the manual work to create files, folder structures, and source code.

If you chose the prebuilt option, jump ahead to download your database credentials. For the manual route, let’s create a barebones project to work from.

Install express-generator to quickly create a skeleton Node.js application for us to work with, and then run express:

$ npm install -g express-generator
$ express

When you run express, a skeleton project is created with the minimal files necessary for a basic Node.js application. Do a simple list to show the files that were created and familiarize yourself with the files we’ll work with.

$ ls -l
  |_app.js
  |_bin
  |_package.json
  |_public
  |_routes
  |_views

With the project structure in place, you’re ready to download the security credentials for your Astra database.

Goal 2: Downloading Database Security Credentials

From your DataStax Astra database, download the secure connect bundle, which contains the security credentials for connecting to your database.

In a browser, log in to DataStax Astra.
From the Databases page, under Actions, select the ellipsis (…) for the database you want to connect to and select Connection details.
In the Connection Details window, select Download secure connect bundle to download the secure-connect-database_name.zip file.

This file contains the security certificates and credentials for your
database.

Important: Do not share this secure connect bundle with anyone outside of your organization. Anyone with access to these credentials can potentially access your database.
In your betterbotzapp directory, create a .data directory:
```
$ cd betterbotzapp
$ mkdir .data
```
Move the secure connect bundle that you downloaded to the .data directory you just created. Placing the bundle in this hidden directory allows you to better protect your database credentials and control access.
```
$ cp ~/Downloads/secure-connect-database_name.zip ~/betterbotzapp/.data
```
Change the permissions on the .data directory so that only you have read/write access:
```
$ chmod 700 .data
```

Goal 3: Connect the server to your database

Ensure that your Express server can access the secure connect bundle for your database, and that the DataStax Node.js driver is configured correctly.

In your betterbotzapp project, open index.js in the routes directory.

$ cd routes
$ nano index.js

In index.js, replace the value for the path variable with the path to your secure connect bundle:
```
const path = './.data/secure-connect-database_name.zip';
const { Client } = require('cassandra-driver');
const client = new Client({
cloud: { secureConnectBundle: path },
credentials: { username: process.env.ASTRAUSERNAME, password: 
process.env.ASTRAPASSWORD}
});
```
This code uses the credentials from the secure connect bundle in your .data directory to make a connection to your Astra database.

Note: if you’re using the files from the Better Botz GitHub repository, the remainder of your index.js file is preconfigured to obtain data from your database. Skip ahead to step 5 to install dependencies and start your server.

In the index.js file, replace the main router.get function with the following code.

The first function gets the home page for your server and adds a Better Botz title.
The second function creates a data report with results from a request that you’ll add in the next step.
The third function generates a direct data response of the JSON response using the API get call.

router.get('/', function(req, res, next) {
  res.render('index', { title: 'Better Botz' });
});

router.get('/datareport', function (req, res) {
  getMoreData().then(function(data){
    res.render('datareport', { data } );
  }).catch(function(filteredData){
    res.send(filteredData);
  })
});

router.get('/data', function (req, res) {
  getMoreData().then(function(data){
    res.send(data);
  }).catch(function(filteredData){
    res.send(filteredData);
  })
});

Lastly, add the following code to the index.js file. This code sends a CQL SELECT statement to retrieve data from your orders table in the betterbotz keyspace.

async function getMoreData(){
  const result = await client.execute('SELECT customer_name, address, 
description, price, prod_id, prod_name, sell_price FROM 
betterbotz.orders');
  return result.rows;
}

Save your index.js file, and then run the following command to install required dependencies and packages for Node.js:
```
$ npm install
```
Running this command downloads dependencies to your machine. The number of downloaded dependencies depends on other packages installed on your system.
From your terminal, run the following command to set username and password variables and then start your Express server. Replace the variables with the username and password for your Astra database.
```
$ ASTRAUSER=myusername ASTRAPASSWORD=mypassword node bin/www
```

Great! You won’t notice anything in your terminal...yet. Let’s review results in a browser to ensure that the server is working, and then check back in the terminal.

Goal 3: Reviewing results

Open a browser and navigate to http://localhost:3000/. You should see a page displaying Better Botz as the title, which verifies that your Express server is running with the included Jade templates.

Note: If you cloned the Better Botz GitHub repository, your Jade template already contains the following lines. Skip ahead to step 3.

We’re going to make some modifications to the layout.jade template, which lives in the /views directory of your betterbotzapp project. For those going the manual route, we’ll add some code to update the Jade template, which affects the information displayed in the browser.

In your betterbotzapp project, navigate to the /views directory and open the layout.jade file.

Replace the body section with the following code:

body
    header
      h1= title

    main
      p
        | Welcome to the #{title} site to help you build what you need for your Better Botz workforce!

    footer
      | Powered by&nbsp;
      a(href='https://astra.datastax.com') DataStax Astra
      | !
      | Lots of HTML turned into Jade with&nbsp;
      a(href='https://html2jade.org/') html2jade
      | . Built by all the robots at&nbsp;
      a(href='https://twitter.com/betterbotz/') Better Botz
      | !

In the /templates directory, create a datareport.jade file and add the following code.

extends layout

block content
    h1 Customer Orders
    ul
        for order in data
            li
                div
                    b Customer Name:
                    =order.customer_name
                    div
                        b Address:
                        =order.address
                    div
                        b Description:
                        =order.description
        else
            li No orders currently.

Save your changes to the layout.jade and datareport.jade files.
Use Ctrl + C to stop the server. Restart the server with your database username and password.
```
$ ASTRAUSER=myusername ASTRAPASSWORD=mypassword node bin/www
```
Refresh your browser pointing to http://localhost:3000/.

Your browser displays the changes, which include a new footer with links to the projects we’re using to build our database, server, and HTML for the page. Great job!

We know that our server is running and that we can actively make changes that are reflected in the browser.

Let’s revisit your terminal. After updating the footer in the layout.jade file and refreshing your browser, you should see a few GET calls:

GET / 304 254.400 ms - -
GET /stylesheets/style.css 304 1.893 ms - -

These calls indicate requests from your Express server to the browser endpoints for data. Each time that you refresh your browser, GET calls are sent to the database and updated in your terminal.

Now, let’s review two different endpoints to view data responses from those GET calls.

In a browser, navigate to http://localhost:3000/datareport. You’ll see similar content to the main URL, except that the content displaying is a direct data response of the JSON response that is using the API GET call.
Navigate to http://localhost:3000/data and you’ll see...a set of square brackets? That’s correct! If you inspect the CQL statement that we added to the index.js file, you’ll see that we’re trying to retrieve data from the orders table:

SELECT customer_name, address, description, price, prod_id, prod_name, sell_price FROM betterbotz.orders

Your orders table is currently empty, which is why no data returns at the /data endpoint. In the next post, we’ll add data to the orders table and create a new table to track which orders are shipping and which have already shipped. We can then check the /data endpoint and see data being returned to the browser.

Stay tuned!

Building applications with Better Botz

BetterBotz — Wed, 04 Mar 2020 18:06:32 +0000

This post is a part of a tutorial series for learning how to build an app with DataStax's Astra database. See all of the posts in this Better Botz series to start using Astra now.

Introducing Better Botz

Better Botz* believes that robots can help improve the health, prosperity, and wellbeing of human beings across the world. The company seeks to build Better Botz for a better future!

For example, a personal care robot can help disabled or elderly people retrieve objects, carry groceries, navigate their home and community, and complete essential daily tasks. Business-oriented robots can be built for tunneling or mining applications, surveying dangerous areas, completing minor surgery, or traversing great distances without tiring.

Better Botz supports learning and teaching through hands-on interactions, so they sell kits to build your robot using predefined blueprints. Alternatively, you can purchase individual components to customize your robot for various applications. Pretty cool, right?

The Growth Problem

Business is booming, and Better Botz needs a simple application to replace their online ordering platform for accepting orders. Customers can either purchase a robot kit or individual components to build their own robot. The problem is, Better Botz is so focused on designing and building robots that they don’t have time to build the ordering application. Here’s where you come in!

Better Botz is looking for a savvy software developer (that’s you) to design and build an online ordering application to provide users with a seamless shopping and checkout experience. In this initial post, we’ll review the available data, create a database to store the data, and generate two tables that are crucial to solving this problem.

With each subsequent post, we’ll dig deeper into different data sets and expand our application, integrating different technologies as we go.

The Data

Initially, we’ll be focusing on two tables: Products and Orders. Products can either be individual parts (arms/legs, torso, head, and so on) or complete robot kits. Orders are just a list of Products that customers want to purchase.

Before creating the tables, we need somewhere to put them. We need a database that is lightweight, performant, and scalable to expand with our application. The DataStax Astra database checks all of those boxes, and also has a Free tier that you can use for zero dollars while building your application.

Goal 1: Create a DataStax Astra database on the Free tier

Use DataStax Astra to create a database on the Free tier:

On the database login page, select Register.
Verify your account using the link from the verification email.
Follow the prompts to create a database:
- Choose the Free Tier as the Compute Size.
- Select a region where you want to create the database.
- Enter the database details, using betterbotz as the keyspace name.

After your database is created, you’re ready to create some tables!

Goal 2: Create tables in your database

Because your database runs on Apache Cassandra, we’ll be using the Cassandra Query Language (CQL) to create tables. If you’ve worked with relational databases like MySQL and Postgres, CQL is similar to SQL. The main difference is that Apache Cassandra databases are non-relational, so they use different concepts to store and retrieve data.

In your database, select the CQL Console tab to open the CQL SHell (CQLSH).

When prompted, enter the database username and password you set when creating the database.

After entering the user credentials, the CQLSH prompt displays:

Connected to caas-cluster at caas-cluster-caas-dc-service:9042.
[cqlsh 6.8.0 | DSE 6.8.0.77 | CQL spec 3.4.5 | DSE protocol v2]
Use HELP for help.
dbuser@cqlsh>

At the CQLSH prompt, copy and paste the following CREATE TABLE statements.

Note: If you named your keyspace something other than betterbotz when creating your database, change betterbotz in the following USE statement to match your keyspace name, such as USE mykeyspace. This statement switches to your keyspace before running the CREATE TABLE statements.

    USE betterbotz;

    CREATE TABLE IF NOT EXISTS products (
      id uuid,
      name text PRIMARY KEY,
      description text,
      price decimal,
      created timestamp
    );

    CREATE TABLE IF NOT EXISTS orders (
      id uuid,
      address text,
      prod_id uuid,
      prod_name text,
      description text,
      price decimal,
      sell_price decimal,
      customer_name text PRIMARY KEY
    );

These statements create two tables in your keyspace, which is like a bucket for your tables:

The first statement creates a products table with several fields that define an individual product.
The second table creates an orders table that includes things like product ID, product name, sell price, and other elements of an order.

Goal 3: Insert some data in your tables

With your tables in place, it's time to insert some data.

In the CQLSH prompt for your database, copy and paste the following INSERT statements to insert data into the products table while using your betterbotz keyspace.

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb0,'Heavy Lift Arms', 'Heavy lift arms capable of lifting 1,250 lbs of weight per arm. Sold as a set.',4199.99,'2019-01-10 09:48:31.020+0040')
IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb1,'Precision Action Arms','Arms for precision activities in manufacturing or repair. Sold as a set.',12199.99,'2019-01-10 09:28:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb2,'Medium Lift Arms','Medium lift arms capable of lifting 850 lbs of weight per arm. Sold as a set.',3199.99,'2019-01-10 09:23:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb3,'Drill Arms','Arms for drilling into surface material. Sold as a set. Does not include drill bits.',2199.99,'2019-01-10 09:12:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb4,'High Process AI CPU','Head processor unit for robot with heavy AI job process capabilities.',2199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb5,'Basic Task CPU','Head processor unit for robot with basic process tasks.',899.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb6,'High Strength Torso','Robot body with reinforced plate to handle heavy workload and weight during jobs.',2199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb7,'Medium Strength Torso','Robot body to handle general jobs.',1999.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

INSERT INTO products ( id , name , description, price, created ) VALUES (31047029-2175-43ce-9fdd-b3d568b19bb8,'Precision Torso','Robot torso built to handle precision jobs with extra stability and accuracy reinforcement.',8199.99,'2019-01-10 18:48:31.020+0040') IF NOT EXISTS;

For each successful INSERT statement, you’ll see a message that the statement succeeded:

 [applied]
-----------
   True

Your products table has some data in it that we can work with, so let’s check it out!

Goal 4: Retrieve data from your database

Let’s view all of the available products in your products table so that we know what the data looks like. Run the following SELECT statement to view the name, description, and price of all products in the products table:

SELECT name, description, price FROM betterbotz.products ;

The results are formatted neatly, and show the name of each product, a description, and the price. This result is a good start, but as our product table grows, we don’t want to return the entire result set every time and have to sift through the results.

name                  | description                                                                                 | price
-----------------------+---------------------------------------------------------------------------------------------+----------
            Drill Arms |        Arms for drilling into surface material. Sold as a set. Does not include drill bits. |  2199.99
 Precision Action Arms |                    Arms for precision activities in manufacturing or repair. Sold as a set. | 12199.99
      Medium Lift Arms |               Medium lift arms capable of lifting 850 lbs of weight per arm. Sold as a set. |  3199.99
        Basic Task CPU |                                     Head processor unit for robot with basic process tasks. |   899.99
   High Process AI CPU |                       Head processor unit for robot with heavy AI job process capabilities. |  2199.99
   High Strength Torso |           Robot body with reinforced plate to handle heavy workload and weight during jobs. |  2199.99
 Medium Strength Torso |                                                          Robot body to handle general jobs. |  1999.99
       Heavy Lift Arms |              Heavy lift arms capable of lifting 1,250 lbs of weight per arm. Sold as a set. |  4199.99
       Precision Torso | Robot torso built to handle precision jobs with extra stability and accuracy reinforcement. |  8199.99

For example, if we wanted to retrieve only the record for the Heavy Lift Arms product, we can take our previous query and add a WHERE clause to specify the criteria we want to match. We expand our query to say:

“Get the name, description, and price from my products table, WHERE the name matches a specific criteria”:

SELECT name, description, price FROM betterbotz.products WHERE name = 'Heavy Lift Arms';

There it is! We returned only the record that we wanted instead of a complete list of every product in the table. Knowing how to ask for the data that you want to retrieve is crucial, because what good is a database if you can’t easily get what you need?

name            | description                                                                    | price
-----------------+--------------------------------------------------------------------------------+---------
 Heavy Lift Arms | Heavy lift arms capable of lifting 1,250 lbs of weight per arm. Sold as a set. | 4199.99

Our orders table is currently empty, but we’re going to fix that in the next post.

Recap

In this first post, you learned about Better Botz and the problem you can help solve. To lay the foundation for your online ordering application, you created a database in the cloud, created two tables, inserted data into those tables, and learned how to retrieve data that you want to view. That’s a great start!

In the next post, we’ll create a sample website built with Node.js that also uses Express. We’ll add more data to your existing cloud database and learn about Node.js as we continue to build the Better Botz online ordering application.

References

If you want to explore on your own, here are some links to learn more about CQL and CQLSH:

Disclaimer

Better Botz is a fictitious company used for creating tutorials and examples. The authors of the Better Botz blog posts are employed by DataStax, and our goal is to write informative, tutorial-driven posts to help developers learn and develop skills.