A sample Service for Sails.js to transform a (in this case sails-postgres) raw result from sendNativeQuery to an regular model result

NativeQueryService.ts

/*
 * Created Date: Monday, April 20th 2020, 3:03:54 pm
 * Author: Johannes Pichler
 *
 * Copyright (c) 2020 Webpixels e.U.
 */

let processAllRecords = require("waterline/lib/waterline/utils/query/process-all-records");

export type RawResult = {
  rows: any[];
  [key: string]: any;
};
class NativeQueryService {
  /**
   * getModelOrIdentity
   *
   * @private
   * @param {(Sails.Model | string)} modelOrIdentity
   * @returns {Sails.Model}
   * @memberof NativeQueryService
   */
  private getModelOrIdentity(
    modelOrIdentity: Sails.Model | string
  ): Sails.Model {
    // declare the orm model variable
    let Model: Sails.Model;

    // we have to get the model identity
    if (typeof modelOrIdentity === "string") {
      // get the model via identity
      Model = <Sails.Model>sails.models[modelOrIdentity];
    } else {
      // get the model as passed object
      Model = <Sails.Model>modelOrIdentity;
    }
    return Model;
  }

  /**
   * getRecordsFromRawResultOrArray
   *
   * @private
   * @param {(any[] | RawResult)} rawResultOrArrayOfRecords
   * @returns {Array<any>}
   * @memberof NativeQueryService
   */
  private getRecordsFromRawResultOrArray(
    rawResultOrArrayOfRecords: any[] | RawResult
  ): Array<any> {
    let records: Array<any>;

    // first check if the result is an array
    if (Array.isArray(rawResultOrArrayOfRecords)) {
      records = [...rawResultOrArrayOfRecords];
    }
    // then check if it is a rawResult with rows
    else if (
      rawResultOrArrayOfRecords &&
      rawResultOrArrayOfRecords["rows"] &&
      Array.isArray(rawResultOrArrayOfRecords["rows"])
    ) {
      records = [...rawResultOrArrayOfRecords["rows"]];
    } else {
      // oh nooo, no array of records or raw result with rows attribute
      // was passed
      throw new Error(
        "You mast pass an Array of plain records as first parameter"
      );
    }
    return records;
  }
  //
  /**
   * getSailsModelsFromNativeQueryResult
   *
   * @param {any[]} [rawRecors=[]]
   * @param {(Sails.Model | string)} modelOrIdentity
   * @memberof NativeQueryService
   */
  getModelsFromNativeQueryResult(
    rawResultOrArrayOfRecords: any[] | RawResult = [],
    modelOrIdentity: Sails.Model | string
  ): any[] {
    // declare the orm model variable
    let Model = this.getModelOrIdentity(modelOrIdentity);
    // declare records variable
    let records = this.getRecordsFromRawResultOrArray(
      rawResultOrArrayOfRecords
    );

    // destructure needed model information
    let { _orm: orm, meta, identity } = Model;

    // transform column names back to attribute names
    records = records.map((item) => {
      return Model._transformer.unserialize(item);
    });

    // modify existing result;
    processAllRecords(records, meta, identity, orm);

    // now return all stuff
    return records;
  }

  /**
   * nativeQuery
   *
   * @param {string} sql
   * @param {(any[] | null | undefined)} [valuesToEscape=[]]
   * @param {(Sails.Model | string)} modelOrIdentity
   * @returns {Promise<RawResult>}
   * @memberof NativeQueryService
   */
  async nativeQuery(
    sql: string,
    valuesToEscape: any[] | null | undefined = [],
    modelOrIdentity: Sails.Model | string
  ): Promise<RawResult> {
    if (!modelOrIdentity && valuesToEscape) {
      modelOrIdentity = <any>valuesToEscape;
    }
    // declare the orm model variable
    let Model = this.getModelOrIdentity(modelOrIdentity);

    // geth the adapter (datastore)
    let datastore: any = Model.getDatastore();

    // define query
    // let nativeQuery: string = `SELECT * FROM "user"`;

    // perform native query
    let rawResult: any = await datastore.sendNativeQuery(sql, valuesToEscape);
    return rawResult;
  }

  async getModelsFromNativeQuery(
    sql: string,
    valuesToEscape: any[] | null | undefined = [],
    modelOrIdentity: Sails.Model | string
  ) {
    let rawResult = await this.nativeQuery(
      sql,
      valuesToEscape,
      modelOrIdentity
    );
    return this.getModelsFromNativeQueryResult(rawResult, modelOrIdentity);
  }
}

export default new NativeQueryService();