Repository: rickbergfalk/sqlpad
Pull request #512 (2020-01-06 19:01:11)

Add Query history
## Description

Adding query history for audit-log purposes. Sometimes we need to know who ran specific queries at a certain time period or keep security and auditor teams happy with audit-logs. This PR creates `queryhistory.db` in NeDB and inserts new item with query details every time when a query executed. The UI provides a form to search certain items in the history.

To avoid large memory footprint introducing a new **queryHistoryRetentionTimeInDays** variable that defaults to 30 days. Every historical query after the retention time will be deleted and compacted automatically in NeDB. If set to 0 then queries not inserted into query history.

## Screenshots

1. New option in the menu:

![Screenshot 2020-01-06 at 18 48 52](https://user-images.githubusercontent.com/643687/71840455-41d65200-30b5-11ea-8e6a-bfbf2997dfa2.png)

2. Filter options in query history window:

![Screenshot 2020-01-06 at 18 47 21](https://user-images.githubusercontent.com/643687/71840516-5f0b2080-30b5-11ea-9beb-0688e7069664.png)



koszti: @rickbergfalk I have one more smaller pending PR on my list: Pre-query statements option for postgres. Similar to #511 but for pg.

Once this PR and the above one is completed (will send the PR later) we will have every required item to start using sqlpad on prod. I hope it will be useful for others as well.
rickbergfalk: @koszti did you want to add query id to history in this PR or should I go ahead and merge as is? Ran this locally to try it out and I'd be okay either way.
koszti: I'll update this PR and add the query id
koszti: @rickbergfalk added `queryId` and `queryName` to query history by 6df0666aa79d944b08f6f67f1283668d6173c0c5. Added some extra tests as well and now this PR is completed.
rickbergfalk: Thanks @koszti! 

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/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(
       {