Table of Contents

LiveDB Client Server

Alan Cheung Updated by Alan Cheung

Overview

LiveDB is a tool that Capacity provides that allows Developer Platform Apps to read and interact with your company's internal database services. Utilizing a secure and persistent connection between the Capacity Cloud and the LiveDB Client Server, your company's copilots will be able to craft database queries that can be used in the Developer Platform Apps. In turn, these apps can be used in workflows, guided conversations, and more for your end users.

Requirements

  • NodeJS 12+
  • Minimum 100mb of memory
  • Network access to the Capacity Cloud (your Customer Success Manager can provide our current IP ranges)
  • Network access to your internal database services

Recommendations

  • We recommend that you setup a reverse proxy (such as Apache, Nginx, or Microsoft IIS) to help create secure and reliable connections to the LiveDB Client Server application.
  • The LiveDB Client Server does not come with innate authentication. So we also recommend integrating some sort of secure auth with your reverse proxy.
  • We recommend, at the very least, denying all public network traffic incoming to the LiveDB Client Server and only adding our Capacity Cloud IP Ranges to an allow-list.
  • We recommend a regular backup of the queries directory. Any query you create in the client interface is saved locally to your server. The Capacity Cloud does not keep a backup of these queries.
  • We recommend pulling in assistance from your Engineering and DBA departments to help secure and stabilize your LiveDB Client Server for longterm use.

Known Issues

  • There is a bug where the LiveDB Client Server cannot read existing queries in the queries folder when it contains subdirectories. For now, only keep the saved json files in the queries folder to avoid this error.
  • (Windows Only) The LiveDB Client Server does not currently log to the Windows Event Log. If you need to view log messages, run the application in a Command Prompt window.

Installation Instructions

Currently, the LiveDB Client Server does not have a one-click installation solution.

Below are some generalized instructions for common Linux and Windows environments. Please note that instructions may need to be modified depending on your companies needs and requirements.

Changing the Client Server Port

The LiveDB Client Server defaults to port 4201 when running. However if you wish to change this to another port, simply set the environment variable PORT to a different value when you start the server.

Linux Installation

  • Download the latest LiveDB Client Server release from the Releases section to your server (the example below uses v1.3.3)
    wget http://public.files.capacity.com/releases/livedb-client/livedb-client-1.3.3.zip
  • Unzip the archive to the /opt/ path
    tar xf livedb-client-1.3.3.zip --directory /opt/
  • Modify the config.json file for your specific database needs, see the Configurations section for further details.
  • Create a systemd service file to launch the app and place in /etc/systemd/system/ , below is an example:
    Make sure that the user you are selecting to run the NodeJS application as has read/write access to the directory where the application is running from. The LiveDB Client Server will write any queries you create into a queries folder in that working directory.
[Unit]
Description=Capacity LiveDB Client Server
Documentation=https://support.capacity.com
After=network.target

[Service]
Environment=PORT=4201
Type=simple
User=ubuntu
WorkingDirectory=/opt/livedb-client-1.3.3
ExecStart=/usr/bin/node /opt/livedb-client-1.3.3/server.js
Restart=on-failure

[Install]
WantedBy=multi-user.target
  • Launch the service using systemctl and then check logs with journalctl, you should see logs similar to the following on a successful connection to the Capacity Cloud:
     {"level":30,"time":1607463836952,"pid":1,"hostname":"livedb-doc-example","msg":"SOCKET CONNECTION ATTEMPT TO HOST https://livedb.capacity.com WITH TOKEN xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx","v":1}
    {"level":30,"time":1607463836956,"pid":1,"hostname":"livedb-doc-example","msg":"Server is listening on port 4201","v":1}
    {"level":30,"time":1607463837059,"pid":1,"hostname":"livedb-doc-example","msg":"SOCKET CONNECTED","v":1}

Windows Installation

You have a few options for installing and running the LiveDB Client Server on a Windows applications. Please be aware of the pros and cons of each method that will be outlined below.

Running as a Windows Service

Running the LiveDB Client Server as a Windows Service is the best way to ensure the highest uptime for the application. However please be aware that the current version of the LiveDB Client Server does not support logging to the Windows Event Log. This means that if you run it as a Windows Service, you will not be able to see any log messages.

We recommend choosing this method if you have tested running the LiveDB Client Server in a command prompt before and are sure that it is correctly connecting to the Capacity Cloud.

