MYSQL-NODE-CLIENT

A Node.js mysql client implementation, using the following mysql driver: https://www.npmjs.com/package/mysql

Highlights

Install & Kickstart

Installation is done using the npm install command

$ npm install mysql-node-client

In order to create an instance of the mysqlClient, you must do the following:

const createMysqlClient = require('mysql-node-client')

const CONNECTION_DATA = {
  host: 'localhost',
  port: '3307',
  user: 'myUser',
  password: 'asd123',
  database: 'test_db'
}

const test = async () => {
  try {
    //create an instance of the client
    const mysqlClient = createMysqlClient(CONNECTION_DATA)
    //call a method exposed by the client
    const data = await mysqlClient.list('test_table')
    //use the response data
    console.log(data.results)
    //destroy the connection pool when it's not needed anymore
    mysqlClient.destroyConnectionPool()
  } catch (err) {
    console.log(err)
  }
}
test()

Connection Management

All the connections are pooled in a connection pool. The default connectionLimit is 100 and the default acquireTimeout is 10 seconds. If all the connections in the pool are used, there's a default queue of 100 slots which can hold requests. All the default options can be overriden by passing a second object to the createMysqlClient method.

Methods exposed by the client

DDLs

• createDatabase - The connection must not be using a database name

const data = await mysqlClient.createDatabase('test_db')

• listDatabases - The connection must not be using a database name

const data = await mysqlClient.listDatabases()

• createTable

const TABLE_SCHEMA = [
  {
      name: 'id',
      type: 'INT AUTO_INCREMENT PRIMARY KEY',
  },
  {
      name: 'name',
      type: 'VARCHAR(255) NOT NULL'
  }
]
const data = await mysqlClient.createTable('test_table', TABLE_SCHEMA)

• listTables

const data = await mysqlClient.listTables()

• describeTable

const data = await mysqlClient.describeTable('test_table')

DMLs

• store

const data = await mysqlClient.store(
  'test_table', //tableName
  { //record
    name: 'test_entry'
  }
)

• update

const data = await mysqlClient.update(
  'test_table', //tableName
  { name: 'test_updated_name' }, //updatedFields
  { placeholders: 'id > ? AND id <= ?', values: [ recordId - 1, recordId ]} //filters
)

• destroy

const data = await mysqlClient.destroy(
  'test_table', //tableName
  { placeholders: 'id = ?', values: [ recordId ]} //filters
)

DQLs

• list - Supports columns projection

const data = await mysqlClient.list(
  'test_table', //tableName
  [ 'id' ] //projection - only these columns will be returned. if absent, all columns are displayed
)

• fetch - Supports columns projection and filtering

const data = await mysqlClient.fetch(
  'test_table', //tableName
  { placeholders: 'id = ?', values: [ recordId ]}, //filters
  [ 'id' ] //projection - only these columns will be returned. if absent, all columns are displayed
)

Other

• getConnection - Used for retrieving a connection from the connection pool, or creating a new one. That connection is locked and can be used until it is released.

//create an instance of the client
const mysqlClient = createMysqlClient(CONNECTION_DATA)
//retrieve or create a connection to the db
const connection = await mysqlClient.getConnection()
//use any method from the connection object, as provided by the mysql driver
connection.query('SELECT * FROM test_table', (err, results, fields) => {
  if (err) throw err
  console.log(results)
})

• destroyConnectionPool - Destroy the connection pool when it's not needed anymore
• query - A wrapper over the base query method. Extras: connection management (retrieval and release from/to the connection pool), logging system (connection id and sql query executed).

const data = await mysqlClient.query('SELECT count(*) FROM test_table')

Notes

For more information, check the mysql driver documentation (https://www.npmjs.com/package/mysql). This implementation should be treated as a wrapper of the base driver.