node-mysql-wrapper
Advanced tools
This is a node js module, written in TypeScript (module is compiled to JavaScript), which manages mysql (node-mysql) connection and models in a way that you expected!
$ npm install node-mysql-wrapper
Sometimes I may also ask you to install the latest version from Github to check if a bugfix is working. In this case, please do:
$ npm install nodets/node-mysql-wrapper
This is a node.js wrapper for node-mysql driver package. It is written in TypeScript, does not require compiling (all JavaScript files you need are inside the 'compiled' folder).
Here is an example on how to use it:
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
database : 'my_db'
});
var wrapper = require('node-mysql-wrapper');
var db = wrapper.wrap(connection);
/* ES6: */
import {wrap} from "node-mysql-wrapper";
let db = wrap(connection);
/* END ES6*/
//or (without need of require mysql module) ->
//var db = wrapper.wrap("mysql://user:pass@127.0.0.1/databaseName?debug=false&charset=utf8");
db.ready(function(){
//your code goes here
//users -> an example table inside the database, just call it like property:
db.table("users").findById(8,function(user){
console.dir(user);
//to destroy the whole connection, its events and its tables use:
db.destroy();
}); //or using promises: findById(8).then(function(rowResults){...});
});
From this example, you can learn the following:
destroy() which makes sure all remaining
queries are executed before sending a quit packet to the mysql server.Thanks goes to the people who have contributed code to this module, see the GitHub Contributors page.
If you'd like to discuss this module, or ask questions about it, please use one of the following:
The recommended way to establish a wrapped-connection is this:
var wrapper = require('node-mysql-wrapper');
var db = wrapper.wrap("mysql://user:pass@127.0.0.1/databaseName?debug=false&charset=utf8");
db.ready(function(){
});
However, a wrapped-connection can also be implicitly established by wrapping an existing mysql connection:
var mysql = require('mysql');
var connection = mysql.createConnection({
host : 'localhost',
user : 'me',
password : 'secret',
database : 'my_db'
});
var wrapper = require('node-mysql-wrapper');
var db = wrapper.wrap(connection);
db.ready(function(){
});
Depending on how you like to code, either method may be appropriate. But in order to works always use .ready and a callback inside it.
Read them at the node-mysql module documentation
There are two ways to end a connection. Terminating a connection gracefully is
done by calling the end() method:
db.end(function(err) {
// The connection, table events and queries are terminated now
});
//Surely you can have a direct access to mysql connection from db.connection object, if you ever need it.
This will make sure all previously enqueued queries are still before sending a
COM_QUIT packet to the MySQL server. If a fatal error occurs before the
COM_QUIT packet can be sent, an err argument will be provided to the
callback, but the connection will be terminated regardless of that.
An alternative way to end the connection is to call the destroy() method.
This will cause an immediate termination of the underlying socket.
Additionally destroy() guarantees that no more events or callbacks will be
triggered for the connection.
db.destroy();
Unlike end() the destroy() method does not take a callback argument.
db.useOnly('users','comments',['or_an_array_of_tables','comment_likes']);
//this goes before db.ready function.
//all code you will see bellow goes inside db.ready(function () { //code here });
var usersTable = db.table("users"); //yes, just this :)
console.log('available columns: '+ usersTable.columns);
console.log('primary key column name: '+ usersTable.primaryKey);
console.log('find, findById, findAll, save and remove methods can be called from this table');
usersTable.find({mail:"= makis@omakis.com"},function(results){
});
They are 4 types of method queries, the find/findById/findSingle,findAll (select), save (insert or update), remove. All values you pass there are auto-escaped to protect the database from sql injections.
All methods return promises.
Column keys are auto converted to object properties, this means that user_id column on database table will be available as userId, same for table names too.
Simple 'find by id' method , this find method always returns one result.
db.table("users").findById(18,function(user){
console.log("SELECT * FROM users WHERE user_id = 18");
console.dir(user);
});
Simple 'find single' method , this find method always returns one result.
db.table("users").findSingle({mail:'= makis@ideopod.com'},function(user){
console.log("SELECT * FROM users WHERE mail = 'makis@ideopod.com' LIMIT 1");
console.dir(user);
});
Simple 'find by' method this find method always returns an array.
db.table("users").find({yearsOld:'>= 18'},function(users){
console.log("SELECT * FROM users WHERE yearsOld >= 18. results: ";
console.dir(users);
});
Simple 'find all' method , which is the same as db.table("tablename").find({},callback); or .find({ tableRules : { limit :42 }} if rules passed.
db.table("users").findAll({limit :42}).then(function(users){
console.log("SELECT * FROM users LIMIT 42. results: ");
console.dir(users);
});
An 'advanced find' method. Find all users where years_old = 22, find the user's info, find user's comments, the comment's likes and users who liked them.
var dbUsers = db.table("users");
var criteria = dbUsers.criteria
.where("yearsOld").gt(18)
.exclude("password","createdDate") //or .except(...columns). Removes that column(s) from the select query.
.joinAs("info","userInfos","userId").at("info").limit(1)
//with.at('tableOrPropertyName') we are going and passing criterias inside the info property
//this will pass the the result as object not as array, because of limit(1)
.parent() // or .original() here will be redirect to parent object, ( user(s) table) to continue our query...
//original() goes to the first-original-primary table, parent() goes to the parent table, you can have unlimited .at('joinedTableOrProperty') functions.
.join("comments","userId")
.at("comments")
.orderBy("commentId",true) //true if you want desceding ( ORDER BY COLUMN_KEY DESC )
.joinAs("likes","commentLikes","commentId")
.at("likes")
.joinAs("likers","users","userId").build();
//In order to go to parent table use : parent(),
//to go to primary-first-iriginal table use .original(),
//.build() builds all in correct order so you don't need to call parent() in this case.
// .build() makes that :
/*
var criteria= {
yearsOld:'= 22', //where
info : { //joined table
userId : '=' , //'=' means: put the parent object's property's value.
tableRules: {
table:'userInfos',
limit:1
}
},
comments: { //another joined table with it's own joined tables also
userId: '=',
tableRules:{
orderByDesc: "commentId" //ORDER BY comment_id DESC
},
likes: {
commentId: '=', //foreign key is comment_id. Where it's value is from comment object's primary key's value.
tableRules{ //NEW
table: "commentLikes" //use commentLikes tables as 'likes' property
}
users: {
userId : '='
}
}
}
};
*/
dbUsers.find(criteria,function(results){
console.log('A lot of queries executed here, you can guess them :)');
console.dir(results);
});
Save method, Returns a single object, also updates the variable you pass into.
var newUser = { username: 'a new user', yearsOld: 23, mail: 'newmail@mail.com' };
db.table("users").save(newUser).then(function(result){ //if you want use a promise
console.log('New user just created, new userId:'
+ result.userId+ ' which is the same as newUser.userId now:' +newUser.userId);
result.username = 'an updated new username';
db.table("users").save(result,function(){
console.log('User was just updated, because we have already a primary key setted at this object');
});
});
Remove method .1
//remove/delete all rows from users table where years_old = 22
db.table("users").remove({yearsOld:'= 22'},function(resultAnswer){
console.log(resultAnswer.affectedRows+ ' rows removed from '+resultAnswer.table);
});
Remove method .2 remove by id also
//remove/delete a single row by its primary key
db.table("users").remove(4,function(results){
console.log('we were just deleted a user with userId(user_id) = 4');
});
Also you can wait for multiple query methods to finish before you do something using db.when method:
var findAUser = db.table("users").findById(16);
var findMoreUsers = db.table("users").find({username: '= a username'});
var findSomeComments = db.table("comments").find({userId:'= 16'});
//you can pass an array of promises too.
db.when(findAUser,findMoreUsers,findSomeComments).then(function(results) {
/*
results -> results[0] -> findAUser (one object) , results[1]->findMoreUsers (array) results , results[2] ->findSomeComments (array) results.
*/
});
To perform a plain custom query call the .query() method on a wrapped-db object.
The simplest form of .query() is .query(sqlString, callback), where a SQL string
is the first argument and the second is a callback:
db.query('SELECT * FROM `users` WHERE `user_id` = 18', function (error, results) {
// error will be an Error if one occurred during the query
// results will contain the results of the query
});
( to escape a value here just use db.connection.escape(value) )
You can call stored procedures from your queries as with any other mysql driver. If the stored procedure produces several result sets, they are exposed to you the same way as the results for multiple statement queries.
Make sure you create connection or wrap a connection with multipleStatements flag set to true: var db = wrap({ user: 'kataras', password: 'mypass', database: 'test', multipleStatements: true});
//first way:
var myParams = "'arg1', 'arg2', ... ";
myParams.map(function(arg){ return db.connection.escape(arg);});
db.query("CALL MyStoredProcedure(" + myParams + ")", function(err, results, fields) {
if (err || results[0].res === 0) {
throw new Error("Error ... ");
} else {
// Stuff...
}
});
//second way, recommended:
var myParams = [];
myParams.push('arg1');
myparams.push('arg2');
try{
db.call('MyStoredProcedure', myParams, function(results, fields) { //arg1,arg2 here are auto-escaping.
console.log('Results returned: ',results);
});
}catch(ex){
console.log('Error info: ',ex);
}
Each method/query will emit a table event when a new type of query executed and parsed.
If you need to log or test results on the table before it gets used, you can
listen to these events: insert, update, remove (or delete) and save( for both insert and update) .
Note: Events are always executed before callbacks or promises.
//users -> an example table on a database, call it like a normal function
var usersInsertWatcher = function(parsedInsertedRows){
console.log('Somewhere an insert query executed on USERS table : ');
console.dir(parsedInsertedRows);
};
var usersRemoveWatcher = function(deleted){
console.log('Somewhere a remove/delete query executed on USERS table : ');
console.log('Affected rows number: '+ deleted.affectedRows);
};
db.table("users").on('insert',usersInsertWatcher);
db.table("users").on('remove',usersRemoveWatcher); //and so on...
To turn off an event on a table just call db.table("tablename").off('event_type',the_callback_variable_to_remove)
db.table("users").off('insert',usersInsertWatcher);
db.table("users").off('remove',usersRemoveWatcher);
Any table can be extending to your own custom needs, with custom method queries, using this syndax:
An example is always better, let's suppose that we have a users table with some columns, one of these columns is the 'mail' column, and we want to find if a user exists with a mail in our users table. Ofcourse we could use the already find method, but just for example purpose:
if(db.table("users").has('mailExists') === false) //not necessary
{
db.table("users").extend('mailExists',function(mail,callback){
//this = the users table, so this.name = 'users'
var q= "SELECT COUNT(*) FROM " + this.name + " WHERE mail = " +
db.connection.escape(mail);
db.query(q, function (err, results) {
if (!err && results.length > 0 && results[0]["COUNT(*)"] > 0) {
callback(true);
} else {
callback(false);
}
});
});
}
db.table("users").mailExists('an_e-mail@mail.com',function(exists){
if(exists) console.log('this mail already exists!');
else console.log('this mail doesnt exists.');
});
First of all you have to enable binary logs in your MySQL Server, In most of the cases this is enabled by default, but if not I will explain you how to enable it.
IF MYSQL SERVER VERSION IS GREATER OR EQUAL THAN 5.7 follow this:
IF OS === Windows
IF (MYSQL INSIDE xampp,wamp)
1. Open: C:/ xampp/ OR wamp/ mysql/YOUR_MYSQL_VERSION/my.ini
ELSE
1. Open explorer and write : %PROGRAMDATA%/MySQL/MySQL Server 5.7/my.ini
2. Go to the lines(119-120) which you can see these contents:
# Binary Logging.
# log-bin
3. Just uncomment the # log-bin, finall result must look like that:
# Binary Logging.
log-bin
4. Restart the mysql server service and you are ready.
ELSE IF OS === (L)Unix
IF (MYSQL INSIDE lampp)
1. Open explorer and open opt/lampp/etc/my.cnf
ELSE
1. Find where is my.cnf using these one of these shell commands:
locate my.cnf
whereis my.cnf
find . -name my.cnf
and open the my.cnf file.
2. Go to the line(119-120) which you can see these contents:
# Binary Logging.
# log-bin
3. Just uncomment the # log-bin, finall result must look like that:
# Binary Logging.
log-bin
4. Restart the mysql server service and you are ready.
ELSE IF MYSQL SERVER VERSION IS LESS OR EQUAL THAN 5.6
Watch on youtube this video: https://www.youtube.com/watch?v=xrTBFZyn-Bk
This package supports BASIC live watch of your database changes, make use of ObservableCollection.
var usersCollection = db.collection("users",function(){
//ready
usersCollection.onCollectionChanged(function(event,oldItems,newItems,oldStartingIndex,newStartingIndex){
//event has 3 times of events
//INSERT,UPDATE,REMOVE
//your code here
});
});
For full support and the best experience you evern seen, take a look at nodets/node-mysql-live package.
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for comments
-- ----------------------------
DROP TABLE IF EXISTS `comments`;
CREATE TABLE `comments` (
`comment_id` int(11) NOT NULL AUTO_INCREMENT,
`content` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of comments
-- ----------------------------
INSERT INTO `comments` VALUES ('1', 'dsadsadsa', '18');
INSERT INTO `comments` VALUES ('2', 'wqewqewqeq', '18');
INSERT INTO `comments` VALUES ('3', 'cxxzczxczcz', '22');
INSERT INTO `comments` VALUES ('4', 'e comment belongs to 23 usersa', '23');
-- ----------------------------
-- Table structure for comment_likes
-- ----------------------------
DROP TABLE IF EXISTS `comment_likes`;
CREATE TABLE `comment_likes` (
`comment_like_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`comment_id` int(11) DEFAULT NULL,
PRIMARY KEY (`comment_like_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of comment_likes
-- ----------------------------
INSERT INTO `comment_likes` VALUES ('1', '18', '1');
INSERT INTO `comment_likes` VALUES ('3', '18', '2');
INSERT INTO `comment_likes` VALUES ('4', '12', '1');
INSERT INTO `comment_likes` VALUES ('5', '16', '3');
INSERT INTO `comment_likes` VALUES ('6', '18', '4');
INSERT INTO `comment_likes` VALUES ('7', '16', '4');
INSERT INTO `comment_likes` VALUES ('8', '16', '3');
INSERT INTO `comment_likes` VALUES ('9', '18', '3');
-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`mail` varchar(255) DEFAULT NULL,
`username` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`years_old` int(11) DEFAULT '0',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5624 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('16', 'an updated username for user_id 30 or 30 2nd time', 'an updated x username 1nd time', 'ewqeq', '2015-08-09 03:55:34', '21');
INSERT INTO `users` VALUES ('18', 'an updated mail for user id 18 2nd time', 'an updated username for user_id 18 3rd time', 'a pass', '2015-08-08 22:58:49', '55');
INSERT INTO `users` VALUES ('19', 'updated19@omakis.com', 'an 19 username', 'a pass', '2015-08-08 22:38:19', '22');
INSERT INTO `users` VALUES ('20', 'mail20_updated@omakis.com', 'an updated20 username', 'a pass', '2015-08-08 22:58:48', '15');
INSERT INTO `users` VALUES ('22', 'mail22@omakis.com', 'a username', 'a passing', '2015-08-08 22:38:13', '22');
INSERT INTO `users` VALUES ('23', 'mailwtf@dsadsa.com', 'a username', 'pass', '2015-08-08 22:38:16', '22');
INSERT INTO `users` VALUES ('28', 'an updated username for user_id 28 or 283rd time', 'an updated x username 2nd time', 'ewqewq', '2015-08-08 22:58:44', '15');
INSERT INTO `users` VALUES ('31', 'an updated username for user_id 31 or 31 2nd time', 'an updated x username 1nd time', 'dsadsada', '2015-08-09 03:55:32', '0');
INSERT INTO `users` VALUES ('5618', 'special@email.com', 'a special username', null, '2015-08-13 06:32:07', '23');
-- ----------------------------
-- Table structure for user_infos
-- ----------------------------
DROP TABLE IF EXISTS `user_infos`;
CREATE TABLE `user_infos` (
`user_info_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
`hometown` varchar(255) DEFAULT NULL,
PRIMARY KEY (`user_info_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user_infos
-- ----------------------------
INSERT INTO `user_infos` VALUES ('1', '18', 'athens');
INSERT INTO `user_infos` VALUES ('3', '22', '22 user hometown');
INSERT INTO `user_infos` VALUES ('4', '23', '23 user hometown');
This project is licensed under the MIT license.
FAQs
This is a node js module, written in TypeScript (module is compiled to JavaScript), which manages mysql (node-mysql) connection and models in a way that you expected!
The npm package node-mysql-wrapper receives a total of 82 weekly downloads. As such, node-mysql-wrapper popularity was classified as not popular.
We found that node-mysql-wrapper demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.
Research
Security News
A threat actor's playbook for exploiting the npm ecosystem was exposed on the dark web, detailing how to build a blockchain-powered botnet.
Security News
NVD’s backlog surpasses 20,000 CVEs as analysis slows and NIST announces new system updates to address ongoing delays.