How to use data after Knex select query

Question

I am having a little trouble with retrieving the data from a table using knex select.

I would like to do something like this

function getUserData(userId){
    let user = knex.select('xp','money','rolls','twenty').from('users').where('user_id', userId);
   return user;
}


user = getUserData(userId);
user.xp;   // do something with this value

but this outputs the following (if i console.log it), but not the requested info from the Select query, unless i am just not sure how to retrieve it:

Builder {
  client:
   Client_MySQL {
 config: { client: 'mysql', connection: [Object] },
 connectionSettings:
  { host: '127.0.0.1',
    user: 'XXXXXXXXXX',
    password: 'XXXXXXXXXX',
    database: 'XXXXXXXXXX' },
 driver:
  { createConnection: [Function: createConnection],
    createPool: [Function: createPool],
    createPoolCluster: [Function: createPoolCluster],
    createQuery: [Function: createQuery],
    escape: [Function: escape],
    escapeId: [Function: escapeId],
    format: [Function: format],
    raw: [Function: raw] },
 pool:
  Pool {
    creator: [Function: create],
    destroyer: [Function: destroy],
    validate: [Function: validate],
    log: [Function],
    acquireTimeoutMillis: 60000,
    createTimeoutMillis: 30000,
    idleTimeoutMillis: 30000,
    reapIntervalMillis: 1000,
    createRetryIntervalMillis: 200,
    propagateCreateError: true,
    min: 2,
    max: 10,
    used: [],
    free: [],
    pendingCreates: [],
    pendingAcquires: [],
    destroyed: false,
    interval: null },
 valueForUndefined:
  Raw {
    client: [Circular],
    sql: 'DEFAULT',
    bindings: undefined,
    _wrappedBefore: undefined,
    _wrappedAfter: undefined,
    _debug: undefined },
 _events:
  { start: [Function],
    query: [Function],
    'query-error': [Function],
    'query-response': [Function] },
 _eventsCount: 4,
 makeKnex: [Function: makeKnex] },
  and: [Circular],
  _single: { table: 'users', only: false },
  _statements:
   [ { grouping: 'columns', value: [Array] },
     { grouping: 'where',
       type: 'whereBasic',
       column: 'user_id',
       operator: '=',
       value: '341007826375802900',
   not: false,
   bool: 'and' } ],
  _method: 'select',
  _debug: undefined,
  _joinFlag: 'inner',
  _boolFlag: 'and',
  _notFlag: false }

I'll write some more words here, as it requires me to do so, since it is mostly code. I hope this will be enough words.

Knex query builder returns a promise, so result can be accessed via .then callback or async/await js feature — coockoo, May 29 '18 at 02:13

score 5 · Answer 1 · answered May 29 '18 at 02:11

5

The query run asynchronous, so you need to explicitly wait for it to finish. One way to do this is using promises:

knex.select('xp','money','rolls','twenty').from('users').where('user_id', userId)
  .then(data => console.log(data));

Also make sure that the connection with the database is already established.

answered May 29 '18 at 02:11

Facundo Matteo

2,347
2
16
19

thanks, the database connection is esatblished already and this function is only called when a discord command is run. but how do i get the 'data' variable and it's usage outside of knex.select? – The-WebGuy May 29 '18 at 02:13
1

@The-WebGuy there's no way to do that exactly. Everything below that line will probably run before the code inside `.then()`. You should put all the code that needs the data inside `then()`. A clean way to do that is to create a function that does what you want with the data: `function doSomething(data) { /* do stuff */ }`, and then in the line of the query put `.then(doSomething)`. More info about promises here https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Promise and here https://stackoverflow.com/questions/27638060/understanding-js-promises – Facundo Matteo May 29 '18 at 02:24
Thanks again. hmmm, i guess I can't get it do do exactly what i want easily. I need this data out of there, so i can iterate over a number of dice rolls and then add to these amounts. I think i may have to rethink the entire functionality. I come from a php/laravel background where everything works a lot simpler - but probably because i understand it... So to select the users data and then inside, i would add to it? – The-WebGuy May 29 '18 at 02:30
@The-WebGuy Why can't you put that logic inside `.then`? If I understood more about your code I could help you a bit more. – Facundo Matteo May 29 '18 at 02:32
it is a lot of code, I have created a discord bot, that allows users to run commands like !r or !roll to roll a d20 (20 sided dice) - or !r 20d20+5d10, etc to roll 20 x d20s and 5 d10s for an example - when i iterate over the dice, i am adding up the money or XP they get and then i need to add it to their stats. Also, i need to create an entry into the table if that user doesn't exist. I would share my code, but it is very long and quite messy. How do you feel about a chat over discord? – The-WebGuy May 29 '18 at 02:38
@The-WebGuy It doesn't matter if the code is big or small. `.then()` receives a function as a parameter, and will call that function passing the data as a parameter. You can put all the logic of the dice inside a function and pass that function to `.then()`. I'm going to bed right now but if you are still with that problem tomorrow I don't have problem chatting with you. – Facundo Matteo May 29 '18 at 02:47
thanks for the help, i will see if i can move things around to make it work. Really appreciate the help. Good night – The-WebGuy May 29 '18 at 02:48
I managed to get it all working but one part, if you have a few mins, can you DM me on here? i can post my Discord username here and remove it after – The-WebGuy May 29 '18 at 18:15
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172022/discussion-between-facundo-matteo-and-the-webguy). – Facundo Matteo May 29 '18 at 19:40

score 0 · Answer 2 · answered Jan 16 '22 at 11:15

After config database just called an async function it will worked

const knex = require('knex')({
    client: 'sqlite3',
    connection: {
      filename: './mydb.db',
    },
    useNullAsDefault: true
  });
(async function(){
const posts = await knex('data');
console.log(posts)
})()

In this code i am using this sql query

SELECT * FROM data

How to use data after Knex select query

2 Answers2