A simple Bookshelfjs sample #db

(README).md

A simple Bookshelfjs sample

Bookshelfjs ( http://bookshelfjs.org/ ) is a javascript ORM for Node.js This gist is a simple bookshelfjs sample.

Installation

1. Clone this gist to a local folder
2. Run npm run build in the local folder

Configuration

• This sample read the database config from config.js.
  The default configuration is sqlite db client. The sqlite database file is "db.sqlite".

Database Schema

• node index.js --createSchema : Create the database schema. (The default SQL client is sqlite3. The schema is created by db_schema.js )
• This simple sample creates 2 tables: MACHINE & MACHINE_STATUS
• MACHINE Table

+------------------------------------------------+
| ID            [ PK, INTEGER, auto-increments ] |
|================================================|
| MACHINE_NAME  [ varchar(255), UNIQUE, INDEX ]  |
| HOST_NAME     [ varchar(255) ]                 |
+------------------------------------------------+

• MACHINE_STATUS Table

+------------------------------------------------------+
| MACHINE_ID    [ FK REFERENCES MACHINE(ID), INTEGER ] |
|======================================================|
| STATUS        [ varchar(255) ]                       |
+------------------------------------------------------+

ORM

• orm.js: defineded all the Object-relational mapping
• orm_query.js: A sample of query Machines data. (node index.js --ormQuery for test it.)
• orm_update.js: A sample of update Machines data. (node index.js --ormUpdate for test it.)

.gitignore

node_modules
*.sqlite

.jshintrc

{
  "curly": true,
  "eqeqeq": true,
  "immed": true,
  "latedef": true,
  "newcap": true,
  "noarg": true,
  "sub": true,
  "undef": true,
  "unused": "vars",
  "boss": true,
  "eqnull": true,
  "node": true,
  "trailing": true,
  "strict": true,
  "indent": 2,
  "predef": []
}

config.js

'use strict';

var path = require('path');

exports.dbClient = process.env.DB_CLIENT || 'sqlite3';

exports.dbConn = {
  sqlite3: {
    filename: path.join(__dirname, 'db.sqlite')
  },
  pg: process.env.PG_CONNECTION_STRING
};

db_schema.js

'use strict';

var logger = require('winston');

exports.recreateSchema = function (knex, callback) {
  callback = callback || function () {};

  /* Drop MACHINE_STATUS, MACHINE tables and re-create these 2 tables */
  logger.info('Re-creating database schema');
  knex.schema.dropTableIfExists('MACHINE_STATUS')
    .then(function() {
      return knex.schema.dropTableIfExists('MACHINE');
    }).then(function () {
      return knex.schema.createTable('MACHINE', function(tbl) {
        tbl.increments('ID').primary();
        tbl.string('MACHINE_NAME').unique().index();
        tbl.string('HOST_NAME');
      });
    }).then(function () {
      return knex.schema.createTable('MACHINE_STATUS', function(tbl) {
        tbl.integer('MACHINE_ID');
        tbl.string('STATUS');
        tbl.foreign('MACHINE_ID').references('ID').on('MACHINE');
      });
    }).then(function () {
      logger.info('Database schema have been updated');
      callback();
    }).catch(function (err) {
      logger.error('Create schema error: %s', err.toString());
      callback(err);
    });
};

index.js

'use strict';

var config = require('./config.js'),
  logger = require('winston'),
  Bookshelf = require('bookshelf'),
  Knex = require('knex'),
  dbSchema = require('./db_schema.js'),
  orm = require('./orm.js'),
  ormUpdate = require('./orm_update.js'),
  ormQuery = require('./orm_query.js'),
  optimist = require('optimist')
    .alias('h', 'help')
    .describe('createSchema', 'ReCreate Database schema')
    .describe('ormUpdate', 'Update all machine data')
    .describe('ormQuery', 'Query all machine data')
    .usage('Knex Sample tests');

(function (argv) {
  var knex, bookshelf;

  if (argv.help) {
    optimist.showHelp();
    process.exit(0);
  }

  /* initialize logger */
  logger.cli();
  logger.default.transports.console.level = 'debug';
  logger.default.transports.console.timestamp = true;
  logger.info('bookshelf test');

  /* initialize knex db client */
  logger.info('creating db client: %s', config.dbClient);
  knex = new Knex({
    client: config.dbClient,
    connection: config.dbConn[config.dbClient]
  });
  /* initialize bookshelf & bookshelf plugins */
  bookshelf = new Bookshelf(knex);
  bookshelf.plugin('virtuals');

  if (argv.createSchema) {
    /* re-create database schema */
    dbSchema.recreateSchema(knex, function (err) {
      if (err) {
        logger.error('create database schema error: %s', err.toString());
      }
      knex.destroy();
    });
    return;
  }
  else if (argv.ormUpdate) {
    ormUpdate.update(orm(bookshelf), function (err) {
      if (err) {
        logger.error('ORM update error: %s', err.toString());
      }
      knex.destroy();
    });
  }
  else if (argv.ormQuery) {
    ormQuery.query(orm(bookshelf), function (err) {
      if (err) {
        logger.error('ORM query error: %s', err.toString());
      }
      knex.destroy();
    });
  }
  else {
    knex.destroy();
    optimist.showHelp();
    process.exit(0);
  }
})(optimist.argv);

orm.js

'use strict';

var logger = require('winston'),
  Bluebird = require('bluebird');

module.exports = exports = function (bookshelf) {
  var Machine, Machines, MachineStatus;

  MachineStatus = bookshelf.Model.extend({
    tableName: 'MACHINE_STATUS',
    virtuals: {
      machineId: {
        get: function () { return this.get('MACHINE_ID'); },
        set: function (val) { this.set('MACHINE_ID', val); }
      },
      status: {
        get: function () { return this.get('STATUS'); },
        set: function (val) { this.set('STATUS', val); }
      }
    }
  });

  Machine = bookshelf.Model.extend({
    tableName: 'MACHINE',
    status: function() {
      return this.hasOne(MachineStatus);
    },
    print: Bluebird.method(function () {
      var self = this;
      return this.status().fetch().then(function (model) {
        logger.info('Name: %s, Host: %s, Status: %s', 
          self.name, self.host, model.status);
      });
    }),
    virtuals: {
      id: {
        get: function () { return this.get('ID'); },
        set: function (val) { this.set('ID', val); }
      },
      host: {
        get: function () { return this.get('HOST_NAME'); },
        set: function (val) { this.set('HOST_NAME', val); }
      },
      name: {
        get: function () { return this.get('MACHINE_NAME'); },
        set: function (val) { this.set('MACHINE_NAME', val); }
      }
    }
  });

  Machines = bookshelf.Collection.extend({
    model: Machine
  });

  return {
    bookshelf: bookshelf,
    Machine: Machine, 
    MachineStatus: MachineStatus,
    Machines: Machines
  };
};

orm_query.js

'use strict';

var logger = require('winston'),
  Bluebird = require('bluebird');

exports.query = function (ormContext, callback) {
  callback = callback || function () {};

  logger.debug('Begin ORM query');
  new ormContext.Machine().fetchAll().then(function (collection) {
    var machinesPrint = [];
    collection.each(function (machine) {
      machinesPrint.push(machine.print());
    });
    return Bluebird.all(machinesPrint);
  }).finally(function () {
    logger.debug('End ORM query');
    callback();
  });
};

orm_update.js

'use strict';

var logger = require('winston');

exports.update = function (ormContext, callback) {
  var machine;

  logger.info('ORM update data');
  callback = callback || function () {};

  machine = new ormContext.Machine({
    name: 'Machine1',
    host: '192.168.1.1'
  });

  machine.save().then(function (model) {
    var machineStatus;

    logger.info('machine saved: %j', model, {});
    machineStatus = new ormContext.MachineStatus({
      machineId: model.id,
      status: 'running'
    });
    machineStatus.save().then(function (model) {
      logger.info('machine status saved: %j', model, {});
      callback();
    });
  });
};

package.json

{
  "name": "knextest",
  "version": "0.0.0",
  "description": "A simple knex test",
  "main": "index.js",
  "scripts": {
    "build": "npm install"
  },
  "author": "lexiongjia@gmail.com",
  "license": "BSD-2-Clause",
  "dependencies": {
    "knex": "~0.6.22",
    "winston": "~0.8.0",
    "pg": "~3.4.4",
    "sqlite3": "~3.0.0",
    "optimist": "~0.6.1",
    "bookshelf": "~0.7.7",
    "bluebird": "~0.11.6"
  }
}