Why won't my query work without await? How to fix 'query.orderBy' is not a function?

Question

I am using node.js, MySQL, knex, and express.

I am doing a simple query of a database, db.findAllEmoji().

const findAllEmoji = () => {
  return knex('emoji')
  .select('*');
};

I am working from previous code that works I am modeling after, but am still stuck. There are two large code blocks below The first is from my routes in which I render the page in routes\dashboard.js. The second is what I am modeling after.

What I have below in the first large code block returns undefined unless I use let query = await db.findAllEmoji();, only then will it return the results of the query. That would be fine, but...if I use await, then the .whereRaw and .orderBy throws these errors and I have not been able to get past these. Here's one of them.

TypeError: query.orderBy is not a function at C:\Users\pauli\repos\all-coursework-node-paulwinka\tests\02. MySQL database schema emoji\routes\dashboard.js:21:21 at processTicksAndRejections (internal/process/task_queues.js:97:5)

My sample code did not need await to work, so I would prefer a solution that figures out why my query doesn't work without await..or maybe.

So my questions are, why won't the original query not work without await...and how can I get it to work without await like in my model code? And if I just must use await in this case, how can I fix the errors with orderBy not working?

I've only been using these for a few weeks and am still learning the ropes. Thanks. :)

const express = require('express');
const db = require('../db');
const debug = require('debug')('app:routes:dashboard');

// creating instance of router.
const router = express.Router();
router.use(express.urlencoded({ extended: false }));
router.use(express.json());

router.get('/', async (req, res, next) => {
  try {
    const search = req.query.search;
    let query = db.findAllEmoji();
    if (search) {
      query = query.whereRaw('description LIKE ?', ['%' + search + '%']);
    } else {
      query = query.orderBy('emoji_id');
    }
    debug(`query length: ${query.length}`);
    res.render('dashboard/dashboard-user', {
      title: 'Dashboard - Emoji',
      active: 'dashboard',
      query,
    });
  } catch (err) {
    next(err);
  }
});

module.exports = router;

This is the code that did work that I am modeling after...maybe I am missed something obvious.

  try {
    const industry = req.query.industry;
    const search = req.query.search;
    const pageSize = parseInt(req.query.pageSize) || 10;
    const pageNumber = parseInt(req.query.page) || 1;

    const industryOptionList = {
      selected: industry || '',
      options: [
        { value: '', text: 'All Categories' },
        { value: 'hospitality', text: 'Hospitality' },
        { value: 'foodservice', text: 'Foodservice' },
        { value: 'IT', text: 'IT' },
        { value: 'defense', text: 'Defense' },
        { value: 'finance', text: 'Finance' },
        { value: 'construction', text: 'Construction' },
      ],
    };

    let query = db.getAllListings();
    if (industry) {
      query = query.where('company.industry', industry);
    }
    if (search) {
      // query = query.whereRaw('MATCH (review.title, review.review_text) AGAINST (? IN NATURAL LANGUAGE MODE)', [search]);
      query = query.whereRaw(
        'listing_title LIKE ? OR company.industry LIKE ? OR company_name LIKE ? OR company_description LIKE ?',
        ['%' + search + '%', '%' + search + '%', '%' + search + '%', '%' + search + '%']
        );
      } else {
        query = query.orderBy('posted_date');
      }
      const pager = await pagerUtils.getPager(query, pageSize, pageNumber, req.originalUrl);
      const listings = await query.limit(pageSize).offset(pageSize * (pageNumber - 1));
      debug(`listings length is ${listings.length}`);

    if (!req.xhr) {
      res.render('listing/listing-list', {
        title: 'Jobify: Listings',
        listings,
        industry,
        industryOptionList,
        search,
        pager,
      });
    } else {
      res.render('listing/search-results', { listings, pager: pager, layout: null });
    }
  } catch (err) {
    next(err);
  }
});

Here is my ..db code too in case it helps.

// get connection config
const config = require('config');
const { sum } = require('lodash');
const databaseConfig = config.get('db');
const debug = require('debug')('app:server');

//connect to the database
const knex = require('knex')({
  client: 'mysql',
  connection: databaseConfig,
});
const findAllEmoji = () => {
  return knex('emoji')
  .select('*');
};
module.exports = {
  knex,
  findAllEmoji,
};

Answer 1

If you are not awaiting or calling .then() for query builder, the query gets only built, but it well never be executed.

Query builder works in a way that you can add more .where, .join etc. parts to the query in different lines of code (like you are doing in router.get('/', async (req, res, next) => {) and when the query is ready it needs to be executed so that it will only then sent to DB sever to get response.