Repository: rickbergfalk/sqlpad
Pull request #565 (2020-03-05 00:42:41)

Add Persistent database connection support for Postgres, ODBC
Adds persistent database connection support for Postgres and ODBC drivers (mock driver too, but it isn't real). This is functional, but I'm not set on the UI, so if anyone has any ideas or suggestions on this let me know.

This adds the ability to run queries over multiple HTTP POST calls, but share the same underlying connection. Drivers can "opt in" to supporting this feature by exposing a `Client` class implementation that supports three methods: `connect`, `disconnect`, and `runQuery`. 

The existing driver module functions `runQuery`, `testConnection`, and `getSchema` still exist, and are still used for cases where one-off queries are desired, or automatic open/closing of connection is desired. For drivers that have a `Client` class implementation, these functions can use this class implementation under the hood.

The `connect` method establishes a database connection, and stores the connection/client reference on some property. What that property is shouldn't matter, as it should be considered private. This method should only be allowed to be called *once* to ensure SQLPad does not leak connections and open more connections than intended.

The `runQuery` method takes a query argument, and runs the query with established client connection made in `connect`. It returns results as sqlpad expects, but *does not* close the client connection after the query.

The `disconnect` method takes the established client connection and closes it. This function should be made to safely be called multiple times or called in paranoid cases where we don't know if a connection was made or not, or if there was a race condition, or if the connection was already closed. 

Various driver implementations are wrapped in the `ConnectionClient` class, which handles the mapping to whatever driver is required for the connection in use. 

For persistent database connections, SQLPad maintains an in-memory store of connections made. When disconnected, the reference is removed. 

As of this PR, the UI has a new button that toggles between modes. `Auto` works like the existing flow, where a connection is made, a query is run, and then the connection is closed. `Connected` mode establishes a connection, and keeps it open until the user toggles the button back to `Auto`. (I'm not sure how I feel about this)

![SQLPad connected](https://user-images.githubusercontent.com/303966/75936196-18346f80-5e47-11ea-9404-5ef478e0e0e7.jpg)
![SQLPad Auto](https://user-images.githubusercontent.com/303966/75936206-208caa80-5e47-11ea-916b-08ee1873b073.jpg)

Once the UI creates a `ConnectionClient` via the `/api/connection-clients` route, an interval timer is started (on the server) to check the last time the UI has signaled that it is using that connection client. Meanwhile the UI will poll the SQLPad server, updating the keep alive timestamp. If some amount of time is reached, it means the UI has left the page or become unresponsive, and the SQLPad server closes the connection.

This is only the first wave of work in this area. Things I expect to add in further iterations:

* Additional fields/options in connection UI for persistent connections (timeout, auto-connect, if they are allowed, etc)
* Management page for Admins to kill SQLPad-originated database connections that SQLPad knows about (not necessarily all connections to a database... though maybe that's an area SQLPad can expand into :) )

@gonace @koszti I'm going to step away from this for a day and come back to self-review and merge. If you have any feedback (before or after) let me know and I'll follow up!


Patch:
diff --git a/client/src/queryEditor/toolbar/Toolbar.js b/client/src/queryEditor/toolbar/Toolbar.js
index 04e3ad1a..64843286 100644
--- a/client/src/queryEditor/toolbar/Toolbar.js
+++ b/client/src/queryEditor/toolbar/Toolbar.js
@@ -13,6 +13,7 @@ import ToolbarShareQueryButton from './ToolbarShareQueryButton';
 import ToolbarSpacer from './ToolbarSpacer';
 import ToolbarTagsButton from './ToolbarTagsButton';
 import ToolbarToggleSchemaButton from './ToolbarToggleSchemaButton';
+import ToolbarConnectionClientButton from './ToolbarConnectionClientButton';
 
 function Toolbar() {
   return (
@@ -47,6 +48,10 @@ function Toolbar() {
 
         <ToolbarSpacer />
 
+        <ToolbarConnectionClientButton />
+
+        <ToolbarSpacer />
+
         <ToolbarRunButton />
 
         <ToolbarSpacer />
diff --git a/client/src/queryEditor/toolbar/ToolbarConnectionClientButton.js b/client/src/queryEditor/toolbar/ToolbarConnectionClientButton.js
new file mode 100644
index 00000000..dc3f1432
--- /dev/null
+++ b/client/src/queryEditor/toolbar/ToolbarConnectionClientButton.js
@@ -0,0 +1,56 @@
+import React, { useState } from 'react';
+import { connect } from 'unistore/react';
+import Button from '../../common/Button';
+import {
+  connectConnectionClient,
+  disconnectConnectionClient
+} from '../../stores/connections';
+
+function ToolbarConnectionClientButton({
+  connectionClient,
+  connections,
+  selectedConnectionId,
+  connectConnectionClient,
+  disconnectConnectionClient
+}) {
+  const [fetching, setFetching] = useState(false);
+
+  async function handleClick() {
+    setFetching(true);
+    if (connectionClient) {
+      await disconnectConnectionClient();
+    } else {
+      await connectConnectionClient();
+    }
+    setFetching(false);
+  }
+
+  if (!connections || connections.length === 0 || !selectedConnectionId) {
+    return null;
+  }
+
+  const connection = connections.find(
+    connection => connection._id === selectedConnectionId
+  );
+  if (!connection || !connection.supportsConnectionClient) {
+    return null;
+  }
+
+  return (
+    <Button
+      tooltip="Connected keeps a single connection open, auto opens and closes for each query"
+      disabled={fetching}
+      onClick={handleClick}
+    >
+      {connectionClient ? 'Connected' : 'Auto'}
+    </Button>
+  );
+}
+
+export default connect(
+  ['connectionClient', 'connections', 'selectedConnectionId'],
+  store => ({
+    connectConnectionClient: connectConnectionClient(store),
+    disconnectConnectionClient
+  })
+)(ToolbarConnectionClientButton);
diff --git a/client/src/stores/connections.js b/client/src/stores/connections.js
index 1b8a236c..b536b312 100644
--- a/client/src/stores/connections.js
+++ b/client/src/stores/connections.js
@@ -11,6 +11,8 @@ function sortConnections(connections) {
 
 export const initialState = {
   selectedConnectionId: '',
+  connectionClient: null,
+  connectionClientInterval: null,
   connections: [],
   connectionsLastUpdated: null,
   connectionsLoading: false
@@ -27,11 +29,101 @@ export async function initSelectedConnection(state) {
   }
 }
 
+/**
+ * Open a client connection for the currently selected connection
+ * @param {*} state
+ */
+export const connectConnectionClient = store => async state => {
+  const { selectedConnectionId } = state;
+  const json = await fetchJson('POST', '/api/connection-clients', {
+    connectionId: selectedConnectionId
+  });
+  if (json.error) {
+    return message.error('Problem connecting to database');
+  }
+
+  // Poll connection-clients api to keep it alive
+  const connectionClientInterval = setInterval(async () => {
+    const updateJson = await fetchJson(
+      'PUT',
+      `/api/connection-clients/${json.connectionClient.id}`
+    );
+
+    // Not sure if this should message user here
+    // In the event of an error this could get really noisy
+    if (updateJson.error) {
+      message.error(updateJson.error);
+    }
+
+    // If the PUT didn't return a connectionClient object,
+    // the connectionClient has been disconnected
+    if (!updateJson.connectionClient && connectionClientInterval) {
+      clearInterval(connectionClientInterval);
+      store.setState({
+        connectionClientInterval: null,
+        connectionClient: null
+      });
+    } else {
+      store.setState({
+        connectionClient: updateJson.connectionClient
+      });
+    }
+  }, 10000);
+
+  return { connectionClient: json.connectionClient, connectionClientInterval };
+};
+
+/**
+ * Disconnect the current connection client if one exists
+ * @param {*} state
+ */
+export const disconnectConnectionClient = async state => {
+  const { connectionClient, connectionClientInterval } = state;
+  if (connectionClientInterval) {
+    clearInterval(connectionClientInterval);
+  }
+  if (connectionClient) {
+    fetchJson('DELETE', `/api/connection-clients/${connectionClient.id}`).then(
+      json => {
+        if (json.error) {
+          message.error(json.error);
+        }
+      }
+    );
+  }
+  return { connectionClient: null, connectionClientInterval: null };
+};
+
+/**
+ * Select connection and disconnect connectionClient if it exists
+ * @param {*} state
+ * @param {*} selectedConnectionId
+ */
 export const selectConnectionId = (state, selectedConnectionId) => {
+  const { connectionClient, connectionClientInterval } = state;
   localforage
     .setItem('selectedConnectionId', selectedConnectionId)
     .catch(error => message.error(error));
-  return { selectedConnectionId };
+
+  if (connectionClient) {
+    fetchJson('DELETE', `/api/connection-clients/${connectionClient.id}`).then(
+      json => {
+        if (json.error) {
+          message.error(json.error);
+        }
+      }
+    );
+  }
+
+  if (connectionClientInterval) {
+    clearInterval(connectionClientInterval);
+  }
+
+  return {
+    selectedConnectionId,
+    connectionClient: null,
+    connectionClientInterval: null
+  };
 };
 
 export const deleteConnection = async (state, connectionId) => {
diff --git a/client/src/stores/queries.js b/client/src/stores/queries.js
index 08a3698b..c8e54509 100644
--- a/client/src/stores/queries.js
+++ b/client/src/stores/queries.js
@@ -108,7 +108,13 @@ export const loadQuery = async (state, queryId) => {
 };
 
 export const runQuery = store => async state => {
-  const { cacheKey, query, selectedText, selectedConnectionId } = state;
+  const {
+    cacheKey,
+    query,
+    selectedText,
+    selectedConnectionId,
+    connectionClient
+  } = state;
 
   store.setState({
     isRunning: true,
@@ -116,6 +122,7 @@ export const runQuery = store => async state => {
   });
   const postData = {
     connectionId: selectedConnectionId,
+    connectionClientId: connectionClient && connectionClient.id,
     cacheKey,
     queryId: query._id,
     queryName: query.name,
diff --git a/server/app.js b/server/app.js
index b54d7947..e3919da1 100644
--- a/server/app.js
+++ b/server/app.js
@@ -116,6 +116,7 @@ function makeApp(config, models) {
     require('./routes/password-reset.js'),
     require('./routes/connections.js'),
     require('./routes/connection-accesses.js'),
+    require('./routes/connection-clients.js'),
     require('./routes/test-connection.js'),
     require('./routes/queries.js'),
     require('./routes/query-history.js'),
diff --git a/server/drivers/mock/index.js b/server/drivers/mock/index.js
index ab735b41..437b8183 100644
--- a/server/drivers/mock/index.js
+++ b/server/drivers/mock/index.js
@@ -237,15 +237,62 @@ Array(500)
 /**
  * Get schema for connection
  */
-async function getSchema() {
+async function getSchema(connection) {
   const fakeSchemaQueryResult = {
     rows: schemaRows,
     incomplete: false
   };
-  await wait(Math.random() * 5000);
+  const waitMs =
+    connection && connection.hasOwnProperty('wait')
+      ? parseInt(connection.wait, 10)
+      : 2000;
+
+  await wait(waitMs);
   return formatSchemaQueryResults(fakeSchemaQueryResult);
 }
 
+class Client {
+  constructor(connection) {
+    this.connection = connection;
+    this.client = null;
+  }
+
+  async connect() {
+    // presumably this would make some db connection call, then populate client
+    if (this.client) {
+      throw new Error('Client already connected');
+    }
+    await wait(10);
+    // this is just a mocked example of what a client might be like
+    this.client = {
+      end: async () => {
+        await wait(10);
+      }
+    };
+  }
+
+  async disconnect() {
+    // presumably this would disconnect some client from the db
+    if (this.client) {
+      const client = this.client;
+      this.client = null;
+      try {
+        await client.end();
+      } catch (error) {
+        // log the error but don't make a big deal
+        // Current thinking is disconnect can be called multiple times
+      }
+    }
+  }
+
+  async runQuery(query) {
+    // Normally this would use the connected client and make a db call
+    // This driver doesn't have that, so it will just call the mock runQuery function
+    await wait(10);
+    return runQuery(query, this.connection);
+  }
+}
+
 const fields = [
   {
     key: 'host',
@@ -325,10 +372,11 @@ const fields = [
 ];
 
 module.exports = {
-  id,
-  name,
+  Client,
   fields,
   getSchema,
+  id,
+  name,
   runQuery,
   testConnection
 };
diff --git a/server/drivers/postgres/index.js b/server/drivers/postgres/index.js
index 027c50b5..2901681f 100644
--- a/server/drivers/postgres/index.js
+++ b/server/drivers/postgres/index.js
@@ -4,11 +4,146 @@ const _ = require('lodash');
 const PgCursor = require('pg-cursor');
 const SocksConnection = require('socksjs');
 const appLog = require('../../lib/appLog');
+const splitSql = require('../../lib/splitSql');
 const { formatSchemaQueryResults } = require('../utils');
 
 const id = 'postgres';
 const name = 'Postgres';
 
+/**
+ * A promise wrapper for pgCursor
+ * Regular use case of cursors is to fetch chunks at a time
+ * SQLPad uses them as a way to confidently get a set number of rows without relying on using LIMIT in a SQL query.
+ * The cursor is then closed after first result is received
+ * @param {pg.Client} client
+ * @param {string} query
+ * @param {number} rows
+ */
+function asyncQueryViaCursor(cursor, rows) {
+  return new Promise((resolve, reject) => {
+    cursor.read(rows, (err, rows) => {
+      cursor.close(closeError => {
+        if (closeError) {
+          appLog.error(closeError, 'Error closing postgres cursor');
+        }
+        if (err) {
+          return reject(err);
+        }
+        resolve(rows);
+      });
+    });
+  });
+}
+
+class Client {
+  constructor(connection) {
+    this.connection = connection;
+    this.client = null;
+  }
+
+  async connect() {
+    if (this.client) {
+      throw new Error('Client already connected');
+    }
+
+    const connection = this.connection;
+
+    const pgConfig = {
+      user: connection.username,
+      password: connection.password,
+      database: connection.database,
+      host: connection.host,
+      port: connection.port || undefined,
+      ssl: connection.postgresSsl,
+      stream: createSocksConnection(connection)
+    };
+
+    // TODO cache key/cert values
+    if (connection.postgresKey && connection.postgresCert) {
+      pgConfig.ssl = {
+        key: fs.readFileSync(connection.postgresKey),
+        cert: fs.readFileSync(connection.postgresCert)
+      };
+      if (connection.postgresCA) {
+        pgConfig.ssl['ca'] = fs.readFileSync(connection.postgresCA);
+      }
+    }
+
+    this.pgConfig = pgConfig;
+    this.client = new pg.Client(pgConfig);
+
+    await this.client.connect();
+
+    if (connection.preQueryStatements) {
+      const queries = splitSql(connection.preQueryStatements);
+      for (const query of queries) {
+        // eslint-disable-next-line no-await-in-loop
+        await this.runQuery(query);
+      }
+    }
+  }
+
+  async disconnect() {
+    if (this.client) {
+      const client = this.client;
+      this.client = null;
+      try {
+        await client.end();
+      } catch (error) {
+        appLog.error(error, 'Error ending postgres client');
+      }
+    }
+  }
+
+  async runQuery(query) {
+    let incomplete = false;
+    const { maxRows } = this.connection;
+    const maxRowsPlusOne = maxRows + 1;
+
+    if (this.connection.denyMultipleStatements) {
+      const statements = splitSql(query);
+      if (statements.length > 1) {
+        throw new Error('Running multiple statements not allowed');
+      }
+    }
+
+    try {
+      const cursor = this.client.query(new PgCursor(query));
+
+      let resultRows = await asyncQueryViaCursor(cursor, maxRowsPlusOne);
+
+      if (resultRows.length === maxRowsPlusOne) {
+        incomplete = true;
+        resultRows.pop(); // get rid of that extra record. we only get 1 more than the max to see if there would have been more...
+      }
+      return { rows: resultRows, incomplete };
+    } catch (error) {
+      // pg_cursor can't handle multi-statements at the moment
+      // as a work around we'll retry the query the old way, but we lose the maxRows protection
+
+      // Run query without try/catch here
+      // The error should throw and buble up
+      const result = await this.client.query(query);
+
+      // multi-statements returns array of result objects but runQuery should return rows array
+      // transform array of results objects to flat rows array
+      let resultRows = [];
+      if (Array.isArray(result)) {
+        resultRows = resultRows.concat(_.flatten(result.map(r => r.rows)));
+      } else {
+        resultRows = resultRows.rows || [];
+      }
+
+      if (resultRows.length >= maxRows) {
+        incomplete = true;
+        resultRows = resultRows.slice(0, maxRows);
+      }
+
+      return { rows: resultRows, incomplete };
+    }
+  }
+}
+
 function createSocksConnection(connection) {
   if (connection.useSocks) {
     return new SocksConnection(
@@ -55,118 +190,17 @@ const SCHEMA_SQL = `
  * @param {string} query
  * @param {object} connection
  */
-function runQuery(query, connection) {
-  const pgConfig = {
-    user: connection.username,
-    password: connection.password,
-    database: connection.database,
-    host: connection.host,
-    ssl: connection.postgresSsl,
-    stream: createSocksConnection(connection),
-    multipleStatements: !connection.denyMultipleStatements,
-    preQueryStatements: connection.preQueryStatements
-  };
-  // TODO cache key/cert values
-  if (connection.postgresKey && connection.postgresCert) {
-    pgConfig.ssl = {
-      key: fs.readFileSync(connection.postgresKey),
-      cert: fs.readFileSync(connection.postgresCert)
-    };
-    if (connection.postgresCA) {
-      pgConfig.ssl['ca'] = fs.readFileSync(connection.postgresCA);
-    }
+async function runQuery(query, connection) {
+  const client = new Client(connection);
+  await client.connect();
+  try {
+    const result = await client.runQuery(query);
+    await client.disconnect();
+    return result;
+  } catch (error) {
+    await client.disconnect();
+    throw error;
   }
-  if (connection.port) pgConfig.port = connection.port;
-
-  return new Promise((resolve, reject) => {
-    const client = new pg.Client(pgConfig);
-    let resultRows = [];
-
-    client.connect(err => {
-      if (err) {
-        client.end();
-        return reject(err);
-      }
-
-      function _runQuery(
-        query,
-        params = { addRowsToResults: true, closeConnection: true }
-      ) {
-        const cursor = client.query(new PgCursor(query));
-        return cursor.read(connection.maxRows + 1, (err, rows) => {
-          if (err) {
-            // pg_cursor can't handle multi-statements at the moment
-            // as a work around we'll retry the query the old way, but we lose the maxRows protection
-            if (pgConfig.multipleStatements) {
-              return client.query(query, (err, result) => {
-                client.end();
-                if (err) {
-                  return reject(err);
-                }
-                if (params.addRowsToResults) {
-                  // multi-statements returns array of result objects but runQuery should return rows array
-                  // transform array of results objects to flat rows array
-                  if (Array.isArray(result)) {
-                    resultRows = resultRows.concat(
-                      _.flatten(result.map(r => r.rows))
-                    );
-                  } else {
-                    resultRows.push(result.rows);
-                  }
-                  return resolve({ rows: resultRows });
-                }
-              });
-            } else {
-              return reject(
-                new Error('Running multiple statements not allowed')
-              );
-            }
-          }
-          let incomplete = false;
-          if (rows.length === connection.maxRows + 1) {
-            incomplete = true;
-            rows.pop(); // get rid of that extra record. we only get 1 more than the max to see if there would have been more...
-          }
-          if (err) {
-            reject(err);
-          } else if (params.addRowsToResults) {
-            resultRows = resultRows.concat(rows);
-            resolve({ rows: resultRows, incomplete });
-          }
-          if (params.closeConnection) {
-            cursor.close(err => {
-              if (err) {
-                appLog.error(err, 'error closing pg-cursor');
-              }
-              // Calling end() without setImmediate causes error within node-pg
-              setImmediate(() => {
-                client.end(error => {
-                  if (error) {
-                    appLog.error(error);
-                  }
-                });
-              });
-            });
-          }
-        });
-      }
-
-      // Run pre query statements
-      // Statements split by JS because Postgres multipleStatements maybe turned off
-      if (pgConfig.preQueryStatements) {
-        pgConfig.preQueryStatements
-          .trim()
-          .split(';')
-          .forEach(q => {
-            if (q)
-              _runQuery(q, { addRowsToResults: false, closeConnection: false });
-          });
-      }
-
-      // Run actual query
-      _runQuery(query);
-    });
-  });
 }
 
 /**
@@ -182,10 +216,9 @@ function testConnection(connection) {
  * Get schema for connection
  * @param {*} connection
  */
-function getSchema(connection) {
-  return runQuery(SCHEMA_SQL, connection).then(queryResult =>
-    formatSchemaQueryResults(queryResult)
-  );
+async function getSchema(connection) {
+  const queryResult = await runQuery(SCHEMA_SQL, connection);
+  return formatSchemaQueryResults(queryResult);
 }
 
 const fields = [
@@ -274,6 +307,7 @@ const fields = [
 ];
 
 module.exports = {
+  Client,
   id,
   name,
   fields,
diff --git a/server/drivers/unixodbc/index.js b/server/drivers/unixodbc/index.js
index f1ed7eea..b04c2b7c 100644
--- a/server/drivers/unixodbc/index.js
+++ b/server/drivers/unixodbc/index.js
@@ -35,101 +35,15 @@ const SCHEMA_SQL_INFORMATION_SCHEMA = `
  */
 
 async function runQuery(query, connection) {
-  const config = {
-    user: connection.username,
-    password: connection.password,
-    connection_string: connection.connection_string
-  };
-
-  let cn = config.connection_string;
-
-  // Not all drivers require auth
-  if (config.user) {
-    cn = cn + ';Uid=' + config.user;
-  }
-  if (config.password) {
-    cn = cn + ';Pwd=' + config.password;
-  }
-
-  let connectionInstance;
+  const client = new Client(connection);
+  await client.connect();
   try {
-    let incomplete = false;
-    let suppressedResultSet = false;
-    connectionInstance = await odbc.connect(cn);
-    const queries = splitSql(query);
-
-    let queryResult;
-    let lastResult;
-
-    for (const query of queries) {
-      // eslint-disable-next-line no-await-in-loop
-      const result = await connectionInstance.query(query);
-
-      // If result has columns it is a candidate for lastQueryResultWithRows
-      // Until SQLPad has capability to show multiple result sets we are showing the last one with results
-      if (result.columns) {
-        // If queryResult was already set we're suppressing a result set
-        // Eventually we'll show all results but not at this point
-        if (queryResult) {
-          suppressedResultSet = true;
-        }
-
-        queryResult = result;
-      }
-
-      // Keep reference to result as last result
-      lastResult = result;
-    }
-
-    // If queryResult was never populated because none of the queries returned results, use last result
-    if (!queryResult) {
-      queryResult = lastResult;
-    }
-
-    await connectionInstance.close();
-
-    // Format data correctly
-    // node-odbc gives a mix of results depending on query type
-    // If columns oject returned with results the query returned rows
-    const { columns } = queryResult;
-    const rows = [];
-
-    if (columns && columns.length > 0) {
-      // iterate over queryResult, which is also an array of rows
-      for (const row of queryResult) {
-        if (connection.maxRows) {
-          if (rows.length < connection.maxRows) {
-            rows.push(row);
-          } else {
-            incomplete = true;
-          }
-        } else {
-          // Just in case maxRows is not defined push the row
-          rows.push(row);
-        }
-      }
-    }
-
-    return { rows, incomplete, suppressedResultSet };
+    const result = await client.runQuery(query);
+    await client.disconnect();
+    return result;
   } catch (error) {
-    appLog.error(error);
-    try {
-      if (connectionInstance && connectionInstance.close) {
-        await connectionInstance.close();
-      }
-    } catch (error) {
-      // Do nothing here.
-      // An error already happened we're just trying to ensure it closed okay
-      appLog.error(error, 'error closing connection after error');
-    }
-    // unixodb error has additional info about why the error occurred
-    // It has an array of objects with messages.
-    // If that exists try to create a message of everything together and throw that
-    // Otherwise throw what we got
-    if (Array.isArray(error.odbcErrors)) {
-      const message = error.odbcErrors.map(e => e.message).join('; ');
-      throw new Error(message);
-    }
+    // try disconnecting just to be sure
+    await client.disconnect();
     throw error;
   }
 }
@@ -143,7 +57,6 @@ function testConnection(connection) {
   return runQuery(query, connection);
 }
 
-// TODO - reviewed no change needed? datatypes need reviewing
 /**
  * Get schema for connection
  * @param {*} connection
@@ -157,6 +70,119 @@ function getSchema(connection) {
   );
 }
 
+class Client {
+  constructor(connection) {
+    this.connection = connection;
+    this.client = null;
+  }
+
+  async connect() {
+    if (this.client) {
+      throw new Error('Client already connected');
+    }
+
+    const { username, password, connection_string } = this.connection;
+
+    let cn = connection_string;
+
+    // Not all drivers require auth
+    if (username) {
+      cn = cn + ';Uid=' + username;
+    }
+    if (password) {
+      cn = cn + ';Pwd=' + password;
+    }
+
+    this.client = await odbc.connect(cn);
+  }
+
+  /**
+   * Disconnect the connected client
+   * Does not propagate error up
+   */
+  async disconnect() {
+    try {
+      if (this.client && this.client.close) {
+        await this.client.close();
+      }
+    } catch (error) {
+      appLog.error(error, 'error closing connection after error');
+    }
+    this.client = null;
+  }
+
+  async runQuery(query) {
+    try {
+      let incomplete = false;
+      let suppressedResultSet = false;
+      const queries = splitSql(query);
+
+      let queryResult;
+      let lastResult;
+
+      for (const query of queries) {
+        // eslint-disable-next-line no-await-in-loop
+        const result = await this.client.query(query);
+
+        // If result has columns it is a candidate for lastQueryResultWithRows
+        // Until SQLPad has capability to show multiple result sets we are showing the last one with results
+        if (result.columns) {
+          // If queryResult was already set we're suppressing a result set
+          // Eventually we'll show all results but not at this point
+          if (queryResult) {
+            suppressedResultSet = true;
+          }
+
+          queryResult = result;
+        }
+
+        // Keep reference to result as last result
+        lastResult = result;
+      }
+
+      // If queryResult was never populated because none of the queries returned results, use last result
+      if (!queryResult) {
+        queryResult = lastResult;
+      }
+
+      // Format data correctly
+      // node-odbc gives a mix of results depending on query type
+      // If columns oject returned with results the query returned rows
+      const { columns } = queryResult;
+      const rows = [];
+
+      if (columns && columns.length > 0) {
+        // iterate over queryResult, which is also an array of rows
+        for (const row of queryResult) {
+          if (this.connection.maxRows) {
+            if (rows.length < this.connection.maxRows) {
+              rows.push(row);
+            } else {
+              incomplete = true;
+            }
+          } else {
+            // Just in case maxRows is not defined push the row
+            rows.push(row);
+          }
+        }
+      }
+
+      return { rows, incomplete, suppressedResultSet };
+    } catch (error) {
+      appLog.error(error);
+      // unixodb error has additional info about why the error occurred
+      // It has an array of objects with messages.
+      // If that exists try to create a message of everything together and throw that
+      // Otherwise throw what we got
+      if (Array.isArray(error.odbcErrors)) {
+        const message = error.odbcErrors.map(e => e.message).join('; ');
+        throw new Error(message);
+      }
+      throw error;
+    }
+  }
+}
+
 const fields = [
   {
     key: 'connection_string',
@@ -183,10 +209,11 @@ const fields = [
 ];
 
 module.exports = {
-  id,
-  name,
+  Client,
   fields,
   getSchema,
+  id,
+  name,
   runQuery,
   testConnection
 };
diff --git a/server/lib/driver-connection.js b/server/lib/connection-client.js
similarity index 50%
rename from server/lib/driver-connection.js
rename to server/lib/connection-client.js
index 84759c04..acec47ae 100644
--- a/server/lib/driver-connection.js
+++ b/server/lib/connection-client.js
@@ -5,12 +5,14 @@ const appLog = require('./appLog');
 const getMeta = require('./getMeta');
 
 /**
- * Driver connection represents a driver and connection pairing, resulting in a connected database connection.
- * In the future, it will optionally support a persisted connection,
- * maintaining state about whether a connection is connected or not.
- * These persisted connections will be reused across runQuery calls.
+ * Connection client runs queries for a given connection and user
+ * It wraps the driver implementation used by the connection configuration
+ * Older-style driver implementations are one-off functions.
+ * Database connections are made, the user query is run, and then the database connection is closed.
+ * Newer-style driver implementations may include a `Client` class,
+ * which provides the ability to connect and disconnect to the database, and run queries with that connection.
  */
-class DriverConnection {
+class ConnectionClient {
   /**
    * @param {object} connection
    * @param {object} [user] - user to run query under. may not be provided if chart links turned on
@@ -20,6 +22,8 @@ class DriverConnection {
     this.connection = renderConnection(connection, user);
     this.driver = drivers[connection.driver];
     this.user = user;
+    this.Client = this.driver.Client;
+    this.connectedAt = null;
 
     appLog.debug(
       {
@@ -32,8 +36,80 @@ class DriverConnection {
   }
 
   /**
-   * Run query using driver implementation of connection
-   * @param {*} query
+   * Determines whether the connectionClient is connected to the db.
+   * For now the existence of this.client indicates an open connection
+   */
+  isConnected() {
+    return Boolean(this.client);
+  }
+
+  /**
+   * Updates lastKeepAliveAt to indicate a request was made to keep this connection alive.
+   * This may need to actually make a db call if drivers implement an automatic disconnect after some period of inactivity
+   */
+  keepAlive() {
+    if (this.isConnected()) {
+      this.lastKeepAliveAt = new Date();
+      return true;
+    }
+    return false;
+  }
+
+  /**
+   * Create a persistent database connection if the driver implementation supports it.
+   * Also sets up a poll to check on keep alive requests,
+   * and disconnects the connection if a user is not actively using the connection
+   */
+  async connect() {
+    const { Client } = this;
+    if (!Client) {
+      throw new Error('Does not support persistent connection');
+    }
+    this.client = new Client(this.connection);
+    await this.client.connect();
+    this.connectedAt = new Date();
+    this.lastKeepAliveAt = new Date();
+
+    // Every n ms check to see if lastKeepAliveAt is too old
+    // If it is too old, disconnect this
+    this.cleanupInterval = setInterval(() => {
+      appLog.info('Checking last keep alive at');
+      const now = new Date();
+      const TIMEOUT = 30000;
+      if (now - this.lastKeepAliveAt > TIMEOUT) {
+        this.disconnect().catch(error => appLog.error(error));
+        if (this.cleanupInterval) {
+          clearInterval(this.cleanupInterval);
+        }
+        const id = this.id;
+        const connectionName = this.connection && this.connection.name;
+        const driver = this.connection && this.connection.driver;
+        appLog.debug(
+          { id, connectionName, driver },
+          'Disconnecting client connection'
+        );
+      }
+    }, 10000);
+  }
+
+  /**
+   * Close the database connection
+   */
+  async disconnect() {
+    if (this.client) {
+      const client = this.client;
+      this.client = null;
+      await client.disconnect();
+    }
+  }
+
+  /**
+   * Run query
+   * If the connectionClient supports persistent database connections and is connected,
+   * it'll use the database connection already established.
+   * If not connected or does not support persistent connection,
+   * it uses the driver.runQuery() implementation that will open a connection, run query, then close.
+   * @param {string} query
    * @returns {Promise}
    */
   async runQuery(query) {
@@ -69,7 +145,15 @@ class DriverConnection {
 
     let results;
     try {
-      results = await driver.runQuery(query, connection);
+      // If client is connected use that connection,
+      // otherwise use driver.runQuery to run query with fresh one-off connection
+      if (this.isConnected()) {
+        // uses pre-existing connection to run query, and keeps connection open
+        results = await this.client.runQuery(query);
+      } else {
+        // Opens a new connection to db, runs query, then closes connection
+        results = await driver.runQuery(query, connection);
+      }
     } catch (error) {
       // It is logged INFO because it isn't necessarily a server/application error
       // It could just be a bad query
@@ -146,4 +230,4 @@ class DriverConnection {
   }
 }
 
-module.exports = DriverConnection;
+module.exports = ConnectionClient;
diff --git a/server/models/connectionClients.js b/server/models/connectionClients.js
new file mode 100644
index 00000000..166bff27
--- /dev/null
+++ b/server/models/connectionClients.js
@@ -0,0 +1,73 @@
+const ConnectionClient = require('../lib/connection-client');
+
+/**
+ * ConnectionClients is a special in-memory store of connected database clients
+ * Once a connectionClient disconnects, it should no longer be in this store
+ * TODO: A UI could be built for admins to see open connections and close them
+ */
+class ConnectionClients {
+  /**
+   * @param {*} nedb
+   * @param {*} sequelizeDb
+   * @param {import('../lib/config')} config
+   */
+  constructor(nedb, sequelizeDb, config) {
+    this.nedb = nedb;
+    this.sequelizeDb = sequelizeDb;
+    this.config = config;
+    this.connectionClients = [];
+  }
+
+  /**
+   * Get all connection clients
+   * @returns {array}
+   */
+  findAll() {
+    return this.connectionClients.slice();
+  }
+
+  /**
+   * Get connected connection client by id
+   * @param {string} id - id of connection client
+   * @returns {ConnectionClient}
+   */
+  getOneById(id) {
+    return this.connectionClients.find(connectionClient => {
+      return connectionClient.id === id;
+    });
+  }
+
+  /**
+   * Create new connection client and connect
+   * @param {object} connection
+   * @param {object} user
+   * @returns {ConnectionClient}
+   */
+  async createNew(connection, user) {
+    const connectionClient = new ConnectionClient(connection, user);
+    await connectionClient.connect();
+    this.connectionClients.push(connectionClient);
+    return connectionClient;
+  }
+
+  /**
+   * Disconnect connection client for id, and remove it from in-memory store.
+   * Operates under the assumption that the connection could have been removed since its removal was requested
+   * @param {string} id
+   */
+  async disconnectForId(id) {
+    const connectionClient = this.getOneById(id);
+
+    // remove client from array immediately
+    // Disconnecting is async but in-memory state should represent what things will be
+    this.connectionClients = this.connectionClients.filter(connectionClient => {
+      return connectionClient.id !== id;
+    });
+
+    if (connectionClient) {
+      await connectionClient.disconnect();
+    }
+  }
+}
+
+module.exports = ConnectionClients;
diff --git a/server/models/connections.js b/server/models/connections.js
index 1bbdbc9c..a6832349 100644
--- a/server/models/connections.js
+++ b/server/models/connections.js
@@ -1,7 +1,22 @@
 const _ = require('lodash');
 const makeCipher = require('../lib/makeCipher');
+const drivers = require('../drivers');
 const validateConnection = require('../lib/validate-connection');
 
+function addSupportsConnectionClient(connection) {
+  if (!connection) {
+    return connection;
+  }
+  const copy = _.cloneDeep(connection);
+  const driver = drivers[connection.driver];
+  if (!driver) {
+    copy.supportsConnectionClient = false;
+  } else {
+    copy.supportsConnectionClient = Boolean(drivers[connection.driver].Client);
+  }
+  return copy;
+}
+
 class Connections {
   /**
    * @param {*} nedb
@@ -34,16 +49,18 @@ class Connections {
       return this.decipherConnection(conn);
     });
 
-    const allConnections = connectionsFromDb.concat(
-      this.config.getConnections()
-    );
+    const allConnections = connectionsFromDb
+      .concat(this.config.getConnections())
+      .map(connection => addSupportsConnectionClient(connection));
+
     return _.sortBy(allConnections, c => c.name.toLowerCase());
   }
 
   async findOneById(id) {
-    const connection = await this.nedb.connections.findOne({ _id: id });
+    let connection = await this.nedb.connections.findOne({ _id: id });
     if (connection) {
       connection.editable = true;
+      connection = addSupportsConnectionClient(connection);
       return this.decipherConnection(connection);
     }
 
@@ -52,7 +69,10 @@ class Connections {
       .getConnections()
       .find(connection => connection._id === id);
 
-    return connectionFromEnv;
+    if (!connectionFromEnv) {
+      return null;
+    }
+    return addSupportsConnectionClient(connectionFromEnv);
   }
 
   async removeOneById(id) {
diff --git a/server/models/index.js b/server/models/index.js
index 70b4f524..81115627 100644
--- a/server/models/index.js
+++ b/server/models/index.js
@@ -6,6 +6,7 @@ const QueryHistory = require('./queryHistory');
 const Queries = require('./queries');
 const Connections = require('./connections');
 const ConnectionAccesses = require('./connectionAccesses');
+const ConnectionClients = require('./connectionClients');
 const QueryAcl = require('./queryAcl');
 const decorateQueryUserAccess = require('../lib/decorateQueryUserAccess');
 
@@ -18,6 +19,7 @@ class Models {
     this.queries = new Queries(nedb, sequelizeDb, config);
     this.connections = new Connections(nedb, sequelizeDb, config);
     this.connectionAccesses = new ConnectionAccesses(nedb, sequelizeDb, config);
+    this.connectionClients = new ConnectionClients(nedb, sequelizeDb, config);
     this.queryAcl = new QueryAcl(nedb, sequelizeDb, config);
   }
 
diff --git a/server/routes/connection-clients.js b/server/routes/connection-clients.js
new file mode 100644
index 00000000..40e893bf
--- /dev/null
+++ b/server/routes/connection-clients.js
@@ -0,0 +1,208 @@
+require('../typedefs');
+const router = require('express').Router();
+const mustBeAdmin = require('../middleware/must-be-admin.js');
+const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
+const sendError = require('../lib/sendError');
+
+/**
+ * @param {import('express').Request & Req} req
+ * @param {*} res
+ */
+async function listConnectionClients(req, res) {
+  const { models } = req;
+  try {
+    const connectionClients = models.connectionClients
+      .findAll()
+      .map(connectionClient => {
+        return {
+          id: connectionClient.id,
+          name: connectionClient.connection.name,
+          connectedAt: connectionClient.connectedAt,
+          lastKeepAliveAt: connectionClient.lastKeepAliveAt
+        };
+      });
+
+    return res.json({
+      connectionClients
+    });
+  } catch (error) {
+    sendError(res, error, 'Problem listing connection clients');
+  }
+}
+
+router.get('/api/connection-clients', mustBeAdmin, listConnectionClients);
+
+/**
+ * Get a connection client by id
+ * If connection client does is not found it means it was probably disconnected, or never existed
+ * (May want to build out a historical reference of connection clients to be able to tell the difference)
+ * @param {import('express').Request & Req} req
+ * @param {*} res
+ */
+async function getConnectionClient(req, res) {
+  const { models, params, user } = req;
+  try {
+    const connectionClient = models.connectionClients.getOneById(
+      params.connectionClientId
+    );
+
+    if (!connectionClient) {
+      return sendError(res, null, 'Connection disconnected');
+    }
+
+    // Only the owner of the connection or admin can get the client
+    const allowed =
+      connectionClient.user._id === user._id || user.role === 'admin';
+
+    if (!allowed) {
+      return res.status(403).json({ error: 'Forbidden' });
+    }
+
+    const data = {
+      connectionClient: {
+        id: connectionClient.id,
+        name: connectionClient.connection.name,
+        connectedAt: connectionClient.connectedAt,
+        lastKeepAliveAt: connectionClient.lastKeepAliveAt
+      }
+    };
+
+    return res.json(data);
+  } catch (error) {
+    sendError(res, error, 'Problem getting connection client');
+  }
+}
+
+router.get(
+  '/api/connection-clients/:connectionClientId',
+  mustBeAuthenticated,
+  getConnectionClient
+);
+
+/**
+ * Creates and connects a connectionClient
+ * @param {import('express').Request & Req} req
+ * @param {*} res
+ */
+async function createConnectionClient(req, res) {
+  const { models, body, user } = req;
+  try {
+    const { connectionId } = body;
+    if (!connectionId) {
+      return sendError(res, null, 'connectionId required');
+    }
+
+    const connection = await models.connections.findOneById(connectionId);
+    if (!connection) {
+      return sendError(res, null, 'Connection not found');
+    }
+
+    const connectionClient = await models.connectionClients.createNew(
+      connection,
+      user
+    );
+
+    const data = {
+      connectionClient: {
+        id: connectionClient.id,
+        name: connectionClient.connection.name,
+        connectedAt: connectionClient.connectedAt,
+        lastKeepAliveAt: connectionClient.lastKeepAliveAt
+      }
+    };
+
+    return res.json(data);
+  } catch (error) {
+    sendError(res, error, 'Problem creating connection client');
+  }
+}
+
+router.post(
+  '/api/connection-clients',
+  mustBeAuthenticated,
+  createConnectionClient
+);
+
+/**
+ * Creates and connects a connectionClient
+ * @param {import('express').Request & Req} req
+ * @param {*} res
+ */
+async function keepAliveConnectionClient(req, res) {
+  const { models, params, user } = req;
+  try {
+    const connectionClient = models.connectionClients.getOneById(
+      params.connectionClientId
+    );
+
+    // If no connection client it was already closed
+    // This is effectively a no-op
+    if (!connectionClient) {
+      return res.json({});
+    }
+
+    // Only the owner of the connection client can keep client alive
+    const allowed = connectionClient.user._id === user._id;
+
+    if (!allowed) {
+      return res.status(403).json({ error: 'Forbidden' });
+    }
+
+    connectionClient.keepAlive();
+
+    const data = {
+      connectionClient: {
+        id: connectionClient.id,
+        name: connectionClient.connection.name,
+        connectedAt: connectionClient.connectedAt,
+        lastKeepAliveAt: connectionClient.lastKeepAliveAt
+      }
+    };
+
+    return res.json(data);
+  } catch (error) {
+    sendError(res, error, 'Problem updating connection client');
+  }
+}
+
+router.put(
+  '/api/connection-clients/:connectionClientId',
+  mustBeAuthenticated,
+  keepAliveConnectionClient
+);
+
+/**
+ * Creates and connects a connectionClient
+ * @param {import('express').Request & Req} req
+ * @param {*} res
+ */
+async function disconnectConnectionClient(req, res) {
+  const { models, params, user } = req;
+  const { connectionClientId } = params;
+  try {
+    const connectionClient = models.connectionClients.getOneById(
+      params.connectionClientId
+    );
+
+    // Only the owner of the connection or admin can disconnect the client
+    const allowed =
+      connectionClient.user._id === user._id || user.role === 'admin';
+
+    if (!allowed) {
+      return res.status(403).json({ error: 'Forbidden' });
+    }
+
+    await models.connectionClients.disconnectForId(connectionClientId);
+    return res.json({});
+  } catch (error) {
+    sendError(res, error, 'Problem disconnecting connection client');
+  }
+}
+
+router.delete(
+  '/api/connection-clients/:connectionClientId',
+  mustBeAuthenticated,
+  disconnectConnectionClient
+);
+
+module.exports = router;
diff --git a/server/routes/drivers.js b/server/routes/drivers.js
index 6a4f8eb1..ae266ed2 100644
--- a/server/routes/drivers.js
+++ b/server/routes/drivers.js
@@ -8,10 +8,12 @@ router.get('/api/drivers', mustBeAuthenticated, function(req, res) {
   const debug = config.get('debug');
 
   const driversArray = Object.keys(drivers).map(id => {
+    const supportsConnectionClient = Boolean(drivers[id].Client);
     return {
       id,
       name: drivers[id].name,
-      fields: drivers[id].fields
+      fields: drivers[id].fields,
+      supportsConnectionClient
     };
   });
 
diff --git a/server/routes/query-result.js b/server/routes/query-result.js
index 64a96d13..6931e425 100644
--- a/server/routes/query-result.js
+++ b/server/routes/query-result.js
@@ -1,8 +1,9 @@
+require('../typedefs');
 const router = require('express').Router();
 const mustHaveConnectionAccess = require('../middleware/must-have-connection-access.js');
 const mustHaveConnectionAccessOrChartLink = require('../middleware/must-have-connection-access-or-chart-link-noauth');
 const sendError = require('../lib/sendError');
-const DriverConnection = require('../lib/driver-connection');
+const ConnectionClient = require('../lib/connection-client');
 
 // This allows executing a query relying on the saved query text
 // Instead of relying on an open endpoint that executes arbitrary sql
@@ -43,13 +44,16 @@ router.post('/api/query-result', mustHaveConnectionAccess, async function(
   req,
   res
 ) {
+  const { body, user } = req;
+
   const data = {
-    cacheKey: req.body.cacheKey,
-    connectionId: req.body.connectionId,
-    queryId: req.body.queryId,
-    queryName: req.body.queryName,
-    queryText: req.body.queryText,
-    user: req.user
+    cacheKey: body.cacheKey,
+    connectionId: body.connectionId,
+    queryId: body.queryId,
+    queryName: body.queryName,
+    queryText: body.queryText,
+    connectionClientId: body.connectionClientId,
+    user
   };
 
   try {
@@ -62,21 +66,47 @@ router.post('/api/query-result', mustHaveConnectionAccess, async function(
   }
 });
 
+/**
+ * @param {import('express').Request & Req} req
+ * @param {object} data
+ */
 async function getQueryResult(req, data) {
-  const { models } = req;
-  const { connectionId, cacheKey, queryId, queryName, queryText, user } = data;
+  const { models, config } = req;
+  const {
+    connectionId,
+    connectionClientId,
+    cacheKey,
+    queryId,
+    queryName,
+    queryText,
+    user
+  } = data;
+
+  let queryResult;
+
   const connection = await models.connections.findOneById(connectionId);
 
   if (!connection) {
     throw new Error('Please choose a connection');
   }
-  connection.maxRows = Number(req.config.get('queryResultMaxRows'));
 
-  const driverConnection = new DriverConnection(connection, user);
-  const queryResult = await driverConnection.runQuery(queryText);
+  if (connectionClientId) {
+    const connectionClient = models.connectionClients.getOneById(
+      connectionClientId
+    );
+    if (!connectionClient) {
+      throw new Error('Connection client disconnected');
+    }
+    queryResult = await connectionClient.runQuery(queryText);
+  } else {
+    connection.maxRows = Number(config.get('queryResultMaxRows'));
+    const connectionClient = new ConnectionClient(connection, user);
+    queryResult = await connectionClient.runQuery(queryText);
+  }
+
   queryResult.cacheKey = cacheKey;
 
-  if (req.config.get('queryHistoryRetentionTimeInDays') > 0) {
+  if (config.get('queryHistoryRetentionTimeInDays') > 0) {
     await models.queryHistory.removeOldEntries();
     await models.queryHistory.save({
       userId: user._id,
@@ -94,7 +124,7 @@ async function getQueryResult(req, data) {
     });
   }
 
-  if (req.config.get('allowCsvDownload')) {
+  if (config.get('allowCsvDownload')) {
     models.resultCache.saveResultCache(cacheKey, queryName);
     await models.resultCache.writeXlsx(cacheKey, queryResult);
     await models.resultCache.writeCsv(cacheKey, queryResult);
diff --git a/server/routes/schema-info.js b/server/routes/schema-info.js
index 3b89c67c..4fd6ffc8 100644
--- a/server/routes/schema-info.js
+++ b/server/routes/schema-info.js
@@ -1,7 +1,7 @@
 const router = require('express').Router();
 const mustHaveConnectionAccess = require('../middleware/must-have-connection-access.js');
 const sendError = require('../lib/sendError');
-const DriverConnection = require('../lib/driver-connection');
+const ConnectionClient = require('../lib/connection-client');
 
 router.get(
   '/api/schema-info/:connectionId',
@@ -24,8 +24,8 @@ router.get(
         return res.json({ schemaInfo });
       }
 
-      const driverConnection = new DriverConnection(conn, user);
-      schemaInfo = await driverConnection.getSchema();
+      const connectionClient = new ConnectionClient(conn, user);
+      schemaInfo = await connectionClient.getSchema();
       if (Object.keys(schemaInfo).length) {
         await models.schemaInfo.saveSchemaInfo(connectionId, schemaInfo);
       }


Test patch:
diff --git a/server/drivers/mock/test.js b/server/drivers/mock/test.js
index 1fe6373f..825bcbf2 100644
--- a/server/drivers/mock/test.js
+++ b/server/drivers/mock/test.js
@@ -46,4 +46,36 @@ describe('drivers/mock', function() {
       assert.equal(results.rows.length, 10, 'row length');
     });
   });
+
+  it('Client cannot connect more than once', async function() {
+    const client = new mock.Client(connection);
+    await client.connect();
+    await assert.rejects(client.connect());
+    await client.disconnect();
+  });
+
+  it('Client handles multiple disconnects', async function() {
+    const client = new mock.Client(connection);
+    await client.connect();
+    await client.disconnect();
+    await client.disconnect();
+  });
+
+  it('Client handles multiple runQuery calls', async function() {
+    const client = new mock.Client(connection);
+    await client.connect();
+
+    const results1 = await client.runQuery(`
+      -- dimensions = product 1, color 1, orderdate 10
+    `);
+    assert.equal(results1.incomplete, false);
+    assert.equal(results1.rows.length, 10);
+    const results2 = await client.runQuery(`
+    -- dimensions = product 1, color 1, orderdate 10
+  `);
+    assert.equal(results2.incomplete, false);
+    assert.equal(results2.rows.length, 10);
+
+    await client.disconnect();
+  });
 });
diff --git a/server/drivers/postgres/test.js b/server/drivers/postgres/test.js
index ff1e246f..3745d739 100644
--- a/server/drivers/postgres/test.js
+++ b/server/drivers/postgres/test.js
@@ -12,15 +12,30 @@ const connection = {
 };
 
 describe('drivers/postgres', function() {
+  // I'm unable to use the test.sh script that stands up the docker-compose prior to running tests
+  // Instead I'm running postgres, and running mocha directly
+  // which means the table created for testing needs to be cleared out each run, but won't exist the first time
+  before(async function() {
+    await postgres.runQuery('DROP TABLE IF EXISTS sqlpad_test;', connection);
+    await postgres.runQuery(
+      'CREATE TABLE sqlpad_test (id INT, name TEXT);',
+      connection
+    );
+  });
+
   it('tests connection', function() {
     return postgres.testConnection(connection);
   });
 
   it('getSchema()', function() {
     return postgres.getSchema(connection).then(schemaInfo => {
-      // Should probably create tables and validate them here
-      // For now this is a smoke test of sorts
-      assert(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);
     });
   });
 
@@ -41,4 +56,53 @@ describe('drivers/postgres', function() {
         assert.equal(results.rows.length, 100, 'row length');
       });
   });
+
+  // This isn't officially supported across all drivers
+  // Official multiple statement support will start to look much different
+  // This is a test for legacy allowed postgres behavior
+  it('runQuery multiple statements', async function() {
+    const query = `
+      SELECT * FROM generate_series(1, 10) g1;
+      SELECT * FROM generate_series(1, 10) g2;
+    `;
+    const results = await postgres.runQuery(query, connection);
+    assert.strictEqual(results.incomplete, false);
+    assert.strictEqual(results.rows.length, 20);
+    assert.strictEqual(results.rows[0].g1, 1);
+    assert.strictEqual(results.rows[0].g2, undefined);
+    assert.strictEqual(results.rows[10].g1, undefined);
+    assert.strictEqual(results.rows[10].g2, 1);
+  });
+
+  it('Client cannot connect more than once', async function() {
+    const client = new postgres.Client(connection);
+    await client.connect();
+    await assert.rejects(client.connect());
+    await client.disconnect();
+  });
+
+  it('Client handles multiple disconnects', async function() {
+    const client = new postgres.Client(connection);
+    await client.connect();
+    await client.disconnect();
+    await client.disconnect();
+  });
+
+  it('Client handles multiple runQuery calls', async function() {
+    const client = new postgres.Client(connection);
+    await client.connect();
+
+    const results1 = await client.runQuery(
+      'SELECT * FROM generate_series(1, 10) g1'
+    );
+    assert.equal(results1.incomplete, false);
+    assert.equal(results1.rows.length, 10);
+    const results2 = await client.runQuery(
+      'SELECT * FROM generate_series(1, 10) g1'
+    );
+    assert.equal(results2.incomplete, false);
+    assert.equal(results2.rows.length, 10);
+
+    await client.disconnect();
+  });
 });
diff --git a/server/drivers/unixodbc/test.js b/server/drivers/unixodbc/test.js
index ae014e10..9e47c60e 100644
--- a/server/drivers/unixodbc/test.js
+++ b/server/drivers/unixodbc/test.js
@@ -1,6 +1,10 @@
 const assert = require('assert');
 const unixodbc = require('./index.js');
 
+// Using Windows? You may need to change your db path to something like
+// process.env.ODBC_CONNECTION_STRING =
+//   'Driver={SQLite3 ODBC Driver};Database=C:\\Users\\<your_user_dir>\\sqlite_test.sqlite';
+
 const connection = {
   connection_string: process.env.ODBC_CONNECTION_STRING, // I.e. ensure os variable is set to connection string
   schema_sql: `
@@ -15,10 +19,11 @@ const connection = {
 };
 const test_schema_name = 'dba'; // sqlite3 does not really have owner
 
-const createTable = 'CREATE TABLE test (id INTEGER, name TEXT );'; // NOTE test(s) will fail if table already exists, expect empty database
-const insert1 = "INSERT INTO test (id, name) VALUES (1, 'one');";
-const insert2 = "INSERT INTO test (id, name) VALUES (2, 'two');";
-const insert3 = "INSERT INTO test (id, name) VALUES (3, 'three');";
+const dropTable = 'DROP TABLE IF EXISTS sqlpad_test;';
+const createTable = 'CREATE TABLE sqlpad_test (id INTEGER, name TEXT );'; // NOTE test(s) will fail if table already exists, expect empty database
+const insert1 = "INSERT INTO sqlpad_test (id, name) VALUES (1, 'one');";
+const insert2 = "INSERT INTO sqlpad_test (id, name) VALUES (2, 'two');";
+const insert3 = "INSERT INTO sqlpad_test (id, name) VALUES (3, 'three');";
 
 // TODO test more datatypes:
 //   * integer (different sizes
@@ -30,13 +35,13 @@ const insert3 = "INSERT INTO test (id, name) VALUES (3, 'three');";
 //   * datetime
 //   * interval
 describe('drivers/unixodbc', function() {
-  before(function() {
+  before(async function() {
     this.timeout(10000);
-    return unixodbc
-      .runQuery(createTable, connection)
-      .then(() => unixodbc.runQuery(insert1, connection))
-      .then(() => unixodbc.runQuery(insert2, connection))
-      .then(() => unixodbc.runQuery(insert3, connection));
+    await unixodbc.runQuery(dropTable, connection);
+    await unixodbc.runQuery(createTable, connection);
+    await unixodbc.runQuery(insert1, connection);
+    await unixodbc.runQuery(insert2, connection);
+    await unixodbc.runQuery(insert3, connection);
   });
 
   it('tests connection', function() {
@@ -46,51 +51,53 @@ 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].test, test_schema_name + '.test');
-      const columns = schemaInfo[test_schema_name].test;
+      assert(
+        schemaInfo[test_schema_name].sqlpad_test,
+        test_schema_name + '.sqlpad_test'
+      );
+      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, 'test', 'table_name');
+      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');
     });
   });
 
-  it('runQuery under limit', function() {
-    return unixodbc
-      .runQuery('SELECT * FROM test WHERE id = 1;', connection)
-      .then(results => {
-        assert(!results.incomplete, 'not incomplete');
-        assert.equal(results.rows.length, 1, 'row length');
-      });
+  it('runQuery under limit', async function() {
+    const results = await unixodbc.runQuery(
+      'SELECT * FROM sqlpad_test WHERE id = 1;',
+      connection
+    );
+    assert(!results.incomplete, 'not incomplete');
+    assert.equal(results.rows.length, 1, 'row length');
   });
 
-  it('runQuery over limit', function() {
+  it('runQuery over limit', async function() {
     const connectionWithMaxRows = { ...connection, maxRows: 2 };
-    return unixodbc
-      .runQuery('SELECT * FROM test;', connectionWithMaxRows)
-      .then(results => {
-        assert(results.incomplete, 'incomplete');
-        assert.equal(results.rows.length, 2, 'row length');
-      });
+    const results = await unixodbc.runQuery(
+      'SELECT * FROM sqlpad_test;',
+      connectionWithMaxRows
+    );
+    assert(results.incomplete, 'incomplete');
+    assert.equal(results.rows.length, 2, 'row length');
   });
 
-  it('Runs multiple statements', function() {
+  it('Runs multiple statements', async function() {
     const query = `
-      SELECT id FROM test;
-      SELECT name from test;
-      SELECT * FROM test WHERE id = 2
+      SELECT id FROM sqlpad_test;
+      SELECT name FROM sqlpad_test;
+      SELECT * FROM sqlpad_test WHERE id = 2
     `;
-    return unixodbc.runQuery(query, connection).then(results => {
-      // incomplete indicates truncated results
-      // suppressedResultSet indicates missing set
-      assert.strictEqual(results.suppressedResultSet, true);
-      assert.strictEqual(results.incomplete, false);
-      assert.equal(results.rows.length, 1, 'row length');
-      assert.strictEqual(results.rows[0].id, 2);
-      assert.strictEqual(results.rows[0].name, 'two');
-    });
+    const results = await unixodbc.runQuery(query, connection);
+    // incomplete indicates truncated results
+    // suppressedResultSet indicates missing set
+    assert.strictEqual(results.suppressedResultSet, true);
+    assert.strictEqual(results.incomplete, false);
+    assert.equal(results.rows.length, 1, 'row length');
+    assert.strictEqual(results.rows[0].id, 2);
+    assert.strictEqual(results.rows[0].name, 'two');
   });
 
   it('Throws helpful error', async function() {
@@ -106,4 +113,32 @@ describe('drivers/unixodbc', function() {
       'Error message has table reference'
     );
   });
+
+  it('Client cannot connect more than once', async function() {
+    const client = new unixodbc.Client(connection);
+    await client.connect();
+    await assert.rejects(client.connect());
+    await client.disconnect();
+  });
+
+  it('Client handles multiple disconnects', async function() {
+    const client = new unixodbc.Client(connection);
+    await client.connect();
+    await client.disconnect();
+    await client.disconnect();
+  });
+
+  it('Client handles multiple runQuery calls', async function() {
+    const client = new unixodbc.Client(connection);
+    await client.connect();
+
+    const results1 = await client.runQuery('SELECT * FROM sqlpad_test');
+    assert.equal(results1.incomplete, false);
+    assert.equal(results1.rows.length, 3);
+    const results2 = await client.runQuery('SELECT * FROM sqlpad_test');
+    assert.equal(results2.incomplete, false);
+    assert.equal(results2.rows.length, 3);
+
+    await client.disconnect();
+  });
 });
diff --git a/server/routes/test-connection.js b/server/routes/test-connection.js
index 06d07615..3a337f16 100644
--- a/server/routes/test-connection.js
+++ b/server/routes/test-connection.js
@@ -1,15 +1,15 @@
 const router = require('express').Router();
 const mustBeAdmin = require('../middleware/must-be-admin.js');
 const sendError = require('../lib/sendError');
-const DriverConnection = require('../lib/driver-connection');
+const ConnectionClient = require('../lib/connection-client');
 
 /**
  * A non-error response is considered a success or valid connection config
  */
 router.post('/api/test-connection', mustBeAdmin, async function(req, res) {
   try {
-    const driverConnection = new DriverConnection(req.body, req.user);
-    await driverConnection.testConnection();
+    const connectionClient = new ConnectionClient(req.body, req.user);
+    await connectionClient.testConnection();
     res.send({ success: true });
   } catch (error) {
     sendError(res, error);
diff --git a/server/test/api/connection-clients.js b/server/test/api/connection-clients.js
new file mode 100644
index 00000000..bee8e4a5
--- /dev/null
+++ b/server/test/api/connection-clients.js
@@ -0,0 +1,142 @@
+const assert = require('assert').strict;
+const TestUtils = require('../utils');
+
+describe('api/connection-accesses', function() {
+  const utils = new TestUtils();
+  let connection1;
+  let connectionClient1;
+
+  before(async function() {
+    await utils.init(true);
+    let connBody = await utils.post('admin', '/api/connections', {
+      name: 'test connection 1',
+      driver: 'mock',
+      host: 'localhost',
+      database: 'sqlpad',
+      username: 'sqlpad',
+      password: 'sqlpad',
+      wait: 10
+    });
+    connection1 = connBody.connection;
+  });
+
+  it('creates a connection client', async function() {
+    const body = await utils.post('editor', '/api/connection-clients', {
+      connectionId: connection1._id
+    });
+    const { connectionClient } = body;
+    connectionClient1 = connectionClient;
+    assert.equal(connectionClient.name, 'test connection 1');
+    assert(connectionClient.id);
+    assert(connectionClient.connectedAt);
+    assert(connectionClient.lastKeepAliveAt);
+  });
+
+  it('creator is allowed to get client', async function() {
+    const body = await utils.get(
+      'editor',
+      `/api/connection-clients/${connectionClient1.id}`
+    );
+    assert(!body.error);
+    assert.equal(body.connectionClient.id, connectionClient1.id);
+  });
+
+  it('admin is allowed to get client', async function() {
+    const body = await utils.get(
+      'admin',
+      `/api/connection-clients/${connectionClient1.id}`
+    );
+    assert(!body.error);
+    assert.equal(body.connectionClient.id, connectionClient1.id);
+  });
+
+  it('Non-creator non-admin is not allowed to get client', async function() {
+    const body = await utils.get(
+      'editor2',
+      `/api/connection-clients/${connectionClient1.id}`,
+      403
+    );
+    assert(body.error);
+  });
+
+  it('does not allow editor access to list', async function() {
+    await utils.get('editor', '/api/connection-clients', 403);
+  });
+
+  it('allows admin to list connection clients', async function() {
+    const body = await utils.get('admin', '/api/connection-clients');
+    assert.equal(body.connectionClients.length, 1);
+    const connectionClient = body.connectionClients[0];
+    assert.equal(connectionClient.name, 'test connection 1');
+    assert(connectionClient.id);
+    assert(connectionClient.connectedAt);
+    assert(connectionClient.lastKeepAliveAt);
+  });
+
+  it('Creator can keepAlive', async function() {
+    const body = await utils.put(
+      'editor',
+      `/api/connection-clients/${connectionClient1.id}`
+    );
+    const { connectionClient } = body;
+    assert.equal(connectionClient.name, 'test connection 1');
+    assert(connectionClient.id);
+    assert(connectionClient.connectedAt);
+    assert(
+      connectionClient.lastKeepAliveAt > connectionClient1.lastKeepAliveAt
+    );
+  });
+
+  it('Admin cannot keepAlive', async function() {
+    await utils.put(
+      'admin',
+      `/api/connection-clients/${connectionClient1.id}`,
+      null,
+      403
+    );
+  });
+
+  it('Non-creator cannot keepAlive', async function() {
+    await utils.put(
+      'editor2',
+      `/api/connection-clients/${connectionClient1.id}`,
+      null,
+      403
+    );
+  });
+
+  it('Non-creator non-admin cannot disconnect client', async function() {
+    await utils.del(
+      'editor2',
+      `/api/connection-clients/${connectionClient1.id}`,
+      403
+    );
+  });
+
+  it('Creator can disconnect client', async function() {
+    const delBody = await utils.del(
+      'editor',
+      `/api/connection-clients/${connectionClient1.id}`
+    );
+    assert(!delBody.error);
+
+    // connection client should now be removed
+    const body = await utils.get('admin', '/api/connection-clients');
+    assert.equal(body.connectionClients.length, 0);
+  });
+
+  it('Admin can disconnect client', async function() {
+    const { connectionClient } = await utils.post(
+      'editor',
+      '/api/connection-clients',
+      {
+        connectionId: connection1._id
+      }
+    );
+    const delBody = await utils.del(
+      'admin',
+      `/api/connection-clients/${connectionClient.id}`
+    );
+    assert(!delBody.error);
+  });
+});
diff --git a/server/test/api/connections.js b/server/test/api/connections.js
index 5c886bc1..8f6ad3b8 100644
--- a/server/test/api/connections.js
+++ b/server/test/api/connections.js
@@ -30,12 +30,20 @@ describe('api/connections', function() {
     assert(body.connection._id, 'has _id');
     assert.equal(body.connection.driver, 'postgres');
     assert.equal(body.connection.username, 'username');
+
+    // As of writing this test, only postgres and mock connections should have this set to true
+    assert.equal(body.connection.supportsConnectionClient, true);
+
     connection = body.connection;
   });
 
-  it('Gets array of 1', async function() {
-    const body = await utils.get('admin', '/api/connections');
-    assert.equal(body.connections.length, 1, '0 length');
+  it('Gets list of connections', async function() {
+    const { connections } = await utils.get('admin', '/api/connections');
+    assert.equal(connections.length, 1);
+    const connection = connections[0];
+
+    // supportsConnectionClient expected to be set for list API as well
+    assert.equal(connection.supportsConnectionClient, true);
   });
 
   it('Updates connection', async function() {
@@ -57,6 +65,7 @@ describe('api/connections', function() {
     assert.equal(body.connection.name, 'test connection update');
     assert.equal(body.connection.driver, 'postgres');
     assert.equal(body.connection.username, 'username');
+    assert.equal(body.connection.supportsConnectionClient, true);
   });
 
   it('Gets updated connection', async function() {
diff --git a/server/test/api/drivers.js b/server/test/api/drivers.js
index c6146f08..ae7e4f9f 100644
--- a/server/test/api/drivers.js
+++ b/server/test/api/drivers.js
@@ -12,9 +12,11 @@ describe('api/drivers', function() {
     const body = await utils.get('editor', '/api/drivers');
     const { drivers, error } = body;
     assert(!error, 'Expect no error');
+
     const postgres = drivers.find(i => i.id === 'postgres');
     assert(postgres, 'has postgres');
     assert.strictEqual(postgres.name, 'Postgres');
+    assert.strictEqual(postgres.supportsConnectionClient, true);
     assert(Array.isArray(postgres.fields));
     assert(
       postgres.fields.find(field => field.key === 'postgresSsl'),
@@ -24,5 +26,9 @@ describe('api/drivers', function() {
       !postgres.fields.find(field => field.key === 'sqlserverEncrypt'),
       'Does not have a SQL Server field'
     );
+
+    const mysql = drivers.find(i => i.id === 'mysql');
+    assert(mysql);
+    assert.strictEqual(mysql.supportsConnectionClient, false);
   });
 });
diff --git a/server/test/lib/driver-connection.js b/server/test/lib/driver-connection.js
deleted file mode 100644
index e035103d..00000000
--- a/server/test/lib/driver-connection.js
+++ /dev/null
@@ -1,20 +0,0 @@
-const assert = require('assert');
-const DriverConnection = require('../../lib/driver-connection');
-
-describe('DriverConnection', function() {
-  it('loads and exposes api', async function() {
-    const driverConnection = new DriverConnection({
-      driver: 'postgres',
-      name: 'test'
-    });
-
-    assert(typeof driverConnection.getSchema === 'function');
-    assert(typeof driverConnection.runQuery === 'function');
-    assert(typeof driverConnection.testConnection === 'function');
-    // Notes for later. These methods will be added eventually
-    // assert(typeof driverConnection.connect === 'function');
-    // assert(typeof driverConnection.disconnect === 'function');
-    // assert(typeof driverConnection.keepAlive === 'function');
-    // assert(typeof driverConnection.getStatus === 'function');
-  });
-});