Tutorial 1: Create a CapacityDB Table, Add Data, and Write Queries
CapacityDB is a Capacity-hosted database that can conveniently and seamlessly integrate with the full Capacity tech stack and our customers’ mission-critical applications. Through an engaging and intuitive interface, even novice users will be able to create, update, and take action on useful data sets.
If you have any questions, contact support@capacity.com.
Introduction
In this first tutorial, you’ll create your first CapacityDB table, populate it with new records, and write some SQL statements that will allow you to manipulate that data. You’ll leverage this data in the following lessons.
Terminology
All of the data in your instance of CapacityDB - your database - must exist as records in tables. Each database structures those records according to a schema. If the terminology is confusing, think of the database as the house, the schema as the blueprint, and a table as a room. Records are everything inside.
1. Create a Table and Schema
The first thing to do is navigate to the CapacityDB section of the Console’s Web UI’s menu bar. Its icon looks like three short cylinders in a stack. If you don’t see it, contact support@capacity.com for help.
The first time you visit the section, you’ll be greeted with an empty database. Not much fun! But rest assured, you'll soon have some new information.
For now, you'll need to navigate to the schema tab to create your first schema (your data blueprint).
Click “New Schema” and provide a name. For this tutorial, we’ll call it “widget”. It’ll contain the blueprint for data about widgets.
After creating the schema, you’ll want to add fields, which are attributes of the data you’ll be storing. Click “Add Field” and enter the fields below, one after the other.
Be careful to match the names and types exactly as shown. While flexible to create schemas, it’s possible that a small change to an existing one - even a single letter - can break your downstream use of stored data.
When you’re finished, click “Save” and “Confirm and Modify”. Note the warning, which reminds users that changes shouldn’t be made without considering potential consequences. You're ok in this instance, and we'll test to make sure that everything is properly entered in a few steps.
2. Add Data
Now that you have the blueprint for your widgets’ data, you can start adding widget records. There are several ways to do this in Capacity:
Adding records one-by-one via the Capacity Console Web UI, covered below.
Adding records in bulk with a specially-formatted CSV, described at https://support.capacity.com/article/775786/loading-data-into-capacitydb-via-csv
Adding records via SQL statements, which you can do by clicking the "SQL Editor" tab and using MySQL commands.
Click the “Add Record” button and you’ll see a popup with fields to enter values about each record.
Enter the following values to create your first widget. When you’re done, click “Save Record”.
Continue to build more records matching the values below, using the “Add another” checkbox if you want, for faster entry.
Note that this process would be slow for adding hundreds or thousands of records. If you have a spreadsheet or CSV file, consider following the steps at https://support.capacity.com/article/775786/loading-data-into-capacitydb-via-csv
After you’ve added the data, test it by clicking on the SQL Editor tab, and write the query shown. Then click “Run” and you should see all of your records returned.
In order to fully use CapacityDB, you’ll occasionally need to compose SQL statements like this. For more information on SQL, start at https://www.w3schools.com/mySQl/default.asp.
3. Write Queries
Now that you have data in your widget table, you’ll want to add some SQL statements (or “queries”) that will return or modify that data. Click the “Queries” tab.
Query 1: Browse
Click “New Query” and name it “Get All Widgets”. This query will be designed to fetch everything from your database. Typically this will be used for presenting the user with a browsable list of your data.
Click “Create Query”, and write the same SELECT statement from before. Save it, and click “Test” at the bottom of the screen. You should see everything from your table returned. If so, congratulations! If not, check that your schema and the query match exactly.
Query 2: Search
You’ll also want to enable searching of the data in CapacityDB, not just browsing. So, add a second SQL query, with the code shown below. Note that the colon is Capacity’s convention for adding an input variable. In this case, we’ll want to add a search term as an input to narrow down our results to matches.
Click “Save”, and this time add an input of “Rivet” at the bottom of the screen. This time, when you click “Test”, you’ll only see records for records named “rivet”.
Query 3: Delete
Create another query called “Delete Widget by ID” that matches the language below. Save, and run it with an ID of 4. Note that this will completely destroy that record. You’ll want to be careful with DELETE statements, but they can be very valuable at keeping your data in sync.
Go back to the DB content tab, and you’ll see that you’ve totally deleted that record! Eventually, you'll be able to do this via chat!
Query 4: Add
Create one more query in the Queries tab. This one will add a new widget, which can be extremely powerful in your automations, workflows, and Guided Conversations.
Enter some values for each input this time, and when you test, you’ll be creating a new widget.
Move to the DB content tab, and you should see a new record for a bracket!
Congratulations! You can now read, add, and delete widgets via CapacityDB. Feel free to add more widget records, execute new SQL code, and add new queries. Or, create a new schema with a different type of object. When you're ready, continue in this tutorial series to create an app that uses these queries.