Repository: rickbergfalk/sqlpad
Pull request #544 (2020-02-20 23:32:38)

Add connection template support
Adds the ability to reference user info in connection configuration using a mustache/handlebars like syntax. 

For any text (string) connection field, user info may be referenced using `{{user.fieldName}}` syntax. 

This also adds additional user fields, `name` and `data`. Name will be used eventually for a the preferred human friendly user name. `data` is to be used to store whatever end users would like to store, and is not something SQLPad will be caring about.

Example: Let's say each SQLPad user has a specific database username they are to use when connecting to a database. You may specify this value in the SQLPad `user.data` field as `user.data.dbuser` (how that is to happen TBD), then reference that value when defining a connection, setting username to `{{user.data.dbuser}}`. When queries are run against a database using that connection, `{{user.data.dbuser}}` gets swapped out with the value associated with the user running the query.


rickbergfalk: This implements #532, but does not add any UI associated with this feature.

Patch:
diff --git a/server/drivers/index.js b/server/drivers/index.js
index 147758c4..1544c938 100644
--- a/server/drivers/index.js
+++ b/server/drivers/index.js
@@ -1,4 +1,5 @@
 const uuid = require('uuid');
+const _ = require('lodash');
 const config = require('../lib/config');
 const utils = require('./utils');
 const getMeta = require('../lib/getMeta');
@@ -35,6 +36,30 @@ function validateArray(path, driver, arrayName) {
   }
 }
 
+/**
+ * Iterates over connection object, replacing any template strings with values from user
+ * This allows dynamic values inserted based on logged in user
+ * This uses a mustache-like syntax, using double mustaches.
+ * User variables can be referenced in connection strings using dot notation
+ * Example: {{user.someKey}} and {{user.data.someKey}}
+ * @param {object} connection
+ * @param {object} user
+ */
+function renderConnection(connection, user) {
+  const replaced = {};
+  Object.keys(connection).forEach(key => {
+    const value = connection[key];
+    if (typeof value === 'string') {
+      _.templateSettings.interpolate = /{{([\s\S]+?)}}/g;
+      const compiled = _.template(value);
+      replaced[key] = compiled({ user });
+    } else {
+      replaced[key] = value;
+    }
+  });
+  return replaced;
+}
+
 /**
  * Require driver implementation for provided path
  * and validate that it meets implementation spec as possible
@@ -127,7 +152,9 @@ function runQuery(query, connection, user) {
     rows: []
   };
 
-  return driver.runQuery(query, connection).then(results => {
+  const renderedConnection = renderConnection(connection, user);
+
+  return driver.runQuery(query, renderedConnection).then(results => {
     const { rows, incomplete } = results;
     if (!Array.isArray(rows)) {
       throw new Error(`${connection.driver}.runQuery() must return rows array`);
@@ -140,7 +167,7 @@ function runQuery(query, connection, user) {
     queryResult.meta = getMeta(rows);
     queryResult.fields = Object.keys(queryResult.meta);
 
-    const connectionName = connection.name;
+    const connectionName = renderedConnection.name;
     const rowCount = rows.length;
     const { startTime, stopTime, queryRunTime } = queryResult;
 
@@ -165,22 +192,26 @@ function runQuery(query, connection, user) {
  * As long as promise resolves without error
  * it is considered a successful connection config
  * @param {object} connection
+ * @param {object} user
  */
