simple-mysql

Description

A thin wrapper around the official MySql8 XDevAPI connector, exposing some common use-cases with a simple promise-based api.

Features

• Depends on @mysql/xdevapi v8.0.22
• Connection pooling
• Easy api to test for db connection
• SQL queries (using SQL statements instead of chainable api)
• Transactions (WIP)
• Collections (only supports chainable api)
• Expose underlying mysqlx.client object

Additional features and notes

• Init Promise:
  • Wait for database connection to be up before resolving (useful for running in docker, esp in dev)
• Dropped connections:
  • If connection to DB drops, queries will fail and throw an error.
  • When connection to DB comes back, queries will be successful again without needing to re-initialize the client.

Usage

Installation

npm install --save @liangwp/simple-mysql

Connect to DB

const simpleMySQL = require('@liangwp/simple-mysql');

var connection_config = {
    user: 'test_user',
    password: 'test_pswd',
    host: 'mysql8-sample',
    port: 33060,
    schema: 'test_db'
};
var connection_options = {
    pooling: { enabled: true, maxIdleTime: 30000, maxSize: 25, queueTimeout: 10000 }
}

var mysql = simpleMySQL(connection_config, connection_options);

var retry_params = {
    retries: 10,
    factor: 2,
    minTimeout: 1000,
    randomise: true
};

# wait for connection
mysql.awaitDbInit(retry_params) # if retry_params not supplied, defaults to the above
.then(() => {
    // connection is ready
})

Test for Connection / Get Connection Params

mysql.inspect()
.then(data => {
    // do your stuff
    // data {
    //     pooling: true,
    //     auth: 'PLAIN',
    //     schema: 'test_db',
    //     ssl: true,
    //     user: 'test_user',
    //     host: 'mysql8-sample',
    //     port: 33060,
    //     dbUser: 'test_user',
    //     socket: undefined
    // }
});

Perform Simple Query

mysql.query('SELECT col1, col2 FROM some_table WHERE col3 = ?', [col3_binding])
.then(data => {
    // do your stuff
});

Close Connection to DB

mysql.close()
.then(() => {
    // connection closed
});

Transaction (WIP)

Promise.resolve()
.then(() => {
    return mysql.startTransaction()
})
.then(mysqlTrans => {
    return mysqlTrans.query('SELECT col1, col2 FROM some_table WHERE col3 = ?', [col3_binding])
    .then(data => {
        // do some stuff
        var col3_binding = 'abcde';
        return mysqlTrans.query('SELECT col1, col2 FROM other_table WHERE col3 = ?', [col3_binding]);
    })
    .then(data => {
        // do more stuff
    })
    .then(() => {
        return mysqlTrans.commit()
    })
    .catch(err => {
        return mysqlTrans.rollback()
    });
})
.catch(err => {
    // transaction failed to start
    // OR rollback failure
})
.then(() => {
    // transaction ok
    // OR rollback successful
});

Run example (in docker)

cd dev
docker-compose -f docker-compose-example.yml up --build

Run tests (in docker)

cd dev
docker-compose -f docker-compose-mocha.yml up --build

Changelog

Date	Version	Description
2021.04.04	v1.0.4	Comment out the console.log in awaitDbInit() from spamming into code that depends on this module. Should figure out a cleaner logging method later.
2021.01.14	v1.0.2	Fix silly typo, add github link.
2021.01.14	v1.0.1	Update example to work dev (git cloned), as well as for npm version.
2021.01.14	v1.0.0	Seems usable for most simple queries. Transactions support is still incomplete.