Knexfile not getting .env variables during command line migration

Question

I am using Knex with a MySQL database and am trying to migrate my tables using npx knex migrate:latest. However, when I run the command I am getting the following error: "Access denied for user ''@'localhost' (using password: NO)".

Here is my knexfile.js which is in the root directory with my .env:

require("dotenv").config();
const path = require("path");

const {
  DB_HOST: host,
  DB_NAME: database,
  DB_USER: user,
  DB_PASSWORD: password,
  TEST_DB_NAME: testDatabase,
} = process.env;

/**
 * @type { Object.<string, import("knex").Knex.Config> }
 */
module.exports = {
  development: {
    client: "mysql2",
    connection: {
      host,
      database,
      user,
      password,
      charset: "utf8",
    },
    migrations: {
      directory: path.join(__dirname, "/db/migrations"),
    },
    seeds: {
      directory: path.join(__dirname, "/db/seeds"),
    },
  },
  test: {
    client: "mysql2",
    connection: {
      host,
      database: testDatabase,
      user,
      password,
      charset: "utf8",
    },
    migrations: {
      directory: path.join(__dirname, "/db/migrations"),
    },
    seeds: {
      directory: path.join(__dirname, "/db/seeds"),
    },
  },
};

I have tried console logging all the env variables and they are all undefined.

The migrations do work when I hardcode the values in the knexfile.js.

Answer 1

The one thing I could think of to try is to set the path to env file with force (even though it should work with .env as default):

require('dotenv').config({
    path: '.env'
});

This is assuming that you have a .env file in the same root folder as the config file.