Cannot connect to the databse

[bartekpacia]

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

1. Clone repo
2. 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

3. docker compose up
4. 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        | > nestjs-prisma-docker@0.0.1 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.

[marcjulian]

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

[bartekpacia]

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]

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]

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        | > nestjs-prisma-docker@0.0.1 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]

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]

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]

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]

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]

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