Skip to content

Instantly share code, notes, and snippets.

@mrtnzagustin
Forked from joeherold/NativeQueryService.ts
Created April 20, 2020 20:14
Show Gist options
  • Save mrtnzagustin/96839673524dfa0a8de5d216bf25a0f1 to your computer and use it in GitHub Desktop.
Save mrtnzagustin/96839673524dfa0a8de5d216bf25a0f1 to your computer and use it in GitHub Desktop.
A sample Service for Sails.js to transform a (in this case sails-postgres) raw result from sendNativeQuery to an regular model result
/*
* 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();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment