Is it pointless to use Knex if I prefer using raw over its built-in functions?

Question

I know Knex has various benefits, which is why I am contemplating using it for my Node application. I have used it before, and I really enjoy writing the raw queries with it. (Note: I'm still in the learning stages for Knex.) Personally, I think they make the code look slightly better than using basic SQL. It's from this point that my question stems, because maybe my preference is silly here.

I would also love opinions on the built-in functions for Knex. For people who prefer those over using raw, what do you like about them? I am aiming for consistency in my application regardless of the route I choose.

Thanks for your input!

Answer 1

I know this post is a little older but I agree with the hybrid approach. There are certain situations where the query builder syntax provides good feedback for a "bad" query being written which is great.

On the opposite side, I have some queries that I think would just become too verbose written using the builder so I use .raw. Here is an example of a query that I would consider as keeping in the .raw format.

    select
    s.client_id,
    s.package_id,
    p.products as pp,
    s.products as sp
    from (
      # grouped subscribed products
      select
      client_id,
      package_id,
      group_concat(product_id order by product_id) as products
      from subscriptions where package_id is not null
      group by client_id, package_id) as s
    inner join (
      # grouped package products
      select
      package_id,
      group_concat(product_id order by product_id) as products
      from package_products
      group by package_id) as p
    on p.package_id = s.package_id where p.products <> s.products
    order by s.client_id

I could certainly use the builder but I find the raw SQL a lot easier to grasp with nested selects. I also create an abstract class to simplify down into a micro-ORM.

class Table {
  constructor() {
    // nothing
  }

  static tableName = null;

  /**
   * Find by id
   * @param {Integer} userId
   * @return {Promise}
   *
   * Example:
   * Table.findById(id).then(result => {
   *   console.log(result)
   * }).catch(err => {
   *   console.error(err);
   * })
   */
  static findById(itemId) {
    return this.findOneBy({id: itemId});
  }

  /**
   * Generic findBy
   * @param {String|Object} column or objcet with keys
   * @param {Any} value
   */
  static findOneBy(column, value = null) {
    return this.findBy(column, value).then(results => {
      return results[0];
    });
  }

  /**
   * Generic findBy
   * @param {String|Object} column or objcet with keys
   * @param {Any} value
   */
  static findBy(column, value = null) {
    return database.getConnection().then(conn => {
      const schema = this.schemaName;
      const query = conn.select().from(this.tableName);
      if (schema){
        query.withSchema(schema);
      }
      if (typeof column === 'object '&& !Array.isArray(column) && value === null) {
        for (const col in column) {
          query.where(col, column[col]);
        }
      } else {
        query.where(column, value);
      }
      return query;
    });
  }
}

This allows me to create models by using:

class Client extends Table {
  static tableName = 'clients';
  constructor() {
    super();
  }
}

Client.findById(1).then(result => {
  console.log(result);
}).catch(console.error);

Answer 2

It's not pointless. It depends a bit on whether you're working in a team environment, who else is likely to have to maintain it, and how comfortable they are with writing SQL queries. Note that many things that can be done with Knex can also be done with a direct database driver, so as with many technology choices it comes down to personal/team preference and ease of maintenance.

Even assuming you never use the query builder, Knex offers:

• relatively simple configuration according to the current NODE_ENV
• connection/pool management
• easy parameterisation
• easy transactions
• easy migrations and seeding

Why not use .raw? Well it's taster's choice, and query builders aren't for everyone. However, query builder fans will tell you:

• it can be easier to migrate between database backends when you're not dealing with a bunch of raw SQL
• many people find Knex syntax easier to read, given the population of people who can reasonably understand a JavaScript promise chain likely outweighs those who grok SQL
• it tends to be rather more compact
• it provides a level of name/syntax safety (and transpile-time type safety and editor support if you're using TypeScript) over .raw.

Query builders also lend themselves nicely to composition, so the likes of:

const userPhoneNumbers = knex('users').select({ id, email });

// later...
const gmailUsers = userPhoneNumbers.where('email', 'ilike', '%gmail.com');

// still later...
return gmailUsers.where('email_confirmed', true);

A contrived example, but it can be quite expressive when dealing with less trivial requirements.