Can't reach database server at postgres:5432

[gabriel-frattini]

Bug description

I have a docker container running with postgres but prisma cant find the database server when i run npx prisma migrate dev

In my docker GUI it says that I have a running container with a name of postgres on PORT:5432

The error i get is

Error: P1001: Can't reach database server at `postgres`:`5432`

Please make sure your database server is running at `postgres`:`5432`.

How to reproduce

1. Install prisma and @prisma/client
2. run npx prisma init to get the following schema

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

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

3. add the docker-compose.yml file

version: "3.8"
services:
  postgres:
    image: postgres:latest
    restart: always
    environment:
      - POSTGRES_USER=postgres
      - POSTGRES_PASSWORD=postgres
    volumes:
      - postgres:/var/lib/postgresql/data
    ports:
      - "5432:5432"
volumes:
  postgres:

5. Add database string to .env

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/mydb?schema=public"

7. run docker compose up
8. run npx prisma migrate dev

Expected behavior

Succesful migration from prisma

Prisma information

"@prisma/client": "^3.5.0"
"prisma": "^3.15.2"

Environment & setup

• OS: Windows

Prisma Version

3.15.2

[sabinadams]

Hey @gabriel-frattini, I believe the issue here is your connection string.

DATABASE_URL="postgresql://postgres:postgres@postgres:5432/mydb?schema=public"

The format of the connection string should be:

postgresql://<USER>:<PASSWORD>@<HOST>:<PORT>/<DATABASE>?schema=<SCHEMA>

Yours is mostly correct, except the HOST shouldn't be "postgres", but instead "localhost".

DATABASE_URL="postgresql://postgres:postgres@localhost:5432/mydb?schema=public"

[maxganiev]

here I described the solution I'd managed to find #15005
hope it will help out

[bablukpik]

I'm getting the same error :(

Here is the error:

Error: P1001: Can't reach database server at postgres:5432

While running npx prisma migrate dev to perform Prisma migrations locally.

Again if I replace the value of POSTGRES_HOST with localhost and run docker compose up then it shows this error:

PrismaClientInitializationError: Can't reach database server at localhost:5432

[99lalo]

Were you able to find the solution?

[kejlerj]

I had the same error and this fixes it :
https://stackoverflow.com/a/77822758/10661841

I had to use the name container as HOST in the db url, then my app works fine, but not the migrations commands. To do so I had to execute the commands from inside the container.

[noobpiyush]

hey @sabinadams . i was also facing the same issue. i am using windows your solution worked thanks. i wan using this string -> DATABASE_URL="postgresql://piyush:piyuush@localhost:5432/postgres". but i then saw you answer and modified it to DATABASE_URL="postgresql://piyush:piyuush@localhost:5432/postgres?schema=public". it worked

[kevinarch]

I'm faced same issue but this worked for me: https://stackoverflow.com/questions/68476229/m1-related-prisma-cant-reach-database-server-at-database5432

Just need to add ?connect_timeout=300 with nodejs-16 (need someone to explain for me how this work)

Second way without adding anything to DATABASE_URL is upgrade nodejs to 18 .

[gmcouto]

Thank you. It seems that docker hostname resolving might be a little bit slow. Will keep that in mind.

[usmansbk]

This fixed my issue but why?

[zhoou]

If you have created a "newuser" to log in and this issue occurs，
like this:
postgresql://newuser:password@localhost:5432/mydb?schema=public
maybe you need to check if “newuser” can log in！！

[erik-metz]

How could this error be handled in the backend, when for example the database is really down?

[MarchenkoVL05]

I was facing the same problem and changing the port from "5432:5432" to "5434: 5432" solved it. So the docker-compose is:

version: '3.8'
services:
  postgres:
    image: postgres:13.5
    restart: always
    environment:
      - POSTGRES_USER=myuser
      - POSTGRES_PASSWORD=mypassword
    volumes:
      - postgres:/var/lib/postgresql/data
    ports:
      - '5434:5432'

volumes:
  postgres:

and .env:
DATABASE_URL="postgres://myuser:mypassword@localhost:5434/median-db"
Dunno why, but it works

[Rormea]

Esto me resultó!!!

[Lepaic6969]

¡Funciona!, hay que resaltar que ahora el puerto que debemos poner en DATABASE_URL es el 5434 como lo sugiere @MarchenkoVL05

