CODEWITHSUNDEEP
node.jsknex.jsnode-oracledb
M.S.Udhaya Raj
M.S.Udhaya Raj

Reputation: 150

knex - Raw query execute - how to get the db result along with data type

while execute the raw query using knex (oracle) npm, how to get the data type of the each column along with the result.

select name,age from customers;

when we execute this as raw query using nodejs we can get the selected columns as array result.

rows:[{name: 'SAM', age: 35},{name:'IMRAN',age:25}]

I need data and also the data type of the each column along with the result of the raw query.

Expected Result:

{ "result": { "rows": [{ "name": "SAM", "age": 35 }, { "name": "IMRAN", "age": 25 }], "columns_details": { "name": "varchar 2(32)", "age": "number(2)" } } }

Is there any option is available to get the above result?pls suggest me to get this requirement.

thanks in advance.

Upvotes: 2

Views: 4144

Answers (2)

Michel Floyd
Michel Floyd

Reputation: 20246

I ran into the same issue using postgres as the db. The db results of a SELECT query are in the .rows key of what is returned from .raw()

Another issue is that raw queries don't automatically camelCase the results so I also had to deal with that.

I ended up making a little async utility function to run the raw query, extract the results, and camelCase the keys.

import { camelCase, mapKeys } from 'lodash';

export const rawQuery = async (query) => {
  const result = await knex.raw(query);
  return result && result.rows
    ? result.rows.map((el) => mapKeys(el, (_, key) => camelCase(key)))
    : [];
};

The parameter query is a string containing an arbitrary SQL SELECT query. This function doesn't allow for variables to be passed into the query but that would be a simple extension.

Upvotes: 0

Mikael Lepistö
Mikael Lepistö

Reputation: 19728

With knex.raw queries some drivers might return also datatype information. Knex doesn't provide any special support for that though, so you need to check node-oracledb documentation how to get that column metadata.

Upvotes: 1

Related Questions