Repository: rickbergfalk/sqlpad
Pull request #476 (2019-10-12 22:12:27)

Define connections from config
Adds support for defining connections via configuration. Supported configuration methods include env var, ini, or json file. 


Patch:
diff --git a/README.md b/README.md
index c4d35d82..97ab943a 100644
--- a/README.md
+++ b/README.md
@@ -65,6 +65,10 @@ sqlpad --dbPath ../db --port 3010
 
 A docker image may be built using the Dockerfile located in `server` directory. See `docker-publish.sh` for example docker build command.
 
+## Development
+
+[Developer guide](DEVELOPER-GUIDE.md)
+
 ## Configuration
 
 SQLPad may be configured via environment variables, config file, or command line flags.
@@ -245,9 +249,257 @@ Default: `true`
 Allows pre-approval of email domains. Delimit multiple domains by empty space.  
 Env var: `WHITELISTED_DOMAINS`
 
-## Development
+### Connection configuration
 
-[Developer guide](DEVELOPER-GUIDE.md)
+As of 3.2.0 connections may be defined via application configuration.
+
+Every connection defined should provide a `name` and `driver` value, with driver equaling the value in header parentheses below. `name` will be the label used in the UI to label the connection.
+
+Field names and values are case sensitive.
+
+The connection ID value used can be any alphanumeric value, and is case-sensitive. This can be a randomly generated value like SQLPad's underlying embedded database uses, or it can be a more human-friendly name, or an id used from another source.
+
+How connections are defined in configuration depends on the source of the configuration.
+
+#### Environment variable
+
+When using environment variables, connection field values must be provided using an environment variable with the convention `SQLPAD_CONNECTIONS__<connectionId>__<fieldName>`. Note double underscores between `SQLPAD_CONNECTIONS`, `<connectionId>`, and `<fieldName>`. Both connection ID and field name values are case sensitive. Boolean values should be the value `true` or `false`.
+
+Example for a MySQL connection with id `prod123`.
+
+```sh
+SQLPAD_CONNECTIONS__prod123__name="Production 123"
+SQLPAD_CONNECTIONS__prod123__driver=mysql
+SQLPAD_CONNECTIONS__prod123__host=localhost
+SQLPAD_CONNECTIONS__prod123__mysqlInsecureAuth=true
+```
+
+#### INI file
+
+When defining a connection in an INI file, use section header with the value `connections.<connectionId>`.
+
+```ini
+[connections.prod123]
+name = Production 123
+driver = mysql
+host = localhost
+mysqlInsecureAuth = true
+```
+
+#### JSON file
+
+When using JSON file, provide `<connectionId>` as a key under `connections`.
+
+```json
+{
+  "connections": {
+    "prod123": {
+      "name": "Production 123",
+      "driver": "mysql",
+      "host": "localhost",
+      "mysqlInsecureAuth": true
+    }
+  }
+}
+```
+
+#### CrateDB (crate)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>crate</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+  </tbody>
+</table>
+
+#### Apache Drill (drill)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>drill</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+    <tr><td>drillDefaultSchema</td><td>Default Schema</td><td>text</td></tr>
+    <tr><td>ssl</td><td>Use SSL to connect to Drill</td><td>boolean</td></tr>
+  </tbody>
+</table>
+
+#### SAP Hana (hdb)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>hdb</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>hanaport</td><td>Port (e.g. 39015)</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+    <tr><td>hanadatabase</td><td>Tenant</td><td>text</td></tr>
+    <tr><td>hanaSchema</td><td>Schema (optional)</td><td>text</td></tr>
+  </tbody>
+</table>
+
+#### MySQL (mysql)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>mysql</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>database</td><td>Database</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+    <tr><td>mysqlInsecureAuth</td><td>Use old/insecure pre 4.1 Auth System</td><td>boolean</td></tr>
+  </tbody>
+</table>
+
+#### PostgreSQL (postgres)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>postgres</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>database</td><td>Database</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+    <tr><td>postgresSsl</td><td>Use SSL</td><td>boolean</td></tr>
+    <tr><td>postgresCert</td><td>Database Certificate Path</td><td>text</td></tr>
+    <tr><td>postgresKey</td><td>Database Key Path</td><td>text</td></tr>
+    <tr><td>postgresCA</td><td>Database CA Path</td><td>text</td></tr>
+    <tr><td>useSocks</td><td>Connect through SOCKS proxy</td><td>boolean</td></tr>
+    <tr><td>socksHost</td><td>Proxy hostname</td><td>text</td></tr>
+    <tr><td>socksPort</td><td>Proxy port</td><td>text</td></tr>
+    <tr><td>socksUsername</td><td>Username for socks proxy</td><td>text</td></tr>
+    <tr><td>socksPassword</td><td>Password for socks proxy</td><td>text</td></tr>
+  </tbody>
+</table>
+
+#### PrestoDB (presto)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>presto</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>prestoCatalog</td><td>Catalog</td><td>text</td></tr>
+    <tr><td>prestoSchema</td><td>Schema</td><td>text</td></tr>
+  </tbody>
+</table>
+
+#### MS SQL Server (sqlserver)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>sqlserver</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>database</td><td>Database</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+    <tr><td>domain</td><td>Domain</td><td>text</td></tr>
+    <tr><td>sqlserverEncrypt</td><td>Encrypt (necessary for Azure)</td><td>boolean</td></tr>
+  </tbody>
+</table>
+
+#### Vertica (vertica)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>vertica</code></td><td>text</td></tr>
+    <tr><td>host</td><td>Host/Server/IP Address</td><td>text</td></tr>
+    <tr><td>port</td><td>Port (optional)</td><td>text</td></tr>
+    <tr><td>database</td><td>Database</td><td>text</td></tr>
+    <tr><td>username</td><td>Database Username</td><td>text</td></tr>
+    <tr><td>password</td><td>Database Password</td><td>text</td></tr>
+  </tbody>
+</table>
+
+#### Cassandra (cassandra)
+
+<table>
+  <thead>
+    <tr>
+      <th>key</th>
+      <th>description</th>
+      <th>data type</th>
+    </tr>
+  </thead>
+  <tbody>
+    <tr><td>name</td><td>Name of connection</td><td>text</td></tr>
+    <tr><td>driver</td><td>Must be <code>cassandra</code></td><td>text</td></tr>
+    <tr><td>contactPoints</td><td>Contact points (comma delimited)</td><td>text</td></tr>
+    <tr><td>localDataCenter</td><td>Local data center</td><td>text</td></tr>
+    <tr><td>keyspace</td><td>Keyspace</td><td>text</td></tr>
+  </tbody>
+</table>
 
 ## License
 
