How to connect to MySQL / MariaDB using Node.JS (the right way)

Use a connection pool. It helps

  • Conserve resource, connections got recycled
  • Better reliability: it automatically reconnects when there’s a problem

How? Simple, instead of creating a connection, just create a pool. It’s designed as a drop in replacement for client.query()

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

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

is a shorthand for

var mysql = require('mysql');
var pool  = mysql.createPool(...);

pool.getConnection(function(err, connection) {
  if (err) throw err; // not connected!

  // Use the connection
  connection.query('SELECT something FROM sometable', function (error, results, fields) {
    // When done with the connection, release it.
    connection.release();

    // Handle error after the release.
    if (error) throw error;

    // Don't use the connection here, it has been returned to the pool.
  });
});

How to fix “Host ‘172.x.0.1’ is not allowed to connect” with MySQL Docker

Using the official MariaDB docker image from Docker Hub, I got this error

Host '172.18.0.1' is not allowed to connect to this MySQL serverI 

I tried adding a shared volume after some google search, but that didn’t work

  volumes:
    # Use this option to persist the MySQL DBs in a shared volume.
    - ./mysqldata:/var/lib/mysql:rw,delegated

Turns out it’s an authentication problem, not a connection problem. By default the maria DB doesn’t give the root use permission to connect from everywhere, just localhost, so you’ll need to do

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;FLUSH PRIVILEGES;

By going into the container and execute the mysql command line tool

Alternatively, you can do

environment:
- MYSQL_ROOT_HOST='%'
- MYSQL_USER='youruser'
- MYSQL_PASSWORD='yourpassword'
- MYSQL_DATABASE='yourdb'

The entry point script only disallow root from any host, so if you create a new user you won’t get this limitation