CapacityDB (Capacity Database)

Why CapacityDB?

  • Organizations need a place to securely store data and don’t want to manage a myriad of databases.

  • CapacityDB is a Capacity hosted database that is tightly integrated with the rest of the Capacity stack.

  • Integrates with and builds off the foundation of LiveDB.

  • Available today with upcoming enhancements in Q2 and Q3.

Comparing LiveDB and CapacityDB


Creating and Editing Tables and Schemas

With CapacityDB, you're able to create data tables via the Capacity console UI - no SQL required!  Just open CapacityDB on the left menu, then pick "Schema" from the header and either select an existing table in the list or click "New Schema".  

(Note: If you're a developer who is more comfortable with writing raw SQL, scroll down to "Using the SQL Editor").

To start, enter a name for your table, then click "Add Field".  Then type in a name, data type, and other attributes for that particular field.  When you're all finished, don't forget to save your work.  With a table now created, you can load data!

(Note that you can also edit existing fields after data has been added to the table.  However, major changes may lead to data corruption or loss.)

Adding and Browsing Data

With its rich interface, CapacityDB allows you to review existing data in your organization's tables.  Just select the "DB Content" tab, and you'll see all available data.  On the top right of the screen, you can apply filters to search by field, add new records one-by-one, and even import a CSV file of data for rapidly populating the table with new records in bulk.  Action buttons are visible in the top right of the DB Content view.

(Note that with CSV import, multiple uploads of the same data will result in duplicate records.  Be sure that you want all items in the files to be added.)

Editing and Deleting Data

For each record, if you'd like to make small changes, you can use the three dot menu to the left of the row.  By clicking on it, you'll be given the option to edit the record or delete it entirely.  Again - without writing any SQL!

Using the SQL Editor

CapacityDB shows up as a database icon on the left menu. From here you can view table details and write SQL queries. CapacityDB's SQL format is similar to MySQL.

SQL Autocomplete

As you are typing in your SQL statements, the SQL Editor will perform autocomplete as well:

Downloading Results

You can download the results by clicking on the download icon on the right:


Stored SQL Queries

You can store template based queries for future reuse in the Queries tab. You can use a variety of different SQL statements including SELECT, INSERT, UPDATE etc.

Adding Template Variables

Template variables can be added with the following syntax:

:your_field_name

These will be used to pass variables into the SQL statement via the Developer Platform.

Connecting to the Dev Platform

In order to enable your access to CapacityDB, you need to create a Dev Platform endpoint.


Let's break this down into a few parts.

Setting the API Endpoint

Your endpoint will look something like this:

{{sys.relay_host}}/?service_name=capacitydb&service_path=/api/v1/stored_queries/STORED_QUERY_ID/execute

You will replace your STORED_QUERY_ID with the id in the query string from your CapacityDB url:

Updating the JSON

Your API call will look something like this:

{
"org_id": 2,
    "parameters": {
        "email":"{{email}}",
        "name":"{{name}}",
        "report_type":"{{report_type}}",
        "section":"{{section}}",
        "rating":{{rating}},
        "comment":"{{comment}}",
        "screenshot_yn":"{{screenshot_yn}}",
        "screenshot_file_uuid":"{{screenshot_file_uuid}}"
    }
}
  • org_id is your organization id from the screenshot above
  • The {{variable_name}} represent the variables passed in from the CapacityDB query editor

Testing your Query

You can test your query by entering test values into the fields.

  1. You can set a display name if you want a different label for the data
  2. Fields can be set to Required.
  3. Test values are used to test the endpoint. You can submit the test by clicking the "Test API" button.
  4. The output JSON Path will return an HTTP Status code 200 upon success
  5. JSON Path can be used to retrieve information from the query.


Conclusion

From here, CapacityDB queries can be added to any standard Guided Conversation or Workflow.

Was this article helpful?