Using SSL to connect to MySQL database in Node.js

; Date: March 7, 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 (www.npmjs.com) 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();

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: (nodejs.org) 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: (nodejs.org) https://nodejs.org/api/crypto.html#crypto_crypto_createcredentials_details That documentation says it is deprecated and we are to instead use tls.createSecureContext (nodejs.org) https://nodejs.org/api/tls.html#tls_tls_createsecurecontext_options

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