Skip to content

Instantly share code, notes, and snippets.

@xiongjia
Last active March 14, 2022 17:34
Show Gist options
  • Star 11 You must be signed in to star a gist
  • Fork 4 You must be signed in to fork a gist
  • Save xiongjia/85df587ce3535d1c6151 to your computer and use it in GitHub Desktop.
Save xiongjia/85df587ce3535d1c6151 to your computer and use it in GitHub Desktop.
A simple knex sample #db

A simple knex sample

knexjs ( http://knexjs.org/ ) is SQL query builder.
This gist is a simple knexjs sample.

Installation

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

Usage & Database Schema

  • node index.js --createSchema : Create the database schema. (The default SQL client is sqlite3. The schema is created by db_schema.js )
  • node index.js --update : Run INSERT & UPDATE & TRANSACTION Samples (db_update.js).
  • node inex.js --query : Run SELECT samples (db_query.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) ]                       |
+------------------------------------------------------+

Configuration

  • This sample read the database config from config.js.
    The default configuration is sqlite db client. The sqlite database file is "db.sqlite".
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.queryData = function (knex, callback) {
callback = callback || function () {};
logger.info('Query machine data');
knex('MACHINE')
.join('MACHINE_STATUS', 'MACHINE_STATUS.MACHINE_ID', '=', 'MACHINE.ID')
.select('MACHINE.ID as id',
'MACHINE.HOST_NAME as host_name',
'MACHINE.MACHINE_NAME as machine_name',
'MACHINE_STATUS.STATUS as status')
.map(function (row) { return row; })
.then (function (data) {
data = data || [];
logger.info('query machine data, length = (%d)', data.length);
callback(null, data);
}).catch(function (err) {
logger.error('query machine data error: %s', err.toString());
callback(err);
});
};
'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 logger = require('winston'),
async = require('async'),
_ = require('underscore');
function updateMachine(ctx) {
var knex, trx;
knex = ctx.knex;
trx = ctx.trx;
return knex('MACHINE').transacting(trx)
.select('ID')
.where({ MACHINE_NAME: ctx.name })
.map(function (row) { return row.ID; })
.then(function (data) {
return (data.length === 0 ? undefined : data[0]);
}).then(function (machineID) {
if (!machineID) {
/* add a new machine */
return knex('MACHINE').transacting(trx)
.insert({ MACHINE_NAME: ctx.name, HOST_NAME: ctx.info.host })
.returning('MACHINE_ID')
.then(function (data) {
return data[0];
});
}
else {
/* update machine item */
return knex('MACHINE').transacting(trx)
.where({ ID: machineID })
.update({ HOST_NAME: ctx.info.host })
.then(function () {
return machineID;
});
}
}).then(function (machineID) {
return knex('MACHINE_STATUS').transacting(trx)
.select('STATUS')
.where({ MACHINE_ID: machineID})
.map(function (row) { return row.STATUS; })
.then(function (data) {
return {
id: machineID,
machineStatus: (data.length === 0 ? undefined : data[0])
};
});
}).then(function (data) {
if (!data.machineStatus) {
return knex('MACHINE_STATUS').transacting(trx)
.insert({ MACHINE_ID: data.id, STATUS: ctx.info.status });
}
else {
return knex('MACHINE_STATUS').transacting(trx)
.where({ MACHINE_ID: data.id })
.update({ STATUS: ctx.info.status });
}
});
}
exports.updateData = function (knex, callback) {
var tasks, MACHINE_DATA;
tasks = [];
MACHINE_DATA = {
machine1: { host: '192.168.1.2', status: 'running' },
machine2: { host: '192.168.1.3', status: 'stopped' }
};
callback = callback || function () {};
_.each(_.keys(MACHINE_DATA), function (item) {
tasks.push(function(done) {
var trxErr;
done = done || function () {};
/* create transaction */
knex.transaction(function(trx) {
updateMachine({
knex: knex,
trx: trx,
name: item,
info: MACHINE_DATA[item]
}).then(function () {
logger.info('Committing machine %s', item);
trx.commit();
}).catch(function (err) {
logger.error('transaction err: %s', err.toString());
trx.rollback();
});
}).then(function () {
logger.info('transaction ended');
}).catch(function (err) {
trxErr = err;
logger.error('transaction ended with err: %s', err.toString());
}).finally(function () {
logger.info('transaction finished');
done(trxErr);
});
});
});
async.series(tasks, function (err) {
callback(err);
});
};
'use strict';
var config = require('./config.js'),
logger = require('winston'),
dbSchema = require('./db_schema.js'),
dbUpdate = require('./db_update.js'),
dbQuery = require('./db_query.js'),
optimist = require('optimist')
.alias('h', 'help')
.describe('createSchema', 'ReCreate Database schema')
.describe('update', 'Update all machine data')
.describe('query', 'Query and list all machine data')
.usage('Knex Sample tests');
(function (argv) {
var knex;
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('knex test');
/* initialize knex */
logger.info('creating db client: %s', config.dbClient);
knex = require('knex')({
client: config.dbClient,
connection: config.dbConn[config.dbClient]
});
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.query) {
/* query machine data */
dbQuery.queryData(knex, function (err, data) {
if (err) {
logger.error('Query data error: %s', err.toString());
knex.destroy();
return;
}
logger.info('Query Data: %j', data || {}, {});
knex.destroy();
});
return;
}
else if (argv.update) {
/* update machine data */
dbUpdate.updateData(knex, function (err) {
if (err) {
logger.error('update database error: %s', err.toString());
}
logger.info('MACHINE data have been updated');
knex.destroy();
});
return;
}
else {
knex.destroy();
optimist.showHelp();
process.exit(0);
}
})(optimist.argv);
{
"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",
"async": "~0.9.0",
"optimist": "~0.6.1",
"underscore": "~1.7.0"
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment