LiveDB Client Server

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

View full size

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

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

• Unzip the archive to the /opt/path

  tar xf livedb-client-1.3.4.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.4
ExecStart=/usr/bin/node /opt/livedb-client-1.3.4/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.4
• 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.jsscript 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.4\\server.js',
    workingDirectory: 'C:\\livedb-client-server-1.3.4'
  });
  
  // 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.4
• You can then navigate to the download path in Command Window or create a simple .batfile 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:

The notificationEmail setting can be set to any valid email address format. This address will receive email notifications from Capacity whenever the LiveDB instance is disconnected from the Capacity Cloud. This is an optional feature that was added to help Customers maintain the highest availability for their LiveDB Connections that might be in use by their Developer Platform applications within the Capacity Cloud.

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

oracleConfig

mysqlConfig

mssqlConfig

postgresConfig

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:

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

PostgreSQL Configuration

The LiveDB Client Server utilizes the pg library for creating connections with your PostgreSQL database. The following is a sample configuration for an PostgreSQL database connected to the LiveDB Client Server:

{
  "databaseType": "postgres",
  "postgresConfig": {
    "host": "internal-postgres-red.internal.company.com",
    "user": "livedb-client-server",
    "password": "crazy-secret-password",
    "databaseName": "internalSalesData",
    "port": 5432
  },
  "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.

View full size

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.

Frequently Asked Questions

• Why is there no authentication for LiveDB?
  There is no built in authentication for the LiveDB Client Server only. This is the portion of the product that will run in your company's infrastructure and we have elected to not have built in authentication so that it will be easier to integrate with your company's own authentication methods. However traffic from the LiveDB Client Server to the Capacity Cloud is authenticated using the capacitySecret value that will be provided by our Customer Success team.
  
  To be clear, all traffic outbound from your infrastructure to the Capacity Cloud is authenticated and secure!
  
  
• Are there any alternatives to using LiveDB in our infrastructure?
  We have another product called CapacityDB that will allow your company to host data in the Capacity Cloud to be easily accessible. However if the data in question cannot be hosted outside of your infrastructure for privacy and security reasons, we would encourage implementing APIs that could be securely queried from our Developer Platform that will retrieve the data you need.

Current Release

1.5.0 - 4/18/2022

• Added support for PostgreSQL databases

https://public.files.capacity.com/releases/livedb-client/livedb-client-1.5.0.zip

Release Archive

1.4.0 - 10/26/2021

• Updated architecture and added email field for disconnection notifications

https://public.files.capacity.com/releases/livedb-client/livedb-client-1.4.0.zip

1.3.4 - 04/20/2021

• Improved overall logging messages

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

1.3.3 - 11/18/2020

• Fixed bug where valid queries were failing validation if they used the same variable twice

https://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

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

https://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

https://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

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