I replaced code that opened a new DB connection on every request with a connection pool
Opening a new DB connection per request took my site down with Too many connections. The mysql2 pool I switched to, and the createConnection calls I found still left.
#MySQL #Node #Backend #Refactoring
One day the whole site went down. When I looked at the logs, the DB was spitting out Too many connections . Connections had kept piling up without being closed until they went over the limit. The culprit was a pattern I had written myself, because I was creating a new DB connection every time a request came in. In this post I go over what I learned while replacing that callback style, which opened a new connection per request, with a connection pool, and also where I had written that I'd switched but actually hadn't. What was wrong with the old way In the old code, this pattern was repeated in every router. app.post('/something', (req, res) = { const connection = mysql.createConnection(db_info); connection.connect(); connection.query(sql, params, (err, results) = { if (err) { connection.end(); return res.status(500).send('error'); } res.json(results); connection.end(); }); }); There were several problems. First, since a new connection is made per request, every request pays the cost of opening and closing a TCP connection. On the MySQL side too, each incoming connection means authenticating and creating a session all over again. The bigger problem is connection leaks. If you forget end() in any error branch, connections pile up without being closed. That is exactly what killed the site above. I had left out end in one branch, so connections kept leaking, and the moment they passed MySQL's default limit of 151, every new request was rejected. The last one is callback hell. When queries are nested inside queries, the code keeps drifting to the right, and it gets harder and harder to tell where inside it end() should be called. The leaks ultimately came from this structure. Switching to a connection pool The fix was the connection pool in mysql2/promise. A pool creates a few connections up front and reuses them. When a request comes in it borrows one from the pool, and when it's done the connection is returned automatically. It doesn't connect from scratch every time. I created one shared pool in a single file and had every router share it. // db/pool.js const mysql = require('mysql2/promise'); const pool = mysql.createPool({ host: process.env.DB_HOST, user: process.env.DB_USER, password: process.env.DB_PASSWORD, database: process.env.DB_DATABASE, connectionLimit: 10, // reuses up to 10 connections waitForConnections: true, queueLimit: 0, }); module.exports = pool; With connectionLimit at 10, the number of connections open at once never goes above 10, however many requests there are. Thanks to waitForConnections, the 11th request doesn't get an error. It waits, and picks up a connection once an earlier one is returned. For the traffic of a personal service, even 10 is more than enough. The router code then gets this short. No connect, no end. It just borrows a connection and the connection gets returned for it. const pool = require('./pool'); app.post('/something', async (req, res) = { try { const [rows] = await pool.query(sql, params); res.json(rows); } catch (err) { res.status(500).send('error'); } }); Cleaning up callback hell with async/await too Once I moved to promises, the nested callbacks went away naturally. Even when I have to run several queries, with await the code reads straight from top to bottom. Only where I needed a transaction did I grab a single connection with pool.getConnection(), and in that case you have to call connection.release() in finally. Not giving back a connection borrowed from the pool is a leak too. The biggest thing is that there's no longer any end() to forget, so the worry about connection leaks disappeared entirely. The room for that mistake was removed by the structure itself. I haven't seen Too many connections since. I wasn't actually doing that Rereading this post, I saw that I had written that I now start every project with a pool already set up. I wondered if that was really true, so I counted in this blog's own code. This is as of September 2026. $ grep -c createConnection db/*.js util/*.js db/board.js createConnection 17 times db/visit.js createConnection 7 times db/code.js createConnection 6 times db/user.js createConnection 3 times db/oauth.js createConnection 1 time util/seo.js createPool 1 time - the only pool is here I had used a pool only in the SEO module I built later, while post lookups, the thing that gets called most often, were still opening a new connection on every request. Compared with what I counted when I first wrote this post, there are actually one or two more places. In the meantime I had added visitor stats and GitHub login, and copied the existing pattern as it was. It seems that what I wrote in the post was a resolution to do it from then on, not a statement that I was already doing it. Because traffic is low, it hasn't shown so far. board.js holds seventeen queries, so rather than changing it all at once, I plan to move the frequently called lookups to the pool first and have the rest follow. If you grep old code for createConnection, you find out quickly how far you really got, so if you have a project you believe you moved to a pool, I'd recommend actually counting.