Unexpected behavior with knex's select

For the following code, I get a result that is sometimes an array and other times an object. I want to receive the array, even if its empty.

export const GetByPId = async (userId, pId) => knex('table1').where({ userId, pId }).select(
  'userId',
  'pId',
  'id',
  'created',
  'updated',
);

In my Business object, I await the response

static async LoadByPId(userId, pId) {
    const data = await GetByPId(userId, pId);
    console.log(`result ${JSON.stringify(data)}`);
}

Once it returned

[{ userId: 1, id: 1 ... - I want this

and the next time it returned

{ userId: 1, id: 1 ... - Don't want this

Whats going on and how can I get it to always return an array?

Update #1

Now it only returns a single result.

Update #2

It went from bad to worse.

Now my other basic functions don't work properly. Knex only works on the first set of parameters and fails for everything else.

For example, if the express server was restarted and send a request for userId: 1 and pId: 1, it works. If I repeat the same request with same parameters, it works. But if I change the parameters (ie userId or pId) to another valid set, it fails. I have to restart the express server before trying any other parameters. I tested this on my app and postman.

My express code looks like follows

router.post('/list', auth, async (req, res) => {
  try {
    const biz= await BizObj.LoadByPId(req.user.id, req.body.pId);
    res.json(biz);
  } catch (ex) {
    console.log(ex);
    res.status(400).json('Unauthorized');
  }
});

Update #4

In case my knex config is the problem

development: {
    client: 'postgresql',
    connection: {
      database: 'somedb',
      user: 'SOMEUSER',
      password: '',
      timezone: 'UTC',
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      tableName: 'knex_migrations',
    },
  },

Update #5 Single page of code (only missing express setup)

in pg db / SomeObj

 id userId
 1  1
 2  2
 3  2

code sample

import knex from 'knex';
import express from 'express';

const config = {
  development: {
    client: 'pg',
    connection: {
      database: 'somedb',
      user: 'SOMEUSER',
      password: '',
      timezone: 'UTC',
    },
    pool: {
      min: 2,
      max: 10,
    },
    migrations: {
      tableName: 'knex_migrations',
    },
  },
};

const knexed = knex(config.development);
const SQL = knexed('SomeObj');
const GetAll = async userId => SQL.where({ userId }).select(
  'id',
  'userId',
);
const GetById = async (userId, id) => SQL.where({ userId, id }).first(
  'id',
  'userId',
);

class SomeObj {
    constructor(data, userId) {
        this.userId = userId;
        this.id = data.id;
    }
    static async LoadAll(userId) {
        const data = await GetAll(userId);
        if (!data || data.length === 0) return null;
        return data.map(r => new SomeObj(r, userId));
    }
    static async Load(userId, id) {
        const data = await GetById(userId, id);
        if (!data) return null;
        return new SomeObj(data, userId);
    }
}

const router = express.Router();

router.post('/list', async (req, res) => {
  try {
    const res1  = await SomeObj.LoadAll(req.body.id); // works and returns array
    const res2 = await SomeObj.Load(req.body.id, 2); // fails and returns undefined
    res.json({ res1, res2 });
  } catch (ex) {
    res.status(401).json(ex);
  }
});

No second query can be ran. Don't know if I'm missing something simple to close the connection.

Update #6

I swear knex is messing with me. Every time I try something (and revert back to confirm changes are due my new inputs), there are different responses. Now, both res1 and res2 return the correct result for the first request but the second request fails.

Update #7

Runkit example: https://runkit.com/tristargod/runkit-npm-knex

It runs for the first request but fails for all other requests on express server.

Update #8

Refer to https://github.com/tgriesser/knex/issues/2346#issuecomment-346757344 for further details. Thanks Mikael!

Solution

knex('table1')
 .where({ userId, pId })
 .select('userId', 'pId', 'id', 'created', 'updated')

Should return always an array of results. You are doing something else wrong that is not shown in the example.

Example code: https://runkit.com/embed/kew7v2lwpibn

RESPONSE TO UPDATE #7

tldr; Knex query builders are mutable so when re-using them .clone() is necessary. https://runkit.com/mikaelle/5a17c6d99cd063001284a20a

Nice example, from that it was easy to spot the problem 👍

You are reusing the same query builder multiple times without cloning it between the queries. If you would run your code with DEBUG=knex:* environment variable set, you would see that constructed queries are not correct after the first call.

const GetAll = async userId => SQL.clone().where({ userId }).select(
  'id',
  'userId',
);
const GetById = async (userId, id) => SQL.clone().where({ userId, id }).first(
  'id',
  'userId',
);