Table of Contents

LiveDB Overview

Sean Kemmis Updated by Sean Kemmis

Setting the Environment

Set values in config.json based on your specific database.

Currently the application supports mssql mysql and oracledb databases by setting the databaseType value.

Default configuration is below in case something is accidentally changed or deleted.

{ "databaseType": "mysql", "oracleConfig": { "user": "user", "password": "password", "connectionString": "oracle-db-url:1521/ORCL", "libraryPath": "./oracle-client" }, "mysqlConfig": { "host": "mysql-db-url", "user": "user", "password": "password", "databaseName": "my_database", "port": 3306 }, "mssqlConfig": { "host": "mssql-db-url", "user": "user", "password": "password", "databaseName": "my_database", "port": 1433, "instanceName": "database-instance" }, "logLevel": "info", "capacityHost": "host_provided_by_capacity", "capacitySecret": "secret_provided_by_capacity"}

NOTE: The capacityHost and capacitySecret fields are provided by Capacity and should not be changed. These values are required for the server to connect to Capacity's servers.

Oracle Configuration

Capacity LiveDB uses node-oracledb for oracledb connections

This library requires you to have an Oracle Instant Client installed on the server where this is running and configured via setting oracleConfig.libraryPath insettings.json

The following properties are required in oracleConfig when databaseType is set to oracledb:

  • user - The database user
  • password - The database password
  • connectionString - the host:port/instance_name string that conforms to [//]host_name[:port][/service_name][:server_type][/instance_name]
  • libraryPath - the path to the oracle instant client, either relative to server.js or absolute.

Oracle seems to sometimes require a valid hostname to be set on the machine hosting the client for loopback address 127.0.1.1 (as opposed to the usual 127.0.0.1). If you're getting "ORA-24454: client host name is not set" errors, the following command may help resolve them:

echo -e "127.0.1.1\t`hostname`" | sudo tee -a /etc/hosts

More on this error can be found at this stackoverflow thread

MySQL Configuration

Capacity LiveDB uses node-mysql2 for mysql connections

The following properties are required in mysqlConfig when databaseType is set to mysql

  • user - The database user
  • password - The database password
  • databaseName - The name of the database LiveDB should connect to
  • host - The url to the MySQL database
  • port - The port the MySQL database is running on
MySQL SSL Connections

Three methods of configuring an SSL connections are supported via the optional mysqlConfig.ssl value in config.json when databaseType is set to mysql

  1. Amazon RDS - "ssl": "Amazon RDS
  2. SSL with a CA - "ssl": { "ca": "./name-of-ca-file.pem" }
  3. SSL with Key and Cert - "ssl": { "key": "./name-of-key-file.pem", "cert": "./name-of-cert-file.pem" }

If using option 1, the library automatically fetches rds-combined-ca-bundle.pem and uses it. If using option 2 or 3 the file paths are relative to server.js

For example, if using Amazon RDS but specifying the CA file, you would place the file rds-combined-ca-bundle.pem in the same directory as server.js and set "ssl": { "ca": "./rds-combined-ca-bundle.pem" }

MSSQL Configuration

Capacity LiveDB uses node-mssql with the tedious driver for mssql connections

The following properties are required in mssqlConfig when databaseType is set to mssql

  • user -The database user
  • password - The database password
  • databaseName - the name of the database LiveDB should connect to
  • host -- the url to the mssql database
  • port - the port the mssql database is running on

The following properties are optional in mssqlConfig when databaseType is set to mssql

  • instanceName -- the named instance LiveDB should connect to, can optionally be defined in host by setting host-url\\instanceName


Starting the server

The command node server.js will automatically start the server listening at port 4201 and start displaying logs. You can override the port by passing a PORT value with the command when running the server. For example: PORT=4400 node server.js

The application will assume it is being started from the directory in which server.js resides. It's important to start the command from the same folder.

Once the server starts, it will attempt to connect with Capacity's server and will send a notification to let you know if the connection was successful or not.

There is a configuration UI available at /configuration that can be accessed via a web browser to assist in creating queries, but manual configuration is also possible. If using the default port, the URL would be http://localhost:4201/configuration



Queries

Queries are stored as flat json files, and are located in the queries directory with the following schema:

{  
"inputs"
: [{ "name": "string", "type": "string" }],

"outputs"
: [{ "name": "string", "type": "string" }]

}

Query strings must be defined using @variable_name syntax, and any included variables in a query string must be defined in the inputs array.

For example, if I want to query my users table by name, and return the id and name of the result, my query might look something like:

get_user_by_name.json

{  
"inputs"
: [
{
"name": "name", "type": "VarChar" }
],

"outputs"
: [

{ "name": "id", "type": "Int" },
{ "name": "name", "type": "VarChar" }
],

"query"
: "select * from users where name = @name"

}



Capacity does not have access via the persistent connection to the values stored in the query field or any values placed in the config.json file besides those we provide.

Capacity can get a list of all file names, which are used to send commands, as well as each file's inputs and outputs array.

Logging

The server logs each incoming request over the persistent connection as well as the raw data contained in each request and the raw data being returned to Capacity at the info log level.

The server logs all encountered errors on the error log level.

No other log levels are utilized.

Log level can be configured by setting the logLevel value in config.json. Accepted values are info and error, default is `info`.For example, to log everything, set logLevel to info, but to log only errors, set logLevel to error

config.json Type Definition

The config.json file must conform to the following typescript definition:


enum DatabaseType {
mysql = 'mysql',
mssql = 'mssql',
oracledb = 'oracledb',
}

enum LogLevel {
info = 'info',
warning = 'warning',
error = 'error',
}

type MSSqlConfig = {
host: string
user: string
password: string
databaseName: string
port: string
instanceName?: string
}

type MySqlSSLConfig = string | { ca: string } | { key: string; cert: string }

type MySqlConfig = {
host: string
user: string
password: string
databaseName: string
port: string
ssl?: MySqlSSLConfig
}

type OracleConfig = {
user: string
password: string
connectionString: string
libraryPath: string
}

type ConfigFile = {
databaseType: DatabaseType
oracleConfig?: OracleConfig
mysqlConfig?: MySqlConfig
mssqlConfig?: MSSqlConfig
capacityHost: string
capacitySecret: string
logLevel?: LogLevel
}

Releases

1.3.3 Release - 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 Release - 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 Release - 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 Release - 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 release - 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?

Authenticated Concierge Overview

Okta Authentification

Contact