[jziggas]

I am having the same problem. Nothing makes sense!

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#accessing-environment-variables-from-the-schema

# Prisma supports the native connection string format for PostgreSQL, MySQL, SQLite, SQL Server, MongoDB and CockroachDB.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

POSTGRES_DB=foo
POSTGRES_USER=bar
POSTGRES_PASSWORD=baz
POSTGRES_SCHEMA=public
POSTGRES_PORT=5432
POSTGRES_HOST=localhost
DATABASE_URL="postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@${POSTGRES_HOST}:${POSTGRES_PORT}/${POSTGRES_DB}?schema=${POSTGRES_SCHEMA}"
APOLLO_PLAYGROUND=true
JWT_SECRET="qux"

###################
# BUILD FOR LOCAL DEVELOPMENT
###################

FROM node:18-alpine As development

# Create app directory
WORKDIR /usr/src/app

# Copy application dependency manifests to the container image.
# A wildcard is used to ensure copying both package.json AND package-lock.json (when available).
# Copying this first prevents re-running npm install on every code change.
COPY --chown=node:node package*.json ./

# Install app dependencies using the `npm ci` command instead of `npm install`
RUN npm ci

# Bundle app source
COPY --chown=node:node . .

# Generate Prisma database client code
RUN npm run prisma:generate

EXPOSE 3000

# Use the node user from the image (instead of the root user)
USER node

###################
# BUILD FOR PRODUCTION
###################

FROM node:18-alpine As build

WORKDIR /usr/src/app

COPY --chown=node:node package*.json ./

# In order to run `npm run build` we need access to the Nest CLI.
# The Nest CLI is a dev dependency,
# In the previous development stage we ran `npm ci` which installed all dependencies.
# So we can copy over the node_modules directory from the development image into this build image.
COPY --chown=node:node --from=development /usr/src/app/node_modules ./node_modules

COPY --chown=node:node . .

# Run the build command which creates the production bundle
RUN npm run build

# Set NODE_ENV environment variable
ENV NODE_ENV production

# Running `npm ci` removes the existing node_modules directory.
# Passing in --only=production ensures that only the production dependencies are installed.
# This ensures that the node_modules directory is as optimized as possible.
RUN npm ci --only=production && npm cache clean --force

USER node

###################
# PRODUCTION
###################

FROM node:18-alpine As production

# Copy the bundled code from the build stage to the production image
COPY --chown=node:node --from=build /usr/src/app/node_modules ./node_modules
COPY --chown=node:node --from=build /usr/src/app/dist ./dist

# Start the server using the production build
CMD [ "node", "dist/main.js" ]

version: '3.8'
services:
  postgres:
    image: postgres:15.3-alpine
    restart: always
    ports:
      - 5432:5432
    env_file:
      - .env
    environment:
      # You'll notice the DATABASE_URL variable in the docker-compose.yml file is the same as the DATABASE_URL variable in the .env file - except the host has been updated to postgres instead of localhost.
      # This will prevent you from getting the error: Error: Can't reach database server at localhost:5432
      DATABASE_URL: postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:${POSTGRES_PORT}/${POSTGRES_DB}?schema=${POSTGRES_SCHEMA}
    volumes:
      - postgres:/var/lib/postgresql/data
    networks:
      - nestjs
  api:
    build:
      dockerfile: Dockerfile
      context: .
      # Only will build development stage from our dockerfile
      target: development
    volumes:
      - .:/usr/src/app
    env_file:
      - .env
    environment:
      DATABASE_URL: postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:${POSTGRES_PORT}/${POSTGRES_DB}?schema=${POSTGRES_SCHEMA}
    # Run a command against the development stage of the image
    command: npm run start:dev
    ports:
      - 3000:3000
    depends_on:
      - postgres
volumes:
   postgres:
      name: nestjs-postgres

networks:
   nestjs:

No matter what I do it's either Error: Can’t reach database server at localhost:5432 or Error: Can’t reach database server at postgres:5432

[jziggas]

