Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
A simple Bookshelfjs sample #db

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.)
node_modules
*.sqlite
{
"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": []
}
'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
};
'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);
});
};
'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);
'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
};
};
'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();
});
};
'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();
});
});
};
{
"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"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment