How to use with dynamic databases?

[srchulo]

The documentation mentions to create the serverless mysql instance outside of your main handler.

However, what if you do not know the database that you want until the request? For example, connecting to a different database based on the client making the api request. Is there anyway to handle this?

[srchulo]

If a map was declared outside of the main handler that stored the created serverless-mysql objects, would that work?

const dbMap = new Map()

// Main handler function
exports.handler = async (event, context) => {
  const key = // something unique to request
  if (!dbMap.has(key)) {
    dbMap.set(createServerlessDb())
  }

  const db = dbMap.get(key)

  // use db...
}

[turiya-fps]

What if you do not know the database that you want until the request? For example, connecting to a different database based on the client making the api request. Is there anyway to handle this?

It is easier if you to do all the configuration and management of your global DB connection in one file. From there you can export initialise, get, and terminate functions that can be called from anywhere. Mine looks a bit like this

connection.js

const mysql = require('serverless-mysql')({
    library: require('mysql2'),
});

let globalConfig;

const initDb = async (dbConfig) => {
    globalConfig = dbConfig;
    mysql.config(dbConfig);
    await mysql.connect();
};

const getDb = async () => {
    if (mysql.getClient() == null) initDb(globalConfig);
    return mysql;
};

const quitDb = async () => {
    await getDb().then(async (db) => {
        await db.end();
        db.quit();
    });
};

module.exports = {
    initDb,
    getDb,
    quitDb,
};

I initialise the connection in my main handler when ready to configure

app.js

const { initDb, getDb, quitDb } = require('../../../db/connection');
const { config } = require('../../../config/config');

exports.createHandler = async (event, context) => {
    ...
    const dbConfig = config.db[process.env.NODE_ENV];
    await initDb(dbConfig);
    const db = await getDb();

    // do multiple queries
    const rows = await db.query(...);
    const rows2 = await db.query(...);

    // before returning a response, close the connection
    await quitDb();
    return {
          statusCode: 200,
          ...
    };
}

[Markkos89]

What if you do not know the database that you want until the request? For example, connecting to a different database based on the client making the api request. Is there anyway to handle this?

It is easier if you to do all the configuration and management of your global DB connection in one file. From there you can export initialise, get, and terminate functions that can be called from anywhere. Mine looks a bit like this

connection.js

const mysql = require('serverless-mysql')({
    library: require('mysql2'),
});

let globalConfig;

const initDb = async (dbConfig) => {
    globalConfig = dbConfig;
    mysql.config(dbConfig);
    await mysql.connect();
};

const getDb = async () => {
    if (mysql.getClient() == null) initDb(globalConfig);
    return mysql;
};

const quitDb = async () => {
    await getDb().then(async (db) => {
        await db.end();
        db.quit();
    });
};

module.exports = {
    initDb,
    getDb,
    quitDb,
};

I initialise the connection in my main handler when ready to configure

app.js

const { initDb, getDb, quitDb } = require('../../../db/connection');
const { config } = require('../../../config/config');

exports.createHandler = async (event, context) => {
    ...
    const dbConfig = config.db[process.env.NODE_ENV];
    await initDb(dbConfig);
    const db = await getDb();

    // do multiple queries
    const rows = await db.query(...);
    const rows2 = await db.query(...);

    // before returning a response, close the connection
    await quitDb();
    return {
          statusCode: 200,
          ...
    };
}

GREAT snippet, thanks!