There are many methods for setting up a Windows Service, the following example utilizes the node package of node-windows to install or uninstall the service:

  • Download the latest release of the LiveDB Client Server from the Releases section. In this example, we have downloaded the release and unzipped it to C:\livedb-client-server-1.3.3
  • In a Command Prompt window, navigate to the path you unpacked the release to and make sure you have installed node-windows
    npm install node-windows
  • Once installed, you will create an install-service.js script with content similar to the following:
    var Service = require('node-windows').Service;

    var svc = new Service({
    name:'LiveDB Client Server',
    description: 'The LiveDB Client Server that interfaces with the Capacity Cloud',
    script: 'C:\\livedb-client-server-1.3.3\\server.js',
    workingDirectory: 'C:\\livedb-client-server-1.3.3'
    });

    // wait for the install event that ensures the service is available
    svc.on('install',function(){
    svc.start();
    });

    svc.install();
  • A service named LiveDB Client Server will now be listed in the Services panel and can be started/stopped there as needed
Running in a Command Prompt

Running the LiveDB Client Server in a Command Prompt window has the benefit of being able to view all of the log messages easily. However the application will only continue to run as long as the Command Prompt stays open. This means you cannot log out of your current session (ie disconnect, do not log off from RDP) otherwise the server will shutdown.

  • Download the latest release of the LiveDB Client Server from the Releases section. In this example, we have downloaded the release and unzipped it to C:\livedb-client-server-1.3.3
  • You can then navigate to the download path in Command Window or create a simple .bat file to run the following command to launch the server.
    Make sure your working directory is the path where you unpacked the LiveDB Client Server to. The user you execute the command with will also need write permissions to this path.
    node server.js

Upgrade Instructions

We recommend keeping a backup copy of your config.json and your queries folder before upgrading the LiveDB Client Server.
  • To update the LiveDB Client Server, we would recommend following the same steps your team used to deploy your existing version (see the above sections for Linux and Windows systems), but unpack the archive to a separate directory. For example, unpacking to livedb-client-server-1.3.4 instead of the previous livedb-client-server-1.3.3
  • Copy over the config.json and the queries folder from your previous installation to the new one
  • Update the Linux systemd, Windows Service, or other method for starting up the LiveDB Client Server to account for the new directory path
  • Start up the new server!

Configurations

The LiveDB Client Server releases come with an example config.json file that has the basic skeleton of the configuration shown. Below is a breakdown of the options available to you:

Name

Valid Values

Req / Opt

databaseType

mysql | mssql | oracle

required

logLevel

default: info

debug | info | error | warn

optional

capacityHost

https://livedb.capacity.com

required

capacitySecret

provided by your

Customer Success Manager

required

And one of the following nested configuration blocks is required depending on the value you selected for databaseType

oracleConfig

Name

Example

Details

user

"user"

string

password

"password"

string

connectionString

"oracle-db-url:1521/ORCL"

string

libraryPath

"./oracle-instant-client"

the path to the oracle instant client

relative to the node app or absolute

mysqlConfig

Name

Example

Details

host

"mysql-db-url"

string

user

"user"

string

password

"password"

string

databaseName

"my_database"

string

port

3306

integer

ssl

""

see MySQL SSL Configuration section below

mssqlConfig

Name

Example

Details

host

"mssql-db-url"

string

user

"user"

string

password

"password"

string

databaseName

"my_database"

string

port

1433

integer

instanceName

"database-instance"

string

Oracle Configuration

The LiveDB Client Server utilizes the node-oracledb library for creating connections to your Oracle database. This requires you having an Oracle Instant Client running on the same server that you are running the LiveDB Client Server application on. The libraryPath value will have to be set to the path at which the Oracle Instant Client is running.

The following is a sample configuration for an Oracle database connected to the LiveDB Client Server:

{
"databaseType": "oracle",
"oracleConfig": {
"user": "livedb-client-server",
"password": "ultra-secret-password",
"connectionString": "INT-ORACLE-201:1521/USERDATA",
"libraryPath": "./oracle-instant-client"
},
"logLevel": "error",
"capacityHost": "https://livedb.capacity.com",
"capacitySecret": "secret_provided_by_capacity"
}

MySQL Configuration

