MySQLTable: Add the .transacting() method

This will allow queries to be made during a transaction using a MySQLTable instance.
nwoltman · Dec 8, 2016 · 8c4905e · 8c4905e
1 parent f8335e1
commit 8c4905e
Show file tree

Hide file tree

Showing 4 changed files with 185 additions and 42 deletions.
diff --git a/README.md b/README.md
@@ -410,11 +410,9 @@ pool.transaction((trxn, done) => {
       done
      );
   });
-})
-.then(result => {
+}).then(result => {
   // result is the result of inserting "Rover" into `pets`
-})
-.catch(err => {
+}).catch(err => {
   // If this is called then the inserts will have been rolled back
   // (so "dog" will not be in the `animals` table)
 });
@@ -423,16 +421,14 @@ pool.transaction((trxn, done) => {
 **Example**: Returning a promise
 ```js
 pool.transaction((trxn) => {
-  return trxn.pquery('INSERT INTO `animals` VALUES ("dog")')
+  return trxn.pquery('INSERT INTO `animals` (`type`) VALUES ("dog")')
     .then(result => trxn.pquery(
-      'INSERT INTO `pets` (`type`,`name`) VALUES (?, "Rover")',
+      'INSERT INTO `pets` (`typeID`,`name`) VALUES (?, "Rover")',
       [result.insertId]
     ));
-})
-.then(result => {
+}).then(result => {
   // result is the result of inserting "Rover" into `pets`
-})
-.catch(err => {
+}).catch(err => {
   // If this is called then the inserts will have been rolled back
 });
 ```
@@ -545,13 +541,15 @@ A class that provides convenient methods for performing queries.
     * [.name](#MySQLTable+name) : <code>string</code>
     * [.schema](#MySQLTable+schema) : <code>Object</code>
     * [.pool](#MySQLTable+pool) : <code>[PoolPlus](#PoolPlus)</code>
+    * [.trxn](#MySQLTable+trxn) : <code>?[Connection](#Connection)</code>
     * [.select(columns, [sqlString], [values], [cb])](#MySQLTable+select) ⇒ <code>Promise</code>
     * [.insert(data, [sqlString], [values], [cb])](#MySQLTable+insert) ⇒ <code>Promise</code>
     * ~~[.insertIgnore(data, cb)](#MySQLTable+insertIgnore) ⇒ <code>void</code>~~
     * ~~[.replace(data, cb)](#MySQLTable+replace) ⇒ <code>void</code>~~
     * [.update([data], [sqlString], [values], [cb])](#MySQLTable+update) ⇒ <code>Promise</code>
     * [.delete([sqlString], [values], [cb])](#MySQLTable+delete) ⇒ <code>Promise</code>
     * [.query()](#MySQLTable+query) ⇒ <code>Promise</code>
+    * [.transacting(trxn)](#MySQLTable+transacting) ⇒ <code>[MySQLTable](#MySQLTable)</code>
 
 
 ---
@@ -588,6 +586,15 @@ The table's schema (as passed to [`poolPlus.defineTable()`](#PoolPlus+defineTabl
 The `PoolPlus` instance that created this table.
 
 
+---
+
+<a name="MySQLTable+trxn"></a>
+
+### mySQLTable.trxn : <code>?[Connection](#Connection)</code>
+The transaction connection that created this table from a call
+to [`table.transacting(trxn)`](#MySQLTable+transacting).
+
+
 ---
 
 <a name="MySQLTable+select"></a>
@@ -857,7 +864,43 @@ userTable.delete((err, result) => {
 <a name="MySQLTable+query"></a>
 
 ### mySQLTable.query() ⇒ <code>Promise</code>
-Exactly the same as [pool.pquery()](#PoolPlus+pquery).
+Exactly the same as [`pool.pquery()`](#PoolPlus+pquery).
+
+
+---
+
+<a name="MySQLTable+transacting"></a>
+
+### mySQLTable.transacting(trxn) ⇒ <code>[MySQLTable](#MySQLTable)</code>
+Returns a new `MySQLTable` instance that will perform queries using the provided transaction connection.
+
+
+| Param | Type | Description |
+| --- | --- | --- |
+| trxn | <code>[Connection](#Connection)</code> | The transaction connection that will be used to perform queries. |
+
+**Returns**: <code>[MySQLTable](#MySQLTable)</code> - A new `MySQLTable` instance that will perform queries using the provided transaction
+    connection instead of the `PoolPlus` instance that was used to create the original instance.  
+**See**: [`pool.transaction()`](#PoolPlus+transaction)
+
+**Example**:
+```js
+const animalsTable = pool.defineTable('animals', schema);
+const petsTable = pool.defineTable('pets', schema);
+
+pool.transaction((trxn) => {
+  return animalsTable.transacting(trxn)
+    .insert({type: 'dog'})
+    .then(result =>
+      petsTable.transacting(trxn)
+        .insert({typeID: result.insertId, name: 'Rover'})
+    );
+}).then(result => {
+  // result is the result of inserting "Rover" into the pets table
+}).catch(err => {
+  // An error occurred during the transaction
+});
+```
 
 
 ---

diff --git a/lib/MySQLTable.js b/lib/MySQLTable.js
@@ -8,7 +8,7 @@ const util = require('util');
  * @see {@link https://github.com/mysqljs/mysql#performing-queries}
  */
 class MySQLTable {
-  constructor(name, schema, pool) {
+  constructor(name, schema, pool, trxn) {
     /**
      * The table's name (as passed to {@link PoolPlus#defineTable|`poolPlus.defineTable()`}).
      * @deprecated since version 0.4.0 and will be removed in version 0.5.0.
@@ -30,8 +30,15 @@ class MySQLTable {
      * @constant {PoolPlus}
      */
     this.pool = pool;
+    /**
+     * The transaction connection that created this table from a call
+     * to {@link MySQLTable#transacting|`table.transacting(trxn)`}.
+     * @constant {?Connection}
+     */
+    this.trxn = trxn;
 
-    this._escapedName = pool.escapeId(name);
+    this._db = trxn || pool;
+    this._escapedName = this._db.escapeId(name);
   }
 
   /**
@@ -81,13 +88,14 @@ class MySQLTable {
    */
   select(columns, sqlString, values, cb) {
     if (typeof columns !== 'string') {
-      columns = this.pool.escapeId(columns);
+      columns = this._db.escapeId(columns);
     }
     if (typeof sqlString === 'function') {
       values = sqlString;
       sqlString = '';
     }
-    return this.pool.pquery(
+
+    return this._db.pquery(
       'SELECT ' + columns + ' FROM ' + this._escapedName + ' ' + sqlString,
       values,
       cb
@@ -144,12 +152,12 @@ class MySQLTable {
    */
   insert(data, sqlString, values, cb) {
     if (cb) {
-      sqlString = this.pool.format(sqlString, values);
+      sqlString = this._db.format(sqlString, values);
     } else if (values) {
       if (typeof values === 'function') {
         cb = values;
       } else {
-        sqlString = this.pool.format(sqlString, values);
+        sqlString = this._db.format(sqlString, values);
       }
     } else if (sqlString === undefined) {
       sqlString = '';
@@ -159,15 +167,15 @@ class MySQLTable {
     }
 
     if (data instanceof Array) {
-      return this.pool.pquery(
+      return this._db.pquery(
         'INSERT INTO ' + this._escapedName + (data.length > 1 ? ' (??)' : '') + ' VALUES  ?',
         data,
         cb
       );
     }
 
-    return this.pool.pquery(
-      'INSERT INTO ' + this._escapedName + ' SET ' + this.pool.escape(data) + ' ' + sqlString,
+    return this._db.pquery(
+      'INSERT INTO ' + this._escapedName + ' SET ' + this._db.escape(data) + ' ' + sqlString,
       cb
     );
   }
@@ -191,8 +199,8 @@ class MySQLTable {
    * });
    */
   insertIgnore(data, cb) {
-    this.pool.query(
-      'INSERT IGNORE INTO ' + this._escapedName + ' SET ' + this.pool.escape(data),
+    this._db.query(
+      'INSERT IGNORE INTO ' + this._escapedName + ' SET ' + this._db.escape(data),
       cb
     );
   }
@@ -213,8 +221,8 @@ class MySQLTable {
    * });
    */
   replace(data, cb) {
-    this.pool.query(
-      'REPLACE INTO ' + this._escapedName + ' SET ' + this.pool.escape(data),
+    this._db.query(
+      'REPLACE INTO ' + this._escapedName + ' SET ' + this._db.escape(data),
       cb
     );
   }
@@ -260,20 +268,20 @@ class MySQLTable {
    */
   update(data, sqlString, values, cb) {
     if (typeof data === 'string') {
-      return this.pool.pquery(
+      return this._db.pquery(
         'UPDATE ' + this._escapedName + ' SET ' + data,
         sqlString,
         values
       );
     }
 
     if (cb) {
-      sqlString = this.pool.format(sqlString, values);
+      sqlString = this._db.format(sqlString, values);
     } else if (values) {
       if (typeof values === 'function') {
         cb = values;
       } else {
-        sqlString = this.pool.format(sqlString, values);
+        sqlString = this._db.format(sqlString, values);
       }
     } else if (sqlString === undefined) {
       sqlString = '';
@@ -282,8 +290,8 @@ class MySQLTable {
       sqlString = '';
     }
 
-    return this.pool.pquery(
-      'UPDATE ' + this._escapedName + ' SET ' + this.pool.escape(data) + ' ' + sqlString,
+    return this._db.pquery(
+      'UPDATE ' + this._escapedName + ' SET ' + this._db.escape(data) + ' ' + sqlString,
       cb
     );
   }
@@ -314,20 +322,49 @@ class MySQLTable {
       values = sqlString;
       sqlString = '';
     }
-    return this.pool.pquery(
+    return this._db.pquery(
       'DELETE FROM ' + this._escapedName + ' ' + sqlString,
       values,
       cb
     );
   }
 
   /**
-   * Exactly the same as {@link PoolPlus#pquery|pool.pquery()}.
+   * Exactly the same as {@link PoolPlus#pquery|`pool.pquery()`}.
    *
    * @returns {?Promise}
    */
   query(sql, values, cb) {
-    return this.pool.pquery(sql, values, cb);
+    return this._db.pquery(sql, values, cb);
+  }
+
+  /**
+   * Returns a new `MySQLTable` instance that will perform queries using the provided transaction connection.
+   *
+   * @param {Connection} trxn - The transaction connection that will be used to perform queries.
+   * @return {MySQLTable} A new `MySQLTable` instance that will perform queries using the provided transaction
+   *     connection instead of the `PoolPlus` instance that was used to create the original instance.
+   * @see {@link PoolPlus#transaction|`pool.transaction()`}
+   *
+   * @example
+   * const animalsTable = pool.defineTable('animals', schema);
+   * const petsTable = pool.defineTable('pets', schema);
+   *
+   * pool.transaction((trxn) => {
+   *   return animalsTable.transacting(trxn)
+   *     .insert({type: 'dog'})
+   *     .then(result =>
+   *       petsTable.transacting(trxn)
+   *         .insert({typeID: result.insertId, name: 'Rover'})
+   *     );
+   * }).then(result => {
+   *   // result is the result of inserting "Rover" into the pets table
+   * }).catch(err => {
+   *   // An error occurred during the transaction
+   * });
+   */
+  transacting(trxn) {
+    return new MySQLTable(this.name, this.schema, this.pool, trxn);
   }
 }
 

diff --git a/lib/PoolPlus.js b/lib/PoolPlus.js
@@ -198,27 +198,23 @@ class PoolPlus extends Pool {
    *       done
    *      );
    *   });
-   * })
-   * .then(result => {
+   * }).then(result => {
    *   // result is the result of inserting "Rover" into `pets`
-   * })
-   * .catch(err => {
+   * }).catch(err => {
    *   // If this is called then the inserts will have been rolled back
    *   // (so "dog" will not be in the `animals` table)
    * });
    *
    * @example <caption>Returning a promise</caption>
    * pool.transaction((trxn) => {
-   *   return trxn.pquery('INSERT INTO `animals` VALUES ("dog")')
+   *   return trxn.pquery('INSERT INTO `animals` (`type`) VALUES ("dog")')
    *     .then(result => trxn.pquery(
-   *       'INSERT INTO `pets` (`type`,`name`) VALUES (?, "Rover")',
+   *       'INSERT INTO `pets` (`typeID`,`name`) VALUES (?, "Rover")',
    *       [result.insertId]
    *     ));
-   * })
-   * .then(result => {
+   * }).then(result => {
    *   // result is the result of inserting "Rover" into `pets`
-   * })
-   * .catch(err => {
+   * }).catch(err => {
    *   // If this is called then the inserts will have been rolled back
    * });
    */