How to use knex - 10 common examples

import Knex from "knex";

const knex = Knex({});
// $ExpectError - invalid Client
Knex({
  client: "foo"
});

knex
  .clearSelect()
  .clearWhere()
  .select("foo")
  .withSchema("a")
  .from("bar")
  .where("foo", 2)
  .where({ mixed: "hi" })
  .orWhere("bar", "foo")
  .whereNot("asd", 1)
  .whereIn("batz", [1, 2]);

var cwd           = process.cwd();
var isDev         = parseInt(process.env.BOOKSHELF_DEV, 10);

var _             = require('lodash');

var Ctors = {};

var Common          = require('knex/lib/common').Common;

Ctors.Raw           = require('knex/lib/raw').Raw;
Ctors.Builder       = require('knex/lib/builder').Builder;
Ctors.SchemaBuilder = require('knex/lib/schemabuilder').SchemaBuilder;

Ctors.Model         = require('../../../dialects/sql/model').Model;
Ctors.Collection    = require('../../../dialects/sql/collection').Collection;

var fs            = require('fs');
var objectdump    = require('objectdump');

// This is where all of the info from the query calls goes...
var output     = {};
var comparable = {};
var counters   = {};

exports.setLib = function(context) {

  var logMe = function(logWhat) {

async function execute() {
  try {
    // Dynamically load app or config from the path provided package.json script
    const [,, command, importPath, ...args] = process.argv
    const execute = command && getCommand(commands, command.split(':'))
    if (!isFunction(execute)) {
      throw new Error(`Unknown command: ${command}`)
    }
    let arg = (await import(path.resolve(importPath))).default
    if (isFunction(arg)) {
      arg = await arg()
    } else if (isPlainObject(arg) && arg.knex) {
      arg = Knex(arg.knex)
    }
    const res = await execute(arg, ...args)
    process.exit(res === true ? 0 : 1)
  } catch (err) {
    if (err instanceof Error) {
      console.error(
        chalk.red(`${err.detail ? `${err.detail}\n` : ''}${err.stack}`)
      )
    } else {
      console.error(chalk.red(err))
    }
    process.exit(1)
  }
}

_.each(suffixMap, (condition, key) => {
    if (_.endsWith(arg.name.value, key) && (!found)) {
      const columnName = _.head(_.split(arg.name.value, key));

      switch (key) {
        case '_IsNull':
          if (arg.value.value) {
            query.whereNull(columnName);
          } else {
            query.whereNotNull(columnName);
          }
          break;
        case '_In':
          query.whereIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_NotIn':
          query.whereNotIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_LikeNoCase':
          query.whereRaw(condition, [columnName,
            isVarDef ? knex.raw(varValue) : values.toLowerCase()
          ]);
          break;
        case '_Like':
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
        default:
          // console.log('columnName, values :: ', columnName, values);
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;

if (_.endsWith(arg.name.value, key) && (!found)) {
      const columnName = _.head(_.split(arg.name.value, key));

      switch (key) {
        case '_IsNull':
          if (arg.value.value) {
            query.whereNull(columnName);
          } else {
            query.whereNotNull(columnName);
          }
          break;
        case '_In':
          query.whereIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_NotIn':
          query.whereNotIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_LikeNoCase':
          query.whereRaw(condition, [columnName,
            isVarDef ? knex.raw(varValue) : values.toLowerCase()
          ]);
          break;
        case '_Like':
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
        default:
          // console.log('columnName, values :: ', columnName, values);
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
      }

      found = true;

var crypto = require('crypto');
var Knex = require('knex');
var twilio = require('twilio');
var client = twilio(process.env.TWILIO_ACCOUNT_SID, process.env.TWILIO_AUTH_TOKEN);

var knex = Knex.initialize({
  client: 'pg',
  connection: process.env.DATABASE_URL
});

// Finds reminders for cases happening tomorrow
var findReminders = function() {
  return knex('reminders')
    .where('sent', false)
    .join('cases', 'reminders.case_id', '=', 'cases.id')
    .where('cases.date', 'tomorrow')
    .select();
};

findReminders().exec(function(err, results) {
  if (results.length === 0) {
    console.log('No reminders to send out today.');

query.whereNotNull(columnName);
          }
          break;
        case '_In':
          query.whereIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_NotIn':
          query.whereNotIn(columnName, isVarDef ? knex.raw(varValue) : values);
          break;
        case '_LikeNoCase':
          query.whereRaw(condition, [columnName,
            isVarDef ? knex.raw(varValue) : values.toLowerCase()
          ]);
          break;
        case '_Like':
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
        default:
          // console.log('columnName, values :: ', columnName, values);
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
      }

      found = true;
      return false;
    }
  });

query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
        default:
          // console.log('columnName, values :: ', columnName, values);
          query.whereRaw(condition, [columnName, isVarDef ? knex.raw(varValue) : values]);
          break;
      }

      found = true;
      return false;
    }
  });

  // Its now, safe to assume that this is equals operator..
  if (!found) {
    query.whereRaw('?? = ?', [arg.name.value, isVarDef ? knex.raw(varValue) : values]);
    found = true;
  }

  return found;
}

if (ref) {
        // Copy all the properties to the reference nodes.
        let actualNode = getNode(this.graph.nodesById, ref);
        let relations = actualNode.modelClass.getRelations();

        _.forOwn(actualNode.model, (value, key) => {
          if (!getRelation(relations, key) && !_.isFunction(value)) {
            refNode.model[key] = value;
          }
        });

        refNode.model.$omit(refNode.modelClass.uidProp, refNode.modelClass.uidRefProp);
      }
    }

    return Promise.resolve(this.models);
  }
}

screen.append(searchBox);
screen.append(tableInfo);

// Quit on Escape, q, or Control-C.
screen.key(['C-q'], function(ch, key) {
  return process.exit(0);
});


screen.key(['r', 'C-r'], function(ch, key) {
  rawQuery.focus();
});

// store list of tables
var tables = [],
    knex = Knex.knex;  //refence to knex instance



// load the table
tablesList.on('select', function(event, selectedIndex){
  var tableName = tables[selectedIndex];
  log.debug('selected table:', tableName);

  knex(tableName)
    .select()
    .then(function(rows){
      if (rows.length === 0) {
        return;
      }
      var columns = _.keys(rows[0]);