Completely lost with promise async/await

Question

I'm trying to develop a relative simple test on NodeJS 11.6.x. I'm not really a developer, but sometimes try to do some coding.

My objective is to create a SQLite database and repeat some steps every time a run the code: 1. Drop a table if it exists 2. Create a table 3. Insert N lines 4. Count how many lines is in the database 5. Close the database

I tried first with a basic approach using callback, but couldn't figure out a way to make the step 3 (insert N lines) and looking for a solution, the promise async/await 'pattern' sounded the way to accomplish everything.

But, after refactoring the code, the step 1 (drop table) isn't running and I still not being able to execute step 3 (insert N lines) and have no idea what is happening. I also tried to use a promise package with no luck.

Could someone please have a look and help on this and if possible, explain and or give some advice?

Thanks in advance

Edited: Well, I'not used to post here at SO and don't know the 'right' way to update things here. I beleave I should had left the first code as reference, buy I don't have anymore. Now I think I'm almoust there. All steps are executing in order. Just the step 3 (insert N lines) that I'm not able to make it work. Or it inserts and stops not going to the next '.then' or it just insert 1 line and I'm cannot visualize what is happening. In the code, I commented in two lines with 'BUG 1:' and 'BUG 2:'.

If I both are commented, I get what is happening, it inserts only 1 line and don't continue the promise chain
If I comment BUG 1 and let BUG 2 active, it inserts just one line and continues. I think I understand why
If I comment BUG 2 and let BUG 1 active, it inserts all lines but don't continue and again, I think I understand why
If I uncomment both (the way I think should work. Don't work, and return an aditional error "Segmentation fault"

Bellow the code:

const sqlite3 = require('sqlite3')

let db = new sqlite3.Database('./test.db');

waitTime = 1

process.stdout.write('Starting...\n')
var test = new Promise((resolve, reject) => {
    process.stdout.write('Drop Table... ');
    db.run(`DROP TABLE IF EXISTS test`, (err) => {
        if (err) {
            process.stdout.write(`Dropping Error ${err.message}\n`)
            reject()
        } else {
            setTimeout(() => {
                process.stdout.write(`Dropped!\n`)
                resolve()
            }, waitTime)
        }
    })
})
test.then(() => {
    return new Promise((resolve, reject) => {
        process.stdout.write('Create Table... ')
        db.run(`CREATE TABLE IF NOT EXISTS test (data TEXT)`, (err) => {
            if (err) {
                process.stdout.write(`Creating Error ${err.message}\n`)
                reject()
            } else {
                setTimeout(() => {
                    process.stdout.write(`Created!\n`)
                    resolve()
                }, waitTime)
            }
        })
    })
}).then(() => {
    return new Promise((resolve, reject) => {
        process.stdout.write('Insert Line... ')
        lines = 10
        let loop = (async () => {
            for (let i = 0; i < lines; i++) {
                await new Promise(resolve =>
                    db.run(`INSERT INTO test (data) VALUES ('a')`, (err) => {
                        if (err) {
                            process.stdout.write(`Inserting Error ${err.message}\n`)
                            throw (err)
                        } else {
                            setTimeout(() => {
                                // process.stdout.write(`Line ${i} Inserted!\n`)
                                process.stdout.write(`, ${i+1}`)
                                resolve() // BUG 1: if this line is commented, comment it, it will insert only 1 line
                            }, waitTime)
                        }
                    })
                )
            }
        })()
        process.stdout.write(`, IDone\n`)
        resolve() // BUG 2: If this line is commented, the promise chain stops here
    })
}).then(() => {
    return new Promise((resolve, reject) => {
        process.stdout.write('Count Line(s)... ')
        db.all(`SELECT COUNT(*) AS totalLines FROM test`, [], (err, rows) => {
            if (err) {
                process.stdout.write(`Count Error ${err.message}\n`)
                reject()
            } else {
                setTimeout(() => {
                    process.stdout.write(` ${rows[0].totalLines} Count!\n`)
                    resolve()
                }, waitTime)
            }
        })
    })
}).then(() => {
    return new Promise((resolve, reject) => {
        process.stdout.write('Select Line(s)... ')
        db.all('SELECT data FROM test', [], (err, rows) => {
            if (err) {
                process.stdout.write(`Select Error ${err.message}\n`)
                reject()
            } else {
                rows.forEach((row) => {
                    console.log(row.data);
                })
                setTimeout(() => {
                    process.stdout.write(`${rows[0].totalLines} Select!\n`)
                    resolve()
                }, waitTime)
            }
        })
    })
}).then(() => {
    return new Promise((resolve, reject) => {
        process.stdout.write('Close DB... ')
        db.close((err) => {
            if (err) {
                process.stdout.write(`Closing Error ${err.message}\n`)
                reject()
            } else {
                setTimeout(() => {
                    process.stdout.write(`Closed!\n`)
                    resolve()
                }, waitTime)
            }
        })
    })
}).then(() => {
    console.log('Finished')
})

After the great explanation from @CertainPerformance (Thanks a lot), I was able to get it running. I believe it is now "the right" way to do it. May be there are some better ways, but for now, it is ok for me, bellow the final code:

const sqlite3 = require('sqlite3')

let db = new sqlite3.Database('./test.db');

lines = 10

process.stdout.write('Starting... ')
var test = new Promise((resolve, reject) => { process.stdout.write(`Promise Created...!\n`)
        resolve()
})
test.then(() => { process.stdout.write('Drop Table... ')
    return new Promise((resolve, reject) => {
        db.run(`DROP TABLE IF EXISTS test`, (err) => {
            if (err) {
                reject(err)
            } else { process.stdout.write(`Dropped!\n`)
                resolve() }
        })
    })
}).then(() => { process.stdout.write('Create Table... ')
    return new Promise((resolve, reject) => {
        db.run(`CREATE TABLE IF NOT EXISTS test (data TEXT)`, (err) => {
            if (err) {
                reject(err)
            } else {
                process.stdout.write(`Created!\n`)
                resolve() }
        })
    })
}).then(() => { process.stdout.write('Insert Line... ')
    let insertLoop = (async () => {
        for (let i = 0; i < lines; i++) {
            await new Promise(resolve =>
                db.run(`INSERT INTO test (data) VALUES ('a')`, (err) => {
                    if (err) {
                        reject(err)
                    } else { ( i == 0 ) ? process.stdout.write(`${i + 1}`) : process.stdout.write(`, ${i + 1}`)
                        resolve() }
                })
            )
        }
        process.stdout.write(`, Inserted!\n`)
    })()
    return insertLoop
}).then(() => { process.stdout.write('Count Line(s)... ')
    return new Promise((resolve, reject) => {
        db.all(`SELECT COUNT(*) AS totalLines FROM test`, [], (err, rows) => {
            if (err) {
                reject(err)
            } else { process.stdout.write(` ${rows[0].totalLines} Counted!\n`)
                resolve()
            }
        })
    })
}).then(() => { process.stdout.write('Close DB... ')
    return new Promise((resolve, reject) => {
        db.close((err) => {
            if (err) {
                reject(err)
            } else { process.stdout.write(`Closed!\n`)
                resolve()
            }
        })
    })
}).then(() => {
    console.log('Finished')
}).catch((err) => {
    process.stdout.write(`The process did not finish successfully: ${err}`)
})

I read that post and made some changes on the code and reposted here with one step one that still wrong and I'm not able to figure out why yet. Shouldn't the Promise "resolve" just after the db.run callback send the "resolve signal"? — Tuts, Feb 13 '19 at 17:53
The code looks just fine now, are you sure the output is as listed in the question? Because the `Dropped! (Callback)` runs before `resolve()`, there shouldn't be any way for the `Finished` to log *before* the `Dropped!` gets logged. — CertainPerformance, Feb 13 '19 at 20:42
Thanks for your attention. Yes, I did something wrong again and it was working... Now I rebuild again and updated with some improviments but still with error. — Tuts, Feb 13 '19 at 23:31

score 1 · Answer 1 · answered Feb 14 '19 at 00:18

There are two main issues. First, in the second .then, you declare loop as an async function that is immediately invoked: this means that loop will resolve to a Promise. The trimmed code looks like:

}).then(() => {
    return new Promise((resolve, reject) => {
        let loop = (async () => {
            // do some asynchronus stuff
        })()
        resolve() // BUG 2
    })
}).then(() => {

Declaring a Promise alone will not cause the current thread to wait for it. The above code doesn't work as expected for the same reason that this code prints after immediately:

console.log('start');
const prom = new Promise((resolve) => {
  setTimeout(resolve, 500);
});
console.log('after');

You must call .then on a Promise (or await the Promise) in order to schedule additional operations after the Promise completes. Or, if you're currently inside a .then, you can return the Promise, which will mean that the next .then will run as soon as the returned Promise resolves:

}).then(() => {
      let loop = (async () => {
        // do some asynchronus stuff
    })();
    return loop;
}).then(() => {
    // this block will run once `loop` resolves

Note the lack of a new Promise((resolve... constructor above - inside a .then, just returning the next Promise is often the preferred way to go, since it means a lot less code and avoids an antipattern.

The other issue with the current code is that errors will not be caught. For example, if your

db.run(`INSERT INTO test (data) VALUES ('a')`, (err) => {
  if (err) {
    process.stdout.write(`Inserting Error ${err.message}\n`)
    throw (err)
  // else call resolve()

throws an error, the Promise that's currently being awaited at that point will never resolve, nor will it reject - it will remain pending and unfulfilled forever. You should pass reject as the second argument to the Promise constructor, and call it when there's an error (instead of throw), for example:

await new Promise((resolve, reject) => {
  db.run(`INSERT INTO test (data) VALUES ('a')`, (err) => {
    if (err) {
      process.stdout.write(`Inserting Error ${err.message}\n`)
      reject(err)
    } else {
      // ...

This way, the awaited Promise will get rejected, which means that the whole loop will reject, and if the loop is returned, it'll allow a .catch to catch the error, for example:

var test = new Promise((resolve, reject) => {
  // ...
});
test.then(() => {
  return new Promise(...
    // ...
})
.then(() => {
  return new Promise(...
    // ..
})
.then(() => {
  return new Promise(...
    // ..
})
.catch((err) => {
  process.stdout.write(`The process did not finish successfully:`, err)
  // handle errors
});

Note that, unless each db. function call needs to execute serially, it would be better to make all the requests at once, and resolve once each request has finished - this can significantly reduce the time required for the script to run. Create an array of Promises for each asynchronous call, then call Promise.all on that array to get a Promise that resolves when all of those Promises are done (or, rejects as soon as one of those Promises rejects). For example, for the second .then:

}).then(() => {
  process.stdout.write('Insert Line... ')
  const proms = Array.from(
    { length: lines },
    (_, i) => new Promise((resolve, reject) => {
      db.run(`INSERT INTO test (data) VALUES ('a')`, (err) => {
        if (err) {
          process.stdout.write(`Inserting Error ${err.message}\n`)
          reject(err)
        } else {
          setTimeout(() => {
            // process.stdout.write(`Line ${i} Inserted!\n`)
            process.stdout.write(`, ${i+1}`)
            resolve()
          }, waitTime);
        }
      });
    })
  );
  return Promise.all(proms);
}).then(() => {

Nothing else in your code looks to deal with asynchronous loops, luckily.

You may also consider a utility function like Promisify which will turn callback-based functions to Promises without all the extra new Promise(... boilerplate every time there's an asynchronous call.

Thanks for you complete explanation in time expended to review and write everything. I was trying to learn and understand Promise and yours explanation was what I needed to get it. That is why I did it using the "long way" to write. I will continue learning and try some Promisify library to make it better. — Tuts, Feb 18 '19 at 14:49

score 0 · Answer 2 · answered Feb 18 '19 at 15:28

Further improvements can be made by promisifying db into reusable functions, and leveraging the full power of async/await instead of mixing it with then:

const sqlite3 = require('sqlite3')
let db = new sqlite3.Database('./test.db');

function runDbAsync(sql) {
    return new Promise((resolve, reject) => {
        db.run(sql, (err) => {
            if (err) reject(err);
            else resolve();
        });
    });
}
function getDbAsync(sql, val) {
    return new Promise((resolve, reject) => {
        db.all(`SELECT COUNT(*) AS totalLines FROM test`, [], (err, rows) => {
            if (err) reject(err);
            else resolve(rows);
        });
    });
}
function closeDbAsync() {
    return new Promise((resolve, reject) => {
        db.close((err) => {
            if (err) reject(err);
            else resolve();
        });
    });
}

function write(text) {
    process.stdout.write(text);
}
function writeLn(text) {
    write(text + "\n");
}

async function main() {
    const lines = 10
    writeLn('Starting... ')
    write('Drop Table... ');
    await runDbAsync(`DROP TABLE IF EXISTS test`);
    writeLn(`Dropped!`);
    write('Create Table... ');
    await runDbAsync(`CREATE TABLE IF NOT EXISTS test (data TEXT)`);
    writeLn(`Created!`);
    write('Insert Line... ');
    for (let i = 0; i < lines; i++) {
        await runDbAsync(`INSERT INTO test (data) VALUES ('a')`);
        write( i == 0 `${i + 1}` : `, ${i + 1}`);
    }
    writeLn(`, Inserted!`);
    write('Count Line(s)... ')
    const rows = getDbAsync(`SELECT COUNT(*) AS totalLines FROM test`, []);
    writeLn(` ${rows[0].totalLines} Counted!`)
    write('Close DB... ');
    await closeDbAsync();
    writeLn(`Closed!`);
}

main().then(() => {
    console.log('Finished')
}, err => {
    writeLn(`The process did not finish successfully: ${err}`)
});

This was something I was thinking on how to accomply too. Thanks for posting it. — Tuts, Feb 18 '19 at 15:49

Completely lost with promise async/await

2 Answers2