The LiveDB Client Server utilizes the node-mysql2 library for creating connections to your MySQL database. The following is a sample configuration for an MySQL database connected to the LiveDB Client Server:

{
"databaseType": "mysql",
"mysqlConfig": {
"host": "internal-rds-101.us-east-1.rds.amazonaws.com",
"user": "livedb-client-server",
"password": "super-secret-password",
"databaseName": "internalDocuments",
"port": 3306
},
"logLevel": "info",
"capacityHost": "https://livedb.capacity.com",
"capacitySecret": "secret_provided_by_capacity"
}

There is also the ability to utilize SSL connections with the MySQL configuration. The value of ssl must be set to only one of the following options:

Option

Details

Amazon RDS

No additional steps needed. The LiveDB Client Server will

automatically fetch the latest rds-combined-ca-bundle.pem from Amazon and use it.

ca

The path to a pem file containing the certificate authority

must be provided, relative to server.js

key

cert

The paths to both the certificate key file and certificate file

must be provided, relative to server.js

""

If left empty, the LiveDB Client Server will not attempt to make a connection with SSL

The following is a sample configuration for an MySQL database connected to the LiveDB Client Server via SSL with a key and certificate:

{
"databaseType": "mysql",
"mysqlConfig": {
"host": "db1001.internal.company.com",
"user": "livedb-client-server",
"password": "super-secret-password",
"databaseName": "internalDocuments",
"port": 3306
},
"ssl": {
"key": "./secure-key.pem",
"cert": "./secure-cert.pem"
},
"logLevel": "info",
"capacityHost": "https://livedb.capacity.com",
"capacitySecret": "secret_provided_by_capacity"
}

MSSQL Configuration

The LiveDB Client Server utilizes the node-mssql library for creating connections with your MSSQL database. The following is a sample configuration for an MSSQL database connected to the LiveDB Client Server:

{
"databaseType": "mssql",
"mysqlConfig": {
"host": "internal-mssql-blue.internal.company.com",
"user": "livedb-client-server",
"password": "crazy-secret-password",
"databaseName": "internalHumanResources",
"port": 1433,
"instanceName": "insuranceDocuments"
},
"logLevel": "info",
"capacityHost": "https://livedb.capacity.com",
"capacitySecret": "secret_provided_by_capacity"
}

Using the LiveDB Client Server

Once you have the LiveDB Client Server running, you can navigate directly to in a browser to pull up its interface. This will either be the direct port of the application (defaulting to 4201) or a normal HTTP/HTTPS connection if you have set up your own reverse proxy.

Creating Queries

An interface has been provided to help facilitate the creation of queries that can be called upon by your Developer Apps.

Query Inputs will be the inputs that Capacity will ask for when utilizing the query and must be declared and used in the Query itself using an @variable_name syntax.

Query Outputs will be the list of fields that the LiveDB Client Server will send back to the Capacity Cloud and will be available for use in further steps by your Developer Apps.

For example, in the interface image above, we have a query get_user_by_id which is expecting an integer input of id . When provided, it will supply that value into the query:

SELECT * FROM users WHERE user_id = @id

The query will return many possible columns, but the LiveDB Client Server will look for 3 columns specifically defined in the outputs. In this case: name, email, and age are sent back to the Capacity Cloud and will be available for further steps in the Developer Apps.

Releases

Current Release

1.3.4 - 04/20/2021
  • Improved overall logging messages

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.3.4.zip

Release Archive

1.3.3 - 11/18/2020
  • Fixed bug where valid queries were failing validation if they used the same variable twice

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.3.3.zip

1.3.1 - 11/11/2020
  • Added ability to duplicate an existing query

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.3.1.zip

1.3 - 10/22/2020
  • OracleDB Support
  • Modifications to config.json structure
  • Client UI now uses a database specific type list, older MySQL clients may see their selected types empty in the configuration UI until they choose a new type. This is because previously the app was only using a type list from MSSQL. Previous queries should still execute without modification, however.

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.3.zip

1.2 - 10/21/2020
  • MySQL SSL Support
  • Client UI Bug fixes
  • Client UI properly handles JSON columns on query test

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.2.zip

1.0 - 4/30/2020
  • Initial LiveDB Client release
  • Adds support for MySQL and MSSQL databases

http://public.files.capacity.com/releases/livedb-client/livedb-client-1.0.zip

How did we do?

Concierge Links

Concierge Session Variables

Contact