larvitdbmigration - npm Package Compare versions

Comparing version 0.2.7 to 0.3.0

dbMigrations.js

 'use strict';
 
 var async = require('async'),
+    exec  = require('child_process').exec,
     log   = require('winston'),
@@ -22,3 +23,3 @@ fs    = require('fs'),
 	function getLock(cb) {
-		db.query('SELECT running FROM `' + options.tableName + '`;', function(err, rows) {
+		db.query('UPDATE `' + options.tableName + '` SET running = 1', function(err, res) {
 			if (err) {
@@ -29,4 +30,4 @@ cb(err);
 
-			if (parseInt(rows[0].running) === 1) {
-				log.verbose('larvitdbmigration: Another process is running the migrations, wait and try again soon.');
+			if (res.changedRows === 0) {
+				log.info('larvitdbmigration: Another process is running the migrations, wait and try again soon.');
 				setTimeout(function() {
@@ -48,3 +49,5 @@ getLock(cb);
 			fs.readdir(options.migrationScriptsPath, function(err, items) {
-				var i;
+				var sql = 'UPDATE `' + options.tableName + '` SET version = ' + parseInt(startVersion) + ';',
+				    cmd,
+				    i;
 
@@ -60,6 +63,4 @@ if (err) {
 					if (items[i] === startVersion + '.js') {
-						log.info('larvitdbmigration: runScripts() - Found migration script #' + startVersion + ', running it now.');
+						log.info('larvitdbmigration: runScripts() - Found js migration script #' + startVersion + ', running it now.');
 						require(options.migrationScriptsPath + '/' + startVersion + '.js')(function(err) {
-							var sql = 'UPDATE `' + options.tableName + '` SET version = ' + parseInt(startVersion) + ';';
-
 							if (err) {
@@ -71,4 +72,3 @@ log.error('larvitdbmigration: runScripts() - Got error running migration script #' + startVersion + ': ' + err.message);
 
-							log.info('larvitdbmigration: runScripts() - Migration script #' + startVersion + ' ran. Update database version and move on.');
-							log.debug('larvitdbmigration: runScripts() - Running SQL: "' + sql + '"');
+							log.info('larvitdbmigration: runScripts() - Js migration script #' + startVersion + ' ran. Updating database version and moving on.');
 							db.query(sql, function(err) {
@@ -85,2 +85,40 @@ if (err) {
 						return;
+					} else if (items[i] === startVersion + '.sql') {
+						log.info('larvitdbmigration: runScripts() - Found sql migration script #' + startVersion + ', running it now.');
+
+						cmd = 'mysql -u ' + db.conf.user + ' -p' + db.conf.password;
+
+						if (db.conf.host) {
+							cmd += ' -h ' + db.conf.host;
+						}
+
+						cmd += ' ' + db.conf.database + ' < ' + options.migrationScriptsPath + '/' + items[i];
+
+						exec(cmd, function(err, stdout, stderr) {
+							var customErr;
+
+							if (err) {
+								cb(err);
+								return;
+							}
+
+							if (stderr) {
+								customErr = new Error('stderr is not empty: ' + stderr);
+								log.error('larvitdbmigration: ' + customErr.message);
+								cb(customErr);
+								return;
+							}
+
+							log.info('larvitdbmigration: runScripts() - Sql migration script #' + startVersion + ' ran. Updating database version and moving on.');
+							db.query(sql, function(err) {
+								if (err) {
+									cb(err);
+									return;
+								}
+
+								runScripts(parseInt(startVersion) + 1, cb);
+							});
+						});
+
+						return;
 					}
@@ -98,4 +136,3 @@
 		tasks.push(function(cb) {
-			var sql = 'CREATE TABLE IF NOT EXISTS `' + options.tableName + '` (`version` int(10) unsigned NOT NULL DEFAULT \'0\', `running` tinyint(3) unsigned NOT NULL DEFAULT \'0\') ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT \'Used for automatic database versioning. Do not modify!\';';
-			log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
+			var sql = 'CREATE TABLE IF NOT EXISTS `' + options.tableName + '` (`id` tinyint(1) unsigned NOT NULL DEFAULT \'1\', `version` int(10) unsigned NOT NULL DEFAULT \'0\', `running` tinyint(3) unsigned NOT NULL DEFAULT \'0\', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin COMMENT=\'Used for automatic database versioning. Do not modify!\';';
 			db.query(sql, cb);
@@ -106,42 +143,11 @@ });
 		tasks.push(function(cb) {
-			var sql = 'SELECT * FROM `' + options.tableName + '`;';
-
-			log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
-			db.query(sql, function(err, rows) {
-				var sql = 'INSERT INTO `' + options.tableName + '` (version, running) VALUES(0, 0);';
-
-				if (err) {
-					cb(err);
-					return;
-				}
-
-				if ( ! rows.length) {
-					log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
-					db.query(sql, cb);
-				} else {
-					cb();
-				}
-			});
+			db.query('INSERT IGNORE INTO `' + options.tableName + '` VALUES(1, 0, 0);', cb);
 		});
 
 		// Lock table by setting the running column to 1
-		tasks.push(function(cb) {
-			getLock(function(err) {
-				var sql = 'UPDATE `' + options.tableName + '` SET running = 1;';
+		tasks.push(getLock);
 
-				if (err) {
-					cb(err);
-					return;
-				}
-
-				log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
-				db.query(sql, cb);
-			});
-		});
-
 		// Get current version
 		tasks.push(function(cb) {
-			var sql = 'SELECT version FROM `' + options.tableName + '`;';
-			log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
-			db.query(sql, function(err, rows) {
+			db.query('SELECT version FROM `' + options.tableName + '`;', function(err, rows) {
 				if (err) {
@@ -154,2 +160,4 @@ cb(err);
 
+				log.info('larvitdbmigration: Current database version is ' + curVer);
+
 				cb();
@@ -166,5 +174,3 @@ });
 		tasks.push(function(cb) {
-			var sql = 'UPDATE `' + options.tableName + '` SET running = 0;';
-			log.debug('larvitdbmigration: Running SQL: "' + sql + '"');
-			db.query(sql, cb);
+			db.query('UPDATE `' + options.tableName + '` SET running = 0;', cb);
 		});
@@ -171,0 +177,0 @@

package.json

@@ -11,4 +11,4 @@ {
 		"async": "~1.5",
-		"lodash": "~3.10",
-		"mysql": "~2.9",
+		"larvitdb": "~0.4",
+		"lodash": "~4.0",
 		"winston": "~2.1"
@@ -34,3 +34,3 @@ },
 	},
-	"version": "0.2.7",
+	"version": "0.3.0",
 	"readmeFilename": "README.md",
@@ -37,0 +37,0 @@ "scripts": {},

README.md

@@ -19,37 +19,82 @@ # Database migration tool
 
-    'use strict';
+```javascript
+'use strict';
 
-    var dbMigration = require('larvitdbmigration');
+var dbMigration = require('larvitdbmigration');
 
-    dbMigration({'host': '127.0.0.1', 'user': 'bar', 'database': 'bar'})(function(err) {
-    	if (err)
-    		throw err;
+dbMigration({
+	'host': '127.0.0.1',
+	'user': 'bar',
+	'database': 'bar'
+})(function(err) {
+	if (err) {
+		throw err;
+	}
 
-    	// Now database is migrated and ready for use!
-    });
+	// Now database is migrated and ready for use!
+});
+```
 
 To use custom table name and/or script path, just change
 
-    	dbMigration({'host': '127.0.0.1', 'user': 'bar', 'database': 'bar'})(function(err) {
+```javascript
+dbMigration({
+	'host': '127.0.0.1',
+	'user': 'bar',
+	'password': 'bar',
+	'database': 'bar'
+})(function(err) {
+```
 
 to
 
-    	dbMigration({'host': '127.0.0.1', 'user': 'bar', 'database': 'bar', 'tableName': 'some_table', 'migrationScriptsPath': './scripts_yo'})(function(err) {
+```javascript
+dbMigration({
+	'host': '127.0.0.1',
+	'user': 'bar',
+	'password': 'bar',
+	'database': 'bar',
+	'tableName': 'some_table',
+	'migrationScriptsPath': './scripts_yo'
+})(function(err) {
+```
 
-### Example migration script
+### Example migration scripts
 
 Lets say the current database have a table like this:
 
-    CREATE TABLE bloj (nisse int(11));
+```SQL
+CREATE TABLE bloj (nisse int(11));
+```
 
-And in the next deploy we'd like to change the column name "nisse" to "hasse". Then create the file <application root>/dbmigration/1.js with this content:
+And in the next deploy we'd like to change the column name "nisse" to "hasse". For this you can do one of two methods:
 
-    'use strict';
+#### Javascript
 
-    var db = require('db');
+Create the file <application root>/<migrationScriptsPath>/1.js with this content:
 
-    exports = module.exports = function(cb) {
-    	db.query('ALTER TABLE bloj CHANGE nisse hasse int(11);', cb);
-    }
+```javascript
+'use strict';
 
-Tadaaa! Now this gets done once and the version will be bumped to 1. If you then create a script named "2.js" you might guess what happends. :)
+var db = require('db');
+
+exports = module.exports = function(cb) {
+	db.query('ALTER TABLE bloj CHANGE nisse hasse int(11);', cb);
+}
+```
+
+#### SQL
+
+_IMPORTANT!_ SQL files will be ignored if a .js file exists.
+
+_ALSO IMPORTANT!_ SQL files require the mysql client to be installed on the host system.
+
+Create the file <application root>/<migrationScriptsPath>/1.sql with this content:
+
+```SQL
+ALTER TABLE bloj CHANGE nisse hasse int(11);
+```
+
+#### Summary
+
+Tadaaa! Now this gets done once and the version will be bumped to 1. If you then create a script named "2.js" or "2.sql" you might guess what happends. :)

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

Shell access

Supply chain risk

This module accesses the system shell. Accessing the system shell increases the risk of executing arbitrary code.

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

8634

increased by20.55%

Number of package files

4

increased by33.33%

Lines of code

135

increased by2.27%

Number of lines in readme file

100

increased by81.82%

Worsened metrics

Number of medium supply chain risk alerts

1

increased byInfinity%

Dependency changes

• + Addedlarvitdb@~0.4

• + Addedbignumber.js@9.0.0(transitive)

• + Addedisarray@1.0.0(transitive)

• + Addedlarvitdb@0.4.5(transitive)

• + Addedlodash@4.0.1(transitive)

• + Addedmysql@2.18.1(transitive)

• + Addedprocess-nextick-args@2.0.1(transitive)

• + Addedreadable-stream@2.3.7(transitive)

• + Addedsafe-buffer@5.1.2(transitive)

• + Addedsqlstring@2.3.1(transitive)

• + Addedstring_decoder@1.1.1(transitive)

• + Addedutil-deprecate@1.0.2(transitive)

• - Removedmysql@~2.9

• - Removedbignumber.js@2.0.7(transitive)

• - Removedisarray@0.0.1(transitive)

• - Removedlodash@3.10.1(transitive)

• - Removedmysql@2.9.0(transitive)

• - Removedreadable-stream@1.1.14(transitive)

• - Removedstring_decoder@0.10.31(transitive)

• Updatedlodash@~4.0