-function testConnection(connection) {
+function testConnection(connection, user) {
   const driver = drivers[connection.driver];
-  return driver.testConnection(connection);
+  const renderedConnection = renderConnection(connection, user);
+  return driver.testConnection(renderedConnection);
 }
 
 /**
  * Gets schema (sometimes called schemaInfo) for connection
  * This data is used by client to build schema tree in editor sidebar
  * @param {object} connection
+ * @param {object} user
  * @returns {Promise}
  */
-function getSchema(connection) {
+function getSchema(connection, user) {
   connection.maxRows = Number.MAX_SAFE_INTEGER;
   const driver = drivers[connection.driver];
-  return driver.getSchema(connection);
+  const renderedConnection = renderConnection(connection, user);
+  return driver.getSchema(renderedConnection);
 }
 
 /**
@@ -243,6 +274,7 @@ function validateConnection(connection) {
 module.exports = {
   getDrivers,
   getSchema,
+  renderConnection,
   runQuery,
   testConnection,
   validateConnection
diff --git a/server/models/users.js b/server/models/users.js
index cda32213..93d33db8 100644
--- a/server/models/users.js
+++ b/server/models/users.js
@@ -4,6 +4,7 @@ const passhash = require('../lib/passhash.js');
 const schema = Joi.object({
   _id: Joi.string().optional(), // will be auto-gen by nedb
   email: Joi.string().required(),
+  name: Joi.string().optional(),
   role: Joi.string()
     .lowercase()
     .allow('admin', 'editor', 'viewer'),
@@ -17,7 +18,12 @@ const schema = Joi.object({
     .strip(),
   createdDate: Joi.date().default(Date.now),
   modifiedDate: Joi.date().default(Date.now),
-  signupDate: Joi.date().optional()
+  signupDate: Joi.date().optional(),
+  // `data` field is intended to be something end users populate via various means
+  // That is data can be anything, managed by user, not by SQLPad
+  // The data object's primary purpose will be to store values for replacement in connection templates
+  // For any official SQLPad use, fields should be specified on user object
+  data: Joi.object().optional()
 });
 
 class Users {
diff --git a/server/routes/schema-info.js b/server/routes/schema-info.js
index c3600f53..0f746f18 100644
--- a/server/routes/schema-info.js
+++ b/server/routes/schema-info.js
@@ -7,7 +7,7 @@ router.get(
   '/api/schema-info/:connectionId',
   mustHaveConnectionAccess,
   async function(req, res) {
-    const { models } = req;
+    const { models, user } = req;
     const { connectionId } = req.params;
     const reload = req.query.reload === 'true';
 
@@ -24,7 +24,7 @@ router.get(
         return res.json({ schemaInfo });
       }
 
-      schemaInfo = await driver.getSchema(conn);
+      schemaInfo = await driver.getSchema(conn, user);
       if (Object.keys(schemaInfo).length) {
         await models.schemaInfo.saveSchemaInfo(connectionId, schemaInfo);
       }


Test patch:
diff --git a/server/routes/test-connection.js b/server/routes/test-connection.js
index 5db94473..cf5e6cb7 100644
--- a/server/routes/test-connection.js
+++ b/server/routes/test-connection.js
@@ -7,9 +7,8 @@ const sendError = require('../lib/sendError');
  * A non-error response is considered a success or valid connection config
  */
 router.post('/api/test-connection', mustBeAdmin, async function(req, res) {
-  const { body } = req;
   try {
-    await testConnection(body);
+    await testConnection(req.body, req.user);
     res.send({ success: true });
   } catch (error) {
     sendError(res, error);
diff --git a/server/test/drivers.js b/server/test/drivers.js
index ba0ea105..546d122b 100644
--- a/server/test/drivers.js
+++ b/server/test/drivers.js
@@ -71,4 +71,33 @@ describe('drivers', function() {
       });
     }, 'boolean not convertable throws error');
   });
+
+  it('renders connection with user', function() {
+    const secret = '123<>!@#$%^&*()-_+=';
+    const user = {
+      data: {
+        secret
+      }
+    };
+    const connection = {
+      connectionString: '{{user.data.secret}}',
+      singleStache: '{single}',
+      port: 6543
+    };
+    const rendered = drivers.renderConnection(connection, user);
+    assert.strictEqual(rendered.connectionString, secret);
+    assert.strictEqual(rendered.singleStache, '{single}', 'single left alone');
+    assert.strictEqual(rendered.port, 6543, 'number left alone');
+  });
+
+  it('renders connection without user', function() {
+    const connection = {
+      connectionString: 'test',
+      singleStache: '{single}',
+      port: 6543
+    };
+    const rendered = drivers.renderConnection(connection);
+    assert.strictEqual(rendered.connectionString, 'test');
+    assert.strictEqual(rendered.singleStache, '{single}');
+  });
 });