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

Question

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.

I think this is your best bet: github.com/knex/knex/issues/1135#issuecomment-293405104. Because, (I think), you are trying to query the data schema with the data. — trk
– trk, Commented Dec 12, 2020 at 10:14

Mikael Lepistö · Accepted Answer · 2021-01-09 21:13:16Z

1

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.

answered Jan 9, 2021 at 21:13

Mikael Lepistö

19.9k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michel Floyd · Accepted Answer · 2022-03-01 23:57:34Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related