Cannot connect to the databse #24

bartekpacia · 2024-01-16T22:12:51Z

Hi, thank you for publishing this sample repo.

I'm having a problem - the app container doesn't connect to the database container.

Reproduction steps

Clone repo
Create .env file so it looks like this:

POSTGRES_USER=prisma
POSTGRES_PASSWORD=topsecret
POSTGRES_DB=food

# nest run in docker container
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}?schema=food&sslmode=prefer
# nest run locally
# DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@localhost:5432/${POSTGRES_DB}?schema=food&sslmode=prefer

docker compose up
curl http://localhost:3000/foods

I'm getting {"statusCode":500,"message":"Internal server error"}% in curl output.

Here are logs:

Details

$ docker compose up
Attaching to nest-api, postgresprisma
postgresprisma  |
postgresprisma  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgresprisma  |
postgresprisma  | 2024-01-16 22:08:32.262 UTC [1] LOG:  starting PostgreSQL 13.13 (Debian 13.13-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgresprisma  | 2024-01-16 22:08:32.263 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgresprisma  | 2024-01-16 22:08:32.263 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgresprisma  | 2024-01-16 22:08:32.265 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgresprisma  | 2024-01-16 22:08:32.267 UTC [27] LOG:  database system was shut down at 2024-01-16 22:05:12 UTC
postgresprisma  | 2024-01-16 22:08:32.271 UTC [1] LOG:  database system is ready to accept connections
nest-api        |
nest-api        | > [email protected] start:prod /
nest-api        | > node dist/main
nest-api        |
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [NestFactory] Starting Nest application...
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [InstanceLoader] PrismaModule dependencies initialized +24ms
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [InstanceLoader] AppModule dependencies initialized +1ms
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [RoutesResolver] AppController {/}: +2ms
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [RouterExplorer] Mapped {/, GET} route +1ms
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [RouterExplorer] Mapped {/foods, GET} route +1ms
nest-api        | [Nest] 19  - 01/16/2024, 10:08:32 PM     LOG [NestApplication] Nest application successfully started +52ms
postgresprisma  | 2024-01-16 22:09:38.937 UTC [34] ERROR:  relation "food.Food" does not exist at character 54
postgresprisma  | 2024-01-16 22:09:38.937 UTC [34] STATEMENT:  SELECT "food"."Food"."id", "food"."Food"."name" FROM "food"."Food" WHERE 1=1 OFFSET $1
nest-api        | [Nest] 19  - 01/16/2024, 10:09:38 PM   ERROR [ExceptionsHandler]
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        | Error:
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        |     at cb (/node_modules/@prisma/client/runtime/index.js:38504:17)
nest-api        |     at async /node_modules/@nestjs/core/router/router-execution-context.js:46:28
nest-api        |     at async /node_modules/@nestjs/core/router/router-proxy.js:9:17
postgresprisma  | 2024-01-16 22:11:34.942 UTC [34] ERROR:  relation "food.Food" does not exist at character 54
postgresprisma  | 2024-01-16 22:11:34.942 UTC [34] STATEMENT:  SELECT "food"."Food"."id", "food"."Food"."name" FROM "food"."Food" WHERE 1=1 OFFSET $1
nest-api        | [Nest] 19  - 01/16/2024, 10:11:34 PM   ERROR [ExceptionsHandler]
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        | Error:
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        |     at cb (/node_modules/@prisma/client/runtime/index.js:38504:17)
nest-api        |     at async /node_modules/@nestjs/core/router/router-execution-context.js:46:28
nest-api        |     at async /node_modules/@nestjs/core/router/router-proxy.js:9:17
postgresprisma  | 2024-01-16 22:11:37.499 UTC [34] ERROR:  relation "food.Food" does not exist at character 54
postgresprisma  | 2024-01-16 22:11:37.499 UTC [34] STATEMENT:  SELECT "food"."Food"."id", "food"."Food"."name" FROM "food"."Food" WHERE 1=1 OFFSET $1
nest-api        | [Nest] 19  - 01/16/2024, 10:11:37 PM   ERROR [ExceptionsHandler]
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        | Error:
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        |     at cb (/node_modules/@prisma/client/runtime/index.js:38504:17)
nest-api        |     at async /node_modules/@nestjs/core/router/router-execution-context.js:46:28
nest-api        |     at async /node_modules/@nestjs/core/router/router-proxy.js:9:17

I'll be very grateful for any help! I cannot seem to make Prisma work properly in my containerized Nest.js app.

The text was updated successfully, but these errors were encountered:

marcjulian · 2024-01-17T10:34:38Z

Hi @bartekpacia can you post your docker-compose.yaml file too? What is the container name of the database?

bartekpacia · 2024-01-17T11:10:32Z

Thanks for such a quick response!

Here's my .env (the one posted above had incorrect host):

POSTGRES_USER=prisma
POSTGRES_PASSWORD=topsecret
POSTGRES_DB=food

# nest run in docker container
DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgresprisma:5432/${POSTGRES_DB}?schema=food&sslmode=prefer
# nest run locally
# DATABASE_URL=postgresql://${POSTGRES_USER}:${POSTGRES_PASSWORD}@localhost:5432/${POSTGRES_DB}?schema=food&sslmode=prefer

Here's my docker-compose.yaml:

version: '3.8'
services:
  nest-api:
    build:
      context: .
      dockerfile: Dockerfile
    container_name: nest-api 
    restart: always
    ports:
      - 3000:3000
    depends_on:
      - postgres
    env_file:
      - .env

  postgres:
    image: postgres:13
    container_name: postgresprisma
    restart: always
    ports:
      - 5432:5432
    env_file:
      - .env
    volumes:
      - postgres:/var/lib/postgresql/data

volumes:
  postgres:
    name: nest-prisma-docker-db

But I'm getting the same error with curl:

$ curl http://localhost:3000/foods
{"statusCode":500,"message":"Internal server error"}

marcjulian · 2024-01-17T11:57:34Z

When you serve the nest api and set the connection name to localhost:5432 does it work?
Do you have logs inside the nest-api docker container?

What if you trie to set the service and container name to the same name

e.g.

  postgresprisma:
    image: postgres:13
    container_name: postgresprisma

https://notiz.dev/blog/dockerizing-nestjs-with-prisma-and-postgresql/#add-docker-compose-with-postgresql

bartekpacia · 2024-01-18T11:49:25Z

When I change the DATABASE_URL to use host localhost:5432 instead of postgres:5432, and docker compose up --build, I get the Can't reach database server at localhost:5432 error immediately after app container starts:

[+] Running 2/1
 ✔ Container postgresprisma  Created                                                                               0.0s
 ✔ Container nest-api        Created                                                                               0.0s
Attaching to nest-api, postgresprisma
postgresprisma  |
postgresprisma  | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgresprisma  |
postgresprisma  | 2024-01-18 11:42:47.595 UTC [1] LOG:  starting PostgreSQL 13.13 (Debian 13.13-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgresprisma  | 2024-01-18 11:42:47.595 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgresprisma  | 2024-01-18 11:42:47.595 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgresprisma  | 2024-01-18 11:42:47.596 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgresprisma  | 2024-01-18 11:42:47.599 UTC [27] LOG:  database system was interrupted; last known up at 2024-01-18 11:17:29 UTC
postgresprisma  | 2024-01-18 11:42:47.634 UTC [27] LOG:  database system was not properly shut down; automatic recovery in progress
postgresprisma  | 2024-01-18 11:42:47.635 UTC [27] LOG:  redo starts at 0/15D3B60
postgresprisma  | 2024-01-18 11:42:47.635 UTC [27] LOG:  invalid record length at 0/15D3B98: wanted 24, got 0
postgresprisma  | 2024-01-18 11:42:47.635 UTC [27] LOG:  redo done at 0/15D3B60
postgresprisma  | 2024-01-18 11:42:47.647 UTC [1] LOG:  database system is ready to accept connections
nest-api        |
nest-api        | > [email protected] start:prod /
nest-api        | > node dist/main
nest-api        |
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [NestFactory] Starting Nest application...
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [InstanceLoader] PrismaModule dependencies initialized +42ms
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [InstanceLoader] AppModule dependencies initialized +0ms
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [RoutesResolver] AppController {/}: +4ms
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [RouterExplorer] Mapped {/, GET} route +2ms
nest-api        | [Nest] 19  - 01/18/2024, 11:42:48 AM     LOG [RouterExplorer] Mapped {/foods, GET} route +0ms
nest-api        | (node:19) UnhandledPromiseRejectionWarning: Error: Can't reach database server at `localhost`:`5432`
nest-api        |
nest-api        | Please make sure your database server is running at `localhost`:`5432`.
nest-api        |     at /node_modules/@prisma/client/runtime/index.js:36260:21
nest-api        | (Use `node --trace-warnings ...` to show where the warning was created)
nest-api        | (node:19) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). To terminate the node process on unhandled promise rejection, use the CLI flag `--unhandled-rejections=strict` (see https://nodejs.org/api/cli.html#cli_unhandled_rejections_mode). (rejection id: 1)
nest-api        | (node:19) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
nest-api exited with code 0
nest-api        |

When I use the same name for both service and container_name (=postgresprisma), and of course I change the .env file accordingly, I don't get the error immediately after the app container starts, but I get the original error when I curl the endpoint:

$ curl http://localhost:3000/foods
{"statusCode":500,"message":"Internal server error"}

postgresprisma  | 2024-01-18 11:45:09.706 UTC [34] ERROR:  relation "food.Food" does not exist at character 54
postgresprisma  | 2024-01-18 11:45:09.706 UTC [34] STATEMENT:  SELECT "food"."Food"."id", "food"."Food"."name" FROM "food"."Food" WHERE 1=1 OFFSET $1
nest-api        | [Nest] 18  - 01/18/2024, 11:45:09 AM   ERROR [ExceptionsHandler]
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        | Error:
nest-api        | Invalid `this.prismaService.food.findMany()` invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        |
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table `food.Food` does not exist in the current database.
nest-api        |     at cb (/node_modules/@prisma/client/runtime/index.js:38504:17)
nest-api        |     at async /node_modules/@nestjs/core/router/router-execution-context.js:46:28
nest-api        |     at async /node_modules/@nestjs/core/router/router-proxy.js:9:1

marcjulian · 2024-01-18T12:37:53Z

Use localhost:5432 only when you serve the Nest API in dev mode (e.g. npm run start:dev).

The error message when you use postgresprisma:5432 doesn't say it cannot connect to the database. It rather points out that the tables are missing. Can you confirm that the table Food exists in the schema food?

bartekpacia · 2024-01-18T13:13:22Z

Here's my prisma/schema.prisma file, I didn't change it:

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

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

model Food {
  id   Int    @id @default(autoincrement())
  name String
}

bartekpacia · 2024-01-18T13:21:24Z

Or maybe do you mean to check whether the Food table exists in the postgresprisma container? I tried doing it (see below) but I cannot get it to work:

$ docker ps --format '{{.ID}} {{.Image}}'                                                                                       
b5d21d9099ef nestjs-prisma-docker-nest-api
f7ba4a728070 postgres:13

$ docker exec -it f7ba4a728070 /bin/bash
root@f7ba4a728070:/#

root@f7ba4a728070:/# psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "root" does not exist
root@f7ba4a728070:/# psql -U postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist
root@f7ba4a728070:/# psql -U postgresprisma
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "postgresprisma" does not exist

kacaleksandra · 2024-01-26T16:45:40Z

I did the same steps which were mentioned in this comment and I have the same problem as Bartek with error as below:

[Nest] 19  - 01/20/2024, 6:38:08 PM   ERROR [ExceptionsHandler] 
nest-api        | Invalid this.prismaService.food.findMany() invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        | 
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table food.Food does not exist in the current database.
nest-api        | Error: 
nest-api        | Invalid this.prismaService.food.findMany() invocation in
nest-api        | /dist/app.controller.js:25:40
nest-api        | 
nest-api        |   22     return this.appService.getHello();
nest-api        |   23 }
nest-api        |   24 getFoods() {
nest-api        | → 25     return this.prismaService.food.findMany()
nest-api        |   The table food.Food does not exist in the current database.
nest-api        |     at cb (/node_modules/@prisma/client/runtime/index.js:38504:17)
nest-api        |     at async /node_modules/@nestjs/core/router/router-execution-context.js:46:28
nest-api        |     at async /node_modules/@nestjs/core/router/router-proxy.js:9:17

bartekpacia · 2024-04-09T13:19:29Z

For future readers: we got it to work! Here's our Dockerfile and docker-compose.yaml.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Cannot connect to the databse #24

Cannot connect to the databse #24

bartekpacia commented Jan 16, 2024

marcjulian commented Jan 17, 2024

bartekpacia commented Jan 17, 2024

marcjulian commented Jan 17, 2024

bartekpacia commented Jan 18, 2024 •

edited

marcjulian commented Jan 18, 2024

bartekpacia commented Jan 18, 2024

bartekpacia commented Jan 18, 2024 •

edited

kacaleksandra commented Jan 26, 2024 •

edited

bartekpacia commented Apr 9, 2024

Cannot connect to the databse #24

Cannot connect to the databse #24

Comments

bartekpacia commented Jan 16, 2024

Reproduction steps

marcjulian commented Jan 17, 2024

bartekpacia commented Jan 17, 2024

marcjulian commented Jan 17, 2024

bartekpacia commented Jan 18, 2024 • edited

marcjulian commented Jan 18, 2024

bartekpacia commented Jan 18, 2024

bartekpacia commented Jan 18, 2024 • edited

kacaleksandra commented Jan 26, 2024 • edited

bartekpacia commented Apr 9, 2024

bartekpacia commented Jan 18, 2024 •

edited

bartekpacia commented Jan 18, 2024 •

edited

kacaleksandra commented Jan 26, 2024 •

edited