anytv-node-mysql

    0.0.57 • Public • Published

    anytv-node-mysql

    Build Status Coverage Status Documentation Dependencies npm version

    Our version of mysql that makes connecting to mysql simpler and more elegant. Especially made for our awesome expressjs boilerplate.

    This module inherits the popular node-mysql.

    Install

    npm install anytv-node-mysql --save

    Features

    • Add a key-config pair once and use it anywhere
    • Chain everything
    • Do transactions elegantly
    • Automatic rollback on transactions
    • Easy to specify if you want a pool connection or a per-query connection
    • Specify errors that can be retried and set their max retries (does not work on transactions yet)
    • Auto-reconnect
    • Easy debugging
    • Use .args(...) to help closures

    Usage

    Adding database config

    On your index.js / server.js / app.js, register your database using a key.

    import mysql from 'anytv-node-mysql';
     
    mysql.add('my_db', {
        host: 'localhost',
        user: 'root',
        password: '',
        database: test
    });

    Doing a single query

    After registering a db key and config, you can now start querying.

    mysql.query(
            'SELECT name FROM users WHERE name = ?',
            ['name'],
            callback
        )
        .end();

    Doing a single query using promise

    Automatically closes the connection and does not work on transactions.

    mysql.build(
            'SELECT name FROM users WHERE name = ?',
            ['name']
        )
        .promise()
        .then()
        .catch();

    Doing a single query using squel

    Works on transactions.

    const query = squel.select()
        .from('users')
        .field('name')
        .where('name = ?', 'name');
     
    mysql.squel(query, callback)
        .end();

    Doing a single query using squel and promise

    const query = squel.select()
        .field('name')
        .from('users')
        .where('name = ?', 'name');
     
    mysql.build(query)
        .promise()
        .then()
        .catch();

    Note: Single connections will only be created on mysql.query(...) while pooled connections will be created on mysql.use('db1', config.DB, true).

    Doing multiple queries using 1 connection

    You can chain queries and use a single connection.

    mysql.query(
            'SELECT name FROM users WHERE name = ?',
            ['name'],
            callback
        )
        .query(
            'SELECT address FROM users WHERE name = ?',
            ['name2'],
            callback2
        )
        .end();

    Using multiple database configs

    In case you registered multiple databases, you can switch the current db by invoking mysql.use(key).

    import mysql from 'anytv-node-mysql';
     
    mysql.add('db_1', config.DB1);
    mysql.add('db_2', config.DB2);
     
    mysql.use('db1')
        .query(
            'SELECT name FROM users WHERE name = ?',
            ['name'],
            callback
        )
        .end();
     
     
    mysql.use('db2')
        .query(
            'SELECT name FROM users WHERE name = ?',
            ['name2'],
            callback2
        )
        .end();

    You can also switch databases right after ending.

    mysql.use('db1')
        .query(
            'SELECT name FROM users WHERE name = ?',
            ['name'],
            callback
        )
        .end() // if you forgot to call this, the .use('db2') will do it for you
        .use('db2')
        .query(
            'SELECT name FROM users WHERE name = ?',
            ['name2'],
            callback2
        )
        .end();

    Doing transactions

    Creating a transaction returns a transaction object, not the mysql object. It's not possible to do another query after doing a commit.

    mysql.use('db1')
        .transaction()
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .query('SELECT * FROM unique_email WHERE email = ?', ['unique'], callback)
        .commit(final_callback);

    If a query fails, the next queries won't be executed anymore. Plus, the callback and final_callback will have the same arguments.

    Solving problem with closures

    Same with you, we had trouble with closures when we're querying inside a loop. We created a function where you can pass anything and we'll include it on the callback.

    mysql.use('db1')
        .args(obj, 2, 'config')
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();
     
    function callback (err, result, args, last_query) {
        args[0] === obj;
        args[1] === 2;
        args[2] === 'config';
    }

    Handling callbacks and debugging errors

    We follow the node convention for callbacks. The error first followed by result.

    mysql.use('db1')
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();
     
    function callback (err, result, args, last_query) {
        if (err) {
            // do something with the error
            ...
            return;
        }
     
        // do something with the result
        ...
    }

    The last executed query is accessible on the last_query variable.

    Setting retryable errors

    To solve intermittent issues, we added a function that accepts an array of error codes where the library will keep on retrying until it works or until it reaches the maximum retries. Default maximum retries is 3.

     
    // Retryable errors from `.retry_if()` will be cleared on the next `mysql.use(key)`
    mysql.use('db1')
        .retry_if(['ER_LOCK_DEADLOCK', 'PROTOCOL_SEQUENCE_TIMEOUT'])
        .set_max_retry(5)
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();
     
     
    // Can also be set in the config and it will be applied on all queries on that database
    mysql.add('db', {
        host: 'localhost',
        user: 'my_user',
        password: 'my_password',
        retryable_errors: ['ER_LOCK_DEADLOCK', 'PROTOCOL_SEQUENCE_TIMEOUT']
    });

    Solving on-the-fly db config

    We added a .open function that accepts a config.

    mysql.open({
            host: 'localhost',
            user: dynamic_variable,
            password: dynamic_variable2,
            database: 'test'
        })
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();

    Instantiating a pooled connection

    The mysql.use(...) accepts a boolean 3rd parameter. If true, we'll use a pool connection and create it immediately. Calling .end() on pooled connections won't do anything.

    mysql.add('db1', config.DB1, true);
     
    mysql.use('db1')
        .query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();

    Closing a pooled connection

    Use mysql.end_pool() to close a pooled connection.

    mysql.add('db1', config.DB1, true);
     
    let db1 = mysql.use('db1');
    db1.query('INSERT INTO unique_email(email) VALUES (?)', ['unique'], callback)
        .end();
     
    db1.query('INSERT INTO unique_email(email) VALUES (?)', ['uniqu3'], callback)
        .end();
     
    db1.end_pool();

    Using a different logger

    mysql.set_logger(my_new_logger);

    Documentation

    Code documentation can be found at here.

    Contributing

    Install the tools needed:

    npm install --only=dev

    To compile the ES6 source code to ES5:

    npm run build
    npm run build -- --watch # for watching 

    To generate the docs:

    npm run docs

    Running test

    Make sure to build the source code before running tests.

    npm test

    Code coverage

    npm run coverage

    Then open coverage/lcov-report/index.html.

    License

    MIT

    Author

    Freedom! Labs, any.TV Limited DBA Freedom!

    Keywords

    Install

    npm i anytv-node-mysql

    DownloadsWeekly Downloads

    18

    Version

    0.0.57

    License

    ISC

    Unpacked Size

    80.6 kB

    Total Files

    21

    Last publish

    Collaborators

    • freedom_sherwin
    • anytv_npmjs
    • wilbertverayin