easy-mysql's Introduction

Node EasyMySQL

Purpose

A small collection of simple functions that make using MySQL with node.js easier. Uses the node-mysql library.

Developed by MOG.com

Installation

npm install easy-mysql

Overview

EasyMySQL allows you to handle MySQL connection pooling, acquiring connections, and releasing them with ease. For example, where you might do this (using node-pool):

var get_widget = function(id, cb) {
    pool.acquire(function (err, client) {
        if (err) {
            pool.release(client);
            cb(err, null);
        } else {
            var sql = 'select * from widgets where id = ?';
            client.query(sql, [id], function (err, results) {
                pool.release(client);
                if (err || results && results.length === 0) {
                    cb(err, null);
                } else {
                    cb(null, results[0]);
                }
            });
        }
    });
};

...you can do this instead with EasyMySQL:

var get_widget = function(id, cb) {
    var sql = 'select * from widgets where id = ?';
    easy_mysql.get_one(sql, [id], cb);
};

Usage

// require the module
var EasyMySQL = require('easy-mysql');

Connection Modes

You can connect three different ways with EasyMySQL:

Direct: Directly establish a single connection for each query. This is probably not a good idea for production code, but may be fine for code where you don't want to set up a pool, such as in unit tests.
Custom Pool: Pass in your own pool object. It must have functions named 'acquire' and 'release'.
Built-in Pool: Use the built-in pool, which uses node-pool

Direct Connect Example

var settings = {
    user     : 'myuser',
    password : 'mypass',
    database : 'mydb'
};

var easy_mysql = EasyMySQL.connect(settings);

Custom Pool Example

var my_pool = /* create your own pool here */;
var easy_mysql = EasyMySQL.connect_with_pool(pool);

Built-in Pool Example

var settings = {
    user      : 'myuser',
    password  : 'mypass',
    database  : 'mydb',
    pool_size : 50
};

var easy_mysql = EasyMySQL.connect_with_easy_pool(settings);

Logging

You can specify a logger to log error events (more event types may be added later).

var settings = {
    user     : 'myuser',
    password : 'mypass',
    database : 'mydb',
    logging: {
        logger: my_logger,
        events: {
            error: {level: 'warn'}
        }
    }
};

You must pass in your own logger and it must have a function with the same name as the level you specify.

Instance methods

get_one / getOne

Returns only one result, and if no results are found, returns null.

var sql = 'select * from widgets where id = ?';
easy_mysql.get_one(sql, [123], function (err, result) {
    cb(err, result);
});

get_all / getAll

Returns an array of results, and if no results are found, returns an empty array.

var sql = 'select * from widgets where id > ?';
easy_mysql.get_all(sql, [123], function (err, results) {
    cb(err, results);
});

execute

Executes an arbitrary SQL query and returns the results from node-mysql.

var sql = 'update widgets set foo = 'bar' where id = ?';
easy_mysql.execute(sql, [123], function (err, results) {
    cb(err, results);
});

Tests and Docs

To run tests and generate docs, first run:

npm install -d

Run the tests:

make test

Generate JSDocs:

make doc

Contribute

If you would like to contribute to the project, please fork it and send us a pull request. Please add tests for any new features or bug fixes. Also run make lint before submitting the pull request.

License

node-easy-mysql is licensed under the MIT license.

easy-mysql's People

Contributors

Stargazers

Watchers

easy-mysql's Issues

verify and use pool in multiple js file

how to verify if i provide pool size 50, it has been created? It will create 50 pool, or it will create just one connection and if that connection is busy, and new request come, it will create one more pool?
i want to use the same pool across the application in multiple JS file. Any help on how to do that?

PS : new to JS world and Node.js.
Thanks in Advance.

Not possible to use multiple pools

The following example code trys to setup multiple pools (to different databases, host, whatever).

var em = require('easy-mysql')
  , db_conns = {}
  , stmt2 = 'INSERT INTO [table only on con2] ...'
;
db_conns['con1'] = em.connect_with_easy_pool({
    user         : 'user1'
  , password     : 'pass1'
  , database     : 'db1'
  , host         : 'host1'
});
db_conns['con2'] = em.connect_with_easy_pool({
    user         : 'user2'
  , password     : 'pass2'
  , database     : 'db2'
  , host         : 'host2'
});
[...]
db_conns.con2.execute(stmt2, [...], function(err, res) {
  console.log(err);
});

Output:

{ [Error: Table '[db1].[tbl2]' doesn't exist]
  number: 1146,
  sqlStateMarker: '#',
  sqlState: '42S02',
  message: 'Table '[db1].[tbl2]' doesn't exist',
  sql: 'INSERT INTO [tbl2] ...',
  ... }

So this module always uses the first pool. It is not possible to handle multiple dofferent databases in one application. Or sis i miss something?

mog-inc / easy-mysql Goto Github PK