diff --git a/client/src/connections/ConnectionList.js b/client/src/connections/ConnectionList.js
index 03105c7d..1f0a1c90 100644
--- a/client/src/connections/ConnectionList.js
+++ b/client/src/connections/ConnectionList.js
@@ -97,7 +97,7 @@ function ConnectionList({
 
         const actions = [];
 
-        if (currentUser.role === 'admin') {
+        if (currentUser.role === 'admin' && item.editable) {
           actions.push(
             <Button
               key="edit"
diff --git a/server/config.dev.ini b/server/config.dev.ini
new file mode 100644
index 00000000..89e37b73
--- /dev/null
+++ b/server/config.dev.ini
@@ -0,0 +1,7 @@
+; INI config file that defines some connections for development purposes
+
+[connections.devdbdriverid123]
+driver = mock
+name = mock connection from ini
+host = mock-host
+useSsl = true
\ No newline at end of file
diff --git a/server/lib/config/fromFile.js b/server/lib/config/fromFile.js
index b6a50eec..0a32a9e0 100644
--- a/server/lib/config/fromFile.js
+++ b/server/lib/config/fromFile.js
@@ -1,13 +1,17 @@
+const minimist = require('minimist');
 const fs = require('fs');
 const ini = require('ini');
 const configItems = require('./configItems');
 
+const argv = minimist(process.argv.slice(2));
+const configFilePathFromConfig = argv.config || process.env.SQLPAD_CONFIG;
+
 /**
  * Reads and parses config file.
  * This file may be either JSON or INI file.
  * @param {string} configFilePath
  */
-function fromFile(configFilePath) {
+function fromFile(configFilePath = configFilePathFromConfig) {
   let parsedFile = {};
   const warnings = [];
 
@@ -28,13 +32,17 @@ function fromFile(configFilePath) {
     // Prior to SQLPad 3 the saved config file was a JSON result of what minimist parsed from argv
     // This means that there could be cliFlag's in the json ie `cert-passphrase` or `dir` for dbPath
     // These are no longer supported from a file
-    Object.keys(parsedFile).forEach(key => {
-      const configItem = configItems.find(item => item.key === key);
-      if (!configItem) {
-        let warningMessage = `Config key ${key} in file ${configFilePath} not recognized.`;
-        warnings.push(warningMessage);
-      }
-    });
+    Object.keys(parsedFile)
+      // connections is a special key that we will ignore for now
+      // It will define connections by id and is not to have a warning message
+      .filter(key => key !== 'connections')
+      .forEach(key => {
+        const configItem = configItems.find(item => item.key === key);
+        if (!configItem) {
+          let warningMessage = `Config key ${key} in file ${configFilePath} not recognized.`;
+          warnings.push(warningMessage);
+        }
+      });
   }
 
   return [parsedFile, warnings];
diff --git a/server/lib/config/index.js b/server/lib/config/index.js
index 738b80d6..70d5ed62 100644
--- a/server/lib/config/index.js
+++ b/server/lib/config/index.js
@@ -6,11 +6,10 @@ const fromFile = require('./fromFile');
 const getOldConfigWarning = require('./getOldConfigWarning');
 
 const argv = minimist(process.argv.slice(2));
-const configFilePath = argv.config || process.env.SQLPAD_CONFIG;
 
 const defaultConfig = fromDefault();
 const envConfig = fromEnv();
-const [fileConfig, warnings] = fromFile(configFilePath);
+const [fileConfig, warnings] = fromFile();
 const cliConfig = fromCli(argv);
 
 // Old files might have some values no longer recognized
diff --git a/server/models/connections.js b/server/models/connections.js
index fa133efb..e83556d7 100644
--- a/server/models/connections.js
+++ b/server/models/connections.js
@@ -3,12 +3,78 @@ const _ = require('lodash');
 const drivers = require('../drivers');
 const cipher = require('../lib/cipher.js');
 const decipher = require('../lib/decipher');
+const getConfigFromFile = require('../lib/config/fromFile.js');
 
-// TODO this file being named connections makes it awkward to use
-// because you'll want to do the following:
-// connections.findAll().then(connections => )
-// Instead models should be db folder, and db.<itemname>.findAll() should do what it needs
-// db.js in lib can become to _db or something
+const [configFromFile] = getConfigFromFile() || {};
+
+/**
+ * Get connections from config.
+ *
+ * For environment variables:
+ * connection env vars must follow the format:
+ * SQLPAD_CONNECTIONS__<connectionId>__<connectionFieldName>
+ *
+ * <connectionId> can be any value to associate a grouping a fields to a connection instance
+ * If supplying a connection that was previously defined in the nedb database,
+ * this would map internally to connection._id object.
+ *
+ * <connectionFieldName> should be a field name identified in drivers.
+ *
+ * To define connections via envvars, `driver` field should be supplied.
+ * _id field is not required, as it is defined in second env var fragment.
+ *
+ * Example: SQLPAD_CONNECTIONS__ab123__sqlserverEncrypt=""
+ *
+ * From file, resulting parsed configuration from file is expected to follow format `connections.<id>.<fieldname>`
+ * {
+ *   connections: {
+ *     ab123: {
+ *       sqlserverEncrypt: true
+ *     }
+ *   }
+ * }
+ *
+ * @param {object} env
+ * @returns {array<object>} arrayOfConnections
+ */
+function getConnectionsFromConfig(env = process.env) {
+  // Create a map of connections from parsing environment variable
+  const connectionsMapFromEnv = Object.keys(env)
+    .filter(key => key.startsWith('SQLPAD_CONNECTIONS__'))
+    .reduce((connectionsMap, envVar) => {
+      // eslint-disable-next-line no-unused-vars
+      const [prefix, id, field] = envVar.split('__');
+      if (!connectionsMap[id]) {
+        connectionsMap[id] = {};
+      }
+      connectionsMap[id][field] = env[envVar];
+      return connectionsMap;
+    }, {});
+
+  // Get copy of connections from config file
+  const { connections } = _.cloneDeep(configFromFile);
+
+  // connections key from file matches format that is constructed from env
+  // merge the 2 together then create an array out of them
+  const connectionsMap = { ...connectionsMapFromEnv, ...connections };
+
+  const connectionsFromConfig = [];
+  Object.keys(connectionsMap).forEach(id => {
+    try {
+      let connection = connectionsMap[id];
+      connection._id = id;
+      connection = drivers.validateConnection(connection);
+      connection.editable = false;
+      connectionsFromConfig.push(connection);
+    } catch (error) {
+      console.log(
+        `Environment connection configuration failed for ${id} %s`,
+        error
+      );
+    }
+  });
+  return connectionsFromConfig;
+}
 
 function decipherConnection(connection) {
   if (connection.username) {
@@ -21,15 +87,29 @@ function decipherConnection(connection) {
 }
 
 async function findAll() {
-  const connections = await db.connections.find({});
-  return _.sortBy(connections, c => c.name.toLowerCase()).map(
-    decipherConnection
-  );
+  let connectionsFromDb = await db.connections.find({});
+  connectionsFromDb = connectionsFromDb.map(conn => {
+    conn.editable = true;
+    return decipherConnection(conn);
+  });
+
+  const allConnections = connectionsFromDb.concat(getConnectionsFromConfig());
+  return _.sortBy(allConnections, c => c.name.toLowerCase());
 }
 
 async function findOneById(id) {
   const connection = await db.connections.findOne({ _id: id });
-  return decipherConnection(connection);
+  if (connection) {
+    connection.editable = true;
+    return decipherConnection(connection);
+  }
+
+  // If connection was not found in db try env
+  const connectionFromEnv = getConnectionsFromConfig().find(
+    connection => connection._id === id
+  );
+
+  return connectionFromEnv;
 }
 
 async function removeOneById(id) {
@@ -64,5 +144,6 @@ module.exports = {
   findAll,
   findOneById,
   removeOneById,
-  save
+  save,
+  getConnectionsFromConfig
 };
diff --git a/server/package.json b/server/package.json
index a4e12c24..ecab8eee 100644
--- a/server/package.json
+++ b/server/package.json
@@ -28,7 +28,7 @@
   },
   "scripts": {
     "prepublishOnly": "cd .. && ./scripts/build.sh",
-    "start": "node-dev server.js --dbPath ../db --port 3010 --debug --baseUrl /sqlpad",
+    "start": "node-dev server.js --dbPath ../db --port 3010 --debug --baseUrl /sqlpad --config './config.dev.ini'",
     "test": "rimraf ../dbtest && SQLPAD_DB_PATH='../dbtest' SQLPAD_TEST='true' mocha test --timeout 10000 --recursive --exit",
     "fixlint": "eslint --fix '**/*.js'",
     "lint": "eslint '**/*.js'"


Test patch:
diff --git a/server/test/models/connections.js b/server/test/models/connections.js
new file mode 100644
index 00000000..b37418a8
--- /dev/null
+++ b/server/test/models/connections.js
@@ -0,0 +1,74 @@
+const assert = require('assert');
+const connections = require('../../models/connections.js');
+
+describe('getConnectionsFromConfig', function() {
+  it('handles empty object', function() {
+    const cs = connections.getConnectionsFromConfig({});
+    assert(Array.isArray(cs));
+  });
+
+  it('skips partial connections', function() {
+    const cs = connections.getConnectionsFromConfig({
+      SQLPAD_CONNECTIONS__abc__driver: 'postgres'
+    });
+    assert(Array.isArray(cs));
+    assert.equal(cs.length, 0, 'c should be empty');
+  });
+
+  it('parses connection properly', function() {
+    const cs = connections.getConnectionsFromConfig({
+      SQLPAD_CONNECTIONS__abc__driver: 'postgres',
+      SQLPAD_CONNECTIONS__abc__name: 'env-postgres',
+      SQLPAD_CONNECTIONS__abc__host: 'localhost',
+      SQLPAD_CONNECTIONS__abc__port: '5432',
+      SQLPAD_CONNECTIONS__abc__postgresSsl: 'true'
+    });
+    assert(Array.isArray(cs));
+    assert.equal(cs.length, 1, 'cs should have 1');
+    const connection = cs[0];
+    assert.strictEqual(connection.editable, false, 'connection.editable');
+    assert.strictEqual(connection._id, 'abc', 'connection._id');
+    assert.strictEqual(connection.driver, 'postgres', 'connection.driver');
+    assert.strictEqual(connection.name, 'env-postgres', 'connection.name');
+    assert.strictEqual(connection.host, 'localhost', 'connection.host');
+    assert.strictEqual(connection.port, '5432', 'connection.port');
+    assert.strictEqual(connection.postgresSsl, true, 'connection.postgresSsl');
+  });
+
+  it('includes env connection in all connections', async function() {
+    process.env.SQLPAD_CONNECTIONS__abc__driver = 'postgres';
+    process.env.SQLPAD_CONNECTIONS__abc__name = 'env-postgres';
+    process.env.SQLPAD_CONNECTIONS__abc__host = 'localhost';
+    process.env.SQLPAD_CONNECTIONS__abc__port = '5432';
+    process.env.SQLPAD_CONNECTIONS__abc__postgresSsl = 'true';
+
+    const allConnections = await connections.findAll();
+    const connection = allConnections.find(c => c._id === 'abc');
+    assert.strictEqual(connection.name, 'env-postgres', 'connection.name');
+    assert.strictEqual(connection.editable, false, 'connection.editable');
+
+    delete process.env.SQLPAD_CONNECTIONS__abc__driver;
+    delete process.env.SQLPAD_CONNECTIONS__abc__name;
+    delete process.env.SQLPAD_CONNECTIONS__abc__host;
+    delete process.env.SQLPAD_CONNECTIONS__abc__port;
+    delete process.env.SQLPAD_CONNECTIONS__abc__postgresSsl;
+  });
+
+  it('includes env connection by id', async function() {
+    process.env.SQLPAD_CONNECTIONS__abc__driver = 'postgres';
+    process.env.SQLPAD_CONNECTIONS__abc__name = 'env-postgres';
+    process.env.SQLPAD_CONNECTIONS__abc__host = 'localhost';
+    process.env.SQLPAD_CONNECTIONS__abc__port = '5432';
+    process.env.SQLPAD_CONNECTIONS__abc__postgresSsl = 'true';
+
+    const connection = await connections.findOneById('abc');
+    assert.strictEqual(connection.name, 'env-postgres', 'connection.name');
+    assert.strictEqual(connection.editable, false, 'connection.editable');
+
+    delete process.env.SQLPAD_CONNECTIONS__abc__driver;
+    delete process.env.SQLPAD_CONNECTIONS__abc__name;
+    delete process.env.SQLPAD_CONNECTIONS__abc__host;
+    delete process.env.SQLPAD_CONNECTIONS__abc__port;
+    delete process.env.SQLPAD_CONNECTIONS__abc__postgresSsl;
+  });
+});