How to connect to a MySQL database server over SSL with Node.js

In this post I show how to connect to MySQL database server with Node.js over SSL. Specifically, I show how to connect to a Google Cloud Platform SQL database.

This post assumes that you have a running MySQL database. Here’s a guide on how to setup a GCP MySQL database.

Once you have a running MySQL database you’ll need to generate SSL certificates. In the GCP web dashboard, navigate to your SQL database and then find the settings for generating SSL certificates. If you’re setting up a server from scratch here’s a guide on how to generate SSL certificates.

Essentially, you need to create three certificate files:

• client-cert.pem: This is the public key which is used to encrypt communication.
• client-key.pem: This is the private key which is used to decrypt data encrypted by the public key. So, it is super sensitive and should not be stored in any version control system.
• server-ca.pem: This is the SSL server certificate used to sign and verify the identify of a public key. This is needed to ensure that you’re not encrypting your data with a public key created by a bad actor, who then has a private key they can use to decrypt the communication.

For more info about the keys, please see this StackOverflow answer.

Testing the keys #

Before using Node.js you can use the command line tool mysql in the terminal to try and connect to the database. The benefit to this is that you avoid having to deal with potentially buggy personal code, and you can verify that you’re able to connect to the database using the keys.

# Example of how you can call mysql to connect to some database on the
# fictitious IP 123.123.123.123. I use '\' to add new-lines.
mysql -uroot -p -h 123.123.123.123 \
    --ssl-ca=server-ca.pem \
    --ssl-cert=client-cert.pem \
    --ssl-key=client-key.pem

So, you verified that the keys work? Great!

Writing some Node.js #

This is where it can get a little complicated. If you look inside the key files you’ll see that they have a very specific formatting using new lines.

-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8
gpgBZ4PybJCQooCBL8Aiit72utEn3UE
T5gnEujDrnHBx3On6OzQaHjeysjMxwA
si3YhDHZAbqdGmtuJKD1B/fbFMCWa4b
FZrVJylEpahWpS0IgPSKuHYpEQhReFV
IToC0KJTYGW+rW3yHcuOYVnxjiFO7cC
-----END PUBLIC KEY-----

This creates a challenge for us on how to access this key file data inside Node.js. I don’t want to store the keys files in git, nor do I want to have to manually upload them to a server. Actually, we want to provide the key file data as environment variables accessible by the running application (see 12factor.net/config).

You can put these variables into a .env file in your project and use a package called dotenv to load them into your project.

Never commit the .env file to version control.

Once you have the variables ready in an .env file you can create a database.js file where you test out the keys.

The database.js file #

require('dotenv').config()
const mysql = require('mysql')
// The url package is a built-in Node.js package.
const { URL } = require('url')

const getDatabaseConnectionConfig = () => {
  // Parse a database url, and destructure the result.
  // The database url should be similar to this:
  // mysql://root:somepassword@127.0.0.1:3306/database-name
  const {
    username: user,
    password,
    port,
    hostname: host,
    pathname = ''
  } = new URL(process.env.DATABASE_URL)
  // Prepare connection configuration for mysql.
  return {
    user,
    password,
    host,
    port,
    database: pathname.replace('/', '')
  }
}

const getSSLConfiguration = () => {
  if (!process.env.SSL_CA || !process.env.SSL_CERT || !process.env.SSL_KEY) {
    return {}
  }
  return {
    ssl: {
      // This is an important step into making the keys work. When loaded into
      // the environment the \n characters will not be actual new-line characters.
      // so the .replace() calls fixes that.
      ca: process.env.SSL_CA.replace(/\\n/g, '\n'),
      cert: process.env.SSL_CERT.replace(/\\n/g, '\n'),
      key: process.env.SSL_KEY.replace(/\\n/g, '\n')
    }
  }
}

const pool = mysql.createPool({
  ...getDatabaseConnectionConfig(),
  ...getSSLConfiguration()
})

module.exports = pool

// For testing: This will execute only if this file is called directly like
// `node database.js`. It won't execute when this file is required as a module
if (require.main === module) {
  pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results[0].solution);
    // Close the pool so that the script will finish quicker.
    pool.end()
  });
}

The .env file #

DATABASE_URL=
SSL_CA=
SSL_CERT=
SSL_KEY=

Once you have created a database.js file and a .env file you can then call node database.js. This will then try to make a connection to the database and run an SQL query that simply sums two numbers. If that works with no error we’ve verified that we have a successful SSL connection.

Et voila! 🎉