Repository: rickbergfalk/sqlpad
Pull request #515 (2020-01-12 12:41:04)

Add Pre-query statements options to Postgres driver
## Description

This PR is the same as #511 but for Postgres driver.

This PR adds optional **Pre-query statements** option to Postgres driver. This is one ore more SQL queries that run before the actual query but in the same session. This is useful to enforce some session variables whenever it's required. Typical use-case is to avoid long running queries from SQLPad by setting session variables (ie. `SET statement_timeout = x`) before running the queries.

Also added the **Deny multiple statements** queries option to avoid to overwrite pre-query statements by running multi-statements queries from the UI.

## Screenshots

<img width="550" alt="Screenshot 2020-01-12 at 12 39 24" src="https://user-images.githubusercontent.com/643687/72219001-1b8f3700-3539-11ea-9ea8-8cdcc898b017.png">



_**PS**.: this is my last PR and no more on my list at the moment ;)_



Patch:
diff --git a/README.md b/README.md
index a232c77f..1550fcb4 100644
--- a/README.md
+++ b/README.md
@@ -203,6 +203,16 @@ Allows access on every connection to every user.
 Env var: `SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE`  
 Default: `true`
 
+**queryHistoryRetentionTimeInDays**  
+Query history entries created before the retention period will be deleted automatically.  
+Env var: `SQLPAD_QUERY_HISTORY_RETENTION_PERIOD_IN_DAYS`  
+Default: `30`
+
+**queryHistoryResultMaxRows**  
+By default query history results are limited to 1,000 records.  
+Env var: `SQLPAD_QUERY_HISTORY_RESULT_MAX_ROWS`  
+Default: `1000`
+
 ### Connection configuration
 
 As of 3.2.0 connections may be defined via application configuration.
@@ -458,4 +468,4 @@ When using JSON file, provide `<connectionId>` as a key under `connections`.
 
 ## License
 
-MIT
+MIT
\ No newline at end of file
diff --git a/client/src/queryEditor/toolbar/Toolbar.js b/client/src/queryEditor/toolbar/Toolbar.js
index ebb10cc4..032b1f5c 100644
--- a/client/src/queryEditor/toolbar/Toolbar.js
+++ b/client/src/queryEditor/toolbar/Toolbar.js
@@ -30,6 +30,7 @@ import { toggleSchema } from '../../stores/schema';
 import UserList from '../../users/UserList';
 import fetchJson from '../../utilities/fetch-json.js';
 import ConnectionDropDown from '../ConnectionDropdown';
+import QueryHistoryModal from '../../queryHistory/QueryHistoryModal';
 import AboutModal from './AboutModal';
 import ChartButton from './ChartButton';
 import QueryListButton from './QueryListButton';
