serverless-mysql: keeping a serverless function from exhausting the connection pool

The connection exhaustion problem, stated precisely

The README describes the problem in terms you can check against your own logs. Serverless functions, like AWS Lambda, Google Cloud Functions and Azure Functions, scale almost infinitely by creating separate instances for each concurrent user. The README calls that a major problem for RDBMS solutions like MySQL, because available connections can be quickly maxed out by competing functions.

This is the specific failure. A database has a hard connection limit, a function platform has no reason to know about it, and a burst of traffic produces one instance per concurrent request. Each instance opens its own connection, holds it across the invocation, and the server refuses new connections long before the function platform notices anything unusual. The symptom is an error that looks like a database outage and is actually a connection accounting problem.

The fix the module proposes is a connection management component added to the mysql2 module, designed specifically for serverless applications. The module constantly monitors the number of connections being utilized and then, based on your settings, manages those connections to allow thousands of concurrent executions to share them. It will clean up zombies, enforce connection limits per user, and retry connections using trusted backoff algorithms.

Two more things come with it. It adds modern async and await support to the mysql2 module, eliminating callback hell or the need to wrap calls in promises, and it dramatically simplifies transactions, giving a simple and consistent pattern to handle common workflows.

The scope note is worth keeping in view. The module should work with any standards-based MySQL server, and it has been tested with AWS's RDS MySQL, Aurora MySQL and Aurora Serverless.

Initialisation happens outside the handler

The single most important rule in the README is about placement. To use Serverless MySQL, require it outside of your main function handler, and this will allow for connection reuse between executions. The module must be initialized before its methods are available, and configuration options must be passed in during initialization.

The example is short enough to be the whole mental model:

// Require and initialize outside of your main handler
const mysql = require('serverless-mysql')({
  config: {
    host     : process.env.ENDPOINT,
    database : process.env.DATABASE,
    user     : process.env.USERNAME,
    password : process.env.PASSWORD
  }
})

// Main handler function
exports.handler = async (event, context) => {
  // Run your query
  let results = await mysql.query('SELECT * FROM table')

  // Run clean up function
  await mysql.end()

  // Return the results
  return results
}

The module can also be required with no arguments at all, in which case the connection options are supplied later through the config method, which is linked to the same set of connection options that the underlying driver supports:

mysql.config({
  host     : process.env.ENDPOINT,
  database : process.env.DATABASE,
  user     : process.env.USERNAME,
  password : process.env.PASSWORD
})

There is a connect method as well, and the README is explicit that it is optional. You can explicitly establish a connection using the connect method if you want to, though it isn't necessary, and the method returns a promise, so you will need to await the response or wrap it in a promise chain.

The options can also include the backoff configuration, which the README shows as a second initialisation form:

const mysql = require('serverless-mysql')({
  backoff: 'decorrelated',
  base: 5,
  cap: 200
})

One inconsistency in the documentation is worth noting. The introduction describes the module as a wrapper for Doug Wilson's mysql2 module, while the section on how to use the module says it wraps the mysql module and links the mysqljs/mysql documentation for the connection options and the query options. The dependency on mysql2 is the one the introduction states, and the query method is described as accepting the same arguments as mysql2 does except the callback.

end() is the part that makes the sharing work

The second half of the example is the one people skip, and it is the part the module exists to make possible.

Once you have run all your queries and your serverless function is ready to return data, call the end method to perform connection management. In the example it is awaited right before the return, and the README describes it as running clean up function in the comment.

The mechanism behind it is in the considerations list, which is a short list of the design decisions. The module monitors active connections and disconnects if more than X% of connections are being used. It supports transactions, and it supports JIT connections, which is the term for opening a connection only when one is needed rather than keeping one warm. It uses exponential backoff, using the algorithm described in the AWS architecture blog post on exponential backoff and jitter, to handle failed connections. And it assumes AWS endorsed best practices, linking a sample Lambda resolver from the aws-samples repository.

The combination is what makes the sharing safe. A connection that is opened and then abandoned when the function instance is frozen or reclaimed is a zombie, and zombies are what exhaust a connection limit. The module monitors usage, disconnects when a configured share of the pool is busy, and cleans up on end so that a returning invocation finds a healthy pool rather than a leaked one.

That is also why the end call is awaited rather than fired and forgotten. If the cleanup does not complete before the runtime suspends the instance, the connection is exactly the zombie the module is trying to prevent.

returnFinalSqlQuery puts the substituted query on the error

