I'm using the changeUser method in the mysql2 npm package to connect our API to different databases in our database instance:
const pool = createPool({
namedPlaceholders: true,
...dbConfig,
});
async function getConnection(database) {
const connection = await poolMain.getConnection();
await connection.changeUser({ database });
return connection;
}
When I pass an invalid database the connection fails as intended.
But after one failed attempt all following attempts of getConnection fail as well, even when a valid database is passed.
The only solution that I found was to recreate the pool in case of err.code === "ER_BAD_DB_ERROR":
let pool = createPool({
namedPlaceholders: true,
...dbConfig,
});
async function getConnection(database) {
try {
if (database === "betenergie_db") {
return await poolBet.getConnection();
} else {
const connection = await pool.getConnection();
connection.changeUser({ database });
return connection;
}
} catch (err) {
if (err.code === "ER_BAD_DB_ERROR") {
pool = createPool({
namedPlaceholders: true,
...dbConfig,
})
}
throw err;
}
}
Is a more elegant way of recovering the pool without reinstantiating it?