mysql-await - npm Package Compare versions

Comparing version 1.0.1 to 2.0.0

example.js

@@ -7,12 +7,64 @@ const fs = require(`fs`);
   /** Create connection pool using loaded config */
-  const db = mysql(JSON.parse(fs.readFileSync(`mysql-config.json`)));
+  const pool = mysql.createPool(JSON.parse(fs.readFileSync(`mysql-config.json`)));
 
-  /** Perform query (opens a connection, runs query, releases connection) */
-  const result = await db.query(`SELECT * FROM transactions WHERE ticker = ?`, [`DE`]);
+  pool.on(`acquire`, (connection) => {
+    console.log(`Connection %d acquired`, connection.threadId);
+  });
   
+  pool.on(`connection`, (connection) => {
+    console.log(`Connection %d connected`, connection.threadId);
+  });
+
+  pool.on(`enqueue`, () => {
+    console.log(`Waiting for available connection slot`);
+  });
+
+  pool.on(`release`, function (connection) {
+    console.log(`Connection %d released`, connection.threadId);
+  });
+
+  const connection = await pool.awaitGetConnection();
+  
+  connection.on(`error`, (err) => {
+    console.error(`Connection error ${err.code}`);
+  });
+
+  /** Perform query on connection */
+  let result = await connection.awaitQuery(`SELECT * FROM transactions WHERE ticker = ?`, [`DE`]);
+  
   /** Log output */
   console.log(result);
   
+  /** Release connection */
+  connection.release();
+    
+  /** Perform query on pool (opens a connection, runs query, releases connection) */
+  result = await pool.awaitQuery(`SELECT * FROM transactions WHERE ticker = ?`, [`KSS`]);
+  
+  /** Log output */
+  console.log(result);
+  
+  /** Get a new connection from the pool */
+  const connection2 = await pool.awaitGetConnection();
+  
+  /** Begin a new transaction */
+  await connection2.awaitBeginTransaction();
+  
+  /** Perform query for max id number in users table */
+  result = await connection2.awaitQuery(`SELECT MAX(id) maxId FROM users`);
+  
+  /** Add one to max id to get new id number */
+  const newId = result[0].maxId + 1;
+  
+  /** Insert new test user with new id number */
+  await connection2.awaitQuery(`INSERT INTO users (id, hash, username, name, accounts) VALUES (?, ?, ?, ?, ?)`, [newId, ``, `testuser`, `Test User`, ``]);
+  
+  /** Commit transaction */
+  await connection2.awaitCommit();
+  
+  /** Release connection */
+  connection2.release();
+  
   /** Close connection pool */
-  db.end();
+  await pool.awaitEnd();
 })();

index.js

@@ -5,52 +5,482 @@ /** Require external modules */
 /**
- * @class ezobjects.MySQLConnection
- * @author Rich Lowe
- * @copyright 2018 Rich Lowe
- * @description Class for establishing and querying MySQL connections.
+ * @class mysqlAwait.ConnectionAwait
+ * @description Async/await version of MySQL Connection object
  */