One option in the README is worth its own look, because it is a debugging aid with a specific shape.

You can enable logging of the final SQL query, with all parameter values substituted, by setting the returnFinalSqlQuery option to true. When it is enabled, the query is attached to the result object:

const results = await mysql.query('SELECT * FROM users WHERE id = ?', [userId])

// Access the SQL query with substituted values
console.log('Executed query:', results.sql)

And the same option attaches the final query to error objects when a query fails, which is the more useful half:

try {
  const results = await mysql.query('SELECT * FROM nonexistent_table')
} catch (error) {
  // The error object will have the SQL property
  console.error('Failed query:', error.sql)
  console.error('Error message:', error.message)
}

The reason this matters in a serverless context is that a parameterised query is unreadable once it fails. The error says a table does not exist and the statement says SELECT * FROM users WHERE id = ?, and the value that broke it is somewhere in the event. With the option on, the error carries the query with the value already in it.

The trade-off is obvious and the README does not hide it. The logged query contains the parameter values, so it contains the data. Turning this on in a production function means query text and data in your logs, and it has to be a deliberate choice rather than a default.

The test database is configured for a thousand connections

The docker-compose file in the repository is the clearest statement of the environment this module expects, and the numbers in it are the point.

services:
  mysql:
    image: mysql:lts
    container_name: serverless-mysql-test-db
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: serverless_mysql_test
      MYSQL_USER: testuser
      MYSQL_PASSWORD: testpassword
      # Allow connections from any host
      MYSQL_ROOT_HOST: "%"
      TZ: UTC
    ports:
      - "3306:3306"
    command: >
      --max_connections=1000
      --wait_timeout=28800

The server is started with --max_connections=1000 and --wait_timeout=28800, which is eight hours in seconds. That wait timeout is the setting that makes connection reuse possible at all, because a connection that the server closes after a short idle period cannot be shared by an invocation that returns minutes later, and the module is built on the assumption that a pooled connection will be reused across invocations.

The root host is set to a wildcard so that connections can arrive from the host rather than only from inside the container network, and the healthcheck uses mysqladmin ping with a five second timeout and ten retries. The timezone is UTC, and the mocha test scripts in package.json set TZ=UTC, so the tests do not depend on the machine's local zone.

The test scripts are the other half of the story. There is a unit script and an integration script, both running mocha with --check-leaks over the spec files, and there are docker variants that call scripts/run-integration-tests.sh and scripts/run-all-tests.sh. The publish gate is prepublishOnly, which runs npm run lint and then npm run test:docker, so nothing reaches the registry without the containerised suite. A benchmark directory with a run.sh script and an update-readme.js shows how the numbers in the README are produced, and the benchmark:readme script regenerates them.

Where a connection manager is the wrong tool

The limits here are specific, and the module itself names some of them.

The first is that streaming rows is not supported. The README says there are a few things that don't make sense in serverless environments, like streaming rows, so there is no support for that yet. If your query returns a result set too large to materialise, this module is not the answer,.

The second is the shape of the workload. This module exists because a function platform creates an instance per concurrent request. If you are running a long lived Node server with a fixed number of processes, you already have a pool, and a pool sized to your process count is a simpler and better understood mechanism than a module that assumes invocations come and go.

The third is that the alternative exists at the platform level. The README lists Aurora Serverless among the servers the module has been tested against,. Where you can put a pooler or a serverless database tier between the function and MySQL, the connection problem is solved once, for every function, instead of per function instance.

The fourth is operational rather than technical. The README says the module should work with any standards-based MySQL server, and the module assumes you will set your server's max_connections and wait_timeout to match the reuse pattern. If your database administrator has set a short wait timeout for a reason, or your provider caps connections on the instance, the sharing will not behave as the module expects, and the symptom will be intermittent connection errors under load rather than an obvious failure.

Editorial conclusion

Adopt serverless-mysql if you run functions against a MySQL instance with a low max_connections and cannot put a proxy in front of it, and if you are willing to call end() at the end of every invocation. Do not adopt it for a long lived server, where a normal pool is the right shape, or for streamed row reads, which the module does not support. Verify first that your database's max_connections and wait_timeout are set for the reuse pattern you are relying on, that you initialise the module outside the handler so the connection is shared, and that the backoff settings you choose match how your provider suspends and resumes functions.

Frequently asked questions

Official sources

1. Issues
2. jeremydaly/serverless-mysql on GitHub
3. License: MIT
4. README
5. Releases