Error: Access denied for user ''@'localhost' (using password: NO)

Question

I'm trying out db migrations with MySQL and Knex.

When I run the command knex migrate:latest, I get

ER_ACCESS_DENIED_ERROR: Access denied for user ''@'localhost' (using password: NO)

I've tried adding a password on the codebase (to '123' and 'NO'), though what confuses me most is that even as I have user: "root" in my database file, the error gives an empty string as the user...

I share what I imagine are the pertinent files:

// mysql_db.js

const knex = require('knex')({
  client: 'mysql',
  connection: {
    host: 'localhost',
    user: 'root',
    password: '',
    database: 'SQL_Data',
  },
});

module.exports = knex;

// knexfile.js

const path = require('path');

module.exports = {
    development: {
      client: 'mysql',
      connection: {
      filename: '/server/SQL/mysql_db',
    },
    migrations: {
      directory: path.join(__dirname, '/server/SQL/migrations'),
    },
    seeds: {
      directory: path.join(__dirname, '/server/SQL/seeds'),
    },
  },
};

//knex.js

const environment = proces.env.NODE_ENV || 'development';
const config = require('../../knexfile.js')[environment];
module.exports = require(knex)('config');

// "migration definition"

exports.up = (knex, Promise) => knex.schema.createTable('sql_table', ((table) => {
  table.increments();
  table.string('name').notNullable();
  table.string('email').notNullable();
  table.string('description').notNullable();
  table.string('url').otNullable();
}));

exports.down = (knex, Promise) => knex.schema.dropTable('sql_table');

Answer 1

As error message say you are trying to login with invalid credentials user whose name is empty string doesn't exist in DB.

This means your configuration is wrong. you have some strange segment in your node-mysql driver configuration, which tries to refer other file, which exports initialized knex instance

  client: 'mysql',
  connection: {
    filename: '/server/SQL/mysql_db'
  }

That is just plain wrong. Correct format for knexfile is pretty much the same that is used to create knex instance, except that knexfile supports also selecting the profile according to NODE_ENV environment variable.

const path = require('path');

module.exports = {
  development: {
    client: 'mysql',
    connection: {
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'SQL_Data',
    },
    migrations: {
      directory: path.join(__dirname, '/server/SQL/migrations'),
    },
    seeds: {
      directory: path.join(__dirname, '/server/SQL/seeds'),
    },
  },
};

In your mysql_db you might like to do something like this to init knex to be able to use the same config:

const knex = require('knex')(
  require('knexfile')[process.env.NODE_ENV || 'development']
);