Hacks to get FeathersJS working with a SQL 2005 Sequelize service

README.md

MASSIVE HACK WARNING

After digging some gh issues, I came with these scripts that enabled me to build a REST API for an existing sql2005 db.

Nobody would pick SQL Server 2005 as their database in 2018, but some of us have to deal with legacy apps.

The main change is for the find queries, with something that's SQL 2005 compatible and not using newer reserved words as OFFSET ROWS FETCH NEXT ROWS ONLY.

CustomSequelieServiceUtils.js

const errors = require('@feathersjs/errors');

exports.errorHandler = function errorHandler (error) {
  let feathersError = error;

  if (error.name) {
    switch (error.name) {
    case 'SequelizeValidationError':
    case 'SequelizeUniqueConstraintError':
    case 'SequelizeExclusionConstraintError':
    case 'SequelizeForeignKeyConstraintError':
    case 'SequelizeInvalidConnectionError':
      feathersError = new errors.BadRequest(error);
      break;
    case 'SequelizeTimeoutError':
    case 'SequelizeConnectionTimedOutError':
      feathersError = new errors.Timeout(error);
      break;
    case 'SequelizeConnectionRefusedError':
    case 'SequelizeAccessDeniedError':
      feathersError = new errors.Forbidden(error);
      break;
    case 'SequelizeHostNotReachableError':
      feathersError = new errors.Unavailable(error);
      break;
    case 'SequelizeHostNotFoundError':
      feathersError = new errors.NotFound(error);
      break;
    }
  }

  throw feathersError;
};

exports.getOrder = function getOrder (sort = {}) {
  let order = [];

  Object.keys(sort).forEach(name =>
    order.push([ name, parseInt(sort[name], 10) === 1 ? 'ASC' : 'DESC' ]));

  return order;
};

exports.getWhere = function getWhere (query) {
  let where = Object.assign({}, query);

  if (where.$select) {
    delete where.$select;
  }

  Object.keys(where).forEach(prop => {
    let value = where[prop];
    if (value && value.$nin) {
      value = Object.assign({}, value);

      value.$notIn = value.$nin;
      delete value.$nin;

      where[prop] = value;
    }
  });

  return where;
};

CustomSequelizeService.js

const { Service } = require('feathers-sequelize');
const { filterQuery } = require('@feathersjs/commons');
const utils = require('./CustomSequelizeServiceUtils');
const logger = require('winston');

// https://github.com/sequelize/sequelize/issues/2325#issuecomment-366060303
function getSqlFromFindAll(Model, options) {
  const id = (Math.random() * 100000).toString();

  return new Promise((resolve, reject) => {
    Model.addHook('beforeFindAfterOptions', id, options => {
      Model.removeHook('beforeFindAfterOptions', id);

      resolve(Model.sequelize.dialect.QueryGenerator.selectQuery(Model.getTableName(), options, Model).slice(0, -1));

      return new Promise(() => {});
    });

    return Model.findAll(options).catch(reject);
  });
}

// Hack to overwrite sql 2012+ paging syntax to sql 2005
function patchSelectQuery(sql, Model) {
  let result = '';
  const regexOffset = /(.*)OFFSET ([0-9]+) ROWS FETCH NEXT ([0-9]+) ROWS ONLY/ig;
  const regexFrom = /(.*)FROM(.*)/;
  let matches = regexOffset.exec(sql);
  if(!matches){
    logger.warn('No matcheó para corregir sintaxis OFFSET');
    return sql;
  }
  let [ , query, offset, limit] = matches;
  offset = parseInt(offset);
  limit = parseInt(limit);
  
  let matchesFrom = regexFrom.exec(query);
  if(!matchesFrom){
    logger.warn('No detectó FROM');
  }
  let [ , beforeFrom, afterFrom] = matchesFrom;
  
  // le saco la , [primkey] previa al offset
  let matchesAfterFrom = /(.*),(.*)/.exec(afterFrom);
  if(matchesAfterFrom){
    afterFrom = matchesAfterFrom[1];
  }
  let orderBy = `ORDER BY ${(matchesAfterFrom && matchesAfterFrom[2]) || Model.primaryKeyAttributes[0]}`;
  let matchesOrderBy = /(.*)(ORDER BY .*)/ig.exec(afterFrom);
  if(matchesOrderBy){
    afterFrom = matchesOrderBy[1];
    orderBy = matchesOrderBy[2];
  }

  result = `SELECT * FROM (
    ${beforeFrom},
    ROW_NUMBER() OVER (${orderBy}) as row
    FROM
    ${afterFrom}
  ) q
  WHERE row BETWEEN ${offset + 1} AND ${offset + limit}
  `;
  return result;
}

class CustomSequelizeService extends Service {

  // this is just a modified version of the service implemented in feathers-sequelize
  _find (params, getFilter = filterQuery, paginate){
    const { filters, query } = getFilter(params.query || {});
    const where = utils.getWhere(query);
    const order = utils.getOrder(filters.$sort);

    const q = Object.assign({
      where,
      order,
      limit: filters.$limit,
      offset: filters.$skip,
      raw: this.raw,
      distinct: true
    }, params.sequelize);

    if (filters.$select) {
      q.attributes = filters.$select;
    }

    let Model = this.applyScope(params);

    // Until Sequelize fix all the findAndCount issues, a few 'hacks' are needed to get the total count correct

    // Adding an empty include changes the way the count is done
    // See: https://github.com/sequelize/sequelize/blob/7e441a6a5ca44749acd3567b59b1d6ceb06ae64b/lib/model.js#L1780-L1782
    q.include = q.include || [];

    // Non-raw is the default but setting it manually breaks paging
    // See: https://github.com/sequelize/sequelize/issues/7931
    if (q.raw === false) {
      delete q.raw;
    }

    if (paginate) {
      return getSqlFromFindAll(Model, q)
        .then(x=>patchSelectQuery(x, Model))
        .then(sql => Promise.all([
          Model.sequelize.query(sql, { type: Model.sequelize.QueryTypes.SELECT }),
          Model.count(q)
        ]))
        .then(([result, count]) => {
          return {
            total: count,
            limit: filters.$limit,
            skip: filters.$skip || 0,
            data: result
          };
        })
        .catch(utils.errorHandler);
    } else {
      return Model.findAll(q).then(result => {
        return {
          data: result
        };
      }).catch(utils.errorHandler);
    }


  }
}

module.exports = CustomSequelizeService;

example.service.js

// Initializes the `example` service on path `/examples`
const CustomSequelizeService = require('../../utils/CustomSequelizeService');
const createModel = require('../../models/example.model');
const hooks = require('./example.hooks');

module.exports = function (app) {
  const Model = createModel(app);
  const paginate = app.get('paginate');

  const options = {
    name: 'examples',
    id: 'id',
    Model,
    paginate
  };

  // Initialize our service with any options it requires
  app.use('/examples', new CustomSequelizeService(options));

  // Get our initialized service so that we can register hooks and filters
  const service = app.service('examples');

  service.hooks(hooks);
};