Prisma won't connect to MySQL Docker Container

Question

I'm trying to use docker-compose to run a project with two Docker containers, MySQL and NodeJS. I'm having trouble connect to the SQL container from the backend container. My backend container uses Prisma as it's ORM and shows me the following error when npx prisma migrate dev is ran (during docker-compose up).

Error: P1001: Can't reach database server at `mysql`:`3306`
Please make sure your database server is running at `mysql`:`3306`.

After some research, I thought the issue was my database url. The host name should match the db container name (mysql in this case), so I updated that but still no luck.

DATABASE_URL=mysql://root:root@mysql:3306/myshowlist # .env file

I also tried the container service name (db) but that wasn't it either.

DATABASE_URL=mysql://root:root@db:3306/myshowlist # .env file

What I'm finding puzzling is that my Prisma has no issue connecting to the MySQL container when it's not run inside the container. When I start the MySQL container solo and run the Prisma migrate command, it works and I can use the backend service like normal and save/read from the database. I do have to use localhost as the hostname though which makes sense.

I'm not sure what could be the issue, I would really appreciate a nudge in the right direction!

docker-compose.yml:

version: "3.8"
services:
    api:
        build:
            context: ./api-old
        ports:
            - "5001:3200"
        container_name: api
        depends_on:
            - db
    
    db:
        image: mysql:5.7
        restart: always
        container_name: mysql
        ports:
            - "3306:3306"
        environment:
          MYSQL_DATABASE: myshowlist
          MYSQL_ROOT_PASSWORD: "root"

./api-old/Dockerfile:

FROM node:latest

WORKDIR /usr/src/app

COPY package*.json ./
COPY .env ./
COPY prisma ./prisma/

RUN npm ci
RUN npm run db-prod

COPY . .

EXPOSE 5001

CMD ["npm", "start"]

schema.prisma:

datasource db {
    provider        = "mysql"
    url             = env("DATABASE_URL")
}

generator client {
    provider        = "prisma-client-js"
}

Answer 1

I know this is really late.

I came across the same issue

In my case when Prisma tries to connect to mysql container but since I didn't have bind-address configured it was failing. This is important because every container has different IPs so by setting 0.0.0.0 will allow every connection made to mysql

First

Check in my.cnf of mysql config that below is set:

bind-address                    =0.0.0.0


[mysqld]
bind-address                    =0.0.0.0
max_allowed_packet              =500M
default-authentication-plugin   =mysql_native_password

Second

Check if all services or on same network

docker network inspect network_name

In my case only one service was connected to the network so, I created a network in docker-compose.yml

version: '3.8'

services:
  api:
    build: ./apis
    networks:
      - backend
    ports:
      - '4100:4100'
    command: yarn prisma:dev:deploy
  database:
    container_name: temp_mysql_db
    image: mysql:5.7
    restart: always
    networks:
      - backend
    environment:
      MYSQL_DATABASE: 'db'
      MYSQL_USER: 'user'
    ports:
      - '3306:3306'
    volumes: 
      - temp-db:/var/lib/mysql
      - ./my.cnf:/etc/mysql/my.cnf:ro
volumes:
  temp-db:
networks:
  backend:
    driver: bridge

Answer 2

You have to create an docker network first, example:

docker network create demo-network

Then add this network to your docker-compose:

version: "3.8"
services:
  api:
    build:
      context: ./api-old
    ports:
      - "5001:3200"
    container_name: api
    depends_on:
      - db
    networks:
      - demo-network

  db:
    image: mysql:5.7
    restart: always
    container_name: mysql
    ports:
      - "3306:3306"
    environment:
      MYSQL_DATABASE: myshowlist
      MYSQL_ROOT_PASSWORD: "root"
    networks:
      - demo-network
networks:
  demo-network:
    external: true

Then re-run the docker-compose and try this url again:

DATABASE_URL=mysql://root:root@mysql:3306/myshowlist

Hope it help!

Answer 3

Yes so the mysql address needs to be changed to the name of the service that's correct. You don't need to create a network to access services launched via the same docker compose file, that's only helpful if you have multiple docker compose and you want your services to communicate with one another.

I believe the issue is that your mysql container is not fully started when the api tries to run the migration. So a timing issue most likely. Check this thread: Docker-compose check if mysql connection is ready