Tag: mysql2

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server

October 11th, 2021

Reading Time: < 1 minute

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

      
-- to deal with ER_NOT_SUPPORTED_AUTH_MODE error. Only run this line for local dev
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';


-- create our main user for local dev
CREATE USER IF NOT EXISTS 'my_user'@'%' IDENTIFIED BY 'root';

CREATE DATABASE IF NOT EXISTS `test_container` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
GRANT ALL PRIVILEGES ON test_container.* TO 'my_user'@'%'  WITH GRANT OPTION;
flush privileges;

Update client to mysql2

      
require("dotenv-safe").config({ allowEmptyValues: false });

module.exports = {
  client: "mysql2",
  version: process.env.MYSQL_VERSION || "8",
  connection: {
    host: process.env.MYSQL_HOST,
    user: process.env.MYSQL_USERNAME,
    password: process.env.MYSQL_PASSWORD,
    database: process.env.MYSQL_DATABASE,
    port: process.env.MYSQL_PORT || 3306,
  },
  migrations: {
    extension: "ts",
    directory: "./migrations",
  },
  debug: process.env.LOG_LEVEL === "debug",
};

How to connect to a MySQL database in Node.js

October 11th, 2021

Reading Time: < 1 minute

To connect to a MySQL database in Node.js is pretty straightforward with the module mysql2. This example is just a starting point to show how to connect it so do not take it as a production-ready code

How to install it

Let’s install the module in our project as usual

      
npm install mysql2

How to implement it

Create a file called database.js wit this content

      

const mysql = require("mysql2");
const pool = mysql.createPool({
  database: process.env.MYSQL_DATABASE,
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USERNAME,
  password: process.env.MYSQL_PASSWORD,
  port: Number(process.env.MYSQL_PORT),
  connectionLimit: Number(process.env.MYSQL_MAX_POOL_SIZE),
});

const query = (query, values = []) => {
  return new Promise((resolve, reject) => {
    pool.query(query, values, (err, results) => {
      if (err) reject(err);
      else resolve(results);
    });
  });
};

module.exports.query = query;

How to use it

      

const db = require("./database");
const result = await db.query("SELECT NOW()");
console.log(result);