I want to create an initialisation file for a simple browser game. I'm using Node.js v8.10.0 and MySQL Ver 14.14 Distrib 5.7.27. Actually what I want to do would be very straightforward with a synchronous language like Java but I would like to do everything in Node.js. I tried to understand promises and async/await for days but I have unexpected results I cannot find explanations for. Important precision: I want the program to finish by itself when everything is done.
The problem is already well-covered all around the web. I even use a middleware found on StackOverflow to use pool connections in with a "promisified" version of the mysql package: a cool middleware for pool connections with mysql original question. I explored many documentation pages on the subject and I thought I had understood: (official Node.js tuto around promises and async/await)[https://nodejs.dev/modern-asynchronous-javascript-with-async-and-await], MDN docu on promises, another nice explanation on async/await. However, I still don't understand my problem.
As a recall, here is the middleware for mysql I use with node.js (written by Matthias Hagemann). It works fine:
MySQL using pool and promises
const util = require('util');
var mysql = require('mysql');
var pool = mysql.createPool({
connectionLimit: 10, // nb of simultaneous active connections
host: "localhost",
user: "root",
password: "",
database: "aelkyr_db"
});
// Ping database to check for common exception errors.
pool.getConnection((err, connection) => {
if (err) {
if (err.code === 'PROTOCOL_CONNECTION_LOST') {
console.error('Database connection was closed.')
};
if (err.code === 'ER_CON_COUNT_ERROR') {
console.error('Database has too many connections.')
};
if (err.code === 'ECONNREFUSED') {
console.error('Database connection was refused.')
};
};
if (connection) connection.release()
return
});
// Promisify for Node.js async/await.
pool.query = util.promisify(pool.query);
module.exports = pool;
The simple original code
Then, my code. At the beginning, there is only a simple code to fill-in a MySQL table with many rows. Note: The table was previously created by hand in the terminal:
var pool = require('./db_pool_connections.js');
const sizeSquareMap = 31;
const gameboardTableName = "gameboard_test_2";
for (let r = 0; r < sizeSquareMap; r++) {
for (let c = 0; c < sizeSquareMap; c++) {
sql = `insert into ${gameboardTableName}
(r, c) values (${r}, ${c});`;
pool.query(sql, function (err, result) {
if (err) {
throw err
};
});
};
};
console.log("ok 2");
};
Adding multiple queries with pool without promises
Real problems begin when I realised I would like to generate the full table from the node.js file directly. Hence, I wrote this:
var pool = require('./db_pool_connections.js');
const sizeSquareMap = 31;
const generateMap = true;
if (generateMap) {
// generate the new table
const gameboardTableName = "gameboard_test_2";
pool.query(`create table aelkyr_db.${gameboardTableName} (
gameboxId int(11) not null primary key auto_increment,
r int not null default 0,
c int not null default 0,
biome varchar(200) default '',
players varchar(2000) default '',
objects varchar(6000) default ''
);`, function (err, result) {
if (err) {
throw err;
}
;
});
// populate the table with empty gameboxes
let sql;
for (let r = 0; r < sizeSquareMap; r++) {
for (let c = 0; c < sizeSquareMap; c++) {
sql = `insert into ${gameboardTableName} (r, c) values (${r}, ${c});`;
pool.query(sql, function (err, result) {
if (err) {
throw err
};
// console.log(result);
});
};
};
//process.exit(1); //error : Error: Pool is closed.
//pool.end();
};
This code works find but never end, from what I can understand, due to the pool connection that never get closed. So I tried to use pool.end(); or process.exit(1); in order to close the program when the execution is done; but I get the same error: error : Error: Pool is closed.
Trying to use promises with async/await
This seems to say that the closing code pool.end(); runs before the for loop, because Node.js (JavaScript) is asynchronous by nature. To face the problem, I dived into promises and sync/await with the hope I could transform my code into something that behave like synchronous code. I tried 2 versions of a code: VERSION 1 (not working)
//V1: I want to use await with all the async functions to make the code synchronous.
var pool = require('./db_pool_connections.js');
// set up the generation code
const generateMap = true; // [OK] properly working
const generateRuins = false; //
// [generateMap]
async function populateGameboard(sizeSquareMap, gameboardTableName) {
for (let r = 0; r < sizeSquareMap; r++) {
for (let c = 0; c < sizeSquareMap; c++) {
sql = `insert into ${gameboardTableName} (r, c) values (${r}, ${c});`;
pool.query(sql, function (err, result) {
if (err) {
throw err
};
});
};
};
console.log("ok 2");
};
const generateNewMap = async function (gameboardTableName, sizeSquareMap) {
const qry = await pool.query(`create table aelkyr_db.${gameboardTableName} (
gameboxId int(11) not null primary key auto_increment,
r int not null default 0,
c int not null default 0,
biome varchar(200) default '',
players varchar(2000) default '',
objects varchar(6000) default ''
);`, function (err, result) {
if (err) {
throw err;
}
;
});
console.log("ok 1", qry);
await populateGameboard(sizeSquareMap, gameboardTableName);
console.log("ok 3");
};
generateNewMap("gameboard_test_2", 31 )
.then(console.log("ok 4"))
.catch(error =>{
console.log(error)
});
And VERSION 2 (working but never ending. )
//V2: Only a small difference, I remove the 2 "await"
var pool = require('./db_pool_connections.js');
// set up the generation code
const generateMap = true;
// [generateMap]
async function populateGameboard(sizeSquareMap, gameboardTableName) {
for (let r = 0; r < sizeSquareMap; r++) {
for (let c = 0; c < sizeSquareMap; c++) {
sql = `insert into ${gameboardTableName} (r, c) values (${r}, ${c});`;
pool.query(sql, function (err, result) {
if (err) {
throw err
};
});
};
};
console.log("ok 2");
};
const generateNewMap = async function (gameboardTableName, sizeSquareMap) {
const qry = pool.query(`create table aelkyr_db.${gameboardTableName} (
gameboxId int(11) not null primary key auto_increment,
r int not null default 0,
c int not null default 0,
biome varchar(200) default '',
players varchar(2000) default '',
objects varchar(6000) default ''
);`, function (err, result) {
if (err) {
throw err;
}
;
});
console.log("ok 1", qry);
populateGameboard(sizeSquareMap, gameboardTableName);
console.log("ok 3");
};
generateNewMap("gameboard_test_2", 31 )
.then(console.log("ok 4"))
.catch(error =>{
console.log(error)
});
The output of VERSION 1 in my terminal:
ʘ node db_gameboard_init.js
ok 4
^C
Note, I see that the .then() code execute directly when the generateNewMap() function is called. And the output of the version 2 in my terminal:
ʘ node db_gameboard_init.js
ok 1 Promise { <pending> }
ok 2
ok 3
ok 4
^C
Hence, I would really like the answers to the following questions:
1) V2 seems to produce a good result, but adding ".then(pool.end())" after ".then(console.log("ok 4"))" gives the error :
ʘ node db_gameboard_init.js
ok 1 Promise { <pending> }
ok 2
ok 3
ok 4
/*****/db_gameboard_init.js:39
throw err;
^
Error: Pool is closed.
Why? If all the code already ran, why such an error? There is no need for another connection? 2) Why is .then() with generateNewMap() being called first, making the code never-ending? 3) How to properly end the execution of the program synchronously. 4) Considering the output of the V2 program, what is the "Promise { }" means?