@@ -89,6 +90,7 @@ function Toolbar({
   const [showTags, setShowTags] = useState(false);
   const [showUsers, setShowUsers] = useState(false);
   const [redirectToSignIn, setRedirectToSignIn] = useState(false);
+  const [showQueryHistory, setShowQueryHistory] = useState(false);
   const [showAbout, setShowAbout] = useState(false);
   const [showConnections, setShowConnections] = useState(false);
   const [showConnectionAccesses, setShowConnectionAccesses] = useState(false);
@@ -203,6 +205,13 @@ function Toolbar({
 
         <IconMenu icon={<DotsVerticalIcon aria-label="menu" />}>
           {menuItems}
+          <MenuItem
+            key="queryHistory"
+            style={{ borderBottom: '1px solid #ddd' }}
+            onSelect={() => setShowQueryHistory(true)}
+          >
+            Query History
+          </MenuItem>
           <MenuItem
             style={{ borderBottom: '1px solid #ddd' }}
             onSelect={() => setShowAbout(true)}
@@ -229,6 +238,11 @@ function Toolbar({
           <UserList />
         </Drawer>
 
+        <QueryHistoryModal
+          visible={showQueryHistory}
+          onClose={() => setShowQueryHistory(false)}
+        />
+
         <AboutModal visible={showAbout} onClose={() => setShowAbout(false)} />
 
         <ConnectionListDrawer
diff --git a/client/src/queryHistory/QueryHistoryContent.js b/client/src/queryHistory/QueryHistoryContent.js
new file mode 100644
index 00000000..2fac6b4f
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryContent.js
@@ -0,0 +1,177 @@
+import React, { useEffect, useState } from 'react';
+import QueryHistoryFilterItem from './QueryHistoryFilterItem';
+import QueryHistoryResultHeader from './QueryHistoryResultHeader';
+import QueryResultContainer from '../common/QueryResultContainer';
+import fetchJson from '../utilities/fetch-json.js';
+import Button from '../common/Button';
+
+function QueryHistoryContent({ onConnectionAccessSaved }) {
+  const [isRunning, setIsRunning] = useState(true);
+  const [filters, setFilters] = useState([]);
+  const [queryError, setQueryError] = useState(null);
+  const [queryHistory, setQueryHistory] = useState({});
+
+  useEffect(() => {
+    function buildFilterUrlParameter() {
+      const urlFilters = filters.map(f => {
+        if (['before', 'after'].includes(f.operator)) {
+          try {
+            f.value = new Date(f.value).toISOString();
+          } catch (error) {
+            f.value = error.message;
+          }
+        }
+        return `${f.field}|${f.operator}|${f.value}`;
+      });
+      return urlFilters.join(',');
+    }
+
+    async function getQueryHistory() {
+      const json = await fetchJson(
+        'GET',
+        `/api/query-history?filter=${buildFilterUrlParameter()}`
+      );
+
+      setIsRunning(false);
+      setQueryError(json.error);
+      setQueryHistory(json.queryHistory);
+    }
+
+    if (isRunning) {
+      getQueryHistory();
+    }
+  }, [filters, isRunning]);
+
+  const handleRefresh = () => {
+    setIsRunning(true);
+  };
+
+  const setFilterValue = (index, filterItem) => {
+    const newFilter = [...filters];
+    if ('field' in filterItem) {
+      newFilter[index].field = filterItem.field;
+    }
+    if ('operator' in filterItem) {
+      newFilter[index].operator = filterItem.operator;
+    }
+    if ('value' in filterItem) {
+      newFilter[index].value = filterItem.value;
+    }
+
+    setFilters(newFilter);
+  };
+
+  const handleAddFilter = () => {
+    const newFilters = [...filters];
+    newFilters.push({ field: 'userEmail', operator: 'regex', value: '' });
+    setFilters(newFilters);
+  };
+
+  const handleRemoveFilter = index => {
+    const newFilters = [...filters];
+    newFilters.splice(index, 1);
+    setFilters(newFilters);
+  };
+
+  const handleApplyFilter = e => {
+    setIsRunning(true);
+  };
+
+  let filterForm;
+  const buttons = [
+    <Button
+      style={{ width: 135 }}
+      key="refresh"
+      type="primary"
+      onClick={handleRefresh}
+      disabled={isRunning}
+    >
+      {isRunning ? 'Searching...' : 'Refresh'}
+    </Button>
+  ];
+  if (filters.length === 0) {
+    buttons.unshift(
+      <Button
+        style={{ width: 135, marginRight: 8 }}
+        key="addFilter"
+        type="primary"
+        onClick={handleAddFilter}
+      >
+        Add Filter
+      </Button>
+    );
+  } else {
+    filterForm = (
+      <form
+        onSubmit={handleApplyFilter}
+        autoComplete="off"
+        style={{
+          display: 'flex',
+          flexDirection: 'column',
+          height: '100%'
+        }}
+      >
+        <p>Display queries that meet all of the following criteria:</p>
+        {filters.map((filterItem, index) => {
+          return (
+            <QueryHistoryFilterItem
+              index={index}
+              key={index}
+              filter={filterItem}
+              onChange={setFilterValue}
+              onAddFilter={
+                index === filters.length - 1 ? handleAddFilter : null
+              }
+              onRemoveFilter={handleRemoveFilter}
+            />
+          );
+        })}
+        <br />
+        <Button
+          htmlType="submit"
+          style={{ width: 120 }}
+          type="primary"
+          onClick={handleApplyFilter}
+          disabled={isRunning}
+        >
+          {isRunning ? 'Searching...' : 'Search'}
+        </Button>{' '}
+      </form>
+    );
+  }
+
+  return (
+    <>
+      <div style={{ display: 'flex', justifyContent: 'flex-end' }}>
+        {buttons}
+      </div>
+      <div style={{ display: 'flex', flexDirection: 'column' }}>
+        {filterForm}
+      </div>
+      <br />
+      <QueryHistoryResultHeader
+        isRunning={isRunning}
+        queryResult={queryHistory}
+        runQueryStartTime={null}
+      />
+      <div style={{ display: 'flex', flexGrow: 1, height: '100%' }}>
+        <div
+          style={{
+            position: 'relative',
+            width: '100%',
+            height: '100%',
+            border: '1px solid #CCC'
+          }}
+        >
+          <QueryResultContainer
+            isRunning={isRunning}
+            queryResult={queryHistory}
+            queryError={queryError}
+          />
+        </div>
+      </div>
+    </>
+  );
+}
+
+export default QueryHistoryContent;
diff --git a/client/src/queryHistory/QueryHistoryFilterItem.js b/client/src/queryHistory/QueryHistoryFilterItem.js
new file mode 100644
index 00000000..4381267f
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryFilterItem.js
@@ -0,0 +1,115 @@
+import React from 'react';
+import Select from '../common/Select';
+import Input from '../common/Input';
+import Button from '../common/Button';
+
+const QueryHistoryFilterItem = ({
+  index,
+  filter,
+  onChange,
+  onAddFilter,
+  onRemoveFilter
+}) => {
+  const operators = {
+    greaterThan: { key: 'gt', label: 'Greater than' },
+    lowerThan: { key: 'lt', label: 'Lower than' },
+    equals: { key: 'eq', label: 'Equals' },
+    notEqual: { key: 'ne', label: 'Not equal' },
+    before: { key: 'before', label: 'Before' },
+    after: { key: 'after', label: 'After' },
+    regex: { key: 'regex', label: 'Contains' }
+  };
+  const fields = {
+    userEmail: { label: 'userEmail', operators: [operators.regex] },
+    connectionName: { label: 'connectionName', operators: [operators.regex] },
+    startTime: {
+      label: 'startTime',
+      operators: [operators.before, operators.after]
+    },
+    queryRunTime: {
+      label: 'queryRunTime',
+      operators: [operators.greaterThan, operators.lowerThan]
+    },
+    queryId: { label: 'queryId', operators: [operators.regex] },
+    queryName: { label: 'queryName', operators: [operators.regex] },
+    queryText: { label: 'queryText', operators: [operators.regex] },
+    rowCount: {
+      label: 'rowCount',
+      operators: [
+        operators.lowerThan,
+        operators.greaterThan,
+        operators.equals,
+        operators.notEqual
+      ]
+    }
+  };
+  const buttons = [];
+  if (onRemoveFilter) {
+    buttons.push(
+      <Button
+        key="removeFilter"
+        htmlType="button"
+        style={{ width: '20px' }}
+        onClick={e => onRemoveFilter(index)}
+      >
+        -
+      </Button>
+    );
+  }
+  if (onAddFilter) {
+    buttons.push(
+      <Button
+        key="addFilter"
+        htmlType="button"
+        style={{ width: '20px' }}
+        onClick={onAddFilter}
+      >
+        +
+      </Button>
+    );
+  }
+
+  return (
+    <div style={{ display: 'flex' }}>
+      <Select
+        style={{ width: '200px', marginRight: 8 }}
+        name="field"
+        value={filter.field}
+        onChange={e =>
+          onChange(index, {
+            field: e.target.value,
+            operator: fields[e.target.value].operators[0].key
+          })
+        }
+      >
+        {Object.keys(fields).map(f => (
+          <option key={f} value={f}>
+            {fields[f].label}
+          </option>
+        ))}
+      </Select>
+
+      <Select
+        style={{ width: '150px', marginRight: 8 }}
+        name="operator"
+        value={filter.operator}
+        onChange={e => onChange(index, { operator: e.target.value })}
+      >
+        {fields[filter.field].operators.map(o => (
+          <option key={o.key} value={o.key}>
+            {o.label}
+          </option>
+        ))}
+      </Select>
+      <Input
+        style={{ width: '300px', marginRight: 8 }}
+        name="value"
+        value={filter.value}
+        onChange={e => onChange(index, { value: e.target.value })}
+      />
+      {buttons}
+    </div>
+  );
+};
+
+export default QueryHistoryFilterItem;
diff --git a/client/src/queryHistory/QueryHistoryModal.js b/client/src/queryHistory/QueryHistoryModal.js
new file mode 100644
index 00000000..79faeef4
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryModal.js
@@ -0,0 +1,18 @@
+import React from 'react';
+import Modal from '../common/Modal';
+import QueryHistoryContent from './QueryHistoryContent';
+
+function QueryHistoryModal({ visible, onClose }) {
+  return (
+    <Modal
+      width="90%"
+      title="Query History"
+      visible={visible}
+      onClose={onClose}
+    >
+      <QueryHistoryContent />
+    </Modal>
+  );
+}
+
+export default QueryHistoryModal;
diff --git a/client/src/queryHistory/QueryHistoryResultHeader.js b/client/src/queryHistory/QueryHistoryResultHeader.js
new file mode 100644
index 00000000..bb98aa15
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryResultHeader.js
@@ -0,0 +1,50 @@
+import PropTypes from 'prop-types';
+import React from 'react';
+import IncompleteDataNotification from '../common/IncompleteDataNotification';
+import SecondsTimer from '../common/SecondsTimer.js';
+import styles from './QueryHistoryResultHeader.module.css';
+
+function QueryHistoryResultHeader({
+  isRunning,
+  queryResult,
+  runQueryStartTime
+}) {
+  if (isRunning || !queryResult) {
+    return (
+      <div className={styles.toolbar}>
+        {isRunning ? (
+          <span className={styles.toolbarItem}>
+            Query time: <SecondsTimer startTime={runQueryStartTime} />
+          </span>
+        ) : null}
+      </div>
+    );
+  }
+
+  const serverSec = queryResult ? queryResult.queryRunTime / 1000 : '';
+  const rowCount =
+    queryResult && queryResult.rows ? queryResult.rows.length : '';
+
+  const incomplete = queryResult ? queryResult.incomplete : false;
+
+  return (
+    <div className={styles.toolbar}>
+      <div className={styles.toolbarItem}>{serverSec} seconds</div>
+      <div className={styles.toolbarItem}>{rowCount} rows</div>
+
+      {incomplete && <IncompleteDataNotification />}
+    </div>
+  );
+}
+
+QueryHistoryResultHeader.propTypes = {
+  isRunning: PropTypes.bool,
+  queryResult: PropTypes.object,
+  runQueryStartTime: PropTypes.instanceOf(Date)
+};
+
+QueryHistoryResultHeader.defaultProps = {
+  isRunning: false
+};
+
+export default QueryHistoryResultHeader;
diff --git a/client/src/queryHistory/QueryHistoryResultHeader.module.css b/client/src/queryHistory/QueryHistoryResultHeader.module.css
new file mode 100644
index 00000000..e8a745e7
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryResultHeader.module.css
@@ -0,0 +1,28 @@
+.toolbar {
+  height: 30px;
+  border-bottom: 1px solid #ccc;
+  background-color: #f4f4f4;
+  /* line-height: 30px; */
+  padding-left: 4px;
+  display: flex;
+  align-items: center;
+}
+
+.toolbarItem {
+  padding-right: 48px;
+  padding-left: 4px;
+}
+
+.iconLinkWrapper {
+  height: 30px;
+  display: inline-flex;
+  align-items: center;
+  line-height: 1;
+}
+
+.iconLink {
+  margin-left: 16px;
+  display: inline-flex;
+  align-items: center;
+  line-height: 1;
+}
diff --git a/client/src/queryHistory/QueryHistoryRunning.js b/client/src/queryHistory/QueryHistoryRunning.js
new file mode 100644
index 00000000..07de4f85
--- /dev/null
+++ b/client/src/queryHistory/QueryHistoryRunning.js
@@ -0,0 +1,12 @@
+import React from 'react';
+import SpinKitCube from './SpinKitCube.js';
+
+function QueryResultRunning() {
+  return (
+    <div className="h-100 flex-center">
+      <SpinKitCube />
+    </div>
+  );
+}
+
+export default QueryResultRunning;
diff --git a/client/src/stores/queries.js b/client/src/stores/queries.js
index 01e6f239..0f8887a8 100644
--- a/client/src/stores/queries.js
+++ b/client/src/stores/queries.js
@@ -110,6 +110,7 @@ export const runQuery = store => async state => {
   const postData = {
     connectionId: selectedConnectionId,
     cacheKey,
+    queryId: query._id,
     queryName: query.name,
     queryText: selectedText || query.queryText
   };
diff --git a/server/app.js b/server/app.js
index 712085e6..f2d4c384 100644
--- a/server/app.js
+++ b/server/app.js
@@ -90,6 +90,7 @@ const routers = [
   require('./routes/connection-accesses.js'),
   require('./routes/test-connection.js'),
   require('./routes/queries.js'),
+  require('./routes/query-history.js'),
   require('./routes/query-result.js'),
   require('./routes/download-results.js'), // streams result download to browser
   require('./routes/schema-info.js'),
diff --git a/server/drivers/postgres/index.js b/server/drivers/postgres/index.js
index 83a3bb6d..42e9a4b1 100644
--- a/server/drivers/postgres/index.js
+++ b/server/drivers/postgres/index.js
@@ -61,7 +61,9 @@ function runQuery(query, connection) {
     database: connection.database,
     host: connection.host,
     ssl: connection.postgresSsl,
-    stream: createSocksConnection(connection)
+    stream: createSocksConnection(connection),
+    multipleStatements: !connection.denyMultipleStatements,
+    preQueryStatements: connection.preQueryStatements
   };
   // TODO cache key/cert values
   if (connection.postgresKey && connection.postgresCert) {
@@ -77,57 +79,92 @@ function runQuery(query, connection) {
 
   return new Promise((resolve, reject) => {
     const client = new pg.Client(pgConfig);
+    let resultRows = [];
+
     client.connect(err => {
       if (err) {
         client.end();
         return reject(err);
       }
-      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
-          return client.query(query, (err, result) => {
-            client.end();
-            if (err) {
-              return reject(err);
-            }
-            // 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 = _.flatten(result.map(r => r.rows));
+
+      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 {
-              resultRows = result.rows;
+              return reject(
+                new Error('Running multiple statements not allowed')
+              );
             }
-            return resolve({ rows: resultRows });
-          });
-        }
-        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 {
-          resolve({ rows, incomplete });
-        }
-        cursor.close(err => {
+          }
+          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) {
-            console.log('error closing pg-cursor:');
-            console.log(err);
+            reject(err);
+          } else if (params.addRowsToResults) {
+            resultRows = resultRows.concat(rows);
+            resolve({ rows: resultRows, incomplete });
           }
-          // Calling end() without setImmediate causes error within node-pg
-          setImmediate(() => {
-            client.end(error => {
-              if (error) {
-                console.error(error);
+          if (params.closeConnection) {
+            cursor.close(err => {
+              if (err) {
+                console.log('error closing pg-cursor:');
+                console.log(err);
               }
+              // Calling end() without setImmediate causes error within node-pg
+              setImmediate(() => {
+                client.end(error => {
+                  if (error) {
+                    console.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);
     });
   });
 }
@@ -221,6 +258,18 @@ const fields = [
     key: 'socksPassword',
     formType: 'TEXT',
     label: 'Password for socks proxy'
+  },
+  {
+    key: 'denyMultipleStatements',
+    formType: 'CHECKBOX',
+    label: 'Deny multiple statements per query'
+  },
+  {
+    key: 'preQueryStatements',
+    formType: 'TEXTAREA',
+    label: 'Pre-query Statements (Optional)',
+    placeholder:
+      'Use to enforce session variables like:\n  SET statement_timeout = 15000;\n\nDeny multiple statements per query to avoid overwritten values.'
   }
 ];
 
diff --git a/server/lib/config/configItems.js b/server/lib/config/configItems.js
index 5d719435..720c6858 100644
--- a/server/lib/config/configItems.js
+++ b/server/lib/config/configItems.js
@@ -198,6 +198,16 @@ const configItems = [
     key: 'allowConnectionAccessToEveryone',
     envVar: 'SQLPAD_ALLOW_CONNECTION_ACCESS_TO_EVERYONE',
     default: true
+  },
+  {
+    key: 'queryHistoryRetentionTimeInDays',
+    envVar: 'SQLPAD_QUERY_HISTORY_RETENTION_TIME_IN_DAYS',
+    default: 30
+  },
+  {
+    key: 'queryHistoryResultMaxRows',
+    envVar: 'SQLPAD_QUERY_HISTORY_RESULT_MAX_ROWS',
+    default: 1000
   }
 ];
 
diff --git a/server/lib/db.js b/server/lib/db.js
index cb5cd226..2cffd200 100644
--- a/server/lib/db.js
+++ b/server/lib/db.js
@@ -25,8 +25,16 @@ const db = {
     filename: path.join(dbPath, 'connectionaccesses.db')
   }),
   queries: datastore({ filename: path.join(dbPath, 'queries.db') }),
+  queryHistory: datastore({ filename: path.join(dbPath, 'queryhistory.db') }),
   cache: datastore({ filename: path.join(dbPath, 'cache.db') }),
-  instances: ['users', 'connections', 'connectionAccesses', 'queries', 'cache']
+  instances: [
+    'users',
+    'connections',
+    'connectionAccesses',
+    'queries',
+    'queryHistory',
+    'cache'
+  ]
 };
 
 // Load dbs, migrate data, and apply indexes
@@ -67,6 +75,9 @@ async function init() {
   await db.cache.ensureIndex({ fieldName: 'cacheKey', unique: true });
   await db.connectionAccesses.ensureIndex({ fieldName: 'connectionId' });
   await db.connectionAccesses.ensureIndex({ fieldName: 'userId' });
+  await db.queryHistory.ensureIndex({ fieldName: 'connectionName' });
+  await db.queryHistory.ensureIndex({ fieldName: 'userEmail' });
+  await db.queryHistory.ensureIndex({ fieldName: 'createdDate' });
   // set autocompaction
   const tenMinutes = 1000 * 60 * 10;
   db.instances.forEach(dbname => {
diff --git a/server/lib/urlFilterToNeDbFilter.js b/server/lib/urlFilterToNeDbFilter.js
new file mode 100644
index 00000000..899bbc1b
--- /dev/null
+++ b/server/lib/urlFilterToNeDbFilter.js
@@ -0,0 +1,76 @@
+/**
+ * Transforms URL formatted filter parameters to NeDB compatible filter objects
+ * @param {string} url formatted filter (i.e. queryText|regex|DELETE)
+ * @returns {object} NeDB compatible filter object
+ */
+module.exports = function urlFilterToNeDbFilter(urlFilter) {
+  let neDbFilter = {};
+  if (typeof urlFilter === 'string') {
+    neDbFilter = {
+      $and: urlFilter
+        .trim()
+        .split(',')
+        .map(f => {
+          let neDbFilterObj = {};
+          if (f) {
+            let [key, operator, value] = f.split('|');
+
+            // Do nothing if no value
+            if (!value) {
+              neDbFilterObj = {};
+
+              // Transform only if every required component available
+            } else if (key && operator && value) {
+              let neDbOperator = operator;
+              let neDbValue = value;
+              switch (operator) {
+                case 'regex':
+                  neDbOperator = `$${operator}`;
+                  neDbValue = new RegExp(value);
+                  break;
+                case 'lt':
+                case 'gt':
+                case 'eq':
+                case 'ne':
+                  neDbOperator = `$${operator}`;
+                  neDbValue = parseInt(value);
+                  break;
+                case 'before':
+                  neDbOperator = '$lt';
+                  neDbValue = new Date(
+                    Date.parse(new Date(value).toISOString())
+                  );
+                  break;
+                case 'after':
+                  neDbOperator = '$gt';
+                  neDbValue = new Date(
+                    Date.parse(new Date(value).toISOString())
+                  );
+                  break;
+                default:
+                  neDbOperator = `$${operator}`;
+                  neDbValue = value;
+              }
+
+              // NeDB filter object format
+              if (neDbOperator === '$eq') {
+                neDbFilterObj = { [key]: neDbValue };
+              } else {
+                neDbFilterObj = {
+                  [key]: { [neDbOperator]: neDbValue }
+                };
+              }
+
+              // Unknown format
+            } else {
+              throw new Error(`Invalid filter format: ${f}`);
+            }
+          }
+
+          return neDbFilterObj;
+        })
+    };
+  }
+
+  return neDbFilter;
+};
diff --git a/server/models/queryHistory.js b/server/models/queryHistory.js
new file mode 100644
index 00000000..77177dec
--- /dev/null
+++ b/server/models/queryHistory.js
@@ -0,0 +1,70 @@
+const db = require('../lib/db.js');
+const Joi = require('@hapi/joi');
+const config = require('../lib/config');
+
+const schema = Joi.object({
+  _id: Joi.string().optional(), // generated by nedb
+  userId: Joi.string().required(),
+  userEmail: Joi.string().required(),
+  connectionId: Joi.string().required(),
+  connectionName: Joi.string().required(),
+  startTime: Joi.date(),
+  stopTime: Joi.date(),
+  queryRunTime: Joi.number().integer(),
+  queryId: Joi.string().allow(''),
+  queryName: Joi.string().allow(''),
+  queryText: Joi.string().required(),
+  rowCount: Joi.number().integer(),
+  incomplete: Joi.boolean(),
+  createdDate: Joi.date().default(new Date())
+});
+
+function findOneById(id) {
+  return db.queryHistory.findOne({ _id: id });
+}
+
+async function findAll() {
+  return db.queryHistory
+    .cfind({}, {})
+    .sort({ startTime: -1 })
+    .exec();
+}
+
+function findByFilter(filter) {
+  return db.queryHistory
+    .cfind(filter || {}, {})
+    .sort({ startTime: -1 })
+    .limit(config.get('queryHistoryResultMaxRows'))
+    .exec();
+}
+
+async function removeOldEntries() {
+  const days = config.get('queryHistoryRetentionTimeInDays') * 86400 * 1000;
+  const retentionPeriodStartTime = new Date(new Date().getTime() - days);
+
+  // Compaction function called separately in every ten minutes
+  return db.queryHistory.remove(
+    { createdDate: { $lt: retentionPeriodStartTime } },
+    { multi: true }
+  );
+}
+/**
+ * Save queryHistory object
+ * returns saved queryHistory object
+ * @param {object} queryHistory
+ */
+async function save(data) {
+  const joiResult = schema.validate(data);
+  if (joiResult.error) {
+    return Promise.reject(joiResult.error);
+  }
+  return db.queryHistory.insert(joiResult.value);
+}
+
+module.exports = {
+  findOneById,
+  findAll,
+  findByFilter,
+  removeOldEntries,
+  save
+};
diff --git a/server/routes/query-history.js b/server/routes/query-history.js
new file mode 100644
index 00000000..bb8cef86
--- /dev/null
+++ b/server/routes/query-history.js
@@ -0,0 +1,65 @@
+const router = require('express').Router();
+const uuid = require('uuid');
+const getMeta = require('../lib/getMeta');
+const queryHistoryUtil = require('../models/queryHistory.js');
+const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
+const urlFilterToNeDbFilter = require('../lib/urlFilterToNeDbFilter');
+const sendError = require('../lib/sendError');
+const config = require('../lib/config');
+
+router.get('/api/query-history', mustBeAuthenticated, async function(req, res) {
+  try {
+    const queryHistory = {
+      id: uuid.v4(),
+      cacheKey: null,
+      startTime: new Date(),
+      stopTime: null,
+      queryRunTime: null,
+      fields: [],
+      incomplete: false,
+      meta: {},
+      rows: []
+    };
+
+    // Convert URL filter to NeDB compatible filter object
+    const dbFilter = urlFilterToNeDbFilter(req.query.filter);
+    const dbQueryHistory = await queryHistoryUtil.findByFilter(dbFilter);
+
+    dbQueryHistory.map(q => {
+      delete q._id;
+      delete q.userId;
+      delete q.connectionId;
+      return q;
+    });
+
+    queryHistory.incomplete =
+      dbQueryHistory.length >= config.get('queryHistoryResultMaxRows');
+    queryHistory.rows = dbQueryHistory;
+    queryHistory.stopTime = new Date();
+    queryHistory.queryRunTime =
+      dbQueryHistory.stopTime - dbQueryHistory.startTime;
+    queryHistory.meta = getMeta(dbQueryHistory);
+    queryHistory.fields = Object.keys(queryHistory.meta);
+
+    return res.json({ queryHistory });
+  } catch (error) {
+    sendError(res, error, error.message);
+  }
+});
+
+router.get('/api/query-history/:_id', mustBeAuthenticated, async function(
+  req,
+  res
+) {
+  try {
+    const queryHistoryItem = await queryHistoryUtil.findOneById(req.params._id);
+    if (!queryHistoryItem) {
+      return sendError(res, null, 'Query history item not found');
+    }
+    return res.json({ queryHistoryItem });
+  } catch (error) {
+    sendError(res, error, 'Problem querying query history database');
+  }
+});
+
+module.exports = router;
diff --git a/server/routes/query-result.js b/server/routes/query-result.js
index 184997b7..0362befb 100644
--- a/server/routes/query-result.js
+++ b/server/routes/query-result.js
@@ -3,6 +3,7 @@ const { runQuery } = require('../drivers/index');
 const connections = require('../models/connections.js');
 const resultCache = require('../models/resultCache.js');
 const queriesUtil = require('../models/queries.js');
+const queryHistory = require('../models/queryHistory');
 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');
@@ -22,6 +23,7 @@ router.get(
       const data = {
         connectionId: query.connectionId,
         cacheKey: query._id,
+        queryId: query._id,
         queryName: query.name,
         queryText: query.queryText,
         user: req.user
@@ -48,6 +50,7 @@ router.post('/api/query-result', mustHaveConnectionAccess, async function(
   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
@@ -62,7 +65,7 @@ router.post('/api/query-result', mustHaveConnectionAccess, async function(
 });
 
 async function getQueryResult(data) {
-  const { connectionId, cacheKey, queryName, queryText, user } = data;
+  const { connectionId, cacheKey, queryId, queryName, queryText, user } = data;
   const connection = await connections.findOneById(connectionId);
 
   if (!connection) {
@@ -73,6 +76,24 @@ async function getQueryResult(data) {
   const queryResult = await runQuery(queryText, connection, user);
   queryResult.cacheKey = cacheKey;
 
+  if (config.get('queryHistoryRetentionTimeInDays') > 0) {
+    await queryHistory.removeOldEntries();
+    await queryHistory.save({
+      userId: user._id,
+      userEmail: user.email,
+      connectionId: connection._id,
+      connectionName: connection.name,
+      startTime: queryResult.startTime,
+      stopTime: queryResult.stopTime,
+      queryRunTime: queryResult.queryRunTime,
+      queryId,
+      queryName,
+      queryText,
+      incomplete: queryResult.incomplete,
+      rowCount: queryResult.rows.length
+    });
+  }
+
   if (config.get('allowCsvDownload')) {
     resultCache.saveResultCache(cacheKey, queryName);
     await resultCache.writeXlsx(cacheKey, queryResult);


Test patch:
diff --git a/server/test/api/query-history.js b/server/test/api/query-history.js
new file mode 100644
index 00000000..9f15525d
--- /dev/null
+++ b/server/test/api/query-history.js
@@ -0,0 +1,169 @@
+const assert = require('assert');
+const utils = require('../utils');
+const urlFilterToNeDbFilter = require('../../lib/urlFilterToNeDbFilter');
+
+const queryText1 = `
+  -- QUERY1
+  -- dimensions = department 10, orderdate 10
+  -- measures = cost, revenue, profit
+  -- orderby = department desc, orderdate asc
+  -- limit = 100
+`;
+
+const queryText2 = `
+  -- QUERY2
+  -- dimensions = department 10, orderdate 10
+  -- measures = cost, revenue, profit
+  -- orderby = department desc, orderdate asc
+  -- limit = 100
+`;
+
+describe('api/query-history', function() {
+  let connection;
+  let query1;
+
+  before(async function() {
+    await utils.resetWithUser();
+
+    const connBody = await utils.post('admin', '/api/connections', {
+      name: 'test postgres',
+      driver: 'mock',
+      host: 'localhost',
+      database: 'sqlpad',
+      username: 'sqlpad',
+      password: 'sqlpad'
+    });
+    connection = connBody.connection;
+
+    const body = await utils.post('admin', '/api/queries', {
+      name: 'test query 1',
+      tags: ['test', 'postgres'],
+      connectionId: connection._id,
+      queryText: queryText1
+    });
+    query1 = body.query;
+  });
+
+  it('Convert URL filters to NeDB compatibles', function() {
+    // String operators
+    assert.deepEqual(urlFilterToNeDbFilter('field1|regex|myPattern'), {
+      $and: [{ field1: { $regex: new RegExp('myPattern') } }]
+    });
+
+    // Numeric operators
+    assert.deepEqual(urlFilterToNeDbFilter('field1|lt|123'), {
+      $and: [{ field1: { $lt: 123 } }]
+    });
+    assert.deepEqual(urlFilterToNeDbFilter('field1|gt|123'), {
+      $and: [{ field1: { $gt: 123 } }]
+    });
+    assert.deepEqual(urlFilterToNeDbFilter('field1|ne|123'), {
+      $and: [{ field1: { $ne: 123 } }]
+    });
+    assert.deepEqual(urlFilterToNeDbFilter('field1|eq|123'), {
+      $and: [{ field1: 123 }]
+    });
+
+    // Datetime operators
+    assert.deepEqual(urlFilterToNeDbFilter('field1|before|2020-03-01'), {
+      $and: [{ field1: { $lt: new Date('2020-03-01T00:00:00.000Z') } }]
+    });
+    assert.deepEqual(
+      urlFilterToNeDbFilter('field1|before|2020-03-01 00:00:00'),
+      {
+        $and: [{ field1: { $lt: new Date('2020-03-01T00:00:00.000Z') } }]
+      }
+    );
+    assert.deepEqual(urlFilterToNeDbFilter('field1|after|2020-03-01'), {
+      $and: [{ field1: { $gt: new Date('2020-03-01T00:00:00.000Z') } }]
+    });
+    assert.deepEqual(
+      urlFilterToNeDbFilter('field1|after|2020-03-01 00:00:00'),
+      {
+        $and: [{ field1: { $gt: new Date('2020-03-01T00:00:00.000Z') } }]
+      }
+    );
+
+    // Multiple filter conditions
+    const multiUrlFilter =
+      'field1|eq|500,field2|regex|myPattern,field3|before|2020-03-01';
+    assert.deepEqual(urlFilterToNeDbFilter(multiUrlFilter), {
+      $and: [
+        { field1: 500 },
+        { field2: { $regex: new RegExp('myPattern') } },
+        { field3: { $lt: new Date('2020-03-01T00:00:00.000Z') } }
+      ]
+    });
+  });
+
+  it('Gets array of 0 items', async function() {
+    const body = await utils.get('admin', '/api/query-history');
+    assert(!body.error, 'Expect no error');
+    assert(Array.isArray(body.queryHistory.rows), 'queryHistory is an array');
+    assert.equal(body.queryHistory.incomplete, false, 'Complete');
+    assert.equal(body.queryHistory.rows.length, 0, '0 length');
+  });
+
+  it('Gets array of 4 items', async function() {
+    // Run some queries to generate query history by saved queries
+    await utils.get('admin', `/api/query-result/${query1._id}`);
+    await utils.get('admin', `/api/query-result/${query1._id}`);
+
+    // Run some queries to generate query history directly from the query editor
+    await utils.post('admin', `/api/query-result`, {
+      connectionId: connection._id,
+      cacheKey: 'cachekey',
+      queryText: queryText2
+    });
+    await utils.post('admin', `/api/query-result`, {
+      connectionId: connection._id,
+      cacheKey: 'cachekey',
+      queryText: queryText2
+    });
+
+    // Check if every query stored in query history
+    const body = await utils.get('admin', '/api/query-history');
+    assert(!body.error, 'Expect no error');
+    assert(Array.isArray(body.queryHistory.rows), 'queryHistory is an array');
+    assert.equal(body.queryHistory.incomplete, false, 'Complete');
+    assert.equal(body.queryHistory.rows.length, 4, '4 length');
+
+    // Check if every history entry has every required key
+    const historyObjectKeys = [
+      'userEmail',
+      'connectionName',
+      'startTime',
+      'stopTime',
+      'queryRunTime',
+      'queryId',
+      'queryName',
+      'queryText',
+      'incomplete',
+      'rowCount',
+      'createdDate'
+    ];
+
+    // First and second two history items (reverse ordered) needs to free text query with queryId and queryName
+    assert.deepEqual(Object.keys(body.queryHistory.rows[3]), historyObjectKeys);
+    assert.deepEqual(Object.keys(body.queryHistory.rows[2]), historyObjectKeys);
+
+    // Third and fourth history items (reverse ordered) needs to saved text query with no queryId and queryName
+    historyObjectKeys.splice(historyObjectKeys.indexOf('queryId'), 1);
+    historyObjectKeys.splice(historyObjectKeys.indexOf('queryName'), 1);
+    assert.deepEqual(Object.keys(body.queryHistory.rows[1]), historyObjectKeys);
+    assert.deepEqual(Object.keys(body.queryHistory.rows[0]), historyObjectKeys);
+  });
+
+  it('Gets filtered array of 2 items', async function() {
+    // Check if filters applied correctly
+    const body = await utils.get(
+      'admin',
+      '/api/query-history?filter=queryText|regex|QUERY2'
+    );
+
+    assert(!body.error, 'Expect no error');
+    assert(Array.isArray(body.queryHistory.rows), 'queryHistory is an array');
+    assert.equal(body.queryHistory.incomplete, false, 'Complete');
+    assert.equal(body.queryHistory.rows.length, 2, '2 length');
+  });
+});
diff --git a/server/test/utils.js b/server/test/utils.js
index cd452949..ef48c7c9 100644
--- a/server/test/utils.js
+++ b/server/test/utils.js
@@ -28,6 +28,7 @@ function reset() {
   return Promise.all([
     db.users.remove({}, { multi: true }),
     db.queries.remove({}, { multi: true }),
+    db.queryHistory.remove({}, { multi: true }),
     db.connections.remove({}, { multi: true }),
     db.connectionAccesses.remove(
       {