-class MySQLConnection {
+class ConnectionAwait {
   /**
-   * @signature new MySQLConnection([data])
-   * @param config Object
-   * @returns MySQLConnection
-   * @description Returns a new [MySQLConnection] instance and initializes using `config`, if provided, otherwise 
-   * it initializes to defaults and will require a config to be set later.
+   * @signature new Connection()
+   * @returns Connection
+   * @description Creates a new MySQL connection.
    */
-  constructor(config) {
-    this.pool = mysql.createPool(config);
+  constructor() {
+    this.connection = mysql.createConnection(...arguments);
+    this.config = this.connection.config;
+    this.inTransaction = false;
   }
   
+  
   /**
+   * @signature awaitBeginTransaction()
+   * @returns Promise
+   * @description Begin a new transaction.
+   */
+  awaitBeginTransaction() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.beginTransaction((err) => {
+          if ( err )
+            throw err;
+          
+          this.inTransaction = true;
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitChangeUser(params)
+   * @param params Object First argument of MySQL's Connection.changeUser()
+   * @returns Promise
+   * @description Change the current user without shutting down socket.
+   */
+  awaitChangeUser(params) {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.changeUser(params, (err) => {
+          if ( err )
+            throw err;
+          
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitCommit()
+   * @returns Promise
+   * @description Commit a transaction.
+   */
+  awaitCommit() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.commit((err) => {
+          if ( err ) {
+            if ( this.inTransaction ) {
+              this.connection.rollback(() => {
+                this.inTransaction = false;
+                throw err;
+              });
+            } else {
+              this.inTransaction = false;
+              throw err;
+            }
+          } else {
+            this.inTransaction = false;
+            resolve();
+          }
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitConnect()
+   * @returns Promise
+   * @description Establishes a connection to the database.
+   */
+  awaitConnect() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.connect((err) => {
+          if ( err )
+            throw err;
+          
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitDestroy()
+   * @returns Promise
+   * @description Destroys the connection.
+   */
+  awaitDestroy() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.destroy((err) => {
+          if ( err )
+            throw err;
+          
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitEnd()
+   * @returns Promise
+   * @description Terminates the connection.
+   */
+  awaitEnd() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.end((err) => {
+          if ( err )
+            throw err;
+          
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitQuery(query[, params])
+   * @param query string First argument of MySQL's Connection.query()
+   * @param params Array (optional) Second argument of MySQL's Connection.query() if not used for callback
+   * @returns Promise
+   * @description Queries the MySQL database, returning a [Promise] that resolves when finished or rejects on error.  
+   */
+  awaitQuery(query, params) {
+    return new Promise((resolve, reject) => {
+      try {
+        if ( typeof params === `undefined` ) {
+          this.connection.query(query, (err, result) => {
+            if ( err ) {
+              if ( this.inTransaction ) {
+                this.connection.rollback(() => {
+                  this.inTransaction = false;
+                  throw err;
+                });
+              } else {
+                throw err;
+              }
+            } else {
+              resolve(result);
+            }
+          });
+        } else {
+          this.connection.query(query, params, (err, result) => {
+            if ( err ) {
+              if ( this.inTransaction ) {
+                this.connection.rollback(() => {
+                  this.inTransaction = false;
+                  throw err;
+                });
+              } else {
+                throw err;
+              }
+            } else {
+              resolve(result);
+            }
+          });
+        }
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature awaitRollback()
+   * @returns Promise
+   * @description Rolls back a transaction.
+   */
+  awaitRollback() {
+    return new Promise((resolve, reject) => {
+      try {
+        this.connection.rollback(() => {
+          resolve();
+        });
+      } catch ( err ) {
+        reject(err);
+      }
+    });
+  }
+  
+  /**
+   * @signature beginTransaction()
+   * @description Pass along functionality of the beginTransaction method.
+   */
+  beginTransaction() {
+    return this.connection.beginTransaction(...arguments);
+  }
+  
+  /**
+   * @signature changeUser()
+   * @description Pass along functionality of the changeUser method.
+   */
+  changeUser() {
+    return this.connection.changeUser(...arguments);
+  }
+  
+  /**
+   * @signature commit()
+   * @description Pass along functionality of the commit method.
+   */
+  commit() {
+    return this.connection.commit(...arguments);
+  }
+  
+  /**
+   * @signature connect()
+   * @description Pass along functionality of the connect method.
+   */
+  connect() {
+    return this.connection.connect(...arguments);
+  }
+  
+  /**
+   * @signature destroy()
+   * @description Pass along functionality of the destroy method.
+   */
+  destroy() {
+    return this.connection.destroy(...arguments);
+  }
+  
+  /**
    * @signature end()
+   * @description Pass along functionality of the end method.
+   */
+  end() {
+    return this.connection.end(...arguments);
+  }
+  
+  /**
+   * @signature escape(data)
+   * @description Pass along functionality of escape.
+   */
+  escape() {
+    return this.connection.escape(data);
+  }
+  
+  /**
+   * @signature escapeId(data)
+   * @description Pass along functionality of escapeId.
+   */
+  escapeId() {
+    return this.connection.escapeId(data);
+  }
+  
+  /**
+   * @signature on()
+   * @description Pass along functionality of event listeners.
+   */
+  on() {
+    return this.connection.on(...arguments);
+  }
+  
+  /**
+   * @signature query()
+   * @description Pass along functionality of the query method.
+   */
+  query() {
+    return this.connection.query(...arguments);
+  }
+  
+  /**
+   * @signature rollback()
+   * @description Pass along functionality of the rollback method.
+   */
+  rollback() {
+    return this.connection.rollback(...arguments);
+  }
+}
+
+/**
+ * @class mysqlAwait.PoolAwait
+ * @description Async/await version of MySQL Pool object
+ */
+class PoolAwait {
+  /**
+   * @signature new Pool()
+   * @returns Pool
+   * @description Creates a new MySQL connection pool.
+   */
+  constructor() {
+    this.pool = mysql.createPool(...arguments);
+  }
+  
+  /**
+   * @signature awaitEnd()
    * @returns Promise
    * @description Closes all connections in the pool and ends the pool.
    */
-  end() {
+  awaitEnd() {
     return new Promise((resolve, reject) => {
-      this.pool.end((err) => {
-        if ( err )
-          reject(err);
-        else
+      try {
+        this.pool.end((err) => {
+          if ( err )
+            throw err;
+
           resolve();
-      });
+        });
+      } catch ( err ) {
+        reject(err);
+      }
     });
   }
   
+  
   /**
-   * @signature query(query, params)
-   * @param query string Valid MySQL query
-   * @param params Array Ordered array with values matching the parameters marked by `?` in the `query`
+   * @signature awaitGetConnection()
    * @returns Promise
-   * @description Queries the MySQL database, returning a [Promise] that resolves when finished or rejects on error.  If the database has not
-   * yet established a connection, it is automatically done prior to query execution.
+   * @description Get a new MySQL connection from the pool.  This will need to be released by
+   * you when you are done using it.
    */
-  query(query, params = []) {
+  awaitGetConnection() {
     return new Promise(async (resolve, reject) => {
-      /** Execute query and return result */
       try {
-        this.pool.query(query, params, (err, result) => {
+        this.pool.getConnection((err, connection) => {
           if ( err )
             throw err;
           
-          resolve(result);
+          connection.inTransaction = false;
+          
+          /**
+           * @signature awaitBeginTransaction()
+           * @returns Promise
+           * @description Begin a new transaction.
+           */
+          connection.awaitBeginTransaction = function () {
+            return new Promise((resolve, reject) => {
+              try {
+                this.beginTransaction((err) => {
+                  if ( err )
+                    throw err;
+                  
+                  this.inTransaction = true;
+                  resolve();
+                });
+              } catch ( err ) {
+                reject(err);
+              }
+            });
+          }
+
+          /**
+           * @signature awaitChangeUser(params)
+           * @param params Object First argument of MySQL's Connection.changeUser()
+           * @returns Promise
+           * @description Change the current user without shutting down socket.
+           */
+          connection.awaitChangeUser = function (params) {
+            return new Promise((resolve, reject) => {
+              try {
+                this.changeUser(params, (err) => {
+                  if ( err )
+                    throw err;
+
+                  resolve();
+                });
+              } catch ( err ) {
+                reject(err);
+              }
+            });
+          }
+
+          /**
+           * @signature awaitCommit()
+           * @returns Promise
+           * @description Commit a transaction.
+           */
+          connection.awaitCommit = function () {
+            return new Promise((resolve, reject) => {
+              try {
+                this.commit((err) => {
+                  if ( err ) {
+                    if ( this.inTransaction ) {
+                      this.connection.rollback(() => {
+                        this.inTransaction = false;
+                        throw err;
+                      });
+                    } else {
+                      this.inTransaction = false;
+                      throw err;
+                    }
+                  } else {
+                    this.inTransaction = false;
+                    resolve();
+                  }
+                });
+              } catch ( err ) {
+                reject(err);
+              }
+            });
+          };
+
+          /**
+           * @signature awaitQuery(query[, params])
+           * @param query string First argument of MySQL's Connection.query()
+           * @param params Array (optional) Second argument of MySQL's Connection.query() if not used for callback
+           * @returns Promise
+           * @description Queries the MySQL database, returning a [Promise] that resolves when finished or rejects on error.  
+           */
+          connection.awaitQuery = function (query, params) {
+            return new Promise((resolve, reject) => {
+              try {
+                if ( typeof params === `undefined` ) {
+                  this.query(query, (err, result) => {
+                    if ( err )
+                      throw err;
+                    
+                    resolve(result);
+                  });
+                } else {
+                  this.query(query, params, (err, result) => {
+                    if ( err ) {
+                      if ( this.inTransaction ) {
+                        this.rollback(() => {
+                          this.inTransaction = false;
+                          throw err;
+                        });
+                      } else {
+                        throw err;
+                      }
+                    } else {
+                      resolve(result);
+                    }
+                  });
+                }
+              } catch ( err ) {
+                reject(err);
+              }
+            });
+          };
+          
+          /**
+           * @signature awaitRollback()
+           * @returns Promise
+           * @description Rolls back a transaction.
+           */
+          connection.awaitRollback = function () {
+            return new Promise((resolve, reject) => {
+              try {
+                this.rollback(() => {
+                  resolve();
+                });
+              } catch ( err ) {
+                reject(err);
+              }
+            });
+          };
+                    
+          resolve(connection);
         });
@@ -61,5 +491,100 @@ } catch ( err ) {
     });
+  };
+  
+  /**
+   * @signature awaitQuery(query[, params])
+   * @param query string First argument of MySQL's Pool.query()
+   * @param params Array (optional) Second argument of MySQL's Pool.query() if not used for callback
+   * @returns Promise
+   * @description Queries the MySQL database, returning a [Promise] that resolves when finished or rejects on error.  
+   */
+  awaitQuery(query, params) {
+    return new Promise((resolve, reject) => {
+      try {
+        if ( typeof params === `undefined` ) {
+          this.pool.query(query, (err, result) => {
+            if ( err )
+              throw err;
+
+            resolve(result);
+          });
+        } else {
+          this.pool.query(query, params, (err, result) => {
+            if ( err )
+              throw err;
+
+            resolve(result);
+          });
+        }
+      } catch ( err ) {
+        reject(err);
+      }
+    });
   }
+  
+  /**
+   * @signature end()
+   * @description Pass along functionality of the end method.
+   */
+  end() {
+    return this.pool.end(...arguments);
+  }
+  
+  /**
+   * @signature getConnection()
+   * @description Pass along functionality of the getConnection method.
+   */
+  getConnection() {
+    return this.pool.getConnection(...arguments);
+  }
+  
+  /**
+   * @signature on()
+   * @description Pass along functionality of event listeners.
+   */
+  on() {
+    return this.pool.on(...arguments);
+  }
+  
+  /**
+   * @signature awaitQuery()
+   * @description Pass along functionality of the pool query method.
+   */
+  query() {
+    return this.pool.query(...arguments);
+  }
 }
 
-module.exports = config => new MySQLConnection(config);
+/**
+ * @class mysqlAwait.MySQLAwait
+ * @author Rich Lowe
+ * @copyright 2019 Rich Lowe
+ * @description Class for establishing and querying MySQL connections.
+ */
+class MySQLAwait {
+  /**
+   * @signature createConnection(data)
+   * @description Intercept creating of MySQL connection and use our own object instead.
+   */
+  createConnection() {
+    return new ConnectionAwait(...arguments);
+  }
+  
+  /**
+   * @signature createPool(data)
+   * @description Intercept creating of MySQL connection pool and use our own object instead.
+   */
+  createPool() {
+    return new PoolAwait(...arguments);
+  }
+  
+  /**
+   * @signature format(data)
+   * @description Pass along functionality of format.
+   */
+  format() {
+    return mysql.format(data);
+  }
+}
+
+module.exports = new MySQLAwait();

package.json

 {
   "name": "mysql-await",
-  "version": "1.0.1",
+  "version": "2.0.0",
   "description": "Simple MySQL Async/Await Connection Pool",
@@ -5,0 +5,0 @@ "main": "index.js",

README.md

@@ -1,21 +0,38 @@
-# MySQL Async/Await Wrapper v1.01
+# MySQL Async/Await Wrapper v2.0
 
-Basic API for performing async/await MySQL queries on a pool.  A new connection is created, queried, and released for each call to `query()`.
+Simple wrapper for MySQL async/await functionality.  Intended for functionality to mimic the popular `mysql` Node.js callback-based package, but with additional methods for awaiting execution.  Goal is for normal methods to be unaffected and only additional await methods added, though accomplished through intermediary class objects.
 
-## Configuration:
+## Provided async/await methods
 
-### JavaScript
+The methods below have been added as async/await wrappers to existing MySQL methods of similar name.  In order to use them, simply "await" their execution rather than passing a callback for the last argument of the function call.
 
-```javascript
-{
-  connectionLimit : 10,
-  host            : 'example.org',
-  user            : 'bob',
-  password        : 'secret',
-  database        : 'my_db'
-}
-```
+### Connections from mysql.createConnect()
 
-### JSON
+* Connection.awaitBeginTransaction()
+* Connection.awaitChangeUser(params)
+* Connection.awaitCommit()
+* Connection.awaitConnect()
+* Connection.awaitDestroy()
+* Connection.awaitEnd()
+* Connection.awaitQuery(query[, params])
+* Connection.awaitRollback()
 
+### Connection Pool's from mysql.createPool()
+
+* Pool.awaitEnd()
+* Pool.awaitGetConnection()
+* Pool.awaitQuery(query[, params])
+
+## Connections from pool.getConnection()
+
+* Connection.awaitBeginTransaction()
+* Connection.awaitChangeUser(params)
+* Connection.awaitCommit()
+* Connection.awaitQuery(query[, params])
+* Connection.awaitRollback()
+
+## Configuration:
+
+### Example JSON configuration
+
 ```json
@@ -31,7 +48,7 @@ {
 
-## Example
+## Example JavaScript
 
 ```javascript
 const fs = require(`fs`);
-const mysql = require(`mysql-await`);
+const mysql = require(`./index`);
 
@@ -41,12 +58,64 @@ /** Self-executing asynchronous function so we can await results in this example */
   /** Create connection pool using loaded config */
-  const db = mysql(JSON.parse(fs.readFileSync(`mysql-config.json`)));
+  const pool = mysql.createPool(JSON.parse(fs.readFileSync(`mysql-config.json`)));
 
-  /** Perform query (opens a connection, runs query, releases connection) */
-  const result = await db.query(`SELECT * FROM transactions WHERE ticker = ?`, [`DE`]);
+  pool.on(`acquire`, (connection) => {
+    console.log(`Connection %d acquired`, connection.threadId);
+  });
   
+  pool.on(`connection`, (connection) => {
+    console.log(`Connection %d connected`, connection.threadId);
+  });
+
+  pool.on(`enqueue`, () => {
+    console.log(`Waiting for available connection slot`);
+  });
+
+  pool.on(`release`, function (connection) {
+    console.log(`Connection %d released`, connection.threadId);
+  });
+
+  const connection = await pool.awaitGetConnection();
+  
+  connection.on(`error`, (err) => {
+    console.error(`Connection error ${err.code}`);
+  });
+
+  /** Perform query on connection */
+  let result = await connection.awaitQuery(`SELECT * FROM transactions WHERE ticker = ?`, [`DE`]);
+  
   /** Log output */
   console.log(result);
   
+  /** Release connection */
+  connection.release();
+    
+  /** Perform query on pool (opens a connection, runs query, releases connection) */
+  result = await pool.awaitQuery(`SELECT * FROM transactions WHERE ticker = ?`, [`KSS`]);
+  
+  /** Log output */
+  console.log(result);
+  
+  /** Get a new connection from the pool */
+  const connection2 = await pool.awaitGetConnection();
+  
+  /** Begin a new transaction */
+  await connection2.awaitBeginTransaction();
+  
+  /** Perform query for max id number in users table */
+  result = await connection2.awaitQuery(`SELECT MAX(id) maxId FROM users`);
+  
+  /** Add one to max id to get new id number */
+  const newId = result[0].maxId + 1;
+  
+  /** Insert new test user with new id number */
+  await connection2.awaitQuery(`INSERT INTO users (id, hash, username, name, accounts) VALUES (?, ?, ?, ?, ?)`, [newId, ``, `testuser`, `Test User`, ``]);
+  
+  /** Commit transaction */
+  await connection2.awaitCommit();
+  
+  /** Release connection */
+  connection2.release();
+  
   /** Close connection pool */
-  db.end();
+  await pool.awaitEnd();
 })();
@@ -59,2 +128,4 @@
 ```console
+Connection 218 connected
+Connection 218 acquired
 [ RowDataPacket {
@@ -78,6 +149,38 @@ id: 56,
     type: 2 } ]
+Connection 218 released
+Connection 218 acquired
+Connection 218 released
+[ RowDataPacket {
+    id: 17,
+    account: 1,
+    commission: 7,
+    date: 2017-11-09T08:00:00.000Z,
+    price: 38.23,
+    shares: 100,
+    ticker: 'KSS',
+    type: 1 },
+  RowDataPacket {
+    id: 18,
+    account: 1,
+    commission: 7,
+    date: 2017-11-10T08:00:00.000Z,
+    price: 42.6,
+    shares: -100,
+    ticker: 'KSS',
+    type: 2 } ]
+Connection 218 acquired
+Connection 218 released
 ```
   
+## Not Currently Supported
+
+* Pool Cluster's -- for now, can be added later if needed
+* Result streams -- for now, can be added later if needed
+
+## Contributing
+
+* Please open issue for any bugs found or feature requests
+
 ## License
 
 MIT

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Major refactor

Supply chain risk

Package has recently undergone a major refactor. It may be unstable or indicate significant internal changes. Use caution when updating to versions that include significant changes.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

22200

increased by409.41%

Lines of code

585

increased by723.94%

Number of lines in readme file

183

increased by128.75%

Worsened metrics

Number of medium supply chain risk alerts

1

increased byInfinity%

No dependency changes