Repository: rickbergfalk/sqlpad
Pull request #838 (2020-09-19 18:36:40)

Add connection schema API
Adds connection schema API, a successor to the `schema-info` API. `schema-info` will be left around for quite some time for backwards compatibility reasons. The UI will transition to the new API in a future PR. 

From docs in this PR:

# Connection Schema API

## Overview

The connection schema API returnes the database schema (schemas, tables, and columns) for the connection specified.

## Get Connection Schema

?> Available in version 5.7.0

The connection schema API returnes the database schema (schemas, tables, and columns) for the connection specified.

By default this API returns cached schema response. A fresh schema may be calculated by sending query string `?reload=true`.

### Example

`GET /api/connections/<connectionId>/schema`

`GET /api/connections/<connectionId>/schema?reload=true`

### Parameters

- `reload`: string set to `true` to force schema refresh

### Response

```json
{
  "schemas": [
    {
      "name": "schema_1_name",
      "description": "",
      "tables": [
        {
          "name": "table_1_name",
          "description": "",
          "columns": [
            {
              "name": "column_1_name",
              "description": "",
              "dataType": "INT"
            },
            {
              "name": "column_2_name",
              "description": "",
              "dataType": "TEXT"
            }
          ]
        }
      ]
    }
  ]
}
```

Some databases do not support a schema concept. In those cases, the top-level field may be `tables`:

```json
{
  "tables": [
    {
      "name": "table_1_name",
      "description": "",
      "columns": [
        {
          "name": "column_1_name",
          "description": "",
          "dataType": "INT"
        },
        {
          "name": "column_2_name",
          "description": "",
          "dataType": "TEXT"
        }
      ]
    }
  ]
}
```

## Get Schema Info (Deprecated)

!> Deprecated

The original schema-info API may be used to get schema details in an object tree format. This API is deprecated, and will be removed at some future release.

The schema result is cached by default. A fresh schema may be forced by providing query parameter `?reload=true`.

### Example

`GET /api/schema-info/<connectionId>`

`GET /api/schema-info/<connectionId>?reload=true`

### Parameters

- `reload`: string set to `true` to force schema refresh

### Response

```json
{
  "<actualSchemaName>": {
    "<actualTableName>": [
      {
        "column_name": "column_1_name",
        "column_description": "",
        "data_type": "INT"
      },
      {
        "column_name": "column_2_name",
        "column_description": "",
        "data_type": "TEXT"
      }
    ]
  }
}
```





Patch:
diff --git a/docs/_sidebar.md b/docs/_sidebar.md
index 5a7ebef2..fe172a5c 100644
--- a/docs/_sidebar.md
+++ b/docs/_sidebar.md
@@ -10,3 +10,4 @@
 - API
   - [Overview](/api-overview.md)
   - [Batches & Statements](/api-batches.md)
