hapiest-mysql - npm Package Compare versions

Comparing version 0.0.13 to 0.0.14

lib/mysqlDaoQueryHelper.js

@@ -125,5 +125,14 @@ 'use strict';
      * @param {object} obj
+     * @param {object} [config]
+     * @param {boolean} [config.dontCleanMysqlFunctions] - defaults to true; includes CURRENT_TIMESTAMP and NOW();
+     *
      * @returns {object} - cleans the values and returns an object with snake case props --> clean values
      */
-    _cleanAndMapValues(obj) {
+    _cleanAndMapValues(obj, config) {
+        const defaultConfig = {
+            dontCleanMysqlFunctions: true
+        };
+
+        config = _.merge(defaultConfig, config);
+
         if (obj instanceof VO) {
@@ -145,4 +154,11 @@ obj = obj.toJsObj();
             if (_.includes(['string', 'number', 'boolean'], typeof(uncleanValue)) || uncleanValue === null) {
-                const cleanValue = this._clean(uncleanValue);
                 const snakeCaseProperty = _.snakeCase(property);
+
+                let cleanValue = null;
+                if (config.dontCleanMysqlFunctions && _.includes(['current_timestamp','now()'],_.toLower(uncleanValue))) {
+                    cleanValue = uncleanValue;
+                } else {
+                    cleanValue = this._clean(uncleanValue);
+                }
+
                 cleanValues[snakeCaseProperty] = cleanValue;
@@ -158,4 +174,11 @@ }
         Object.keys(cleanValues).forEach(columnName => {
+            const whereClauseKey = _.camelCase(columnName);
+            const uncleanValue = whereClause[whereClauseKey];
             const cleanValue = cleanValues[columnName];
-            sqlObject = sqlObject.where(`${columnName} = ${cleanValue}`);
+
+            if (uncleanValue === null) {
+                sqlObject = sqlObject.where(`${columnName} IS NULL`);
+            } else {
+                sqlObject = sqlObject.where(`${columnName} = ${cleanValue}`);
+            }
         });
@@ -162,0 +185,0 @@ return sqlObject;

package.json

 {
   "name": "hapiest-mysql",
-  "version": "0.0.13",
+  "version": "0.0.14",
   "description": "A wrapper around mysql that provides a very descriptive way of running queries.",
@@ -5,0 +5,0 @@ "main": "index.js",

test/unit/test-mysqlDaoQueryHelper.js

@@ -5,2 +5,3 @@ 'use strict';
 const Mysql = require('mysql');
+const Squel = require('squel');
 const VO = require('hapiest-vo');
@@ -97,4 +98,11 @@
             sql.should.eql("SELECT * FROM users WHERE (url = 'http://www.youtube.com/?q=somevideo') LIMIT 1");
-        })
+        });
 
+        it('Should generate an IS NULL in where clause ', function() {
+            const sql = mysqlDaoQueryHelper.getOne({firstName: null, lastName: 'Doe'});
+            Should.exist(sql);
+
+            sql.should.eql("SELECT * FROM users WHERE (first_name IS NULL) AND (last_name = 'Doe') LIMIT 1");
+        });
+
     });
@@ -129,2 +137,9 @@
 
+        it('Should generate an UPDATE statement and not escape CURRENT_TIMESTAMP', function() {
+            const sql = mysqlDaoQueryHelper.updateOne({id: 1}, {email: '[e-mail]', dateDeleted: 'CURRENT_TIMESTAMP'});
+            Should.exist(sql);
+
+            sql.should.eql("UPDATE users SET email = '[e-mail]', date_deleted = CURRENT_TIMESTAMP WHERE (id = 1) LIMIT 1");
+        });
+
     });
@@ -143,2 +158,116 @@
 
