0

I want to run a couple of SQL scripts from Gulp:

const gulp = require('gulp');
const dotenv = require('dotenv')
const child_process = require('child_process');

function runSQLScript(script) {
    child_process.execFile('sqlcmd', [
        '-S', process.env.DB_SERVER,
        '-U', process.env.DB_USER,
        '-P', process.env.DB_PASSWORD,
        '-d', process.env.DB_DATABASE,
        '-f', 'i:65001',
        '-i', script
    ], {
        cwd: '../sql/sqlcmd'
    }, function(error, stdout, stderr){
        if (error !== null) {
            console.log("" + stdout);
            console.error("" + error);
        }
        if (stderr.length>0) {
            console.error('' + stderr);
        }
    });
}

gulp.task('sql', ['install-app', 'load-data']);
gulp.task('install-app', function(){
    runSQLScript('install-app.sql');
});
gulp.task('load-data', function(){
    runSQLScript('load-data.sql');
});

Then I run:

gulp sql

In an earlier version I was using child_process.execFileSync() and my scripts were running in sequence as expected. I switched to child_process.execFile() to be able to report errors but now load-data.sql fails because it no longer waits for install-app.sql to finish.

What can I do to make the task wait until runSQLScript() finishes? Did I choose a wrong approach to print errors?

Álvaro González
  • 142,137
  • 41
  • 261
  • 360

2 Answers2

1

Looks like gulp-sequence is the best choice to use (If your gulp version is < 4).

const util = require('util');
const gulp = require('gulp');
const gulpSequence = require('gulp-sequence');
const dotenv = require('dotenv')
const execFile = util.promisify(require('child_process').execFile);

function runSQLScript(script) {
    return execFile('sqlcmd', [
        '-S', process.env.DB_SERVER,
        '-U', process.env.DB_USER,
        '-P', process.env.DB_PASSWORD,
        '-d', process.env.DB_DATABASE,
        '-f', 'i:65001',
        '-i', script
    ], {
        cwd: '../sql/sqlcmd'
    }, function(error, stdout, stderr){
        if (error !== null) {
            console.log("" + stdout);
            console.error("" + error);
        }
        if (stderr.length>0) {
            console.error('' + stderr);
        }
    });
}

gulp.task('sql', gulpSequence('install-app', 'load-data'));
gulp.task('install-app', function(){
    return runSQLScript('install-app.sql');
});
gulp.task('load-data', function(){
    return runSQLScript('load-data.sql');
});
Blue
  • 22,608
  • 7
  • 62
  • 92
  • Does gulp-sequence figure out when calls to `child_process.execFile` complete? – Álvaro González Jun 27 '17 at 11:55
  • I was actually looking at the documentation for that. From the docs, you may need to "promisfy" it. I'll post an updated code snippet – Blue Jun 27 '17 at 11:57
  • I've tried your code as-is and it seems to work. Not sure if it's robust but it definitively works in my specific use case. – Álvaro González Jun 27 '17 at 11:58
  • Check the updated code. Looking [here](https://nodejs.org/api/child_process.html#child_process_child_process_execfile_file_args_options_callback) it seems you can use `util.promisfy` to make execFile return a promise. – Blue Jun 27 '17 at 11:59
  • Also, check out gulp 4.0. Seems you can do this quite easily with [`gulp.series`](https://stackoverflow.com/a/31329150/4875631). – Blue Jun 27 '17 at 12:01
  • Can't verify it right now, `util.promisify(original)` was added in Node/8.0.0 and I'm currently using 6.9.5. But you've given me a lot of stuff to play with, thank you very much. – Álvaro González Jun 27 '17 at 12:09
  • I've just learn why I (incorrectly) thought that the first version it was working. I had leftovers from previous tests where I was using [q](https://www.npmjs.com/package/q) to make `execFile()` return a promise. `gulp-sequence` was just the missing brick. – Álvaro González Jun 27 '17 at 12:16
1

Unfortunately, switching from .execFileSync() to .execFile() appears to be the simplest way to capture the script output in all circumstances. On error, .execFileSync() no longer returns script stdout and there isn't an obvious way to fetch it; you can redirect it to process stdout but, then, you have the problem of fetching it from there if you need further processing. Verbose and illogical as it is, asynchronous code is the least evil.

You can then use gulp-sequence() as FrankerZ's answer explains. As an alternative to util.promisify() (which requires Node/8) you can also use q to make child_process.execFile() return a promise.

Additionally, it seems that you need to catch() the promise returned by runSQLScript(). Otherwise, the second scripts executes anyway, no matter the result of the prior script.

const gulp = require('gulp');
const dotenv = require('dotenv')
const child_process = require('child_process');
const Q = require('q');

function runSQLScript(script) {
    var deferred = Q.defer();
    child_process.execFile('sqlcmd', [
        '-S', process.env.DB_SERVER,
        '-U', process.env.DB_USER,
        '-P', process.env.DB_PASSWORD,
        '-d', process.env.DB_DATABASE,
        '-f', 'i:65001',
        '-i', script
    ], {
        cwd: '../sql/sqlcmd'
    }, function(error, stdout, stderr){
        if (error !== null) {
            console.log("" + stdout);
            console.error("" + error);
            deferred.reject();
        } else {
            deferred.resolve();
        }
        if (stderr.length>0) {
            console.error('' + stderr);
        }

    });
    return deferred.promise;
}

gulp.task('sql', gulpSequence('install-app', 'load-data'));
gulp.task('install-app', function(callback){
    runSQLScript('install-app.sql')
        .then(function(){
            callback();
        })
        .catch(function(){
            callback("install-app failed, aborting");
        });
});
gulp.task('load-data', function(callback){
    runSQLScript('load-data.sql')
        .then(function(){
            callback();
        })
        .catch(function(){
            callback("load-data failed, aborting");
        });
});
Álvaro González
  • 142,137
  • 41
  • 261
  • 360