Repository: rickbergfalk/sqlpad
Pull request #555 (2020-02-28 00:07:38)

Add Connection and Query Seed Data support
Adds functionality to load predefined connections and queries at server start via JSON files.

JSON files must be arranged in a specific directory structure located within directory specified by config variable `seedDataPath`. 

This is primarily being added to support a use case for bootstrapping a SQLPad environment with canned queries available to users immediately. Ideally I'd like to extend this approach to other objects as well, hence supporting connections to be defined this way as well.

**Something I'm not set on yet** is how to handle updates and cases where seed data is later removed from the file system.

The implementation in this PR just always upserts the data, and doesn't flag it in any way that prevents it from being treated differently from other data. That is, this data can be altered via the UI if desired, and those edits would be overwritten next server restart. 

If a seed data JSON file is removed from the file system, the previously loaded seed data entries are left behind.

I'm kind of leaning towards making seed data read only, and giving it a special designation so that it can be removed on future restarts if the seed JSON file is removed.

# Addition to README

## Seed Data (experimental)

**This feature may continue to change before being finalized. Use at your own risk**

Data may be seeded to a SQLPad instance via JSON files on the file system. At this time only query and connection data may be seeded.

Seed data is loaded into SQLPad at server start, with the data being created or updated based on matching an attribute. Previously loaded seed data will be updated each server start, so plan accordingly.

If data is seeded and then removed from the seed data directory, it will not be removed from a SQLPad instance.

This mechanism is intended to be used for "system" data that is not intended to be modified by end users. For example, a "system" user can be created along with a set of canned queries, set to be shared with everyone.

To seed data, specify the directory to load data via config setting `seedDataPath`. This directory should contain directories for each item type to be loaded, each containing `.json` files for each item to be loaded.

Example structure:

```
/path/to/seed-data
  /connections
    connection-1.json
  /queries
    some-query.json
    another-query.json
```

### Seed Connections

Connections may be seeded to SQLPad as an alternative to defining connections via configuration. For fields supported refer to documentation on connection configuration via config file or environment variable. Seed connections differ in that the connection ID is provided by the `id` field.

```json
{
  "id": "seed-connection-1",
  "name": "postgres seed connection",
  "driver": "postgres",
  "host": "pghost",
  "database": "pgdatabase",
  "username": "pgusername",
  "password": "pguserpassword"
}
```

### Seed Queries

Queries are created or replaced matching on query id. At this time the query ACL implementation controls whether queries may be updated within SQLPad. It is entirely possible for these to be loaded, altered in the UI, then have those changes lost on next server start.

At this point SQLPad does not enforce referential integrity, so queries may be created with a `createdBy` containing an email address for a user that does not exist.

```json
{
  "id": "seed-query-1",
  "name": "Seed query 1",
  "connectionId": "seed-connection-1",
  "queryText": "SELECT * FROM seed_table",
  "createdBy": "admin@sqlpad.com",
  "acl": [
    {
      "userId": "__EVERYONE__",
      "write": true
    }
  ]
}
```


Patch:
diff --git a/README.md b/README.md
index 67b989de..824f20cb 100644
--- a/README.md
+++ b/README.md
@@ -227,6 +227,10 @@ Minimum level for web logs. Should be one of 'fatal', 'error', 'warn', 'info', '
 Env var: `SQLPAD_WEB_LOG_LEVEL`  
 Default: `info`
 
+**seedDataPath**  
+Path to root of seed data directories. See Seed Data documentation.  
+Env var: `SQLPAD_SEED_DATA_PATH`
+
 ### Connection configuration
 
 As of 3.2.0 connections may be defined via application configuration.
@@ -481,6 +485,73 @@ When using JSON file, provide `<connectionId>` as a key under `connections`.
   </tbody>
 </table>
 
