MySqlDatabaseConnection
A 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.
Connection options
There are five properties used for each connection:
1. Database name
The same name is used for all the connections.
- environment variable:
DB_NAME=myDbName
- constructor config:
{database: 'myDbName'}
2. Database user
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'}]}
3. Database password
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'}]}
4. Database host
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'}]}
5. Database port
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}]}
Environment variable configuration example
An example of configuring the connections via the environment variables.
# The same name is used in all connections DB_NAME=myDatabase # Default connection info used to populate other connections DB_USER=myUserDB_PASS=secretDB_PORT=8888 # Write connection 1 has a different host while using the same user, # password and port DB_WRITE0_HOST=master.mysql.example.com # Read connection 1 has a different host DB_READ0_HOST=1.slave.mysql.example.com # Read connection 2 has different host, user and password DB_READ1_HOST=2.slave.mysql.example.comDB_READ1_USER=readonlyDB_READ1_PASS=someOtherSecret # Read connection 3 DB_READ2_HOST=6.slave.mysql.example.com
const MySqlDatabaseConnection = ;const dbConnections = MySqlDatabaseConnection;
In Code Configuration Example
const MySqlDatabaseConnection = ;const dbConnections = // 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
constructor(connectionOptions [, poolingOptions [, mysql]])
- 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.
methods
readQuery(sqlString [, values])
Execute a read query.
db ;
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.
db ;
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`.
- mysql
- An optional mysql compatible module. This is mostly used for using a mock in tests.