How can I retrieve database credentials from AWS parameter store in Node.js / Express / MySQL

Question

Trying to get credentials from AWS parameter store, im not sure if I can do something similar to below or if I need to retrieve them into a dotenv file first, im not familiar with async/await.

I get the following error when executeSQL() is called

TypeError: pool.query is not a function

require('dotenv').config()
const AWS = require('aws-sdk');
const ssm = new AWS.SSM({
    region: 'us-east-1',
  });
const mysql = require('mysql');

let pool = async () => await dbConnection();


async function key(param) {
    const parameter = await ssm.getParameter({
          Name: param,
          WithDecryption: true
        })
        .promise()
        .catch((err) => {
            console.error('Failed getting parameter');
            console.error(err);
        });
    const data = parameter.Parameter.Value;
    console.log(data);
    return data;
}


async function dbConnection() {  
    var pw = await key('pw-d');
    const pool = mysql.createPool({
      connectionLimit: 10,
      host: "xxxxxxxx",
      user: "xxxxxxxxxxx",
      password: pw,
      database: "xxxxxxxxxxxx"
    });
    return pool;
};

async function executeSQL(sql, params) {
  return new Promise(function (resolve, reject) {
    pool.query(sql, params, function (err, rows, fields) {
      if (err) throw err;
      resolve(rows);
    });
  });
}

module.exports = { executeSQL };

Answer 1

I'd suggest creating a single promise to represent the retrieval of SSM params and exporting a function to return the connection pool.

// connection.js for example
const AWS = require("aws-sdk");
const ssm = new AWS.SSM({ region: "us-east-1" });
const mysql = require("mysql");

const key = async (Name) => {
  try {
    const {
      Parameter: { Value },
    } = await ssm.getParameter({ Name, WithDecrytpion: true }).promise();
    return Value;
  } catch (err) {
    console.error("Failed getting parameter", err);
    throw err; // make sure the promise fails
  }
};

// Store a promise that resolves with the connection pool
const poolPromise = key("pw-d").then((password) =>
  mysql.createPool({
    connectionLimit: 10,
    host: "xxxx",
    user: "xxxx",
    password,
    database: "xxxx",
  })
);

// Export a function that returns the same promise every time
const getPool = () => poolPromise;

module.exports = { getPool };

Now wherever you need the connection pool, you'd use this

const { getPool } = require("../connection");

const executeSQL = async (sql, params) => {
  // get the connection pool
  const pool = await getPool();

  // promise-ify pool.query()
  return new Promise((resolve, reject) => {
    pool.query(sql, params, (err, rows, fields) => {
      if (err) {
        return reject(err);
      }
      resolve(rows);
    });
  });
};

Storing a single promise means you won't be retrieving SSM params and creating a new connection pool every time you call getPool().

---

An alternative I see a lot is to populate a .env file (or set environment variables in a container) as part of your build / deploy process using the AWS CLI. That way your app can run with everything it needs to know up front.