Using Environment Files in Docker-Compose

Question

I've looked this up in StackOverflow and asked ChatGPT, but cannot seem to get an answer.

The situation is that I have a PHP application that uses MySQL and phpMyAdmin. I want to execute SQL on startup to create the user and base tables if they don't exist. At the same time, I don't want to expose credentials. So I have a file called generate-init.sh that looks like this:

#!/bin/bash

# Check if the .env file exists
if [ ! -f /docker-entrypoint-initdb.d/.env ]; then
  echo "Error: .env file not found in /docker-entrypoint-initdb.d/"
  exit 1
fi

# Load variables from the .env file
set -o allexport
source /docker-entrypoint-initdb.d/.env
set +o allexport

# Check if required variables are set
if [ -z "$DB_DATABASE" ] || [ -z "$DB_USERNAME" ] || [ -z "$DB_PASSWORD" ]; then
  echo "Error: Required environment variables (DB_DATABASE, DB_USERNAME, DB_PASSWORD) are not set in .env"
  exit 1
fi

# Generate the init.sql file
cat <<EOL > /docker-entrypoint-initdb.d/init.sql
-- do stuff with ${DB_*} values
EOL

Then in my docker-compose.yml, I have the following:

mysql:
  image: mysql:5.7
  ports:
    - "3306:3306"
  environment:
    MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}
    MYSQL_DATABASE: ${DB_DATABASE}
  volumes:
    - ./.env:/docker-entrypoint-initdb.d/.env.sh
    - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    - ./generate-init.sh:/docker-entrypoint-initdb.d/generate-init.sh
  command: ["/bin/bash", "-c", "/docker-entrypoint-initdb.d/generate-init.sh && docker-entrypoint.sh mysqld"]
  restart: always
  networks:
    - app-network

While my generate-init.sh executes when I execute docker-compose up --build, it always hits the if statement and prints that it could not find the .env file.

I'm a bit confused because the file definitely exists in the same directory as my docker-compose. To be sure, I executed ls -l .env in the directory that my docker-compose lives and sure enough it is there.

I would have thought that by binding my.env to docker-entrypoint-initdb.d/.env at the beginning of my volumes that it'd work, but it doesn't. What am I missing here?