Run a bunch of promises but wait for the next one to be executed until the precedent resolved

Question

I'm building a script that apply all .sql files found in a specific folder. I want those script to be executed one by one and stop execution if one fails. My folder tree is this (and I call my script by npm run migrate-dev from root folder)

root/
    ├── db/
    │   ├── migrations/
    │   │   ├── 01.sql
    │   │   └── 02.sql
    │   ├── dbConfig.js
    │   └── migrate.js
    ├── package.json
    └── .env

The two files in /migrations can be for example

01.sql

USE gamehub;

02.sql

CREATE TABLE `user` (`id` CHAR (32) NOT NULL);

migrate.js

const sqlParams = require('./dbConfig');
const mysql = require('mysql');
const fs = require('fs');

const pool = mysql.createPool(sqlParams);

if (process.env.RUN_MODE) {
  console.log(`Running in dev mode`);
}
const migrate = () => {
  console.log('Running migrations');
  // get files list
  fs.readdir('./db/migrations', (err, files) => {
    if (err) {
      console.log('Error while reading files in .db/migrations');
      console.log(err);
      throw err;
    }
    // generate promises list
    const promises = files.map(
      (file) =>
        new Promise((resolve, reject) => {
          fs.readFile(`./db/migrations/${file}`, 'utf-8', (err, data) => {
            if (err) {
              console.log(`Error while reading script ${file}`);
              reject(err);
            }
            pool.query(data, (err, results) => {
              if (err) {
                console.log('Error while querying SQL');
                reject(err);
              }
              resolve();
            });
          });
        })
    );
    console.log(promises);

    promises.reduce(async (prev, promise, index) => {
      await prev;
      console.log(`Executing script #${index}: ${files[index]}`);
      promise.then(
        () => Promise.resolve(),
        (err) => {
          console.log(err);
          return;
        }
      );
    }, Promise.resolve());
  });
};
migrate();

Problems are two:

1. This code is not ended, I must end it manually with CTRL+C;
2. If an error occurs the execution is not stopped.

Output in case no error occurred:

> node -r dotenv/config ./db/migrate.js

Running in dev mode
Running migrations
[ Promise { <pending> }, Promise { <pending> } ]
Executing script #0: 01_selectGameHub.sql       
Executing script #1: 02_createUserTable.sql   

Output in case error occurred (for example if user table already exists):

> node -r dotenv/config ./db/migrate.js

Running in dev mode
Running migrations
[ Promise { <pending> }, Promise { <pending> } ]
Executing script #0: 01_selectGameHub.sql
Executing script #1: 02_createUserTable.sql
Error while querying SQL
Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3
    at Query.Sequence._packetToError
[....]
    
    at Pool.query ([....]\node_modules\mysql\lib\Pool.js:199:23)
    at [....]\db\migrate.js:28:18
    at FSReqCallback.readFileAfterClose [as oncomplete] (internal/fs/read_file_context.js:63:3) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3",
  sqlState: '42000',
  index: 0,
  sql: 'CREATE TABLE `user` (\n\t`id` CHAR (32) NOT NULL,\n    );\n    '
}

Answer 1

The first thing I would do would be to break that huge block of code into smaller, easier to understand, functions. Your logic will become really clear:

const migrate = async () => {
  console.log('Running migrations');
  const files = await getFilesList('./db/migrations');
  for (let i of files) {
    console.log(`Executing script: ${file}`);
    const data = await readFile(`./db/migrations/${file}`);
    await runQuery(data);
  }
};

function getFilesList(dir) {
  return new Promise((resolve, reject) => {
    fs.readdir(dir, (err, files) => {
      if (err) {
        console.log('Error while reading files in ' + dir);
        console.log(err);
        return reject(err);
      }
      resolve(files);
    });
  });
}

function readFile(file) {
  return new Promise((resolve, reject) => {
    fs.readFile(file, 'utf-8', (err, data) => {
      if (err) {
        console.log(`Error while reading script ${file}`);
        return reject(err);
      }
      resolve(data);
    });
  });
}

function runQuery() {
  return new Promise((resolve, reject) => {
    pool.query(data, (err, results) => {
      if (err) {
        console.log('Error while querying SQL');
        return reject(err);
      }
      resolve();
    });
  });
}

// Don't forget to close the pool, when you're done, or an error occurred:
migrate().catch(e => console.log(e)).finally(() => pool.end());

You could even go further into the simplification by using util.promisify, which converts callback functions into promise functions, so that your code remains consistent with promises:

const { promisify } = require("util");

const migrate = async() => {
  console.log('Running migrations');
  const files = await getFilesList('./db/migrations');
  for (let file of files) {
    console.log(`Executing script: ${file}`);
    const data = await readFile(`./db/migrations/${file}`);
    await runQuery(data);
  }
};

function getFilesList(dir) {
  return promisify(fs.readdir)(dir)
    .catch(err => {
      console.log('Error while reading files in ' + dir);
      throw err;
    });
}

function readFile(file) {
  return promisify(fs.readFile)(file, 'utf-8')
    .catch(err => {
      console.log(`Error while reading script ${file}`);
      throw err;
    });
}

function runQuery() {
  return promisify(pool.query.bind(pool))(data)
    .catch(err => {
      console.log('Error while querying SQL');
      throw err;
    });
}

// Don't forget to close the pool, when you're done, or an error occurred:
migrate().catch(e => console.log(e)).finally(() => pool.end());