+  - [Connection Schema](/api-connection-schema.md)
diff --git a/docs/api-connection-schema.md b/docs/api-connection-schema.md
new file mode 100644
index 00000000..6dcb126b
--- /dev/null
+++ b/docs/api-connection-schema.md
@@ -0,0 +1,122 @@
+# Connection Schema API
+
+## Overview
+
+The connection schema API returnes the database schema (schemas, tables, and columns) for the connection specified.
+
+## Get Connection Schema
+
+?> Available in version 5.7.0
+
+The connection schema API returnes the database schema (schemas, tables, and columns) for the connection specified.
+
+By default this API returns cached schema response. A fresh schema may be calculated by sending query string `?reload=true`.
+
+### Example
+
+`GET /api/connections/<connectionId>/schema`
+
+`GET /api/connections/<connectionId>/schema?reload=true`
+
+### Parameters
+
+- `reload`: string set to `true` to force schema refresh
+
+### Response
+
+_Note: Column `dataType` will contain value as sent back from database driver._
+
+```json
+{
+  "schemas": [
+    {
+      "name": "schema_1_name",
+      "description": "",
+      "tables": [
+        {
+          "name": "table_1_name",
+          "description": "",
+          "columns": [
+            {
+              "name": "column_1_name",
+              "description": "",
+              "dataType": "INT"
+            },
+            {
+              "name": "column_2_name",
+              "description": "",
+              "dataType": "TEXT"
+            }
+          ]
+        }
+      ]
+    }
+  ]
+}
+```
+
+Some databases do not support a schema concept. In those cases, the top-level field may be `tables`:
+
+```json
+{
+  "tables": [
+    {
+      "name": "table_1_name",
+      "description": "",
+      "columns": [
+        {
+          "name": "column_1_name",
+          "description": "",
+          "dataType": "INT"
+        },
+        {
+          "name": "column_2_name",
+          "description": "",
+          "dataType": "TEXT"
+        }
+      ]
+    }
+  ]
+}
+```
+
+## Get Schema Info (Deprecated)
+
+!> Deprecated
+
+The original schema-info API may be used to get schema details in an object tree format. This API is deprecated, and will be removed at some future release.
+
+The schema result is cached by default. A fresh schema may be forced by providing query parameter `?reload=true`.
+
+### Example
+
+`GET /api/schema-info/<connectionId>`
+
+`GET /api/schema-info/<connectionId>?reload=true`
+
+### Parameters
+
+- `reload`: string set to `true` to force schema refresh
+
+### Response
+
+_Note: Column `data_type` will contain value as sent back from database driver._
+
+```json
+{
+  "<actualSchemaName>": {
+    "<actualTableName>": [
+      {
+        "column_name": "column_1_name",
+        "column_description": "",
+        "data_type": "INT"
+      },
+      {
+        "column_name": "column_2_name",
+        "column_description": "",
+        "data_type": "TEXT"
+      }
+    ]
+  }
+}
+```
diff --git a/server/app.js b/server/app.js
index 5fe3c6f3..9c56455f 100644
--- a/server/app.js
+++ b/server/app.js
@@ -194,6 +194,7 @@ async function makeApp(config, models) {
     require('./routes/connections.js'),
     require('./routes/connection-accesses.js'),
     require('./routes/connection-clients.js'),
+    require('./routes/connection-schema.js'),
     require('./routes/test-connection.js'),
     require('./routes/query-history.js'),
     require('./routes/schema-info.js'),
diff --git a/server/drivers/utils.js b/server/drivers/utils.js
index e4e5894a..edd32960 100644
--- a/server/drivers/utils.js
+++ b/server/drivers/utils.js
@@ -1,9 +1,65 @@
 const _ = require('lodash');
 
+/**
+ * Get an array of table objects from column rows for given schema
+ * @param {Object[]} schemaTableColumnRows - rows of column-level data for tables of a given schema
+ * @param {string} schemaTableColumnRows[].table_name - field containing table_name
+ * @param {string} schemaTableColumnRows[].column_name - field containing column name
+ * @param {string} schemaTableColumnRows[].data_type - field containing data type of column
+ * @param {string} schemaTableColumnRows[].column_description - field containing description of column
+ */
+function getTables(schemaTableColumnRows) {
+  const tables = [];
+  const byTableName = _.groupBy(schemaTableColumnRows, 'table_name');
+  for (const tableName in byTableName) {
+    if (byTableName.hasOwnProperty(tableName)) {
+      const tableObj = {
+        name: tableName,
+        // TODO populate table description?
+        description: '',
+        columns: byTableName[tableName].map((row) => {
+          return {
+            name: row.column_name,
+            description: row.column_description,
+            dataType: row.data_type,
+          };
+        }),
+      };
+      tables.push(tableObj);
+    }
+  }
+  return tables;
+}
+
 /**
  * Formats schema query results into
  * a nested map of objects representing schema tree
+ *
+ * Returned format is
+ * {
+ *   schemas: [
+ *     {
+ *       name: '',
+ *       description: '',
+ *       tables: [
+ *         {
+ *           name: '',
+ *           description: '',
+ *           columns: [
+ *             {
+ *               name: '',
+ *               description: '',
+ *               dataType: ''
+ *             }
+ *           ]
+ *         }
+ *       ]
+ *     }
+ *   ]
+ * }
+ *
  * @param {object} queryResult
+ * @param {array} [queryResult.rows]
  */
 function formatSchemaQueryResults(queryResult) {
   if (!queryResult || !queryResult.rows || !queryResult.rows.length) {
@@ -21,34 +77,30 @@ function formatSchemaQueryResults(queryResult) {
     return cleanRow;
   });
 
-  const tree = {};
-  const bySchema = _.groupBy(rows, 'table_schema');
-  for (const schema in bySchema) {
-    if (bySchema.hasOwnProperty(schema)) {
-      tree[schema] = {};
-      const byTableName = _.groupBy(bySchema[schema], 'table_name');
-      for (const tableName in byTableName) {
-        if (byTableName.hasOwnProperty(tableName)) {
-          tree[schema][tableName] = byTableName[tableName];
-        }
+  const hasSchema = rows[0].hasOwnProperty('table_schema');
+
+  if (hasSchema) {
+    const tree = {
+      schemas: [],
+    };
+    const bySchema = _.groupBy(rows, 'table_schema');
+    for (const schema in bySchema) {
+      if (bySchema.hasOwnProperty(schema)) {
+        const schemaObj = {
+          name: schema,
+          // TODO populate schema description?
+          description: '',
+          tables: getTables(bySchema[schema]),
+        };
+        tree.schemas.push(schemaObj);
       }
     }
+    return tree;
   }
-  /*
-  At this point, tree should look like this:
-    {
-      "schema-name": {
-        "table-name": [
-          {
-            column_name: "the column name",
-            data_type: "string"
-            column_description: "an optional description"
-          }
-        ]
-      }
-    }
-  */
-  return tree;
+
+  return {
+    tables: getTables(rows),
+  };
 }
 
 module.exports = {
diff --git a/server/lib/connection-client.js b/server/lib/connection-client.js
index ce82fd78..b4f97efc 100644
--- a/server/lib/connection-client.js
+++ b/server/lib/connection-client.js
@@ -295,7 +295,11 @@ class ConnectionClient {
    * so we don't want to cache this on (connectionId, userId) pairing.
    * Instead we'll use a hash of connection after template rendering
    */
-  getSchemaCacheId() {
+  getSchemaCacheId(formatVersion) {
+    const identifier = formatVersion
+      ? `schemacache${formatVersion}:`
+      : 'schemacache:';
+
     const keyValuesString = Object.keys(this.connection)
       .sort()
       .map((key) => {
@@ -304,7 +308,7 @@ class ConnectionClient {
       .join('::');
 
     return (
-      'schemacache:' + uuidv5(keyValuesString, consts.CONNECTION_HASH_NAMESPACE)
+      identifier + uuidv5(keyValuesString, consts.CONNECTION_HASH_NAMESPACE)
     );
   }
 }
diff --git a/server/routes/connection-schema.js b/server/routes/connection-schema.js
new file mode 100644
index 00000000..f850f76a
--- /dev/null
+++ b/server/routes/connection-schema.js
@@ -0,0 +1,51 @@
+require('../typedefs');
+const router = require('express').Router();
+const mustHaveConnectionAccess = require('../middleware/must-have-connection-access.js');
+const ConnectionClient = require('../lib/connection-client');
+const wrap = require('../lib/wrap');
+
+/**
+ * @param {Req} req
+ * @param {Res} res
+ */
+async function getConnectionSchema(req, res) {
+  const { models, user } = req;
+  const { connectionId } = req.params;
+  const reload = req.query.reload === 'true';
+
+  const conn = await models.connections.findOneById(connectionId);
+
+  if (!conn) {
+    return res.utils.notFound();
+  }
+
+  const connectionClient = new ConnectionClient(conn, user);
+  const schemaCacheId = connectionClient.getSchemaCacheId(2);
+
+  let schemaInfo = await models.schemaInfo.getSchemaInfo(schemaCacheId);
+
+  if (schemaInfo && !reload) {
+    return res.utils.data(schemaInfo);
+  }
+
+  try {
+    schemaInfo = await connectionClient.getSchema();
+  } catch (error) {
+    // Assumption is that error is due to user configuration
+    // letting it bubble up results in 500, but it should be 400
+    return res.utils.error(error);
+  }
+
+  if (Object.keys(schemaInfo).length) {
+    await models.schemaInfo.saveSchemaInfo(schemaCacheId, schemaInfo);
+  }
+  return res.utils.data(schemaInfo);
+}
+
+router.get(
+  '/api/connections/:connectionId/schema',
+  mustHaveConnectionAccess,
+  wrap(getConnectionSchema)
+);
+
+module.exports = router;
diff --git a/server/routes/schema-info.js b/server/routes/schema-info.js
index 20cea6f9..03d10d83 100644
--- a/server/routes/schema-info.js
+++ b/server/routes/schema-info.js
@@ -36,10 +36,49 @@ async function getSchemaInfo(req, res) {
     return res.utils.error(error);
   }
 
-  if (Object.keys(schemaInfo).length) {
-    await models.schemaInfo.saveSchemaInfo(schemaCacheId, schemaInfo);
+  // schemaInfo format returned from connectionClient changed since this route was originally implemented
+  // It needs to change from this:
+  // { schemas: [{ name, description, tables: [{ name, description, columns: [{ name, description, dataType }] }] }] }
+  // to the following:
+  /*
+    {
+      "schema-name": {
+        "table-name": [
+          {
+            column_name: "the column name",
+            data_type: "string"
+            column_description: "an optional description"
+          }
+        ]
+      }
+    }
+  */
+
+  const oldFormat = {};
+  if (schemaInfo.schemas) {
+    schemaInfo.schemas.forEach((schema) => {
+      oldFormat[schema.name] = {};
+      if (schema.tables) {
+        schema.tables.forEach((table) => {
+          oldFormat[schema.name][table.name] = [];
+          if (table.columns) {
+            table.columns.forEach((column) => {
+              oldFormat[schema.name][table.name].push({
+                column_name: column.name,
+                data_type: column.dataType,
+                column_description: column.description,
+              });
+            });
+          }
+        });
+      }
+    });
+  }
+
+  if (Object.keys(oldFormat).length) {
+    await models.schemaInfo.saveSchemaInfo(schemaCacheId, oldFormat);
   }
-  return res.utils.data(schemaInfo);
+  return res.utils.data(oldFormat);
 }
 
 router.get(


Test patch:
diff --git a/server/drivers/bigquery/test.js b/server/drivers/bigquery/test.js
index 7a39133d..ebd5dc2f 100644
--- a/server/drivers/bigquery/test.js
+++ b/server/drivers/bigquery/test.js
@@ -1,5 +1,6 @@
 const assert = require('assert');
 const bigquery = require('./index.js');
+const testUtils = require('../test-utils.js');
 
 const connection = {
   name: 'test bigquery',
@@ -52,17 +53,13 @@ describe('drivers/bigquery', function () {
 
   it('implements getSchema', function () {
     return bigquery.getSchema(connection).then((schemaInfo) => {
-      const schema = schemaInfo[connection.datasetName];
-      const MSG = 'schema query returns expected results';
-
-      assert(schema, MSG);
-      assert(schema.hasOwnProperty(testTable), MSG);
-      const columns = schema[testTable];
-      assert.equal(columns.length, 1, MSG);
-      assert.equal(columns[0].table_schema, connection.datasetName, MSG);
-      assert.equal(columns[0].table_name, testTable, MSG);
-      assert.equal(columns[0].column_name, 'id', MSG);
-      assert.equal(columns[0].data_type, 'INTEGER', MSG);
+      testUtils.hasColumnDataType(
+        schemaInfo,
+        connection.datasetName,
+        testTable,
+        'id',
+        'INTEGER'
+      );
     });
   });
 
diff --git a/server/drivers/cassandra/test.js b/server/drivers/cassandra/test.js
index 84164632..54910f7e 100644
--- a/server/drivers/cassandra/test.js
+++ b/server/drivers/cassandra/test.js
@@ -1,6 +1,7 @@
 /* eslint-disable no-await-in-loop */
 const assert = require('assert');
 const cassandra = require('./index.js');
+const testUtils = require('../test-utils.js');
 
 const connection = {
   name: 'test cassandra',
@@ -31,15 +32,8 @@ describe('drivers/cassandra', function () {
 
   it('getSchema()', async function () {
     const schemaInfo = await cassandra.getSchema(connection);
-    assert(schemaInfo);
-    assert(schemaInfo.test, 'test');
-    assert(schemaInfo.test.test, 'test.test');
-    const columns = schemaInfo.test.test;
-    assert.equal(columns.length, 2, 'columns.length');
-    assert.equal(columns[0].table_schema, 'test', 'table_schema');
-    assert.equal(columns[0].table_name, 'test', 'table_name');
-    assert.equal(columns[0].column_name, 'id', 'column_name');
-    assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+    const column = testUtils.getColumn(schemaInfo, 'test', 'test', 'id');
+    assert(column.hasOwnProperty('dataType'));
   });
 
   it('runQuery under limit', async function () {
diff --git a/server/drivers/clickhouse/test.js b/server/drivers/clickhouse/test.js
index 417bb6f9..061e628e 100644
--- a/server/drivers/clickhouse/test.js
+++ b/server/drivers/clickhouse/test.js
@@ -1,5 +1,6 @@
 const assert = require('assert');
 const clickhouse = require('./index.js');
+const testUtils = require('../test-utils.js');
 
 const connection = {
   name: 'test clickhouse',
@@ -48,15 +49,8 @@ describe('drivers/clickhouse', function () {
 
   it('getSchema()', function () {
     return clickhouse.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo);
-      assert(schemaInfo.test, 'test');
-      assert(schemaInfo.test.test, 'test.test');
-      const columns = schemaInfo.test.test;
-      assert.equal(columns.length, 2, 'columns.length');
-      assert.equal(columns[0].table_schema, 'test', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'test', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/crate/test.js b/server/drivers/crate/test.js
index e962ea71..2bea2900 100644
--- a/server/drivers/crate/test.js
+++ b/server/drivers/crate/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const crate = require('./index.js');
 
 const connection = {
@@ -31,14 +32,8 @@ describe('drivers/crate', function () {
 
   it('getSchema()', function () {
     return crate.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.doc, 'doc');
-      assert(schemaInfo.doc.test, 'doc.test');
-      const columns = schemaInfo.doc.test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'doc', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'doc', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/hdb/test.js b/server/drivers/hdb/test.js
index ad949760..b369f745 100644
--- a/server/drivers/hdb/test.js
+++ b/server/drivers/hdb/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const hdb = require('./index.js');
 
 const connection = {
@@ -38,14 +39,8 @@ describe('drivers/hdb', function () {
 
   it('getSchema()', function () {
     return hdb.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.SYSTEM, 'SYSTEM');
-      assert(schemaInfo.SYSTEM.TEST, 'SYSTEM.TEST');
-      const columns = schemaInfo.SYSTEM.TEST;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'SYSTEM', 'table_schema');
-      assert.equal(columns[0].table_name, 'TEST', 'table_name');
-      assert.equal(columns[0].column_name, 'ID', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'SYSTEM', 'TEST', 'ID');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/mysql/test.js b/server/drivers/mysql/test.js
index 17476392..9e48c9c0 100644
--- a/server/drivers/mysql/test.js
+++ b/server/drivers/mysql/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const mysql = require('./index.js');
 
 const connection = {
@@ -30,14 +31,8 @@ describe('drivers/mysql', function () {
 
   it('getSchema()', function () {
     return mysql.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.sqlpad, 'sqlpad');
-      assert(schemaInfo.sqlpad.test, 'sqlpad.test');
-      const columns = schemaInfo.sqlpad.test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'sqlpad', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'sqlpad', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/mysql2/test.js b/server/drivers/mysql2/test.js
index fe198909..f3c3cd60 100644
--- a/server/drivers/mysql2/test.js
+++ b/server/drivers/mysql2/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const mysql2 = require('./index.js');
 
 const connection = {
@@ -30,14 +31,8 @@ describe('drivers/mysql2', function () {
 
   it('getSchema()', function () {
     return mysql2.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.sqlpad, 'sqlpad');
-      assert(schemaInfo.sqlpad.test, 'sqlpad.test');
-      const columns = schemaInfo.sqlpad.test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'sqlpad', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'sqlpad', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/pinot/test.js b/server/drivers/pinot/test.js
index 851d4848..841fc16c 100644
--- a/server/drivers/pinot/test.js
+++ b/server/drivers/pinot/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const pinot = require('./index.js');
 
 const connection = {
@@ -17,15 +18,13 @@ describe('drivers/pinot', function () {
 
   it('getSchema()', async function () {
     const schemaInfo = await pinot.getSchema(connection);
-    assert(schemaInfo);
-    assert(schemaInfo.main, 'main');
-    assert(schemaInfo.main.baseballStats, 'main.baseballStats');
-    const columns = schemaInfo.main.baseballStats;
-    assert.equal(columns.length, 25, 'columns.length');
-    assert.equal(columns[0].table_schema, 'main', 'table_schema');
-    assert.equal(columns[0].table_name, 'baseballStats', 'table_name');
-    assert.equal(columns[0].column_name, 'playerID', 'column_name');
-    assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+    const column = testUtils.getColumn(
+      schemaInfo,
+      'main',
+      'baseballStats',
+      'playerID'
+    );
+    assert(column.hasOwnProperty('dataType'));
   });
 
   it('runQuery under limit', async function () {
diff --git a/server/drivers/postgres/test.js b/server/drivers/postgres/test.js
index 2fdb2c0d..ddd57548 100644
--- a/server/drivers/postgres/test.js
+++ b/server/drivers/postgres/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const postgres = require('./index.js');
 
 const connection = {
@@ -29,13 +30,13 @@ describe('drivers/postgres', function () {
 
   it('getSchema()', function () {
     return postgres.getSchema(connection).then((schemaInfo) => {
-      const { sqlpad_test } = schemaInfo.public;
-      assert(sqlpad_test);
-      assert.strictEqual(sqlpad_test[0].table_schema, 'public');
-      assert.strictEqual(sqlpad_test[0].table_name, 'sqlpad_test');
-      assert.strictEqual(sqlpad_test[0].column_name, 'id');
-      assert.strictEqual(sqlpad_test[0].data_type, 'int4');
-      assert.strictEqual(sqlpad_test[0].column_description, null);
+      testUtils.hasColumnDataType(
+        schemaInfo,
+        'public',
+        'sqlpad_test',
+        'id',
+        'int4'
+      );
     });
   });
 
diff --git a/server/drivers/presto/test.js b/server/drivers/presto/test.js
index 933360a7..883d7725 100644
--- a/server/drivers/presto/test.js
+++ b/server/drivers/presto/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const presto = require('./index.js');
 
 const connection = {
@@ -50,15 +51,8 @@ describe('drivers/presto', function () {
 
   it('getSchema()', function () {
     return presto.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo);
-      assert(schemaInfo.test, 'test');
-      assert(schemaInfo.test.test, 'test.test');
-      const columns = schemaInfo.test.test;
-      assert.equal(columns.length, 2, 'columns.length');
-      assert.equal(columns[0].table_schema, 'test', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'test', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/redshift/test.js b/server/drivers/redshift/test.js
index d9450813..a2eff2d4 100644
--- a/server/drivers/redshift/test.js
+++ b/server/drivers/redshift/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const redshift = require('./index.js');
 
 const connection = {
@@ -28,13 +29,13 @@ describe('drivers/fake-redshift', function () {
 
   it.skip('getSchema()', function () {
     return redshift.getSchema(connection).then((schemaInfo) => {
-      const { sqlpad_test } = schemaInfo.public;
-      assert(sqlpad_test);
-      assert.strictEqual(sqlpad_test[0].table_schema, 'public');
-      assert.strictEqual(sqlpad_test[0].table_name, 'sqlpad_test');
-      assert.strictEqual(sqlpad_test[0].column_name, 'id');
-      assert.strictEqual(sqlpad_test[0].data_type, 'int4');
-      assert.strictEqual(sqlpad_test[0].column_description, null);
+      testUtils.hasColumnDataType(
+        schemaInfo,
+        'public',
+        'sqlpad_test',
+        'id',
+        'int4'
+      );
     });
   });
 
diff --git a/server/drivers/snowflake/test.js b/server/drivers/snowflake/test.js
index a165309e..504d69be 100644
--- a/server/drivers/snowflake/test.js
+++ b/server/drivers/snowflake/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const snowflake = require('./index.js');
 
 const connection = {
@@ -33,14 +34,8 @@ describe('drivers/snowflake', function () {
   it('getSchema()', function () {
     this.timeout(60000);
     return snowflake.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.SQLPAD, 'SQLPAD');
-      assert(schemaInfo.SQLPAD.TEST, 'SQLPAD.TEST');
-      const columns = schemaInfo.SQLPAD.TEST;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'SQLPAD', 'TABLE_SCHEMA');
-      assert.equal(columns[0].table_name, 'TEST', 'TABLE_NAME');
-      assert.equal(columns[0].column_name, 'ID', 'COLUMN_NAME');
-      assert(columns[0].hasOwnProperty('data_type'), 'DATA_TYPE');
+      const column = testUtils.getColumn(schemaInfo, 'SQLPAD', 'TEST', 'ID');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/drivers/sqlite/test.js b/server/drivers/sqlite/test.js
index ac0a038e..b29613a6 100644
--- a/server/drivers/sqlite/test.js
+++ b/server/drivers/sqlite/test.js
@@ -1,5 +1,6 @@
 const assert = require('assert');
 const sqlite3 = require('./index.js');
+const testUtils = require('../test-utils.js');
 
 const connection = {
   filename: './sqlpad_test_sqlite.db',
@@ -27,20 +28,20 @@ describe('drivers/sqlite', function () {
 
   it('getSchema()', async function () {
     const schemaInfo = await sqlite3.getSchema(connection);
-
-    assert(schemaInfo.main);
-    assert(schemaInfo.main.sqlpad_test, 'main.sqlpad_test');
-    const columns = schemaInfo.main.sqlpad_test;
-    assert.equal(columns.length, 2, 'columns.length');
-    assert.equal(columns[0].table_schema, 'main', 'table_schema');
-    assert.equal(columns[0].table_name, 'sqlpad_test', 'table_name');
-    assert.equal(columns[0].column_name, 'id', 'column_name');
-    assert.equal(columns[0].data_type, 'INTEGER', 'data_type');
-
-    assert.equal(columns[1].table_schema, 'main', 'table_schema');
-    assert.equal(columns[1].table_name, 'sqlpad_test', 'table_name');
-    assert.equal(columns[1].column_name, 'name', 'column_name');
-    assert.equal(columns[1].data_type, 'TEXT', 'data_type');
+    testUtils.hasColumnDataType(
+      schemaInfo,
+      'main',
+      'sqlpad_test',
+      'id',
+      'INTEGER'
+    );
+    testUtils.hasColumnDataType(
+      schemaInfo,
+      'main',
+      'sqlpad_test',
+      'name',
+      'TEXT'
+    );
   });
 
   it('runQuery under limit', async function () {
diff --git a/server/drivers/sqlserver/test.js b/server/drivers/sqlserver/test.js
index e10a98aa..89cc50e2 100644
--- a/server/drivers/sqlserver/test.js
+++ b/server/drivers/sqlserver/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const sqlserver = require('./index.js');
 
 const masterConnection = {
@@ -52,14 +53,7 @@ describe('drivers/sqlserver', function () {
 
   it('getSchema()', function () {
     return sqlserver.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.dbo, 'dbo');
-      assert(schemaInfo.dbo.test, 'dbo.test');
-      const columns = schemaInfo.dbo.test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'dbo', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert.equal(columns[0].data_type, 'int', 'data_type');
+      testUtils.hasColumnDataType(schemaInfo, 'dbo', 'test', 'id', 'int');
     });
   });
 
diff --git a/server/drivers/test-utils.js b/server/drivers/test-utils.js
new file mode 100644
index 00000000..a9a9e7c4
--- /dev/null
+++ b/server/drivers/test-utils.js
@@ -0,0 +1,23 @@
+const assert = require('assert');
+
+function getColumn(tree, schemaName, tableName, columnName) {
+  const s = tree.schemas.find((s) => s.name === schemaName);
+  assert(s, `${schemaName} exists`);
+  const t = s.tables.find((t) => t.name === tableName);
+  assert(t, `${tableName} exists`);
+  const c = t.columns.find((c) => c.name === columnName);
+  assert(c, `${columnName} exists`);
+  return c;
+}
+
+function hasColumnDataType(tree, schemaName, tableName, columnName, dataType) {
+  const c = getColumn(tree, schemaName, tableName, columnName);
+  if (dataType) {
+    assert.strictEqual(c.dataType, dataType, 'column dataType expected');
+  }
+}
+
+module.exports = {
+  getColumn,
+  hasColumnDataType,
+};
diff --git a/server/drivers/unixodbc/test.js b/server/drivers/unixodbc/test.js
index 9c06bf7f..db1d82aa 100644
--- a/server/drivers/unixodbc/test.js
+++ b/server/drivers/unixodbc/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const unixodbc = require('./index.js');
 
 // Using Windows? You may need to change your db path to something like
@@ -50,18 +51,13 @@ describe('drivers/unixodbc', function () {
 
   it('getSchema()', function () {
     return unixodbc.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo[test_schema_name], test_schema_name);
-      assert(
-        schemaInfo[test_schema_name].sqlpad_test,
-        test_schema_name + '.sqlpad_test'
+      testUtils.hasColumnDataType(
+        schemaInfo,
+        test_schema_name,
+        'sqlpad_test',
+        'unknown',
+        'unknown'
       );
-      const columns = schemaInfo[test_schema_name].sqlpad_test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, test_schema_name, 'table_schema');
-      assert.equal(columns[0].table_name, 'sqlpad_test', 'table_name');
-      // column metadata not available in sqlite3
-      assert.equal(columns[0].column_name, 'unknown', 'column_name');
-      assert.equal(columns[0].data_type, 'unknown', 'data_type');
     });
   });
 
diff --git a/server/drivers/vertica/test.js b/server/drivers/vertica/test.js
index da68e635..024213fc 100644
--- a/server/drivers/vertica/test.js
+++ b/server/drivers/vertica/test.js
@@ -1,4 +1,5 @@
 const assert = require('assert');
+const testUtils = require('../test-utils.js');
 const vertica = require('./index.js');
 
 const connection = {
@@ -34,14 +35,8 @@ describe('drivers/vertica', function () {
 
   it('getSchema()', function () {
     return vertica.getSchema(connection).then((schemaInfo) => {
-      assert(schemaInfo.public, 'public');
-      assert(schemaInfo.public.test, 'public.test');
-      const columns = schemaInfo.public.test;
-      assert.equal(columns.length, 1, 'columns.length');
-      assert.equal(columns[0].table_schema, 'public', 'table_schema');
-      assert.equal(columns[0].table_name, 'test', 'table_name');
-      assert.equal(columns[0].column_name, 'id', 'column_name');
-      assert(columns[0].hasOwnProperty('data_type'), 'data_type');
+      const column = testUtils.getColumn(schemaInfo, 'public', 'test', 'id');
+      assert(column.hasOwnProperty('dataType'));
     });
   });
 
diff --git a/server/test/api/connection-schema.js b/server/test/api/connection-schema.js
new file mode 100644
index 00000000..e01c8663
--- /dev/null
+++ b/server/test/api/connection-schema.js
@@ -0,0 +1,108 @@
+const assert = require('assert');
+const TestUtils = require('../utils');
+const ConnectionClient = require('../../lib/connection-client');
+
+describe('api/connections/<id>/schema', function () {
+  this.timeout(10000);
+  const utils = new TestUtils();
+  let connection;
+  let user1;
+  let user2;
+
+  before(async function () {
+    await utils.init(true);
+
+    // Create some users to use to ensure dynamic connection is cached properly
+    user1 = await utils.addUserApiHelper('user1', {
+      email: 'user1@sqlpad.com',
+      password: 'user1',
+      role: 'admin',
+      name: 'user1',
+      data: {
+        dbfilename: 'connection-schema.user1.sqlite',
+      },
+    });
+
+    user2 = await utils.addUserApiHelper('user2', {
+      email: 'user2@sqlpad.com',
+      password: 'user2',
+      role: 'admin',
+      name: 'user2',
+      data: {
+        dbfilename: 'connection-schema.user2.sqlite',
+      },
+    });
+
+    // Create a connection that looks to user data for db connection info
+    // While this is the same connection, the underlying database is different
+    const body = await utils.post('admin', '/api/connections', {
+      driver: 'sqlite',
+      name: 'sqlite-test',
+      data: {
+        filename: './test/artifacts/{{user.data.dbfilename}}',
+      },
+    });
+    connection = body;
+
+    // With user 1 create a table
+    // Then with user 2 create a different table
+    // Later ensure each user sees the correct schema
+    const user1CC = new ConnectionClient(connection, user1);
+    await user1CC.runQuery(`CREATE TABLE user1 (id INT, name TEXT)`);
+
+    const user2CC = new ConnectionClient(connection, user2);
+    await user2CC.runQuery(`CREATE TABLE user2 (id INT, name TEXT)`);
+  });
+
+  it('Gets schema-info honoring connection templates', async function () {
+    const body1 = await utils.get(
+      'user1',
+      `/api/connections/${connection.id}/schema`
+    );
+    assert(body1, 'body');
+
+    // Ensure format is as expected
+    assert.strictEqual(body1.schemas[0].name, 'main');
+    const table = body1.schemas[0].tables.find((t) => t.name === 'user1');
+    assert.strictEqual(table.columns[0].name, 'id');
+    assert.strictEqual(table.columns[0].dataType, 'INT');
+    assert.strictEqual(table.columns[1].name, 'name');
+    assert.strictEqual(table.columns[1].dataType, 'TEXT');
+
+    // Ensure proper access for user1
+    assert(
+      body1.schemas[0].tables.find((t) => t.name === 'user1'),
+      'user1 table exists'
+    );
+    assert(
+      !body1.schemas[0].tables.find((t) => t.name === 'user2'),
+      'user2 table does not exist'
+    );
+
+    // Ensure proper access for user2
+    const body2 = await utils.get(
+      'user2',
+      `/api/connections/${connection.id}/schema`
+    );
+    assert(
+      !body2.schemas[0].tables.find((t) => t.name === 'user1'),
+      'user1 table does not exist'
+    );
+    assert(
+      body2.schemas[0].tables.find((t) => t.name === 'user2'),
+      'user2 table exists'
+    );
+  });
+
+  it('Read from cache is successful', async function () {
+    const body1 = await utils.get(
+      'user1',
+      `/api/connections/${connection.id}/schema`
+    );
+    assert(body1, 'body');
+    assert(
+      body1.schemas[0].tables.find((t) => t.name === 'user1'),
+      'user1 table exists'
+    );
+  });
+});
diff --git a/server/test/api/schema-info.js b/server/test/api/schema-info.js
index 22540b9e..903e343d 100644
--- a/server/test/api/schema-info.js
+++ b/server/test/api/schema-info.js
@@ -19,7 +19,7 @@ describe('api/schema-info', function () {
       role: 'admin',
       name: 'user1',
       data: {
-        dbfilename: 'user1.sqlite',
+        dbfilename: 'schema-info.user1.sqlite',
       },
     });
 
@@ -29,7 +29,7 @@ describe('api/schema-info', function () {
       role: 'admin',
       name: 'user2',
       data: {
-        dbfilename: 'user2.sqlite',
+        dbfilename: 'schema-info.user2.sqlite',
       },
     });