Repository: rickbergfalk/sqlpad
Pull request #474 (2019-10-09 02:12:58)

Convert Model object/classes to utility functions
One of the regrets I have with all the v2 work I did was using a model "class" type API with database interactions. I personally found it confusing and not very node-like, and only did it because I thought it was the way real software did things 😂 

`this` is one of the difficult things about JavaScript. Using basic functions removes the need to deal with `this` and also makes it easier to write unit tests if necessary down the road (thankfully the testing in SQLPad is mostly integration tests, so this refactor didn't actually require many test changes).


Patch:
diff --git a/server/lib/pushQueryToSlack.js b/server/lib/pushQueryToSlack.js
new file mode 100644
index 00000000..9b937e6c
--- /dev/null
+++ b/server/lib/pushQueryToSlack.js
@@ -0,0 +1,33 @@
+const config = require('./config');
+const request = require('request');
+
+function pushQueryToSlack(query) {
+  const SLACK_WEBHOOK = config.get('slackWebhook');
+  if (SLACK_WEBHOOK) {
+    const PUBLIC_URL = config.get('publicUrl');
+    const BASE_URL = config.get('baseUrl');
+
+    const options = {
+      method: 'post',
+      body: {
+        text: `New Query <${PUBLIC_URL}${BASE_URL}/queries/${query._id}|${
+          query.name
+        }> 
+            saved by ${query.modifiedBy} on SQLPad 
+            ${'```'}
+            ${query.queryText}
+            ${'```'}`
+      },
+      json: true,
+      url: SLACK_WEBHOOK
+    };
+    request(options, function(err) {
+      if (err) {
+        console.error('Something went wrong while sending to Slack.');
+        console.error(err);
+      }
+    });
+  }
+}
+
+module.exports = pushQueryToSlack;
diff --git a/server/middleware/passport.js b/server/middleware/passport.js
index 1581c2e1..b0020673 100644
--- a/server/middleware/passport.js
+++ b/server/middleware/passport.js
@@ -3,7 +3,7 @@ const PassportLocalStrategy = require('passport-local').Strategy;
 const PassportGoogleStrategy = require('passport-google-oauth20').Strategy;
 const BasicStrategy = require('passport-http').BasicStrategy;
 const SamlStrategy = require('passport-saml').Strategy;
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const config = require('../lib/config');
 const checkWhitelist = require('../lib/check-whitelist.js');
 const passhash = require('../lib/passhash.js');
@@ -26,7 +26,7 @@ passport.serializeUser(function(user, done) {
 
 passport.deserializeUser(async function(id, done) {
   try {
-    const user = await User.findOneById(id);
+    const user = await usersUtil.findOneById(id);
     if (user) {
       return done(null, {
         id: user._id,
@@ -49,7 +49,7 @@ if (!disableUserpassAuth) {
       },
       async function passportLocalStrategyHandler(email, password, done) {
         try {
-          const user = await User.findOneByEmail(email);
+          const user = await usersUtil.findOneByEmail(email);
           if (!user) {
             return done(null, false, { message: 'wrong email or password' });
           }
@@ -76,7 +76,7 @@ if (!disableUserpassAuth) {
   passport.use(
     new BasicStrategy(async function(username, password, callback) {
       try {
-        const user = await User.findOneByEmail(username);
+        const user = await usersUtil.findOneByEmail(username);
         if (!user) {
           return callback(null, false);
         }
@@ -124,7 +124,7 @@ if (samlEntryPoint) {
           p[
             'http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress'
           ];
-        const user = await User.findOneByEmail(email);
+        const user = await usersUtil.findOneByEmail(email);
         console.log(`User logged in with SAML as ${email}`);
         if (!user) {
           return done(null, false);
@@ -156,24 +156,23 @@ async function passportGoogleStrategyHandler(
 
   try {
     let [openAdminRegistration, user] = await Promise.all([
-      User.adminRegistrationOpen(),
-      User.findOneByEmail(email)
+      usersUtil.adminRegistrationOpen(),
+      usersUtil.findOneByEmail(email)
     ]);
 
     if (user) {
       user.signupDate = new Date();
-      const newUser = await user.save();
+      const newUser = await usersUtil.save(user);
       newUser.id = newUser._id;
       return done(null, newUser);
     }
     const whitelistedDomains = config.get('whitelistedDomains');
     if (openAdminRegistration || checkWhitelist(whitelistedDomains, email)) {
-      user = new User({
+      const newUser = await usersUtil.save({
         email,
         role: openAdminRegistration ? 'admin' : 'editor',
         signupDate: new Date()
       });
-      const newUser = await user.save();
       newUser.id = newUser._id;
       return done(null, newUser);
     }
diff --git a/server/models/Cache.js b/server/models/Cache.js
deleted file mode 100644
index b2f4e599..00000000
--- a/server/models/Cache.js
+++ /dev/null
@@ -1,136 +0,0 @@
-const fs = require('fs');
-const path = require('path');
-const Joi = require('joi');
-const db = require('../lib/db.js');
-const xlsx = require('node-xlsx');
-const json2csv = require('json2csv');
-const config = require('../lib/config');
-const dbPath = config.get('dbPath');
-
-const schema = {
-  _id: Joi.string().optional(), // will be auto-gen by nedb
-  cacheKey: Joi.string().required(), // unique, manually provided
-  expiration: Joi.date().optional(), // item and associated cache files are removed on expiration
-  queryName: Joi.string().optional(), // used for file names if a file is downloaded
-  schema: Joi.any().optional(), // schema tree in JSON if that's what we're caching
-  createdDate: Joi.date().default(new Date(), 'time of creation'),
-  modifiedDate: Joi.date().default(new Date(), 'time of modification')
-};
-
-function Cache(data) {
-  this._id = data._id;
-  this.cacheKey = data.cacheKey;
-  this.expiration = data.expiration;
-  this.queryName = data.queryName;
-  this.schema = data.schema; // schema tree in JSON if that's what we're caching
-  this.createdDate = data.createdDate;
-  this.modifiedDate = data.modifiedDate;
-}
-
-Cache.prototype.xlsxFilePath = function xlsxFilePath() {
-  return path.join(dbPath, '/cache/', this.cacheKey + '.xlsx');
-};
-
-Cache.prototype.csvFilePath = function csvFilePath() {
-  return path.join(dbPath, '/cache/', this.cacheKey + '.csv');
-};
-
-Cache.prototype.filePaths = function filePaths() {
-  // these may not exist.
-  // eventually actual files should be stored on the cache item
-  return [this.xlsxFilePath(), this.csvFilePath()];
-};
-
-Cache.prototype.removeFiles = function removeFiles() {
-  const filepaths = this.filePaths();
-  filepaths.forEach(fp => {
-    if (fs.existsSync(fp)) {
-      fs.unlinkSync(fp);
-    }
-  });
-};
-
-Cache.prototype.expire = function expire() {
-  this.removeFiles();
-  return db.cache.remove({ _id: this._id }, {});
-};
-
-Cache.prototype.writeXlsx = function writeXlsx(queryResult) {
-  const self = this;
-  // loop through rows and build out an array of arrays
-  const resultArray = [];
-  resultArray.push(queryResult.fields);
-  for (let i = 0; i < queryResult.rows.length; i++) {
-    const row = [];
-    for (let c = 0; c < queryResult.fields.length; c++) {
-      const fieldName = queryResult.fields[c];
-      row.push(queryResult.rows[i][fieldName]);
-    }
-    resultArray.push(row);
-  }
-  const xlsxBuffer = xlsx.build([{ name: 'query-results', data: resultArray }]);
-  return new Promise(resolve => {
-    fs.writeFile(self.xlsxFilePath(), xlsxBuffer, function(err) {
-      // if there's an error log it but otherwise continue on
-      // we can still send results even if download file failed to create
-      if (err) {
-        console.log(err);
-      }
-      return resolve();
-    });
-  });
-};
-
-Cache.prototype.writeCsv = function writeCsv(queryResult) {
-  const self = this;
-  return new Promise(resolve => {
-    json2csv({ data: queryResult.rows, fields: queryResult.fields }, function(
-      err,
-      csv
-    ) {
-      if (err) {
-        console.log(err);
-        return resolve();
-      }
-      fs.writeFile(self.csvFilePath(), csv, function(err) {
-        if (err) {
-          console.log(err);
-        }
-        return resolve();
-      });
-    });
-  });
-};
-
-Cache.prototype.save = function save() {
-  const self = this;
-  this.modifiedDate = new Date();
-  const joiResult = Joi.validate(self, schema);
-  if (joiResult.error) {
-    return Promise.reject(joiResult.error);
-  }
-  return db.cache
-    .update({ cacheKey: self.cacheKey }, joiResult.value, { upsert: true })
-    .then(() => Cache.findOneByCacheKey(self.cacheKey));
-};
-
-/*  Query methods
-============================================================================== */
-Cache.findOneByCacheKey = cacheKey =>
-  db.cache.findOne({ cacheKey }).then(doc => doc && new Cache(doc));
-
-Cache.findExpired = () =>
-  db.cache
-    .find({ expiration: { $lt: new Date() } })
-    .then(docs => docs.map(doc => new Cache(doc)));
-
-Cache.removeExpired = () =>
-  Cache.findExpired()
-    .then(caches => Promise.all(caches.map(cache => cache.expire())))
-    .catch(console.error);
-
-// Every five minutes check and expire cache
-const FIVE_MINUTES = 1000 * 60 * 5;
-setInterval(Cache.removeExpired, FIVE_MINUTES);
-
-module.exports = Cache;
diff --git a/server/models/Query.js b/server/models/Query.js
deleted file mode 100644
index 8bc85fa2..00000000
--- a/server/models/Query.js
+++ /dev/null
@@ -1,140 +0,0 @@
-const db = require('../lib/db.js');
-const config = require('../lib/config');
-const Joi = require('joi');
-const request = require('request');
-
-/*
-"chartConfiguration": {
-    "chartType": "line",
-    "fields": {
-        "x": "created_month",
-        "y": "package_count",
-        "split": "keyword",
-        "xFacet": "",
-        "yFacet": "keyword",
-        "trendline": "true"
-    }
-}
-*/
-
-const schema = {
-  _id: Joi.string().optional(), // generated by nedb
-  name: Joi.string().required(),
-  tags: Joi.array()
-    .items(Joi.string().empty(''))
-    .sparse()
-    .optional(),
-  connectionId: Joi.string()
-    .optional()
-    .empty(''),
-  queryText: Joi.string()
-    .optional()
-    .empty(''),
-  chartConfiguration: Joi.object({
-    chartType: Joi.string()
-      .optional()
-      .empty(''),
-    // key value pairings. key=chart property, value=field mapped to property
-    fields: Joi.object()
-      .unknown(true)
-      .optional()
-  }).optional(),
-  createdDate: Joi.date().default(new Date(), 'time of creation'),
-  modifiedDate: Joi.date().default(new Date(), 'time of modification'),
-  createdBy: Joi.string().required(),
-  modifiedBy: Joi.string().required(),
-  lastAccessDate: Joi.date().default(new Date(), 'time of last access')
-};
-
-function Query(data) {
-  this._id = data._id;
-  this.name = data.name;
-  this.tags = data.tags;
-  this.connectionId = data.connectionId;
-  this.queryText = data.queryText;
-  this.chartConfiguration = data.chartConfiguration;
-  this.createdDate = data.createdDate;
-  this.createdBy = data.createdBy;
-  this.modifiedDate = data.modifiedDate;
-  this.modifiedBy = data.modifiedBy;
-  this.lastAccessDate = data.lastAccessedDate;
-}
-
-Query.prototype.save = function save() {
-  const self = this;
-  this.modifiedDate = new Date();
-  this.lastAccessDate = new Date();
-  // clean tags if present
-  // sqlpad v1 saved a lot of bad inputs
-  if (Array.isArray(self.tags)) {
-    self.tags = self.tags
-      .filter(tag => {
-        return typeof tag === 'string' && tag.trim() !== '';
-      })
-      .map(tag => {
-        return tag.trim();
-      });
-  }
-  const joiResult = Joi.validate(self, schema);
-  if (joiResult.error) {
-    return Promise.reject(joiResult.error);
-  }
-  if (self._id) {
-    return db.queries
-      .update({ _id: self._id }, joiResult.value, { upsert: true })
-      .then(() => Query.findOneById(self._id));
-  }
-  return db.queries.insert(joiResult.value).then(doc => new Query(doc));
-};
-
-Query.prototype.pushQueryToSlackIfSetup = function() {
-  const SLACK_WEBHOOK = config.get('slackWebhook');
-  if (SLACK_WEBHOOK) {
-    const PUBLIC_URL = config.get('publicUrl');
-    const BASE_URL = config.get('baseUrl');
-    const options = {
-      method: 'post',
-      body: {
-        text: `New Query <${PUBLIC_URL}${BASE_URL}/queries/${this._id}|${
-          this.name
-        }> 
-            saved by ${this.modifiedBy} on SQLPad 
-            ${'```'}
-            ${this.queryText}
-            ${'```'}`
-      },
-      json: true,
-      url: SLACK_WEBHOOK
-    };
-    request(options, function(err) {
-      if (err) {
-        console.error('Something went wrong while sending to Slack.');
-        console.error(err);
-      }
-    });
-  }
-};
-
-/*  Query methods
-============================================================================== */
-Query.findOneById = id =>
-  db.queries.findOne({ _id: id }).then(doc => new Query(doc));
-
-Query.findAll = () =>
-  db.queries.find({}).then(docs => docs.map(doc => new Query(doc)));
-
-Query.findByFilter = filter =>
-  db.queries.find(filter).then(docs => docs.map(doc => new Query(doc)));
-
-Query.prototype.logAccess = function logAccess() {
-  const self = this;
-  return db.queries.update(
-    { _id: self._id },
-    { $set: { lastAccessedDate: new Date() } },
-    {}
-  );
-};
-
-Query.removeOneById = id => db.queries.remove({ _id: id });
-
-module.exports = Query;
diff --git a/server/models/User.js b/server/models/User.js
deleted file mode 100644
index 486540aa..00000000
--- a/server/models/User.js
+++ /dev/null
@@ -1,85 +0,0 @@
-const Joi = require('joi');
-const db = require('../lib/db.js');
-const passhash = require('../lib/passhash.js');
-
-const schema = {
-  _id: Joi.string().optional(), // will be auto-gen by nedb
-  email: Joi.string().required(),
-  role: Joi.string()
-    .lowercase()
-    .allow('admin', 'editor', 'viewer'),
-  passwordResetId: Joi.string()
-    .guid()
-    .optional()
-    .empty(''),
-  passhash: Joi.string().optional(), // may not exist if user hasn't signed up yet
-  password: Joi.string()
-    .optional()
-    .strip(),
-  createdDate: Joi.date().default(new Date(), 'time of creation'),
-  modifiedDate: Joi.date().default(new Date(), 'time of modification'),
-  signupDate: Joi.date().optional()
-};
-
-function User(data) {
-  this._id = data._id;
-  this.email = data.email;
-  this.role = data.role;
-  this.passwordResetId = data.passwordResetId;
-  this.passhash = data.passhash;
-  this.password = data.password;
-  this.createdDate = data.createdDate;
-  this.modifiedDate = data.modifiedDate;
-  this.signupDate = data.signupDate;
-}
-
-User.prototype.save = async function save() {
-  const self = this;
-  this.modifiedDate = new Date();
-
-  if (this.password) {
-    this.passhash = await passhash.getPasshash(this.password);
-  }
-
-  // validate and save
-  const joiResult = Joi.validate(self, schema);
-  if (joiResult.error) {
-    return Promise.reject(joiResult.error);
-  }
-
-  await db.users.update({ email: self.email }, joiResult.value, {
-    upsert: true
-  });
-  return User.findOneByEmail(self.email);
-};
-
-/*  Query methods
-============================================================================== */
-User.findOneByEmail = email =>
-  db.users
-    .findOne({ email: { $regex: new RegExp(email, 'i') } })
-    .then(doc => doc && new User(doc));
-
-User.findOneById = id =>
-  db.users.findOne({ _id: id }).then(doc => doc && new User(doc));
-
-User.findOneByPasswordResetId = id =>
-  db.users.findOne({ passwordResetId: id }).then(doc => doc && new User(doc));
-
-User.findAll = () =>
-  db.users
-    .cfind({}, { password: 0, passhash: 0 })
-    .sort({ email: 1 })
-    .exec()
-    .then(docs => docs.map(doc => new User(doc)));
-
-/**
- * Returns boolean regarding whether admin registration should be open or not
- * @returns {Promise<boolean>} administrationOpen
- */
-User.adminRegistrationOpen = () =>
-  db.users.findOne({ role: 'admin' }).then(doc => !doc);
-
-User.removeOneById = id => db.users.remove({ _id: id });
-
-module.exports = User;
diff --git a/server/models/queries.js b/server/models/queries.js
new file mode 100644
index 00000000..438f3294
--- /dev/null
+++ b/server/models/queries.js
@@ -0,0 +1,103 @@
+const db = require('../lib/db.js');
+const Joi = require('joi');
+
+/*
+"chartConfiguration": {
+    "chartType": "line",
+    "fields": {
+        "x": "created_month",
+        "y": "package_count",
+        "split": "keyword",
+        "xFacet": "",
+        "yFacet": "keyword",
+        "trendline": "true"
+    }
+}
+*/
+
+const schema = {
+  _id: Joi.string().optional(), // generated by nedb
+  name: Joi.string().required(),
+  tags: Joi.array()
+    .items(Joi.string().empty(''))
+    .sparse()
+    .optional(),
+  connectionId: Joi.string()
+    .optional()
+    .empty(''),
+  queryText: Joi.string()
+    .optional()
+    .empty(''),
+  chartConfiguration: Joi.object({
+    chartType: Joi.string()
+      .optional()
+      .empty(''),
+    // key value pairings. key=chart property, value=field mapped to property
+    fields: Joi.object()
+      .unknown(true)
+      .optional()
+  }).optional(),
+  createdDate: Joi.date().default(new Date(), 'time of creation'),
+  modifiedDate: Joi.date().default(new Date(), 'time of modification'),
+  createdBy: Joi.string().required(),
+  modifiedBy: Joi.string().required(),
+  lastAccessDate: Joi.date().default(new Date(), 'time of last access')
+};
+
+function findOneById(id) {
+  return db.queries.findOne({ _id: id });
+}
+
+function findAll() {
+  return db.queries.find({});
+}
+
+function findByFilter(filter) {
+  return db.queries.find(filter);
+}
+
+function removeById(id) {
+  return db.queries.remove({ _id: id });
+}
+
+/**
+ * Save query object
+ * returns saved query object
+ * @param {object} query
+ */
+async function save(query) {
+  query.modifiedDate = new Date();
+  query.lastAccessDate = new Date();
+
+  // clean tags if present
+  // sqlpad v1 saved a lot of bad inputs
+  if (Array.isArray(query.tags)) {
+    query.tags = query.tags
+      .filter(tag => {
+        return typeof tag === 'string' && tag.trim() !== '';
+      })
+      .map(tag => {
+        return tag.trim();
+      });
+  }
+  const joiResult = Joi.validate(query, schema);
+  if (joiResult.error) {
+    return Promise.reject(joiResult.error);
+  }
+  if (query._id) {
+    await db.queries.update({ _id: query._id }, joiResult.value, {
+      upsert: true
+    });
+    return findOneById(query._id);
+  }
+  const newQuery = await db.queries.insert(joiResult.value);
+  return newQuery;
+}
+
+module.exports = {
+  findOneById,
+  findAll,
+  findByFilter,
+  removeById,
+  save
+};
diff --git a/server/models/resultCache.js b/server/models/resultCache.js
new file mode 100644
index 00000000..17531ec6
--- /dev/null
+++ b/server/models/resultCache.js
@@ -0,0 +1,124 @@
+const fs = require('fs');
+const path = require('path');
+const moment = require('moment');
+const sanitize = require('sanitize-filename');
+const db = require('../lib/db.js');
+const xlsx = require('node-xlsx');
+const json2csv = require('json2csv');
+const config = require('../lib/config');
+const dbPath = config.get('dbPath');
+
+function xlsxFilePath(cacheKey) {
+  return path.join(dbPath, '/cache/', cacheKey + '.xlsx');
+}
+
+function csvFilePath(cacheKey) {
+  return path.join(dbPath, '/cache/', cacheKey + '.csv');
+}
+
+async function findOneByCacheKey(cacheKey) {
+  return db.cache.findOne({ cacheKey });
+}
+
+async function saveResultCache(cacheKey, queryName) {
+  if (!cacheKey) {
+    throw new Error('cacheKey required');
+  }
+  const EIGHT_HOURS = 1000 * 60 * 60 * 8;
+  const expiration = new Date(Date.now() + EIGHT_HOURS);
+  const modifiedDate = new Date();
+
+  const savedQueryName = sanitize(
+    (queryName || 'SQLPad Query Results') + ' ' + moment().format('YYYY-MM-DD')
+  );
+
+  const doc = { cacheKey, expiration, queryName: savedQueryName, modifiedDate };
+
+  const existing = await findOneByCacheKey(cacheKey);
+  if (!existing) {
+    doc.createdDate = new Date();
+  }
+
+  return db.cache.update({ cacheKey }, doc, {
+    upsert: true
+  });
+}
+
+function writeXlsx(cacheKey, queryResult) {
+  // loop through rows and build out an array of arrays
+  const resultArray = [];
+  resultArray.push(queryResult.fields);
+  for (let i = 0; i < queryResult.rows.length; i++) {
+    const row = [];
+    for (let c = 0; c < queryResult.fields.length; c++) {
+      const fieldName = queryResult.fields[c];
+      row.push(queryResult.rows[i][fieldName]);
+    }
+    resultArray.push(row);
+  }
+  const xlsxBuffer = xlsx.build([{ name: 'query-results', data: resultArray }]);
+  return new Promise(resolve => {
+    fs.writeFile(xlsxFilePath(cacheKey), xlsxBuffer, function(err) {
+      // if there's an error log it but otherwise continue on
+      // we can still send results even if download file failed to create
+      if (err) {
+        console.log(err);
+      }
+      return resolve();
+    });
+  });
+}
+
+function writeCsv(cacheKey, queryResult) {
+  return new Promise(resolve => {
+    json2csv({ data: queryResult.rows, fields: queryResult.fields }, function(
+      err,
+      csv
+    ) {
+      if (err) {
+        console.log(err);
+        return resolve();
+      }
+      fs.writeFile(csvFilePath(cacheKey), csv, function(err) {
+        if (err) {
+          console.log(err);
+        }
+        return resolve();
+      });
+    });
+  });
+}
+
+/*  Result cache maintenance
+============================================================================== */
+
+async function removeExpired() {
+  try {
+    const docs = await db.cache.find({ expiration: { $lt: new Date() } });
+    for (const doc of docs) {
+      const filepaths = [xlsxFilePath(doc.cacheKey), csvFilePath(doc.cacheKey)];
+      filepaths.forEach(fp => {
+        if (fs.existsSync(fp)) {
+          fs.unlinkSync(fp);
+        }
+      });
+      // eslint-disable-next-line no-await-in-loop
+      await db.cache.remove({ _id: doc._id }, {});
+    }
+  } catch (error) {
+    console.log(error);
+  }
+}
+
+// Every five minutes check and expire cache
+const FIVE_MINUTES = 1000 * 60 * 5;
+setInterval(removeExpired, FIVE_MINUTES);
+
+module.exports = {
+  csvFilePath,
+  findOneByCacheKey,
+  saveResultCache,
+  writeCsv,
+  writeXlsx,
+  xlsxFilePath
+};
diff --git a/server/models/schemaInfo.js b/server/models/schemaInfo.js
new file mode 100644
index 00000000..d85fb910
--- /dev/null
+++ b/server/models/schemaInfo.js
@@ -0,0 +1,55 @@
+const db = require('../lib/db.js');
+
+function getCacheKey(connectionId) {
+  return 'schemaCache:' + connectionId;
+}
+
+/**
+ * Get schemaInfo for connection id
+ * @param {string} connectionId
+ */
+async function getSchemaInfo(connectionId) {
+  const cacheKey = getCacheKey(connectionId);
+  const doc = await db.cache.findOne({ cacheKey });
+
+  if (!doc) {
+    return;
+  }
+
+  let schemaInfo;
+  try {
+    schemaInfo =
+      typeof doc.schema === 'string' ? JSON.parse(doc.schema) : doc.schema;
+  } catch (error) {
+    // do nothing. valid schema will be updated
+  }
+
+  return schemaInfo;
+}
+
+/**
+ * Save schemaInfo to cache db object
+ * Schema needs to be stringified as JSON
+ * Column names could have dots in name (incompatible with nedb)
+ * @param {string} connectionId
+ * @param {object} schemaInfo
+ */
+async function saveSchemaInfo(connectionId, schemaInfo) {
+  const cacheKey = getCacheKey(connectionId);
+  if (schemaInfo && Object.keys(schemaInfo).length) {
+    const schema = JSON.stringify(schemaInfo);
+    const doc = {
+      cacheKey,
+      schema,
+      modifiedDate: new Date()
+    };
+    return db.cache.update({ cacheKey }, doc, {
+      upsert: true
+    });
+  }
+}
+
+module.exports = {
+  getSchemaInfo,
+  saveSchemaInfo
+};
diff --git a/server/models/users.js b/server/models/users.js
new file mode 100644
index 00000000..5a057459
--- /dev/null
+++ b/server/models/users.js
@@ -0,0 +1,86 @@
+const Joi = require('joi');
+const db = require('../lib/db.js');
+const passhash = require('../lib/passhash.js');
+
+const schema = {
+  _id: Joi.string().optional(), // will be auto-gen by nedb
+  email: Joi.string().required(),
+  role: Joi.string()
+    .lowercase()
+    .allow('admin', 'editor', 'viewer'),
+  passwordResetId: Joi.string()
+    .guid()
+    .optional()
+    .empty(''),
+  passhash: Joi.string().optional(), // may not exist if user hasn't signed up yet
+  password: Joi.string()
+    .optional()
+    .strip(),
+  createdDate: Joi.date().default(new Date(), 'time of creation'),
+  modifiedDate: Joi.date().default(new Date(), 'time of modification'),
+  signupDate: Joi.date().optional()
+};
+
+async function save(data) {
+  if (!data.email) {
+    throw new Error('email required when saving user');
+  }
+
+  data.modifiedDate = new Date();
+
+  if (data.password) {
+    data.passhash = await passhash.getPasshash(data.password);
+  }
+
+  const joiResult = Joi.validate(data, schema);
+  if (joiResult.error) {
+    return Promise.reject(joiResult.error);
+  }
+
+  await db.users.update({ email: data.email }, joiResult.value, {
+    upsert: true
+  });
+  return findOneByEmail(data.email);
+}
+
+function findOneByEmail(email) {
+  return db.users.findOne({ email: { $regex: new RegExp(email, 'i') } });
+}
+
+function findOneById(id) {
+  return db.users.findOne({ _id: id });
+}
+
+function findOneByPasswordResetId(passwordResetId) {
+  return db.users.findOne({ passwordResetId });
+}
+
+function findAll() {
+  return db.users
+    .cfind({}, { password: 0, passhash: 0 })
+    .sort({ email: 1 })
+    .exec();
+}
+
+/**
+ * Returns boolean regarding whether admin registration should be open or not
+ * @returns {Promise<boolean>} administrationOpen
+ */
+async function adminRegistrationOpen() {
+  const doc = await db.users.findOne({ role: 'admin' });
+  return !doc;
+}
+
+function removeById(id) {
+  return db.users.remove({ _id: id });
+}
+
+module.exports = {
+  findOneByEmail,
+  findOneById,
+  findOneByPasswordResetId,
+  findAll,
+  adminRegistrationOpen,
+  removeById,
+  save
+};
diff --git a/server/routes/app.js b/server/routes/app.js
index 2faaa92a..6ae5a1a8 100644
--- a/server/routes/app.js
+++ b/server/routes/app.js
@@ -1,6 +1,6 @@
 const router = require('express').Router();
 const packageJson = require('../package.json');
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const sendError = require('../lib/sendError');
 const config = require('../lib/config');
 
@@ -9,7 +9,7 @@ const config = require('../lib/config');
 // the root of a domain or if there is a base-url provided in the config
 router.get('*/api/app', async (req, res) => {
   try {
-    const adminRegistrationOpen = await User.adminRegistrationOpen();
+    const adminRegistrationOpen = await usersUtil.adminRegistrationOpen();
     const currentUser =
       req.isAuthenticated() && req.user
         ? {
diff --git a/server/routes/download-results.js b/server/routes/download-results.js
index 427219f2..6847aa9b 100644
--- a/server/routes/download-results.js
+++ b/server/routes/download-results.js
@@ -1,14 +1,15 @@
 const fs = require('fs');
 const router = require('express').Router();
-const Cache = require('../models/Cache.js');
+const resultCache = require('../models/resultCache.js');
 const config = require('../lib/config');
 
 router.get('/download-results/:cacheKey.csv', async function(req, res, next) {
+  const { cacheKey } = req.params;
   try {
     if (config.get('allowCsvDownload')) {
-      const cache = await Cache.findOneByCacheKey(req.params.cacheKey);
+      const cache = await resultCache.findOneByCacheKey(cacheKey);
       if (!cache) {
-        return next(new Error('Cache not found'));
+        return next(new Error('Result cache not found'));
       }
       let filename = cache.queryName + '.csv';
       res.setHeader(
@@ -16,7 +17,9 @@ router.get('/download-results/:cacheKey.csv', async function(req, res, next) {
         'attachment; filename="' + encodeURIComponent(filename) + '"'
       );
       res.setHeader('Content-Type', 'text/csv');
-      fs.createReadStream(cache.csvFilePath()).pipe(res);
+      fs.createReadStream(resultCache.csvFilePath(cacheKey)).pipe(res);
+    } else {
+      return next(new Error('CSV download disabled'));
     }
   } catch (error) {
     console.error(error);
@@ -26,11 +29,12 @@ router.get('/download-results/:cacheKey.csv', async function(req, res, next) {
 });
 
 router.get('/download-results/:cacheKey.xlsx', async function(req, res, next) {
+  const { cacheKey } = req.params;
   try {
     if (config.get('allowCsvDownload')) {
-      const cache = await Cache.findOneByCacheKey(req.params.cacheKey);
+      const cache = await resultCache.findOneByCacheKey(cacheKey);
       if (!cache) {
-        return next(new Error('Cache not found'));
+        return next(new Error('Result cache not found'));
       }
       let filename = cache.queryName + '.xlsx';
       res.setHeader(
@@ -41,7 +45,9 @@ router.get('/download-results/:cacheKey.xlsx', async function(req, res, next) {
         'Content-Type',
         'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
       );
-      fs.createReadStream(cache.xlsxFilePath()).pipe(res);
+      fs.createReadStream(resultCache.xlsxFilePath(cacheKey)).pipe(res);
+    } else {
+      return next(new Error('XLSX download disabled'));
     }
   } catch (error) {
     console.error(error);
diff --git a/server/routes/forgot-password.js b/server/routes/forgot-password.js
index b7ba2620..8870fda2 100644
--- a/server/routes/forgot-password.js
+++ b/server/routes/forgot-password.js
@@ -1,6 +1,6 @@
 const router = require('express').Router();
 const uuid = require('uuid');
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const email = require('../lib/email');
 const sendError = require('../lib/sendError');
 const config = require('../lib/config');
@@ -14,7 +14,7 @@ router.post('/api/forgot-password', async function(req, res) {
   }
 
   try {
-    const user = await User.findOneByEmail(req.body.email);
+    const user = await usersUtil.findOneByEmail(req.body.email);
 
     // If user not found send success regardless
     // This is not a user-validation service
@@ -24,7 +24,7 @@ router.post('/api/forgot-password', async function(req, res) {
 
     user.passwordResetId = uuid.v4();
 
-    await user.save();
+    await usersUtil.save(user);
 
     // Send email, but do not block response
     const resetPath = `/password-reset/${user.passwordResetId}`;
diff --git a/server/routes/password-reset.js b/server/routes/password-reset.js
index c04316d8..38360a31 100644
--- a/server/routes/password-reset.js
+++ b/server/routes/password-reset.js
@@ -1,11 +1,11 @@
 const router = require('express').Router();
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const sendError = require('../lib/sendError');
 
 // This route used to set new password given a passwordResetId
 router.post('/api/password-reset/:passwordResetId', async function(req, res) {
   try {
-    const user = await User.findOneByPasswordResetId(
+    const user = await usersUtil.findOneByPasswordResetId(
       req.params.passwordResetId
     );
 
@@ -20,7 +20,7 @@ router.post('/api/password-reset/:passwordResetId', async function(req, res) {
     }
     user.password = req.body.password;
     user.passwordResetId = '';
-    await user.save();
+    await usersUtil.save(user);
     return res.json({});
   } catch (error) {
     sendError(res, error, 'Problem querying user database');
diff --git a/server/routes/queries.js b/server/routes/queries.js
index 3060eb5b..3e3f0a69 100644
--- a/server/routes/queries.js
+++ b/server/routes/queries.js
@@ -1,12 +1,10 @@
 const router = require('express').Router();
-const Query = require('../models/Query.js');
+const queriesUtil = require('../models/queries.js');
 const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
 const mustBeAuthenticatedOrChartLink = require('../middleware/must-be-authenticated-or-chart-link-noauth.js');
 const sendError = require('../lib/sendError');
 const config = require('../lib/config');
-
-/*  render page routes
-============================================================================= */
+const pushQueryToSlack = require('../lib/pushQueryToSlack');
 
 // NOTE: this non-api route is special since it redirects legacy urls
 router.get('/queries/:_id', mustBeAuthenticatedOrChartLink, function(
@@ -24,15 +22,12 @@ router.get('/queries/:_id', mustBeAuthenticatedOrChartLink, function(
   next();
 });
 
-/*  API routes
-============================================================================= */
-
 router.delete('/api/queries/:_id', mustBeAuthenticated, async function(
   req,
   res
 ) {
   try {
-    await Query.removeOneById(req.params._id);
+    await queriesUtil.removeById(req.params._id);
     return res.json({});
   } catch (error) {
     sendError(res, error, 'Problem deleting query');
@@ -41,7 +36,7 @@ router.delete('/api/queries/:_id', mustBeAuthenticated, async function(
 
 router.get('/api/queries', mustBeAuthenticated, async function(req, res) {
   try {
-    const queries = await Query.findAll();
+    const queries = await queriesUtil.findAll();
     return res.json({ queries });
   } catch (error) {
     sendError(res, error, 'Problem querying query database');
@@ -53,7 +48,7 @@ router.get('/api/queries/:_id', mustBeAuthenticatedOrChartLink, async function(
   res
 ) {
   try {
-    const query = await Query.findOneById(req.params._id);
+    const query = await queriesUtil.findOneById(req.params._id);
     if (!query) {
       return res.json({
         query: {}
@@ -65,21 +60,24 @@ router.get('/api/queries/:_id', mustBeAuthenticatedOrChartLink, async function(
   }
 });
 
-// create new
 router.post('/api/queries', mustBeAuthenticated, async function(req, res) {
-  const query = new Query({
-    name: req.body.name || 'No Name Query',
-    tags: req.body.tags,
-    connectionId: req.body.connectionId,
-    queryText: req.body.queryText,
-    chartConfiguration: req.body.chartConfiguration,
-    createdBy: req.user.email,
-    modifiedBy: req.user.email
-  });
+  const { name, tags, connectionId, queryText, chartConfiguration } = req.body;
+  const { email } = req.user;
+
+  const query = {
+    name: name || 'No Name Query',
+    tags,
+    connectionId,
+    queryText,
+    chartConfiguration,
+    createdBy: email,
+    modifiedBy: email
+  };
+
   try {
-    const newQuery = await query.save();
+    const newQuery = await queriesUtil.save(query);
     // This is async, but save operation doesn't care about when/if finished
-    newQuery.pushQueryToSlackIfSetup();
+    pushQueryToSlack(newQuery);
     return res.json({
       query: newQuery
     });
@@ -90,19 +88,30 @@ router.post('/api/queries', mustBeAuthenticated, async function(req, res) {
 
 router.put('/api/queries/:_id', mustBeAuthenticated, async function(req, res) {
   try {
-    const query = await Query.findOneById(req.params._id);
+    const query = await queriesUtil.findOneById(req.params._id);
     if (!query) {
       return sendError(res, null, 'Query not found');
     }
 
-    query.name = req.body.name || '';
-    query.tags = req.body.tags;
-    query.connectionId = req.body.connectionId;
-    query.queryText = req.body.queryText;
-    query.chartConfiguration = req.body.chartConfiguration;
-    query.modifiedBy = req.user.email;
+    const {
+      name,
+      tags,
+      connectionId,
+      queryText,
+      chartConfiguration
+    } = req.body;
+    const { email } = req.user;
+
+    Object.assign(query, {
+      name,
+      tags,
+      connectionId,
+      queryText,
+      chartConfiguration,
+      modifiedBy: email
+    });
 
-    const newQuery = await query.save();
+    const newQuery = await queriesUtil.save(query);
     return res.json({ query: newQuery });
   } catch (error) {
     sendError(res, error, 'Problem saving query');
diff --git a/server/routes/query-result.js b/server/routes/query-result.js
index 11f3ce3c..e7236543 100644
--- a/server/routes/query-result.js
+++ b/server/routes/query-result.js
@@ -1,10 +1,8 @@
-const sanitize = require('sanitize-filename');
-const moment = require('moment');
 const router = require('express').Router();
 const { runQuery } = require('../drivers/index');
 const connections = require('../models/connections.js');
-const Cache = require('../models/Cache.js');
-const Query = require('../models/Query.js');
+const resultCache = require('../models/resultCache.js');
+const queriesUtil = require('../models/queries.js');
 const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
 const mustBeAuthenticatedOrChartLink = require('../middleware/must-be-authenticated-or-chart-link-noauth.js');
 const sendError = require('../lib/sendError');
@@ -17,7 +15,7 @@ router.get(
   mustBeAuthenticatedOrChartLink,
   async function(req, res) {
     try {
-      const query = await Query.findOneById(req.params._queryId);
+      const query = await queriesUtil.findOneById(req.params._queryId);
       if (!query) {
         return sendError(res, null, 'Query not found (save query first)');
       }
@@ -68,27 +66,14 @@ async function getQueryResult(data) {
     throw new Error('Please choose a connection');
   }
   connection.maxRows = Number(config.get('queryResultMaxRows'));
-  let cache = await Cache.findOneByCacheKey(cacheKey);
-
-  if (!cache) {
-    cache = new Cache({ cacheKey });
-  }
-  cache.queryName = sanitize(
-    (queryName || 'SQLPad Query Results') + ' ' + moment().format('YYYY-MM-DD')
-  );
-
-  // Expire cache in 8 hours
-  const now = new Date();
-  cache.expiration = new Date(now.getTime() + 1000 * 60 * 60 * 8);
-  const newCache = await cache.save();
 
   const queryResult = await runQuery(queryText, connection, user);
-
   queryResult.cacheKey = cacheKey;
 
   if (config.get('allowCsvDownload')) {
-    await newCache.writeXlsx(queryResult);
-    await newCache.writeCsv(queryResult);
+    resultCache.saveResultCache(cacheKey, queryName);
+    await resultCache.writeXlsx(cacheKey, queryResult);
+    await resultCache.writeCsv(cacheKey, queryResult);
   }
 
   return queryResult;
diff --git a/server/routes/schema-info.js b/server/routes/schema-info.js
index a8e3ce78..45272981 100644
--- a/server/routes/schema-info.js
+++ b/server/routes/schema-info.js
@@ -1,6 +1,6 @@
 const router = require('express').Router();
 const connections = require('../models/connections');
-const Cache = require('../models/Cache.js');
+const schemaInfoUtil = require('../models/schemaInfo.js');
 const driver = require('../drivers');
 const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
 const sendError = require('../lib/sendError');
@@ -9,38 +9,25 @@ router.get(
   '/api/schema-info/:connectionId',
   mustBeAuthenticated,
   async function(req, res) {
+    const { connectionId } = req.params;
     const reload = req.query.reload === 'true';
-    const cacheKey = 'schemaCache:' + req.params.connectionId;
+
     try {
-      let [conn, cache] = await Promise.all([
-        connections.findOneById(req.params.connectionId),
-        // This has problems in TravisCI for some reason...
-        Cache.findOneByCacheKey(cacheKey)
-      ]);
+      const conn = await connections.findOneById(connectionId);
 
       if (!conn) {
         throw new Error('Connection not found');
       }
 
-      if (cache && !reload) {
-        const schemaInfo =
-          typeof cache.schema === 'string'
-            ? JSON.parse(cache.schema)
-            : cache.schema;
+      let schemaInfo = await schemaInfoUtil.getSchemaInfo(connectionId);
 
+      if (schemaInfo && !reload) {
         return res.json({ schemaInfo });
       }
 
-      if (!cache) {
-        cache = new Cache({ cacheKey });
-      }
-
-      const schemaInfo = await driver.getSchema(conn);
+      schemaInfo = await driver.getSchema(conn);
       if (Object.keys(schemaInfo).length) {
-        // Schema needs to be stringified as JSON
-        // Column names could have dots in name (incompatible with nedb)
-        cache.schema = JSON.stringify(schemaInfo);
-        await cache.save();
+        await schemaInfoUtil.saveSchemaInfo(connectionId, schemaInfo);
       }
       return res.json({ schemaInfo });
     } catch (error) {
diff --git a/server/routes/signup-signin-signout.js b/server/routes/signup-signin-signout.js
index d5524ada..e497dd3f 100644
--- a/server/routes/signup-signin-signout.js
+++ b/server/routes/signup-signin-signout.js
@@ -1,7 +1,7 @@
 const passport = require('passport');
 const router = require('express').Router();
 const checkWhitelist = require('../lib/check-whitelist');
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const sendError = require('../lib/sendError');
 const config = require('../lib/config');
 
@@ -14,36 +14,37 @@ async function handleSignup(req, res, next) {
     }
 
     let [user, adminRegistrationOpen] = await Promise.all([
-      User.findOneByEmail(req.body.email),
-      User.adminRegistrationOpen()
+      usersUtil.findOneByEmail(req.body.email),
+      usersUtil.adminRegistrationOpen()
     ]);
 
     if (user && user.passhash) {
       return sendError(res, null, 'User already signed up');
     }
+
     if (user) {
       user.password = req.body.password;
       user.signupDate = new Date();
+      await usersUtil.save(user);
+      return next();
     }
-    if (!user) {
-      // if open admin registration or whitelisted email create user
-      // otherwise exit
-      if (
-        adminRegistrationOpen ||
-        checkWhitelist(whitelistedDomains, req.body.email)
-      ) {
-        user = new User({
-          email: req.body.email,
-          password: req.body.password,
-          role: adminRegistrationOpen ? 'admin' : 'editor',
-          signupDate: new Date()
-        });
-      } else {
-        return sendError(res, null, 'Email address not whitelisted');
-      }
+
+    // if open admin registration or whitelisted email create user
+    // otherwise exit
+    if (
+      adminRegistrationOpen ||
+      checkWhitelist(whitelistedDomains, req.body.email)
+    ) {
+      user = await usersUtil.save({
+        email: req.body.email,
+        password: req.body.password,
+        role: adminRegistrationOpen ? 'admin' : 'editor',
+        signupDate: new Date()
+      });
+      return next();
+    } else {
+      return sendError(res, null, 'Email address not whitelisted');
     }
-    await user.save();
-    next();
   } catch (error) {
     sendError(res, error, 'Error saving user');
   }
diff --git a/server/routes/tags.js b/server/routes/tags.js
index ca61160a..3a2aab2a 100644
--- a/server/routes/tags.js
+++ b/server/routes/tags.js
@@ -1,12 +1,12 @@
 const _ = require('lodash');
 const router = require('express').Router();
-const Query = require('../models/Query.js');
+const queriesUtil = require('../models/queries.js');
 const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
 const sendError = require('../lib/sendError');
 
 router.get('/api/tags', mustBeAuthenticated, async function(req, res) {
   try {
-    const queries = await Query.findAll();
+    const queries = await queriesUtil.findAll();
     const tags = _.uniq(_.flatten(_.map(queries, 'tags')))
       .sort()
       .filter(t => t);
diff --git a/server/routes/users.js b/server/routes/users.js
index 82a586fd..1bad84f2 100644
--- a/server/routes/users.js
+++ b/server/routes/users.js
@@ -1,5 +1,5 @@
 const router = require('express').Router();
-const User = require('../models/User.js');
+const usersUtil = require('../models/users.js');
 const email = require('../lib/email');
 const mustBeAdmin = require('../middleware/must-be-admin.js');
 const mustBeAuthenticated = require('../middleware/must-be-authenticated.js');
@@ -8,7 +8,7 @@ const config = require('../lib/config');
 
 router.get('/api/users', mustBeAuthenticated, async function(req, res) {
   try {
-    const users = await User.findAll();
+    const users = await usersUtil.findAll();
     return res.json({ users });
   } catch (error) {
     sendError(res, error, 'Problem getting uers');
@@ -18,15 +18,14 @@ router.get('/api/users', mustBeAuthenticated, async function(req, res) {
 // create/whitelist/invite user
 router.post('/api/users', mustBeAdmin, async function(req, res) {
   try {
-    let user = await User.findOneByEmail(req.body.email);
+    let user = await usersUtil.findOneByEmail(req.body.email);
     if (user) {
       return sendError(res, null, 'User already exists');
     }
-    user = new User({
+    user = await usersUtil.save({
       email: req.body.email.toLowerCase(),
       role: req.body.role
     });
-    user = await user.save();
 
     if (config.smtpConfigured()) {
       email.sendInvite(req.body.email).catch(error => console.error(error));
@@ -43,7 +42,7 @@ router.put('/api/users/:_id', mustBeAdmin, async function(req, res) {
     return sendError(res, null, "You can't unadmin yourself");
   }
   try {
-    const updateUser = await User.findOneById(params._id);
+    const updateUser = await usersUtil.findOneById(params._id);
     if (!updateUser) {
       return sendError(res, null, 'user not found');
     }
@@ -55,8 +54,8 @@ router.put('/api/users/:_id', mustBeAdmin, async function(req, res) {
     if (body.passwordResetId != null) {
       updateUser.passwordResetId = body.passwordResetId;
     }
-    await updateUser.save();
-    return res.json({ user: updateUser });
+    const updatedUser = await usersUtil.save(updateUser);
+    return res.json({ user: updatedUser });
   } catch (error) {
     sendError(res, error, 'Problem saving user');
   }
@@ -67,7 +66,7 @@ router.delete('/api/users/:_id', mustBeAdmin, async function(req, res) {
     return sendError(res, null, "You can't delete yourself");
   }
   try {
-    await User.removeOneById(req.params._id);
+    await usersUtil.removeById(req.params._id);
     return res.json({});
   } catch (error) {
     sendError(res, error, 'Problem deleting user');


Test patch:
diff --git a/server/test/api/password-reset.js b/server/test/api/password-reset.js
index 3467b4b8..db4de75b 100644
--- a/server/test/api/password-reset.js
+++ b/server/test/api/password-reset.js
@@ -1,13 +1,13 @@
 const assert = require('assert');
 const utils = require('../utils');
 const uuid = require('uuid');
-const User = require('../../models/User');
+const usersUtil = require('../../models/users');
 
 async function setReset() {
-  const user = await User.findOneByEmail('admin@test.com');
+  const user = await usersUtil.findOneByEmail('admin@test.com');
   const passwordResetId = uuid.v4();
   user.passwordResetId = passwordResetId;
-  await user.save();
+  await usersUtil.save(user);
   return passwordResetId;
 }
 
diff --git a/server/test/utils.js b/server/test/utils.js
index c4413457..de0b6704 100644
--- a/server/test/utils.js
+++ b/server/test/utils.js
@@ -1,6 +1,6 @@
 const assert = require('assert');
 const request = require('supertest');
-const User = require('../models/User');
+const usersUtil = require('../models/users');
 const db = require('../lib/db');
 const app = require('../app');
 
@@ -34,8 +34,7 @@ function reset() {
 async function resetWithUser() {
   await reset();
   const saves = Object.keys(users).map(key => {
-    const user = new User(users[key]);
-    return user.save();
+    return usersUtil.save(users[key]);
   });
   return Promise.all(saves);
 }