how to connect Node.js with MySQL 8.0 using Express?

Question

I am trying to connect Nodejs with MySQL 8.0 using Express. my code:

var express = require('express');
var router = express.Router();
var mysql = require('mysql');

router.get('/', function(req, res, next) { 
    var connection = mysql.createConnection({
      host     : 'localhost',
      user     : 'root',
      password : 'rootpass'
    });    
    connection.connect(function(err) {
      if (err) throw err;
      console.log('Connected!');
    });
});

the above code is in the official Express page, but i suppose it is for MySQL versions under 8.0.

when i run the project i get the next error:

 throw err; // Rethrow non-MySQL errors
    ^
Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client
at Handshake.Sequence._packetToError (G:\Fractal projets\fractalpage\bfweb\node_modules\mysql\lib\protocol\sequences\Sequence.js:52:14)

MySQL was previously installed

Answer 1

It looks like you have user configured with mysql_old_auth You can change using anyone of this option convert to new auth protocol: http://dev.mysql.com/doc/refman/5.7/en/old-client.html or allow to use insecure auth: https://github.com/mysqljs/mysql#connection-options insecureAuth option

Above did not work for me but the following link DOES WORK https://insidemysql.com/introducing-connector-node-js-for-mysql-8-0/

Answer 2

use @mysqlx instead of mysql

$ npm install @mysql/xdevapi

read following pages for using xdevapi

• https://dev.mysql.com/doc/dev/connector-nodejs/8.0/
• https://dev.mysql.com/doc/x-devapi-userguide/en/devapi-connection-concepts.html

My dbconn looks like that:

const fs =require("fs");
const path = require("path");
const cfgPath=path.join("./configs", "database.json"); //Database Config-File
const content=fs.readFileSync(cfgPath);
const cfg=JSON.parse(content); //load configs as cfg
const mysql=require("@mysql/xdevapi");
const dbconn = mysql.getSession({"host":cfg.host, "port":cfg.port, "user":cfg.user, "password":cfg.password})
                    .then(session=>{session.getSchema(cfg.database)
                                           .createCollection(cfg.project + "_col", {reuseExisting: true })
                                           .catch(function (err) {
                                                console.log("error occured while connecting database");
                                                console.error('error connecting: ' + err.stack);
                                            })
                                        });
module.exports = dbconn;

in my directory configs the database.json looks so:

{
    "project": "test",
    "host":"localhost",
    "port": 33060,
    "database": "test",
    "user": "dev",
    "password": "devpass"
}