+## Seed Data (experimental)
+
+**This feature may continue to change before being finalized. Use at your own risk**
+
+Data may be seeded to a SQLPad instance via JSON files on the file system. At this time only query and connection data may be seeded.
+
+Seed data is loaded into SQLPad at server start, with the data being created or updated based on matching an attribute. Previously loaded seed data will be updated each server start, so plan accordingly.
+
+If data is seeded and then removed from the seed data directory, it will not be removed from a SQLPad instance.
+
+This mechanism is intended to be used for "system" data that is not intended to be modified by end users. For example, a "system" user can be created along with a set of canned queries, set to be shared with everyone.
+
+To seed data, specify the directory to load data via config setting `seedDataPath`. This directory should contain directories for each item type to be loaded, each containing `.json` files for each item to be loaded.
+
+Example structure:
+
+```
+/path/to/seed-data
+  /connections
+    connection-1.json
+  /queries
+    some-query.json
+    another-query.json
+```
+
+### Seed Connections
+
+Connections may be seeded to SQLPad as an alternative to defining connections via configuration. For fields supported refer to documentation on connection configuration via config file or environment variable. Seed connections differ in that the connection ID is provided by the `id` field.
+
+Example seed connection JSON file:
+
+```json
+{
+  "id": "seed-connection-1",
+  "name": "postgres seed connection",
+  "driver": "postgres",
+  "host": "pghost",
+  "database": "pgdatabase",
+  "username": "pgusername",
+  "password": "pguserpassword"
+}
+```
+
+### Seed Queries
+
+Queries are created or replaced matching on query id. At this time the query ACL implementation controls whether queries may be updated within SQLPad. It is entirely possible for these to be loaded, altered in the UI, then have those changes lost on next server start.
+
+At this point SQLPad does not enforce referential integrity, so queries may be created with a `createdBy` containing an email address for a user that does not exist.
+
+Example seed query JSON file:
+
+```json
+{
+  "id": "seed-query-1",
+  "name": "Seed query 1",
+  "connectionId": "seed-connection-1",
+  "queryText": "SELECT * FROM seed_table",
+  "createdBy": "admin@sqlpad.com",
+  "acl": [
+    {
+      "userId": "__EVERYONE__",
+      "write": true
+    }
+  ]
+}
+```
+
 ## Logging
 
 SQLPad logs json messages using [pino](https://github.com/pinojs/pino), a simple and fast logging library. Log messages are sent to stdout, leaving how to handle the messages up to you. The pino ecosystem supports a variety of transports http://getpino.io/#/docs/transports that should cover most logging setups.
diff --git a/config-example.ini b/config-example.ini
index 9af7b1b6..a1328d5a 100644
--- a/config-example.ini
+++ b/config-example.ini
@@ -117,3 +117,6 @@ appLogLevel="info"
 
 ; Minimum level for web logs. Should be one of 'fatal', 'error', 'warn', 'info', 'debug', 'trace' or 'silent'.
 webLogLevel="info"
+
+; Path to root of seed data directories
+seedDataPath=""
\ No newline at end of file
diff --git a/config-example.json b/config-example.json
index 044b8f55..04e53b8a 100644
--- a/config-example.json
+++ b/config-example.json
@@ -38,5 +38,6 @@
   "timeoutSeconds": 300,
   "whitelistedDomains": "",
   "appLogLevel": "info",
-  "webLogLevel": "info"
+  "webLogLevel": "info",
+  "seedDataPath": ""
 }
diff --git a/server/lib/config/configItems.js b/server/lib/config/configItems.js
index 8a6787a0..feb671cf 100644
--- a/server/lib/config/configItems.js
+++ b/server/lib/config/configItems.js
@@ -223,6 +223,11 @@ const configItems = [
     key: 'dbInMemory',
     envVar: 'SQLPAD_DB_IN_MEMORY',
     default: false
+  },
+  {
+    key: 'seedDataPath',
+    envVar: 'SQLPAD_SEED_DATA_PATH',
+    default: ''
   }
 ];
 
