Best Practice Pool config

[mdodge-ecgrow]

Is there a best practice Pool config out there somewhere for this library? Everytime I do any searching, they always say "use what works best for your situation". But I just want it to work! We've been fighting with this for several months now and it mostly works. I can't tell you how many iterations of the pool config or opening/closing/releasing/destroying connections we have tried.

But after a month or two go by, it seems like the stale/idle connections build up and eventually calls start silently failing and no one can figure out why things aren't working. Then I go and kill all the active database connections (on Digital Ocean). And magically everything just starts working again.

Here is what I currently have for our pool config:

import mysql2 from 'mysql2/promise';
import 'dotenv/config';
import * as fs from 'fs';

const pool = mysql2.createPool({
	host: process.env.MYSQL_HOST,
	port: Number(process.env.MYSQL_PORT),
	user: process.env.MYSQL_USER,
	password: process.env.MYSQL_PASS,
	database: process.env.MYSQL_QUOTES_DB,
	debug: false,
	connectionLimit: 40,
	waitForConnections: true,
	maxIdle: 40, // max idle connections, the default value is the same as `connectionLimit`
	idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
	queueLimit: 0,
	enableKeepAlive: true,
	keepAliveInitialDelay: 0,
	ssl: {
		// key: fs.readFileSync('./certs/client-key.pem'),
		// cert: fs.readFileSync('./certs/client-cert.pem')
		ca: fs.readFileSync(process.env.CERT_FILE_PATH || '')
	},
	typeCast: function castField(field, useDefaultTypeCasting) {
		// We only want to cast bit fields that have a single-bit in them. If the field
		// has more than one bit, then we cannot assume it is supposed to be a Boolean.
		if (field.type === 'BIT' && field.length === 1) {
			const bytes = field.buffer();
			// A Buffer in Node represents a collection of 8-bit unsigned integers.
			// Therefore, our single "bit field" comes back as the bits '0000 0001',
			// which is equivalent to the number 1.
			return bytes ? bytes[0] === 1 : false;
		}
		return useDefaultTypeCasting();
	}
});

export default pool;

// Singleton pattern
// let pool: mysql2.Pool;
// const getPool = () => {
// 	if (pool) return pool;
// 	pool = mysql2.createPool({
// 		host: process.env.MYSQL_HOST,
// 		port: Number(process.env.MYSQL_PORT),
// 		user: process.env.MYSQL_USER,
// 		password: process.env.MYSQL_PASS,
// 		database: process.env.MYSQL_QUOTES_DB,
// 		connectionLimit: 40,
// 		waitForConnections: true,
// 		//maxIdle: 40, // max idle connections, the default value is the same as `connectionLimit`
// 		idleTimeout: 60000, // idle connections timeout, in milliseconds, the default value 60000
// 		queueLimit: 0,
// 		enableKeepAlive: false,
// 		keepAliveInitialDelay: 0,
// 		ssl: {
// 			// key: fs.readFileSync('./certs/client-key.pem'),
// 			// cert: fs.readFileSync('./certs/client-cert.pem')
// 			ca: fs.readFileSync(process.env.CERT_FILE_PATH || '')
// 		},
// 		typeCast: function castField(field, useDefaultTypeCasting) {
// 			// We only want to cast bit fields that have a single-bit in them. If the field
// 			// has more than one bit, then we cannot assume it is supposed to be a Boolean.
// 			if (field.type === 'BIT' && field.length === 1) {
// 				const bytes = field.buffer();
// 				// A Buffer in Node represents a collection of 8-bit unsigned integers.
// 				// Therefore, our single "bit field" comes back as the bits '0000 0001',
// 				// which is equivalent to the number 1.
// 				return bytes ? bytes[0] === 1 : false;
// 			}
// 			return useDefaultTypeCasting();
// 		}
// 	});
// 	return pool;
// };
//
// export default getPool();

I left the comments in so you can see what we've tried.
Like I said earlier we are using Digital Ocean with a managed MySQL DB.

And this is how we use it in the app:

export const executeQuery = async (
	query: string,
	multipleResults: boolean,
	params: (string | null)[] | []
) => {
	let connection;

	// if (!connection) {
	// const connection = await pool.getConnection();
	// } else {
	// }
	let rows: any;//, fields, err;

	try {
		connection = await pool.getConnection();
		if (!params.length) {
			[rows] = await connection.query(query);
		} else {
			[rows] = await connection.query(query, params);
		}
	} catch (e) {
		// console.error('Query Failed:', e);
		throw e;
	} finally {
		if (connection) {
			// console.log('Releasing connection');
			if (connection && (connection as any).connection && !(connection as any).connection._closing) {
				try {
					connection.release();
				} catch (releaseErr) {
					console.warn('Failed to release connection:', releaseErr);
				}
			} else {
				console.warn('Skipping release: connection was already closing or closed');
			}
		}
	}

	if (!multipleResults) {
		return Array.isArray(rows) ? rows[0] : rows;
	} else {
		return rows;
	}
};

[mdodge-ecgrow]

Once again, I just had a user complain the app was spinning and spinning when he tried to do a copy action. I closed all the open connections in Digital Ocean and it magically started working! Would love some help on this one because this is not sustainable.

[mdodge-ecgrow]

Just had this issue bite us in the butt again today!! Some queries work working and some were not and I could not figure out why. Eventually I saw something about a "lock" error and that instantly made me remember to go out to Digital Ocean and kill all the open connections and once again, everything is working!