node-mysql-wrapper
nodets:mysql for meteor
NEW mysql-live for live database collections, server and client side!
Table of Contents
- Install
- Introduction
- Establishing connections
- Connection options
- Terminating connections
- Tables
- Performing queries
- Stored procedures
- Table events
- Extending a table
- Watch database changes
- Running tests
- Contributors
- Community
- Todo
Install
$ 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
Introduction
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 = ;var connection = mysql; var wrapper = ; var db = wrapper; /* ES6: */;let db = ;/* 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;
From this example, you can learn the following:
- Every method you invoke on a table is queued and executed in asynchronous way, using callbacks and/or promises.
- Closing the connection is done using
destroy()
which makes sure all remaining queries are executed before sending a quit packet to the mysql server.
Contributors
Thanks goes to the people who have contributed code to this module, see the GitHub Contributors page.
Community
If you'd like to discuss this module, or ask questions about it, please use one of the following:
- Chat: https://gitter.im/nodets/node-mysql-wrapper
- Mailing list: https://groups.google.com/forum/#!forum/node-mysql-wrapper
- IRC channel: http://irc.lc/freenode/node-mysql-wrapper/
Establishing connections
The recommended way to establish a wrapped-connection is this:
var wrapper = ;var db = wrapper; db;
However, a wrapped-connection can also be implicitly established by wrapping an existing mysql connection:
var mysql = ;var connection = mysql; var wrapper = ;var db = wrapper; db;
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.
Connection options
Read them at the node-mysql module documentation
Terminating connections
There are two ways to end a connection. Terminating a connection gracefully is
done by calling the end()
method:
db; //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;
Unlike end()
the destroy()
method does not take a callback argument.
Tables
Manual select which tables you want to use. (default all)
db; //this goes before db.ready function.
Getting a table object
//all code you will see bellow goes inside db.ready(function () { //code here });var usersTable = db; //yes, just this :)console;console;console; usersTable;
Performing queries
Method queries
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;
Simple 'find single' method , this find method always returns one result.
db;
Simple 'find by' method this find method always returns an array.
db
Simple 'find all' method , which is the same as db.table("tablename").find({},callback); or .find({ tableRules : { limit :42 }} if rules passed.
db;
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; var criteria = dbUserscriteria //or .except(...columns). Removes that column(s) from the select query. //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. //true if you want desceding ( ORDER BY COLUMN_KEY DESC ); //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;
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;
Remove method .1
//remove/delete all rows from users table where years_old = 22db;
Remove method .2 remove by id also
//remove/delete a single row by its primary keydb;
Also you can wait for multiple query methods to finish before you do something using db.when method:
var findAUser = db;var findMoreUsers = db;var findSomeComments = db; //you can pass an array of promises too.db;
Plain queries - the module's purpose is to never need to use this way.
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;
( to escape a value here just use db.connection.escape(value) )
Stored procedures
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; db; //second way, recommended:var myParams = ;myParams;myparams; try db; catchex console;
Table events
on /watch
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 functionvar { console; consoledirparsedInsertedRows;}; var { console; console;}; db;db; //and so on...
off /unwatch
To turn off an event on a table just call db.table("tablename").off('event_type',the_callback_variable_to_remove)
db;db;
Extending a table
Any table can be extending to your own custom needs, with custom method queries, using this syndax:
db.table("tablename").extend('functionName',theFunction);
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:
ifdb === false //not necessary db;
Use an extended method is simple
db;
Watch database changes
Enable Binary Logging
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
Example
This package supports BASIC live watch of your database changes, make use of ObservableCollection.
var usersCollection = db;
For full support and the best experience you evern seen, take a look at nodets/node-mysql-live package.
Running tests
Import this database example to your local server, and have fan!
SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for comments -- ---------------------------- IF EXISTS `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 -- ---------------------------- IF EXISTS `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 -- ---------------------------- IF EXISTS `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 -- ---------------------------- IF EXISTS `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');
Todo
- Waiting for stable yield/*async support
Licence
This project is licensed under the MIT license.