diff --git a/server/lib/loadSeedData.js b/server/lib/loadSeedData.js
new file mode 100644
index 00000000..7cc36088
--- /dev/null
+++ b/server/lib/loadSeedData.js
@@ -0,0 +1,76 @@
+/* eslint-disable no-await-in-loop */
+const fs = require('fs');
+const path = require('path');
+
+function dirExists(path) {
+  try {
+    fs.accessSync(path);
+    return true;
+  } catch (error) {
+    return false;
+  }
+}
+
+async function getItemDirectoryData(appLog, seedDataPath, dir) {
+  const items = [];
+
+  const dirPath = path.join(seedDataPath, dir);
+  if (dirExists(dirPath)) {
+    const fileNames = fs.readdirSync(dirPath);
+    const jsonFileNames = fileNames.filter(
+      name => path.extname(name.toLowerCase()) === '.json'
+    );
+    for (const jsonFileName of jsonFileNames) {
+      const filePath = path.join(dirPath, jsonFileName);
+      const data = fs.readFileSync(filePath, 'utf8');
+      try {
+        const parsed = JSON.parse(data);
+        if (parsed.id) {
+          parsed._id = parsed.id;
+          delete parsed.id;
+        }
+        items.push(parsed);
+      } catch (error) {
+        appLog.error(error, 'Error reading and parsing seed data %s', filePath);
+      }
+    }
+  }
+
+  return items;
+}
+
+/**
+ * @param {*} appLog
+ * @param {*} config
+ * @param {import('../models')} models
+ */
+async function loadSeedData(appLog, config, models) {
+  const seedDataPath = config.get('seedDataPath');
+
+  const resolved = path.resolve(seedDataPath);
+  appLog.info('Loading seed data from %s', resolved);
+
+  if (!seedDataPath || !dirExists(seedDataPath)) {
+    return;
+  }
+
+  const queries = await getItemDirectoryData(appLog, seedDataPath, 'queries');
+  for (const query of queries) {
+    const existing = await models.queries.findOneById(query._id);
+    const data = { ...existing, ...query };
+    await models.upsertQuery(data);
+  }
+
+  const connections = await getItemDirectoryData(
+    appLog,
+    seedDataPath,
+    'connections'
+  );
+  for (const connection of connections) {
+    const existing = await models.connections.findOneById(connection.id);
+    const data = { ...existing, ...connection };
+    await models.connections.save(data);
+  }
+}
+
+module.exports = loadSeedData;
diff --git a/server/lib/pushQueryToSlack.js b/server/lib/pushQueryToSlack.js
index 4a454140..5708ab6a 100644
--- a/server/lib/pushQueryToSlack.js
+++ b/server/lib/pushQueryToSlack.js
@@ -13,7 +13,7 @@ function pushQueryToSlack(config, query) {
         text: `New Query <${PUBLIC_URL}${BASE_URL}/queries/${query._id}|${
           query.name
         }> 
-saved by ${query.modifiedBy} on SQLPad 
+saved by ${query.modifiedBy || query.createdBy} on SQLPad 
 ${'```sql'}
 ${query.queryText}
 ${'```'}`
diff --git a/server/models/connections.js b/server/models/connections.js
index ca6dd9ad..182fb2c4 100644
--- a/server/models/connections.js
+++ b/server/models/connections.js
@@ -59,6 +59,7 @@ class Connections {
     return this.nedb.connections.remove({ _id: id });
   }
 
+  // TODO - break save function out into create/update
   async save(connection) {
     if (!connection) {
       throw new Error('connections.save() requires a connection');
@@ -75,10 +76,12 @@ class Connections {
     connection = drivers.validateConnection(connection);
     const { _id } = connection;
 
-    if (_id) {
+    const existing = await this.findOneById(_id);
+    if (existing) {
       await this.nedb.connections.update({ _id }, connection, {});
       return this.findOneById(_id);
     }
+
     const newDoc = await this.nedb.connections.insert(connection);
     return this.findOneById(newDoc._id);
   }
diff --git a/server/models/index.js b/server/models/index.js
index 4f4418de..8cd49ebb 100644
--- a/server/models/index.js
+++ b/server/models/index.js
@@ -87,6 +87,35 @@ class Models {
 
     return query;
   }
+
+  /**
+   * Create or Update query
+   * Remove existing query acl entries and add new ones if they should be added
+   * TODO should probably validate userIds are valid
+   * TODO should email be allowed here and be translated to userIds?
+   * TODO add transaction support here once all models are in SQLite (this is risky otherwise)
+   * @param {*} data
+   */
+  async upsertQuery(data) {
+    const { acl, ...query } = data;
+    const newOrUpdatedQuery = await this.queries.save(query);
+    const queryId = newOrUpdatedQuery._id;
+
+    await this.queryAcl.removeByQueryId(queryId);
+
+    if (acl && acl.length) {
+      const aclRows = acl.map(row => {
+        return {
+          queryId,
+          userId: row.userId,
+          write: row.write
+        };
+      });
+      await this.queryAcl.bulkCreate(aclRows);
+    }
+
+    return this.findQueryById(queryId);
+  }
 }
 
 module.exports = Models;
diff --git a/server/models/queries.js b/server/models/queries.js
index 06c2d648..236bd95b 100644
--- a/server/models/queries.js
+++ b/server/models/queries.js
@@ -43,7 +43,7 @@ const schema = Joi.object({
   createdBy: Joi.string().required(),
   // modifiedBy is EMAIL of user
   // TODO change userId once data moved to sqlite
-  modifiedBy: Joi.string().required(),
+  modifiedBy: Joi.string().optional(),
   lastAccessDate: Joi.date().default(Date.now)
 });
 
diff --git a/server/routes/queries.js b/server/routes/queries.js
index 208832f1..30e860e4 100644
--- a/server/routes/queries.js
+++ b/server/routes/queries.js
@@ -106,30 +106,6 @@ async function getQuery(req, res) {
 
 router.get('/api/queries/:_id', mustBeAuthenticatedOrChartLink, getQuery);
 
-/**
- * Remove existing query acl entries and add new ones if they should be added
- * TODO should probably validate userIds are valid
- * TODO should email be allowed here and be translated to userIds?
- * TODO add transaction support here once all models are in SQLite (this is risky otherwise)
- * @param {import('../models')} models
- * @param {string} queryId
- * @param {array<object>} acl
- */
-async function updateQueryAcl(models, queryId, acl) {
-  await models.queryAcl.removeByQueryId(queryId);
-
-  if (acl && acl.length) {
-    const aclRows = acl.map(row => {
-      return {
-        queryId,
-        userId: row.userId,
-        write: row.write
-      };
-    });
-    await models.queryAcl.bulkCreate(aclRows);
-  }
-}
-
 /**
  * @param {import('express').Request & Req} req
  * @param {*} res
@@ -146,21 +122,18 @@ async function createQuery(req, res) {
     queryText,
     chartConfiguration,
     createdBy: email,
-    modifiedBy: email
+    modifiedBy: email,
+    acl
   };
 
   try {
-    const newQuery = await models.queries.save(query);
+    const newQuery = await models.upsertQuery(query);
+
     // This is async, but save operation doesn't care about when/if finished
     pushQueryToSlack(req.config, newQuery);
 
-    await updateQueryAcl(models, newQuery._id, acl);
-
-    // Get the full query object for response
-    const queryWithAcl = await models.findQueryById(newQuery._id);
-
     return res.json({
-      query: queryWithAcl
+      query: newQuery
     });
   } catch (error) {
     sendError(res, error, 'Problem saving query');
@@ -190,6 +163,7 @@ async function updateQuery(req, res) {
     const isCreator = query.createdBy === user.email;
     const isAdmin = user.role === 'admin';
     const hasPermission = hasAclWrite || isCreator || isAdmin;
+
     if (!hasPermission) {
       // TODO send 403 forbidden
       return sendError(res, null, 'Access to query not permitted');
@@ -211,17 +185,13 @@ async function updateQuery(req, res) {
       connectionId,
       queryText,
       chartConfiguration,
-      modifiedBy: email
+      modifiedBy: email,
+      acl
     });
 
-    const newQuery = await models.queries.save(query);
-
-    await updateQueryAcl(models, newQuery._id, acl);
-
-    // Get the full query object for response
-    const queryWithAcl = await models.findQueryById(newQuery._id);
+    const updatedQuery = await models.upsertQuery(query);
 
-    return res.json({ query: queryWithAcl });
+    return res.json({ query: updatedQuery });
   } catch (error) {
     sendError(res, error, 'Problem saving query');
   }
diff --git a/server/server.js b/server/server.js
index 5bb65239..3b43efae 100755
--- a/server/server.js
+++ b/server/server.js
@@ -10,6 +10,7 @@ const appLog = require('./lib/appLog');
 const Config = require('./lib/config');
 const { makeDb, getDb } = require('./lib/db');
 const migrate = require('./lib/migrate');
+const loadSeedData = require('./lib/loadSeedData');
 
 // Parse command line flags to see if anything special needs to happen
 require('./lib/cli-flow.js');
@@ -81,7 +82,13 @@ let server;
 async function startServer() {
   const { models, nedb, sequelizeDb } = await getDb();
 
+  // Before application starts up migrate data models
   await migrate(config, appLog, nedb, sequelizeDb.sequelize);
+
+  // Load seed data after migrations
+  await loadSeedData(appLog, config, models);
+
+  // Create expressjs app
   const app = makeApp(config, models);
 
   // determine if key pair exists for certs


Test patch:
diff --git a/server/test/fixtures/seedData/connections/seed-connection-1.json b/server/test/fixtures/seedData/connections/seed-connection-1.json
new file mode 100644
index 00000000..579b4890
--- /dev/null
+++ b/server/test/fixtures/seedData/connections/seed-connection-1.json
@@ -0,0 +1,9 @@
+{
+  "id": "seed-connection-1",
+  "name": "postgres seed connection",
+  "driver": "postgres",
+  "host": "pghost",
+  "database": "pgdatabase",
+  "username": "pgusername",
+  "password": "pguserpassword"
+}
diff --git a/server/test/fixtures/seedData/queries/nested_to_ignore/dummy.txt b/server/test/fixtures/seedData/queries/nested_to_ignore/dummy.txt
new file mode 100644
index 00000000..4f5368e7
--- /dev/null
+++ b/server/test/fixtures/seedData/queries/nested_to_ignore/dummy.txt
@@ -0,0 +1,1 @@
+This is just a dummy file to ensure non-json files are ignored
\ No newline at end of file
diff --git a/server/test/fixtures/seedData/queries/seed-query-1.json b/server/test/fixtures/seedData/queries/seed-query-1.json
new file mode 100644
index 00000000..731fb557
--- /dev/null
+++ b/server/test/fixtures/seedData/queries/seed-query-1.json
@@ -0,0 +1,13 @@
+{
+  "id": "seed-query-1",
+  "name": "Seed query 1",
+  "connectionId": "seed-connection-1",
+  "queryText": "SELECT * FROM seed_table",
+  "createdBy": "admin@sqlpad.com",
+  "acl": [
+    {
+      "userId": "__EVERYONE__",
+      "write": true
+    }
+  ]
+}
diff --git a/server/test/fixtures/seedData/queries/seed-query-2.json b/server/test/fixtures/seedData/queries/seed-query-2.json
new file mode 100644
index 00000000..8e653e24
--- /dev/null
+++ b/server/test/fixtures/seedData/queries/seed-query-2.json
@@ -0,0 +1,8 @@
+{
+  "id": "seed-query-2",
+  "name": "Seed query 2",
+  "connectionId": "seed-connection-1",
+  "queryText": "SELECT * FROM seed_table",
+  "chartConfiguration": {},
+  "createdBy": "admin@sqlpad.com"
+}
diff --git a/server/test/fixtures/seedDataBadFile/queries/seed-query-1.json b/server/test/fixtures/seedDataBadFile/queries/seed-query-1.json
new file mode 100644
index 00000000..02b125c4
--- /dev/null
+++ b/server/test/fixtures/seedDataBadFile/queries/seed-query-1.json
@@ -0,0 +1,3 @@
+{
+  "bad_data": "This should throw"
+}
diff --git a/server/test/fixtures/seedDataEmptyChild/queries/dummy.txt b/server/test/fixtures/seedDataEmptyChild/queries/dummy.txt
new file mode 100644
index 00000000..4f5368e7
--- /dev/null
+++ b/server/test/fixtures/seedDataEmptyChild/queries/dummy.txt
@@ -0,0 +1,1 @@
+This is just a dummy file to ensure non-json files are ignored
\ No newline at end of file
diff --git a/server/test/lib/seedDataPath.js b/server/test/lib/seedDataPath.js
new file mode 100644
index 00000000..663de415
--- /dev/null
+++ b/server/test/lib/seedDataPath.js
@@ -0,0 +1,44 @@
+const assert = require('assert');
+const TestUtils = require('../utils');
+
+describe('seedDataPath', function() {
+  it('Loads files as expected', async function() {
+    const utils = new TestUtils({
+      seedDataPath: './test/fixtures/seedData'
+    });
+    await utils.init();
+    const queries = await utils.models.queries.findAll();
+    assert.strictEqual(queries.length, 2);
+    assert(queries.find(q => q._id === 'seed-query-1'));
+    const connections = await utils.models.connections.findAll();
+    assert.equal(connections.length, 1);
+    assert.strictEqual(connections[0]._id, 'seed-connection-1');
+  });
+
+  it('Handles child directories with no valid files', async function() {
+    const utils = new TestUtils({
+      seedDataPath: './test/fixtures/seedDataEmptyChild'
+    });
+    await utils.init();
+    const queries = await utils.models.queries.findAll();
+    assert.strictEqual(queries.length, 0);
+  });
+
+  it('Handles directory that does not exist', async function() {
+    const utils = new TestUtils({
+      seedDataPath: './test/fixtures/doesnotexist'
+    });
+    await utils.init();
+    const queries = await utils.models.queries.findAll();
+    assert.strictEqual(queries.length, 0);
+  });
+
+  it('throws for invalid data', async function() {
+    const utils = new TestUtils({
+      seedDataPath: './test/fixtures/seedDataBadFile'
+    });
+    await assert.rejects(() => utils.init());
+    const queries = await utils.models.queries.findAll();
+    assert.strictEqual(queries.length, 0);
+  });
+});
diff --git a/server/test/migrations/QueryAcl.js b/server/test/migrations/QueryAcl.js
index 18addfbd..eb7cf9b3 100644
--- a/server/test/migrations/QueryAcl.js
+++ b/server/test/migrations/QueryAcl.js
@@ -15,7 +15,6 @@ describe('QueryAcl', function() {
       createdDate: new Date(),
       updatedDate: new Date(),
       createdBy: 'testuser',
-      modifiedBy: 'testuser',
       lastAccessDate: new Date()
     });
 
@@ -27,7 +26,6 @@ describe('QueryAcl', function() {
       createdDate: new Date(),
       updatedDate: new Date(),
       createdBy: 'testuser',
-      modifiedBy: 'testuser',
       lastAccessDate: new Date()
     });
 
diff --git a/server/test/utils.js b/server/test/utils.js
index 04199712..f84fe5d7 100644
--- a/server/test/utils.js
+++ b/server/test/utils.js
@@ -6,6 +6,7 @@ const appLog = require('../lib/appLog');
 const db = require('../lib/db');
 const makeApp = require('../app');
 const migrate = require('../lib/migrate');
+const loadSeedData = require('../lib/loadSeedData');
 
 class TestUtils {
   constructor(args = {}, env = {}) {
@@ -74,9 +75,14 @@ class TestUtils {
     );
   }
 
+  async loadSeedData() {
+    await loadSeedData(this.appLog, this.config, this.models);
+  }
+
   async init(withUsers) {
     await this.initDbs();
     await this.migrate();
+    await this.loadSeedData();
 
     this.app = makeApp(this.config, this.models);