larvitdb - npm Package Compare versions

Comparing version 1.4.0 to 2.0.0

larvitdb.js

@@ -115,73 +115,80 @@ 'use strict';
 
-	try {
-		ready(function () {
-			let startTime;
+	ready(function () {
+		let startTime;
 
-			if (typeof options === 'function') {
-				cb	= options;
-				options	= {};
-			}
+		if (typeof options === 'function') {
+			cb	= options;
+			options	= {};
+		}
 
-			if (typeof dbFields === 'function') {
-				cb	= dbFields;
-				dbFields	= [];
-				options	= {};
-			}
+		if (typeof dbFields === 'function') {
+			cb	= dbFields;
+			dbFields	= [];
+			options	= {};
+		}
 
-			if (typeof cb !== 'function') {
-				cb	= function () {};
-				options	= {};
-			}
+		if (typeof cb !== 'function') {
+			cb	= function () {};
+			options	= {};
+		}
 
-			if (options.retryNr	=== undefined) { options.retryNr	= 0;	}
-			if (options.ignoreLongQueryWarning	=== undefined) { options.ignoreLongQueryWarning	= true;	}
+		if (options.retryNr	=== undefined) { options.retryNr	= 0;	}
+		if (options.ignoreLongQueryWarning	=== undefined) { options.ignoreLongQueryWarning	= true;	}
 
-			if (exports.pool === undefined) {
-				const	err	= new Error('No pool configured. sql: "' + sql + '" dbFields: ' + JSON.stringify(dbFields));
-				log.error(logPrefix + err.message);
-				return cb(err);
+		if (exports.pool === undefined) {
+			const	err	= new Error('No pool configured. sql: "' + sql + '" dbFields: ' + JSON.stringify(dbFields));
+			log.error(logPrefix + err.message);
+			return cb(err);
+		}
+
+		// Convert datetimes to UTC
+		if (Array.isArray(dbFields)) {
+			for (let i = 0; dbFields[i] !== undefined; i ++) {
+				if (typeof dbFields[i] === Date) {
+					const	dbField	= dbFields[i];
+					dbField	= dbField.toISOString();
+					dbField[10]	= ' '; // Replace T with a space
+					dbField	= dbField.substring(0, dbField.length - 1);	// Cut the last Z off
+				}
 			}
+		}
 
-			startTime	= process.hrtime();
+		startTime	= process.hrtime();
 
-			exports.pool.query(sql, dbFields, function (err, rows, rowFields) {
-				const	queryTime	= utils.hrtimeToMs(startTime, 4);
+		exports.pool.query(sql, dbFields, function (err, rows, rowFields) {
+			const	queryTime	= utils.hrtimeToMs(startTime, 4);
 
-				if (conf.longQueryTime !== false && conf.longQueryTime < queryTime && options.ignoreLongQueryWarning !== true) {
-					log.warn(logPrefix + 'Ran SQL: "' + sql + '" with dbFields: ' + JSON.stringify(dbFields) + ' in ' + queryTime + 'ms');
-				} else {
-					log.debug(logPrefix + 'Ran SQL: "' + sql + '" with dbFields: ' + JSON.stringify(dbFields) + ' in ' + queryTime + 'ms');
-				}
+			if (conf.longQueryTime !== false && conf.longQueryTime < queryTime && options.ignoreLongQueryWarning !== true) {
+				log.warn(logPrefix + 'Ran SQL: "' + sql + '" with dbFields: ' + JSON.stringify(dbFields) + ' in ' + queryTime + 'ms');
+			} else {
+				log.debug(logPrefix + 'Ran SQL: "' + sql + '" with dbFields: ' + JSON.stringify(dbFields) + ' in ' + queryTime + 'ms');
+			}
 
-				// We log and handle plain database errors in a unified matter
-				if (err) {
-					err.sql	= sql;
-					err.fields	= dbFields;
+			// We log and handle plain database errors in a unified matter
+			if (err) {
+				err.sql	= sql;
+				err.fields	= dbFields;
 
-					// If this is a coverable error, simply try again.
-					if (conf.recoverableErrors.indexOf(err.code) !== - 1) {
-						options.retryNr = options.retryNr + 1;
-						if (options.retryNr <= conf.retries) {
-							log.warn(logPrefix + 'Retrying database recoverable error: ' + err.message + ' retryNr: ' + options.retryNr + ' SQL: "' + sql + '" dbFields: ' + JSON.stringify(dbFields));
-							setTimeout(function () {
-								query(sql, dbFields, {'retryNr': options.retryNr}, cb);
-							}, 50);
-							return;
-						}
-
-						log.error(logPrefix + 'Exhausted retries (' + options.retryNr + ') for database recoverable error: ' + err.message + ' SQL: "' + err.sql + '" dbFields: ' + JSON.stringify(dbFields));
-						return cb(err);
+				// If this is a coverable error, simply try again.
+				if (conf.recoverableErrors.indexOf(err.code) !== - 1) {
+					options.retryNr = options.retryNr + 1;
+					if (options.retryNr <= conf.retries) {
+						log.warn(logPrefix + 'Retrying database recoverable error: ' + err.message + ' retryNr: ' + options.retryNr + ' SQL: "' + sql + '" dbFields: ' + JSON.stringify(dbFields));
+						setTimeout(function () {
+							query(sql, dbFields, {'retryNr': options.retryNr}, cb);
+						}, 50);
+						return;
 					}
 
-					log.error(logPrefix + 'Database error msg: ' + err.message + ', code: "' + err.code + '" SQL: "' + err.sql + '" dbFields: ' + JSON.stringify(dbFields));
+					log.error(logPrefix + 'Exhausted retries (' + options.retryNr + ') for database recoverable error: ' + err.message + ' SQL: "' + err.sql + '" dbFields: ' + JSON.stringify(dbFields));
 					return cb(err);
 				}
 
-				cb(null, rows, rowFields);
-			});
+				log.error(logPrefix + 'Database error msg: ' + err.message + ', code: "' + err.code + '" SQL: "' + err.sql + '" dbFields: ' + JSON.stringify(dbFields));
+				return cb(err);
+			}
+
+			cb(null, rows, rowFields);
 		});
-	} catch (err) {
-		log.error(logPrefix + 'Throwed error from database driver: ' + err.message);
-		cb(err);
-	}
+	});
 };
@@ -198,68 +205,63 @@
 
-	try {
-		ready(function () {
-			exports.pool.getConnection(function (err, con) {
-				const	tables	= [],
-					tasks	= [];
+	ready(function () {
+		exports.pool.getConnection(function (err, con) {
+			const	tables	= [],
+				tasks	= [];
 
-				if (err) {
-					log.error(logPrefix + 'Could not get a connection from the pool: ' + err.message);
-					cb(err);
-					return;
-				}
+			if (err) {
+				log.error(logPrefix + 'Could not get a connection from the pool: ' + err.message);
+				cb(err);
+				return;
+			}
 
-				// Disalbe foreign key checks to be able to remove tables in any order
-				tasks.push(function (cb) {
-					con.query('SET FOREIGN_KEY_CHECKS=0;', cb);
-				});
+			// Disalbe foreign key checks to be able to remove tables in any order
+			tasks.push(function (cb) {
+				con.query('SET FOREIGN_KEY_CHECKS=0;', cb);
+			});
 
-				// Gather table names
-				tasks.push(function (cb) {
-					con.query('SHOW TABLES', function (err, rows) {
-						if (err) {
-							log.error(logPrefix + 'Error when running "SHOW TABLES": ' + err.message);
-							cb(err);
-							return;
-						}
+			// Gather table names
+			tasks.push(function (cb) {
+				con.query('SHOW TABLES', function (err, rows) {
+					if (err) {
+						log.error(logPrefix + 'Error when running "SHOW TABLES": ' + err.message);
+						cb(err);
+						return;
+					}
 
-						for (let i = 0; rows[i] !== undefined; i ++) {
-							tables.push(rows[i]['Tables_in_' + exports.conf.database]);
-						}
+					for (let i = 0; rows[i] !== undefined; i ++) {
+						tables.push(rows[i]['Tables_in_' + exports.conf.database]);
+					}
 
-						cb();
-					});
+					cb();
 				});
+			});
 
-				// Actually remove tables
-				tasks.push(function (cb) {
-					const sqlTasks = [];
+			// Actually remove tables
+			tasks.push(function (cb) {
+				const sqlTasks = [];
 
-					for (let i = 0; tables[i] !== undefined; i ++) {
-						let tableName = tables[i];
+				for (let i = 0; tables[i] !== undefined; i ++) {
+					let tableName = tables[i];
 
-						sqlTasks.push(function (cb) {
-							con.query('DROP TABLE `' + tableName + '`;', cb);
-						});
-					}
+					sqlTasks.push(function (cb) {
+						con.query('DROP TABLE `' + tableName + '`;', cb);
+					});
+				}
 
-					async.parallel(sqlTasks, cb);
-				});
+				async.parallel(sqlTasks, cb);
+			});
 
-				// Set foreign key checks back to normal
-				tasks.push(function (cb) {
-					con.query('SET FOREIGN_KEY_CHECKS=1;', cb);
-				});
+			// Set foreign key checks back to normal
+			tasks.push(function (cb) {
+				con.query('SET FOREIGN_KEY_CHECKS=1;', cb);
+			});
 
-				tasks.push(function (cb) {
-					con.release();
-					cb();
-				});
+			tasks.push(function (cb) {
+				con.release();
+				cb();
+			});
 
-				async.series(tasks, cb);
-			});
+			async.series(tasks, cb);
 		});
-	} catch (err) {
-		log.error(logPrefix + 'Throwed error from database driver: ' + err.message);
-		cb(err);
-	}
+	});
 }
@@ -274,10 +276,12 @@
 	function tryToConnect(cb) {
-		const dbCon = mysql.createConnection(conf);
+		const	dbCon	= mysql.createConnection(conf);
 
 		dbCon.connect(function (err) {
 			if (err) {
-				log.warn(logPrefix + 'Could not connect to database, retrying in 5 seconds');
+				const	retryIntervalSeconds	= 1;
+
+				log.warn(logPrefix + 'Could not connect to database, retrying in ' + retryIntervalSeconds + ' seconds');
 				return setTimeout(function () {
 					tryToConnect(cb);
-				}, 1000);
+				}, retryIntervalSeconds * 1000);
 			}
@@ -297,37 +301,42 @@
 			exports.pool	= mysql.createPool(conf); // Expose pool
+		} catch (err) {
+			log.error(logPrefix + 'Throwed error from database driver: ' + err.message);
+			cb(err);
+		}
 
-			// Default to 3 retries on recoverable errors
-			if (conf.retries === undefined) {
-				conf.retries	= 3;
-			}
+		// Default to 3 retries on recoverable errors
+		if (conf.retries === undefined) {
+			conf.retries	= 3;
+		}
 
-			// Default to setting recoverable errors to lost connection
-			if (conf.recoverableErrors === undefined) {
-				conf.recoverableErrors	= ['PROTOCOL_CONNECTION_LOST', 'ER_LOCK_DEADLOCK'];
-			}
+		// Default to setting recoverable errors to lost connection
+		if (conf.recoverableErrors === undefined) {
+			conf.recoverableErrors	= ['PROTOCOL_CONNECTION_LOST', 'ER_LOCK_DEADLOCK'];
+		}
 
-			// Default slow running queries to 10 seconds
-			if (conf.longQueryTime === undefined) {
-				conf.longQueryTime	= 10000;
+		// Default slow running queries to 10 seconds
+		if (conf.longQueryTime === undefined) {
+			conf.longQueryTime	= 10000;
+		}
+
+		// Set timezone
+		exports.pool.on('connection', function (connection) {
+			connection.query('SET time_zone = \'+00:00\';');
+		});
+
+		// Make connection test to database
+		exports.pool.query('SELECT 1', function (err, rows) {
+			if (err || rows.length === 0) {
+				log.error(logPrefix + 'Database connection test failed!');
+			} else {
+				log.info(logPrefix + 'Database connection test succeeded.');
 			}
 
-			// Make connection test to database
-			exports.pool.query('SELECT 1', function (err, rows) {
-				if (err || rows.length === 0) {
-					log.error(logPrefix + 'Database connection test failed!');
-				} else {
-					log.info(logPrefix + 'Database connection test succeeded.');
-				}
+			dbSetup	= true;
+			eventEmitter.emit('checked');
 
-				dbSetup = true;
-				eventEmitter.emit('checked');
-
-				if (typeof cb === 'function') {
-					cb(err);
-				}
-			});
-		} catch (err) {
-			log.error(logPrefix + 'Throwed error from database driver: ' + err.message);
-			cb(err);
-		}
+			if (typeof cb === 'function') {
+				cb(err);
+			}
+		});
 	});
@@ -334,0 +343,0 @@

package.json

 {
   "name": "larvitdb",
-  "version": "1.4.0",
+  "version": "2.0.0",
   "description": "DB wrapper module for node.js",
@@ -5,0 +5,0 @@ "main": "larvitdb.js",

README.md

@@ -25,4 +25,5 @@ [![Build Status](https://travis-ci.org/larvit/larvitdb.svg?branch=master)](https://travis-ci.org/larvit/larvitdb) [![Dependencies](https://david-dm.org/larvit/larvitdb.svg)](https://david-dm.org/larvit/larvitdb.svg)
 db.setup({
-	'connectionLimit':   10,
-	'socketPath':        '/var/run/mysqld/mysqld.sock',
+	'host':              '127.0.0.1',                   // Do not use with socketPath
+	'socketPath':        '/var/run/mysqld/mysqld.sock', // Do not use with host
+	'connectionLimit':   10,                            // Connections in the pool
 	'user':              'foo',
@@ -130,1 +131,24 @@ 'password':          'bar',
 ```
+
+## Important about time zones!
+
+All sessions with the database will be set to UTC time!
+
+When setting datetime stuff, use the javascript native Date object, like this:
+
+```javascript
+db.query('INSERT INTO users (created, username) VALUES(?,?)', [new Date(), 'foobar']);
+```
+
+If you do, this library will convert the time zone info for you.
+
+However, please note that all date time you get back from the database will be in UTC.
+
+## Version history
+
+### 2.0.0
+
+* Always set all new sessions to UTC time zone
+* Convert Date objects to UTC datetimestamps that fits MariaDB and MySQL
+
+Major from 1.x to 2.0 since this might break functionality for some implementations.

test/test.js

@@ -313,2 +313,51 @@ 'use strict';
 	});
+
+	it('Time zone dependent data', function (done) {
+		const	tasks	= [];
+
+		// Create table
+		tasks.push(function (cb) {
+			const	sql	= 'CREATE TABLE tzstuff (id int(11), tzstamp timestamp, tzdatetime datetime);';
+			db.query(sql, cb);
+		});
+
+		// Set datetime as javascript Date object
+		tasks.push(function (cb) {
+			const	dateObj	= new Date('2018-03-04T17:38:20Z');
+			db.query('INSERT INTO tzstuff VALUES(?,?,?);', [1, dateObj, dateObj], cb);
+		});
+
+		// Check the values
+		tasks.push(function (cb) {
+			db.query('SELECT * FROM tzstuff ORDER BY id', function (err, rows) {
+				let	foundRows	= 0;
+
+				if (err) throw err;
+
+				for (let i = 0; rows[i] !== undefined; i ++) {
+					const	row	= rows[i];
+
+					if (row.id === 1) {
+						foundRows ++;
+						assert.strictEqual(row.tzstamp.toISOString(),	'2018-03-04T17:38:20.000Z');
+						assert.strictEqual(row.tzdatetime.toISOString(),	'2018-03-04T17:38:20.000Z');
+					}
+				}
+
+				assert.strictEqual(foundRows,	1);
+
+				cb();
+			});
+		});
+
+		// Remove table
+		tasks.push(function (cb) {
+			db.query('DROP TABLE tzstuff;', cb);
+		});
+
+		async.series(tasks, function (err) {
+			if (err) throw err;
+			done();
+		});
+	});
 });

No alert changes

Improved metrics

Total package byte prevSize

25697

increased by9.2%

Lines of code

575

increased by8.29%

Number of lines in readme file

152

increased by17.83%

No dependency changes