+    describe('_cleanAndMapValues', function() {
+
+        it('Should convert camelCase property names to snakecase property names', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({firstName: 'John', lastName: 'Doe'});
+            Should.exist(output);
+            output.should.have.properties(['first_name','last_name']);
+            output.first_name.should.eql("'John'");
+            output.last_name.should.eql("'Doe'");
+        });
+
+        it('Should converts a VO to JS object and cleans that', function() {
+            const input = new UserCreateArgs({firstName: 'firstName', lastName: 'lastName', password: 'boom!'});
+            const output = mysqlDaoQueryHelper._cleanAndMapValues(input);
+            Should.exist(output);
+            output.should.have.properties(['first_name','last_name','password']);
+            output.first_name.should.eql("'firstName'");
+            output.last_name.should.eql("'lastName'");
+            output.password.should.eql("'boom!'");
+        });
+
+        it('Should converts an object with toJsObj defined cleans that', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({
+                toJsObj: function() {
+                    return {firstName: 'firstName', lastName: 'lastName', password: 'boom!'};
+                }
+            });
+            Should.exist(output);
+            output.should.have.properties(['first_name','last_name','password']);
+            output.first_name.should.eql("'firstName'");
+            output.last_name.should.eql("'lastName'");
+            output.password.should.eql("'boom!'");
+        });
+
+        it('Should converts an object with toJSON defined cleans that', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({
+                toJSON: function() {
+                    return {firstName: 'John', lastName: 'Doe', password: 'another'};
+                }
+            });
+            Should.exist(output);
+            output.should.have.properties(['first_name','last_name','password']);
+            output.first_name.should.eql("'John'");
+            output.last_name.should.eql("'Doe'");
+            output.password.should.eql("'another'");
+        });
+
+        it('Drops arrays and objects', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({
+                firstName: 'firstName',
+                lastName: 'lastName',
+                password: 'boom!',
+                wontBeInOutput: [],
+                alsoWontBeThere: {}
+            });
+            Should.exist(output);
+            output.should.have.properties(['first_name','last_name','password']);
+            output.should.not.have.properties(['wont_be_in_output','also_wont_be_there']);
+            output.first_name.should.eql("'firstName'");
+            output.last_name.should.eql("'lastName'");
+            output.password.should.eql("'boom!'");
+        });
+
+        it('Allows special value CURRENT_TIMESTAMP and does not escape with quotes', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({
+                firstName: 'firstName',
+                dateCreated: 'CURRENT_TIMESTAMP',
+                dateAgain: 'NOW()'
+            });
+            Should.exist(output);
+            output.should.have.properties(['first_name','date_created']);
+            output.first_name.should.eql("'firstName'");
+            output.date_created.should.eql("CURRENT_TIMESTAMP");
+            output.date_again.should.eql("NOW()");
+        });
+
+        it('Escapes special value CURRENT_TIMESTAMP when explicitly asked', function() {
+            const output = mysqlDaoQueryHelper._cleanAndMapValues({
+                firstName: 'firstName',
+                dateCreated: 'CURRENT_TIMESTAMP',
+                dateAgain: 'NOW()'
+            }, {dontCleanMysqlFunctions: false});
+            Should.exist(output);
+            output.should.have.properties(['first_name','date_created']);
+            output.first_name.should.eql("'firstName'");
+            output.date_created.should.eql("'CURRENT_TIMESTAMP'");
+            output.date_again.should.eql("'NOW()'");
+        });
+
+    });
+
+    describe('_appendWhereClause', function() {
+
+        it('Should generate good WHERE clause for standard object input', function() {
+            const sqlObj = Squel.select().from('users');
+
+            mysqlDaoQueryHelper._appendWhereClause(sqlObj, {firstName: 'John', lastName: 'Doe'});
+
+            const sqlString = sqlObj.toString();
+
+            sqlString.should.eql("SELECT * FROM users WHERE (first_name = 'John') AND (last_name = 'Doe')");
+        });
+
+        it('Should generate good WHERE clause when input contains NULL value', function() {
+            const sqlObj = Squel.select().from('users');
+
+            mysqlDaoQueryHelper._appendWhereClause(sqlObj, {firstName: 'John', lastName: null});
+
+            const sqlString = sqlObj.toString();
+
+            sqlString.should.eql("SELECT * FROM users WHERE (first_name = 'John') AND (last_name IS NULL)");
+        });
+
+    });
+
 });

No alert changes

Improved metrics

Total package byte prevSize

119340

increased by5.89%

Lines of code

2717

increased by4.78%

No dependency changes