A class to establish read and write MySQL connections
npm install mysql-database-connectionA class to using multiple read and write MySQL connections.
Under the hood, this is a using the mysql package and its pooling
feature.
It comes with a factory to create an instance from environment variables
which follows common best practices of not storing DB info in code.
There are five properties used for each connection:
The same name is used for all the connections.
* environment variable: DB_NAME=myDbName
* constructor config: {database: 'myDbName'}
Each connection may have a different username. If a connection doesn't have a username set, the username set
in the default connection will be used.
* default environment variable: DB_USER=myuser
* default constructor config: {defaultConnection: {user: 'myuser'}}
* per connection environment variable: DB_READ0_USER=readonlyuser
* per connection constructor config: {readConnections: [{user: 'readonlyuser'}]}
Each connection may have a different password. If a connection doesn't have a password set, the password set
in the default connection will be used.
* default environment variable: DB_PASS=secret
* default constructor config: {defaultConnection: {pass: 'secret'}}
* per connection environment variable: DB_WRITE0_PASS=secret
* per connection constructor config: {writeConnections: [{pass: 'secret'}]}
Each connection may have a different host. If a connection doesn't have a host set, the host set
in the default connection will be used.
* default environment variable: DB_HOST=mysql.example.com
* default constructor config: {defaultConnection: {host: 'mysql.example.com'}}
* per connection environment variable: DB_READ0_HOST=mysql.example.com
* per connection constructor config: {readConnections: [{host: 'mysql.example.com'}]}
Each connection may have a different port. If a connection doesn't have a port set, the port set
in the default connection will be used. If no default port is set, then the MySQL default port of 3306
is used.
* default environment variable: DB_PORT=8888
* default constructor config: {defaultConnection: {port: 8888}}
* per connection environment variable: DB_READ0_PORT=8888
* per connection constructor config: {readConnections: [{port: 8888}]}
An example of configuring the connections via the environment variables.
``iniThe same name is used in all connections
DB_NAME=myDatabase
`javascript
const MySqlDatabaseConnection = require('mysql-database-connection');
const dbConnections = MySqlDatabaseConnection.createConnectionFromEnv();
`$3
`javascript
const MySqlDatabaseConnection = require('mysql-database-connection');
const dbConnections = new MySqlDatabaseConnection.MySqlDatabaseConnection({
// The same name is used in all connections
database: 'myDatabase',
// Default connection info used to populate other connections
defaultConnection: {
user: 'myUser',
password: 'secret',
port: 8888,
}, writeConnections: [
// Write connection 1 has a different host while using the same user,
// password and port
{
host: 'master.mysql.example.com',
}
],
readConnections: [
// Read connection 1 has a different host
{
host: '1.slave.mysql.example.com',
},
// Read connection 2 has different host, user and password
{
host: '2.slave.mysql.example.com',
user: 'readonly',
pass: 'someOtherSecret',
},
// Read connection 3
{
host: '6.slave.mysql.example.com',
},
],
});
`
MySqlDatabaseConnection class
$3
- connectionOptions
- The connection options as defined in 'Connection options'
section
- poolingOptions
- An optional object of options for configuring the pooling
- mysql
- An optional mysql compatible module. This is mostly used for
using a mock in tests.
$3
#### readQuery(sqlString [, values])
Execute a read query.
`javascript
db.readQuery('SELECT * FROM users WHERE id=?', [5])
.then((dbResults) => {
console.log('User DB', dbResults);
}, (dbError) => {
console.log('Error: ', dbError);
});
`##### Parameters
- sqlString
- A string of SQL to execute
- values
- An optional array of values to replace the
? in the sqlString
##### Return value
A promise with the results.
#### writeQuery(query, args)
Execute a write query.
`javascript
db.readQuery('INSERT INTO users VALUES (?, ?)', ['John', 'john@example.com'])
.then((dbResults) => {
console.log('Insert', dbResults);
}, (dbError) => {
console.log('Error: ', dbError);
});
`##### Parameters
- sqlString
- A string of SQL to execute
- values
- An optional array of values to replace the
? in the sqlString
##### Return value
A promise with the results.
#### getReadConnection()
Get a mysql read connection.
#### getWriteConnection()
Get a mysql write connection.
#### destroy([done])
Destroy the pool of connections.
##### Parameters
- done
- An optional function to call when the connections are
destroyed.
getDataRowsFromResults(results)
A utility to get just the RowDataPackets in the results.
The DB results will contain the
OkPackets and sometimes be nested
arrays. This will walk through nested arrays to find the
RowDataPackets.##### Parameters
- results
- The results from a query
##### Return value
An array of just
RowDataPackets.createConnectionFromEnv([poolOptions [, envs [, mysql]]])
#### Parameters
- poolingOptions
- An optional object of options for configuring the pooling
- envs
- An optional object of environmental variables.
Defaults to
process.env`.