Ugh. Had to add the network to the api service just like the postgres service :(

networks:
      - nestjs

Now it works. In any case, I hope my docker files serve as a template for others.

[dev-sumanaditya]

@jziggas
Replacing host in the DATABASE_URL with "host.docker.internal" also works but is not recommended.
link - Docker Docs

final code looks like this.
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@host.docker.internal:5432/${POSTGRES_DB}?schema=public

[welcomemonth]

我是在运行这个npm run migrate:dev命令的时候一直提示Error: P1001: Can't reach database server at postgres:5432，上面的办法我都试过了，但是都不行，当我直接在数据库中创建了一个《POSTGRES_DB》表的之后再运行就不报错了

[lili21]

怎么建的

[fazil-softprodigy]

If you encounter the same issue, it is essential to ensure that Docker is running correctly. In my case, I experienced a similar problem, and upon investigation, I found that the PostgreSQL server was not accessible because Docker was not up and running.

To avoid facing this problem, please make sure that you have Docker up and operational before attempting to connect to the PostgreSQL database. Docker plays a crucial role in providing the necessary environment for the database server to function correctly.

[solutionsSlayer]

captain obvious

[anasdevv]

if you encounter same issue and your url is also in correct format try replacing
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@host.docker.internal:5432/${POSTGRES_DB}?schema=public
with
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@localhost:5432/${POSTGRES_DB}?schema=public

[anasdevv]

if you are using docker windows

[AprajitKatiyar]

Adding schema=public&connect_timeout=30&pool_timeout=30&socket_timeout=30 to the connection string worked for me.
Also make sure you don't have a VPN enabled. (Struggled with Zscaler in my case)

[kelokchan]

This has fixed my issue. Thanks!

[trishan9]

this fixed my issue

[hassanebendaoud]

thanks

[mischulz14]

Had this issue today at work and this worked like a charm. Thanks @AprajitKatiyar ! 🚀

I only used the string after schema=public

[lil-id]

thank you, it works for me.

[IkeCairns]

I have been facing the same problem for a week, and have tried every advice above, but unfortunately, it is still not solved.

[IkeCairns]

When I hit the command below on my terminal, problem has been solved!
$ sudo docker compose run app npx prisma migrate dev

ChatGpt 3.5 advised me to do so.

[slarkvan]

it works

[1qh]

This solution works for me

[Anhdao153]

i resolved
This is my step
i added this key to .env
PRISMA_SCHEMA_DISABLE_ADVISORY_LOCK=false
then i execute my container

docker exec -it your-application-container bash

and finally, run command migrate

npx prisma migrate dev --name init-source
or 
npx prisma migrate dev

it worked to me
i spent more than 2 hours because this issue T_T

my docker compose

version: '3.9'
services:
  
  api:
    container_name: portfolio-api
    build:
      dockerfile: Dockerfile
      context: .
    env_file: .env
    ports:
      - "5050:5050"
    networks:
      - portfolio
    depends_on:
      - database

  database:
    container_name: postgres
    image: postgres:15.6-alpine3.19
    restart: unless-stopped
    ports:
      - '5432:5432'
    networks:
      - portfolio
    environment:
      POSTGRES_USER: ${POSTGRES_USER:-postgres}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD:-postgres}
      PGDATA: /data/postgres
    volumes:
       - postgres:/data/postgres

  pgadmin:
    container_name: pgadmin_container
    image: dpage/pgadmin4
    environment:
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL:-pgadmin4@pgadmin.org}
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD:-admin}
      PGADMIN_CONFIG_SERVER_MODE: 'False'
    volumes:
       - pgadmin:/var/lib/pgadmin

    ports:
      - "${PGADMIN_PORT:-8080}:80"
    networks:
      - portfolio
    restart: unless-stopped

networks:
  portfolio:
    driver: bridge
  
volumes:
  postgres:
  pgadmin:

[BrunoBorges12]

What worked for me was to replace localhost with the Docker service, which is db. This ensures that the Docker application can communicate with the PostgreSQL database container, as defined in the 'docker-compose.yml' file.

[bytemasterdan]

En mi caso note que el contenedor intenta levantarse pero queda intermitente y no lograba establecer una conexión exitosa, lo que hice fue:

1. Bajar el contenedor con docker-compose down
2. Eliminar la carpeta de postgres en mi caso estaba al mismo nivel del archivo yaml
3. Subir de nuevo el contenedor con docker-compose up -d
4. Intentar de nuevo la migracion de datos con npx prisma migrate dev --name init