Knex.js support for Node.js

Knex.js is a “batteries included” SQL query builder for Postgres, Microsoft SQL Server, MySQL, MariaDB, SQLite3, Oracle, and Amazon Redshift, designed to be flexible, portable, and fun to use. We do not support the creation of tables for this framework. Example:

Define database config:

const Config = require('../config');

module.exports = {
  client: 'postgresql',
  connection: Config.DATABASE_URL || {
    database: Config.DB_NAME,
    host: Config.DB_HOST,
    username: Config.DB_USER,
    password: Config.DB_PASSWORD
  }
};

Create bookshelf from Knex and Bookshelf:

const DatabaseConfig = require('../../db');

const Bookshelf = require('bookshelf');
const Knex      = require('knex')(DatabaseConfig);

module.exports = Bookshelf(Knex);

Add model for bookshelf:

const Bookshelf = require('../util/bookshelf');

const Config = require('../../config');

module.exports = Bookshelf.Model.extend({
  tableName: 'todos',
  url: function () {
    return `${Config.DOMAIN}/${this.get('id')}`;
  },
  serialize: function () {
    return {
      id: this.get('id'),
      title: this.get('title'),
      url: this.url(),
      completed: this.get('completed'),
      order: this.get('order'),
      object: 'todo'
    };
  }
});

Define method for model:

const Todo = require('../../models/todo');

exports.deleteAll = () => {
  // hack to get around Bookshelf's lacking destroyAll
  return new Todo().where('id', '!=', 0).destroy()
  .then(() => []);
};

Access model from webservice method:

exports.register = (server, options, next) => {

server.route([{
    method: 'DELETE',
    path: '/',
    config: {
      handler: (request, reply) => {
        reply(Controller.deleteAll());
      }
    }
  },
])
}

If table isn’t found from external, an unknown database table will be created: