## Feathers Knex Raw Sql Examples 2022-11-10
(cross-referenced in https://github.com/edwardsmarkf/comptonTransAnlys-conversion/blob/main/raw-feathers-example)0) Install feathers if not already done
npm install @feathersjs/feathers --save ;
1) Initialize new feathers environment
npm init --yes ;
npm install knex feathers-knex ;
2) create a username/password:
DROP USER IF EXISTS ‘knexUser’@‘localhost’ ;
CREATE USER ‘knexUser’@‘localhost’ IDENTIFIED BY ‘knexPassword’;
GRANT ALL ON `comptonTransAnlys`.* TO ‘knexUser’@‘localhost’
IDENTIFIED BY ‘knexPassword’ WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON `comptonTransAnlys`.* TO ‘knexUser’@‘localhost’ ;
# to test:
# mariadb —host=localhost —user=knexUser —password=knexPassword comptonTransAnlys ;
3) Create feather application
feathers generate app ; ## probably select "n" for security, which can be created later
4) Create default Knex service
****** !!Only required if this is a NEW app, otherwise skip this entire step!! ******
feathers generate service ; ### create type "knex" named "knex-raw-init" and MariaDb"
4a) edit knex-raw-init.model.js
should look like this:
module.exports = function (app) {
return app.get('knexClient');
};
its possible AFTER generating other calls that this file can (should?) be deleted
4b) move (or delete) directory
mv ./src/services/initialize-knex/ ./src/services/knex-raw-init-UNUSED/ ;
## -- or --
tar -zcvf knex-raw-init.gz ./src/services/knex-raw-init/ ;
## -- or/and --
rm -Rf ./src/services/knex-raw-init/ ;
4c) change index.js file
vi ./src/services/index.js ; COMMENT OUT:
// const initializeKnex = require('./initialize-knex/initialize-knex.service.js');
// app.configure(knexInitialize);
4e) modify ./config/default.json to reflect the database access
(or answer this in the “generate service” prompt)
'connection': 'mysql://knexUser:knexPassword@localhost:3306/comptonTransAnlys'
-- or --
"mysql": {
"client": "mysql2",
"connection":
{ "host" : "localhost"
, "port" : 3306
, "user" : "knexUser"
, "password" : "knexPassword"
, "database" : "comptonTransAnlys"
}
}
5) Create our custom service!
feathers generate service ; ## create custom service named "my-custom-service"
5a) change my-custom-service.class.js file
./src/services/my-custom-service/my-custom-service.class.js :
/* eslint-disable no-unused-vars */
const { QueryTypes } = require(‘feathers-knex’); // new line!!
exports.MyCustomService = class MyCustomService {
constructor (options, app) { // formerly just constructor(options)
this.options = options || {};
this.app = app; // new line!
}
async find (key) { /* note the default is “params” you may need to change this */
/* Postman GET: http://123.123.123.123:3030/my-custom-service/?one=twooo
notice /?one=twoooo – last slash between service name and question mark is OPTIONAL!
*/
console.info(JSON.stringify(key.query) + ‘ in my find routine!!’);
const knexClient = this.app.get(‘knexClient’);
// const rows = await knexClient.raw(‘SELECT VERSION;’);
// https://knexjs.org/guide/raw.html#raw-parameter-binding ::
// Positional bindings ? are interpreted as values and ?? are interpreted as identifiers.
const sqlStatement = ‘SELECT variable_value FROM information_schema.global_variables WHERE ?? = ?;’ ;
const rows = await knexClient.raw( sqlStatement, [‘VARIABLE_NAME’, ‘VERSION’]);
// should execute this: SELECT variable_value FROM information_schema.global_variables where variable_name = ‘VERSION’ ;
console.table (rows);
return `${JSON.stringify(key.query) + JSON.stringify(rows)} find routine!!`;
}
async get (key) {
/* Postman GET: http://123.123.123.123:3030/my-custom-service/123456789
notice /123456789 – only ONE parameter as a suffix
*/
console.info(JSON.stringify(key) + ‘ in get routine!!’);
const knexClient = this.app.get(‘knexClient’);
const mariaDbDate = await knexClient.raw(‘SELECT CURDATE ;’ );
console.table(mariaDbDate);
return `${JSON.stringify(key) + JSON.stringify(mariaDbDate)} get routine!`;
}
};
6) stop firewall if necessary
systemctl stop firewalld ;
7) Start the app
run DEBUG=* npm start ; run DEBUG=knex:query npm start ;
8) either postman or browser do:
http://123.123.123.123:3030/my-custom-service/1234 # get
http://123.123.123.123:3030/my-custom-service/?one=twoooooo # find
Optionally include parameters
/* named variables, please notice DOUBLE-COLON does backticks while SINGLE-COLON does quote characters! */
const sqlStatement = 'SELECT variable_value FROM information_schema.global_variables WHERE :var_name: = :val ;' ;
const sqlQuery = { var_name: 'VARIABLE_NAME' , val: 'VERSION' } ;
const rows = await knexClient.raw( sqlStatement, sqlQuery);
#########################################################################
easier to just generate…. to many steps to get this working.
4X) Create initial raw init model file (????)
mkdir --parent ./src/models/ ; // just in case this does not exist yet.
cat <<END > ./src/models/INIT-KNEX-RAW.model.js;
module.exports = function (app) {
return app.get('knexClient');
};
END
###############################################################################
## S E Q U E L I Z E:
0) npm install sequelize
1) create sequelize CUSTOM SERVICE named: “my-custom-service”
2) edit ./src/sequelize.js change logging false to logging true
3) change: src/services/my-custom-service/my-custom-service.class.js :
/* eslint-disable no-unused-vars */
const { QueryTypes } = require(‘sequelize’);
exports.MyCustomService = class MyCustomService { constructor (options, app) { this.options = options || {}; this.app = app; }
async find (key) {
const sequelize = this.app.get(‘sequelizeClient’);
const stimwordPositionSelectFind =
‘SELECT * FROM stimwordPosition WHERE 1 AND stimwordPositionWord = $stimwordPositionWord;’ ;
const stimwordPositionSelectParms =
{ ‘stimwordPositionWord’ : JSON.parse(key.query).stimwordPositionWord } ;
const rows = await sequelize.query
( stimwordPositionSelectFind
, { ‘bind’ : stimwordPositionSelectParms }
);
return rows;
}
};
################################ browser-console:
in browser console (common initial setup for all examples)
[you may need to visit the “about:blank” webpage — 2022-22-21 ]
first visit http://123.123.123.123:3030/ to “initialize” chrome
[ ‘//cdnjs.cloudflare.com/ajax/libs/socket.io/2.0.4/socket.io.js’
, ‘//unpkg.com/@feathersjs/client@^3.0.0/dist/feathers.js’
].forEach( (src) => {
let script = document.createElement(‘script’); script.src = src; script.async = false;
document.head.appendChild(script);
});
const app = feathers(); const socket = io(); app.configure(feathers.socketio(socket));
// optional if trying to run on another http[s] webpage rather than at http://123.123.123.123:3030:
const socket = io.connect ( ’35.192.14.166:3030’ , { ‘path’ : ‘/socket.io/’ , ‘port’ : ‘3030’ , ‘hostname’ : ’35.192.14.166’ , ‘secure’ : false } );
// socket.io.engine.transport
async function stimwordPositionFind(param) {
console.log( ‘param: ‘ + param);
let getResult = await app.service(‘my-custom-service’).find(param);
console.log(‘stimwordPosition function: ‘ + JSON.stringify(getResult) );
return getResult;
};
let stimwordPositionResult = null;
let key = { ‘query’ : { ‘stimwordWord’ : ‘horse’, ‘parm2’ : ‘answer2’ } } ;
stimwordPositionFind(key).then( value => { /* get(GET) */
stimwordPositionResult = value;
console.log(‘stimwordPosition(’ + key + ‘) : ‘ + JSON.stringify(stimwordPositionResult) );
});
async function stimwordPosition(id) { /* get(GET) (just one by index) */
console.log( ‘id: ‘ + id);
let getResult = await app.service(‘my-custom-service’).find(id);
console.log(‘stimwordPosition function: ‘ + JSON.stringify(getResult) );
return getResult;
};
############ GET GET GET GET GET GET
async get (id) { const sequelize = this.app.get(‘sequelizeClient’); const stimwordPositionSelectGet = `SELECT * FROM stimwordPosition WHERE 1 AND stimwordPositionAutoIncr = ${id};`; const rows = await sequelize.query(stimwordPositionSelectGet); return rows; }async function stimwordPosition(id) { /* get(GET) (just one by index) */
console.log( ‘id: ‘ + id);
let getResult = await app.service(‘my-custom-service’).get(id);
console.log(‘stimwordPosition function: ‘ + JSON.stringify(getResult) );
return getResult;
};
let stimwordPositionResult = null; let key = 1; // assuming your row ID is one
stimwordPosition(key).then( value => { /* get(GET) */
stimwordPositionResult = value;
console.log(‘stimwordPosition(’ + key + ‘) : ‘ + JSON.stringify(stimwordPositionResult) );
});
#####################################################
let stimwordPositionResult = null; let key = 1; // assuming your row ID is one
stimwordPosition(key).then( value => { /* get(GET) */
stimwordPositionResult = value;
console.log(‘stimwordPosition(’ + key + ‘) : ‘ + JSON.stringify(stimwordPositionResult) );
});
