Using SSL to connect to MySQL database in Node.js

; Date: Wed Mar 07 2018

Tags: Node.JS »»»» MySQL

Encrypting database connections can be extremely important for security. The documentation for the Node.js MySQL driver briefly mentions SSL support, and does not give adequate documentation. What follows is an example showing how to connect using PEM certificates to a MySQL server that was configured with a self-signed root CA.

For a complete discussion of setting up MySQL with SSL connection support, see: Connect with SSL to MySQL in Docker container

In that tutorial I set up three PEM certificates using the openssl tool. Unfortunately the SSL documentation at https://www.npmjs.com/package/mysql#ssl-options is completely inadequate. The sole example is:

var connection = mysql.createConnection({
  host : 'localhost',
  ssl  : {
    ca : fs.readFileSync(__dirname + '/mysql-ca.crt')
  }
});

This is a start, but it doesn't show what to do with the three certificates generated in the tutorial linked above. But in the MySQL issue queue, I found one giving this example (which I've modified a bit)

const fs = require('fs');
const mysql = require('mysql');

var connection = mysql.createConnection({
    host: '127.0.0.1',
    port: '3306',
    user: 'root',
    password: 'passw0rd',
    database: 'test',
    ssl: {
        ca: fs.readFileSync(__dirname + '/certs/ca.pem'),
        key: fs.readFileSync(__dirname + '/certs/client-key.pem'),
        cert: fs.readFileSync(__dirname + '/certs/client-cert.pem')
    }
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
    if (error) throw error;
    console.log('The solution is: ', results[0].solution);
});
   
connection.end();

Of course this can be improved by using an async function and the await keywords. For more on that see Node.js Script writers: Top-level async/await now available

This works perfectly:

$ node ./test.js 
The solution is:  2

Reading between the lines, what's probably happening is the ssl object is being passed to tls.createServer using an options object like this: https://nodejs.org/dist/latest-v8.x/docs/api/tls.html#tls_tls_createserver_options_secureconnectionlistener

Back on the Node.js mysql driver documentation, it links to: https://nodejs.org/api/crypto.html#crypto_crypto_createcredentials_details That documentation says it is deprecated and we are to instead use tls.createSecureContext https://nodejs.org/api/tls.html#tls_tls_createsecurecontext_options

If you wish to use the mysql2 driver, see: https://github.com/sidorares/node-mysql2/blob/master/documentation/Examples.md The connection object appears to have the same ssl object.