I am currently collaborating on a project with a partner. The backbone of our project is node js, and we are using an AWS RDS MySQL DB for our database. Our main challenge lies in establishing effective communication with the DB through SSH within NodeJS. Below, you will find the setup we have put in place after extensive research over the past few days. The code snippet showcases the configuration for our SSH Tunnel, the DB setup, and the outcome following the execution of our API call. Any assistance or guidance on this matter would be greatly appreciated!
Setting up the SSH Tunnel
const { createTunnel } = require ('tunnel-ssh');
const fs = require('fs');
const path = require('path');
require('dotenv').config();
async function createSSHTunnel(srcAddr = process.env.SSH_HOST, srcPort = process.env.SSH_PORT) {
// SSH tunnel options
const tunnelOptions = {
autoClose: true,
};
const serverOptions = {
port: srcPort,
};
// SSH connection options
const sshOptions = {
host: process.env.SSH_HOST,
port: parseInt(process.env.SSH_PORT),
username: process.env.SSH_User,
privateKey: fs.readFileSync(path.join(__dirname, 'JD KeyPair.pem')),
};
// Port forwarding options
const forwardOptions = {
srcAddr: srcAddr,
srcPort: srcPort,
dstAddr: process.env.DB_Host,
dstPort: parseInt(process.env.DB_Port),
};
try {
await createTunnel(
tunnelOptions,
serverOptions,
sshOptions,
forwardOptions
);
} catch (error) {
if (error.code === "EADDRINUSE") {
// Reuse existing SSH tunnel connection
console.log(`Returning existing SSH tunnel on ${srcAddr}:${srcPort}.`);
return { srcAddr, srcPort };
} else {
throw error;
}
}
console.log(`SSH tunnel successfully created on ${srcAddr}:${srcPort}.`);
return { srcAddr, srcPort };
}
module.exports = { createSSHTunnel };
Configuring the Database
const { createPool } = require('mysql2/promise');
const { createSSHTunnel } = require ('./sshTunnel');
require('dotenv').config();
// Fetch existing SSH tunnel connection details
const { srcAddr, srcPort } = createSSHTunnel();
// Initialize database connection pool
const db = createPool({
host: process.env.DB_Host,
port: process.env.DB_Port,
user: process.env.DB_User,
password: process.env.DB_Pass,
database: process.env.DB_Data,
waitForConnections: true,
connectionLimit: 10,
queueLimit: 0,
});
module.exports = db;
An Example API Call
router.post('/retrieveCardInfo', async (req, res) => {
const cardID = req.body.CardID.CardID;
try {
// Query to retrieve card information
const uploadCardStatus = await db.query('SELECT * FROM cardstorage WHERE cardID = ?', [cardID]);
// Return card info if found
if (typeof uploadCardStatus[0][0] !== 'undefined') {
return res.send(uploadCardStatus[0][0]);
}
// Send error message if card not found
return res.json({errorMessage: "Card Not Found"});
}
catch (err) {
// Handle any server errors
return res.json({ errorMessage: 'A Server Error Occurred!' });
}
});
Outcome of API Execution
code: 'ETIMEDOUT',
errno: undefined,
sql: undefined,
sqlState: undefined,
sqlMessage: undefined