Client error "Can't reach database" for some tables not others

[linojon]

1. Prisma version (prisma -v or npx prisma -v):
   3.9.2

2. Logs from Developer Tools Console or Command line, if any:

3. Does the issue persist even after updating to the latest prisma CLI dev version? (npm i -D prisma@dev)
   YES

4. Prisma schema (if relevant):

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

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

model Attachment {
  id                    Int      @id @default(autoincrement())
  attachable_id         Int
  attachable_type       String
  attached_file_name    String?
  attached_content_type String?
  attached_file_size    Int?
  attached_updated_at   DateTime @updatedAt
  title                 String?
  description           String?
  hidden                Boolean?
  image_width           Int?
  image_height          Int?
  attached_processing   Boolean?
  post                  Post     @relation(fields: [attachable_id], references: [id], onDelete: Cascade)

  @@map("attachments")
}

model Location {
  id        Int     @id @default(autoincrement())
  continent String?
  country   String?
  state     String?

  posts Post[] @relation("LocationsOnPosts")
  @@map("locations")
}

model Newsletter {
  id           Int       @id @default(autoincrement())
  published_on DateTime?
  created_at   DateTime  @default(now())
  updated_at   DateTime  @updatedAt
  posts        Post[]

  @@map("newsletters")
}

model Post {
  id               Int                      @id @default(autoincrement())
  type             String?
  title            String?
  intro            String?
  body             String?
  source_link      String?
  author_name      String?
  author_email     String?
  status           String?                  @default("draft")
  published_on     DateTime?
  created_at       DateTime                 @default(now())
  updated_at       DateTime // @updatedAt
  posted_at        DateTime?
  is_opinion       Boolean?
  published_in     String?
  truncate_content Boolean?
  hit_count        Int?
  xarid            Int?
  newsletter_id    Int?
  newsletter       Newsletter?              @relation(fields: [newsletter_id], references: [id])
  ebulletin_issue  DateTime?
  highlighted      Boolean?
  pinned           Boolean?
  embed            String?
  tsv              Unsupported("tsvector")?
  attachments      Attachment[]
  topics           Topic[]                  @relation("TopicsOnPosts")
  locations        Location[]               @relation("LocationsOnPosts")

  @@index([newsletter_id], name: "index_posts_on_newsletter_id")
  @@index([posted_at], name: "index_posts_on_posted_at")
  @@index([published_on], name: "index_posts_on_published_on")
  @@map("posts")
}

model Topic {
  id   Int     @id @default(autoincrement())
  name String?

  posts Post[] @relation("TopicsOnPosts")
  @@map("topics")
}

// next-auth

model Account {
  id                 String  @id @default(cuid())
  userId             String
  type               String
  provider           String
  providerAccountId  String
  refresh_token      String?
  access_token       String?
  expires_at         Int?
  token_type         String?
  scope              String?
  id_token           String?
  session_state      String?
  oauth_token_secret String?
  oauth_token        String?

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerAccountId])
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime
  user         User     @relation(fields: [userId], references: [id], onDelete: Cascade)
}

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  isAdmin       Boolean   @default(false)
  accounts      Account[]
  sessions      Session[]
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
}

In Prisma Studio, i can open some tables (eg Post, Attachment) but two other tables -- Topic and Location -- give the error " Invalid prisma.location.findMany() invocation: Can't reach database server at localhost:5432 "
I've tried this in browser running npx prisma studio (in a NExt.js project) and also same errors in Prisma Studio Desktop (Windows 10).
I'm able to query the same tables within my app ok.
I've tried this with several different Postgres databases that share the schema, eg a local development, a cloud preview (on AWS) and a cloud production (on DigitalOcean), same errors. I've tried uninstalling and reinstalling prisma and @prisma/client, deleting my Appdata/Roaming/Prisma/Studio/ folder and other thing.
See video:

https://www.loom.com/share/3b73f7230dbe4550b73810643a719f66

[linojon]

with DEBUG="*"
Topic tab fails, Post tab succeeds

$ npx prisma studio
Environment variables loaded from .env
Prisma schema loaded from prisma\schema.prisma
Prisma Studio is up on http://localhost:5555
  prisma:tryLoadEnv Environment variables not found at  +0ms
  prisma:tryLoadEnv Environment variables not found at  +0ms
  prisma:tryLoadEnv No Environment variables loaded +0ms
  prisma:client clientVersion: in-memory +0ms
  prisma:client clientEngineType: library +0ms
  prisma:client:libraryEngine internalSetup +0ms
  prisma:client Prisma Client call: +8ms
  prisma:client prisma.attachment.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregateAttachment {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +0ms
  prisma:client prisma.location.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +0ms
  prisma:client query {
  prisma:client   aggregateLocation {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
  prisma:client Prisma Client call: +0ms
  prisma:client prisma.newsletter.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregateNewsletter {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +1ms
  prisma:client prisma.post.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +0ms
  prisma:client query {
  prisma:client   aggregatePost {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +1ms
  prisma:client prisma.topic.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregateTopic {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +0ms
  prisma:client prisma.account.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +0ms
  prisma:client query {
  prisma:client   aggregateAccount {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +1ms
  prisma:client prisma.session.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +0ms
  prisma:client query {
  prisma:client   aggregateSession {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
  prisma:client Prisma Client call: +0ms
  prisma:client prisma.user.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregateUser {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client Prisma Client call: +1ms
  prisma:client prisma.verificationToken.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregateVerificationToken {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client:libraryEngine loadEngine using D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\engines\bcc2ff906db47790ee902e7bbc76d7ffb1893009\query_engine-windows.dll.node +24ms
  prisma:client:libraryEngine requestBatch +8ms
  prisma:client:libraryEngine library starting +0ms
  prisma:client:libraryEngine library started +93ms
  prisma:client:libraryEngine library stopping +40ms
  prisma:client:libraryEngine library stopped +2ms
  prisma:client Prisma Client call: +199ms
  prisma:client prisma.topic.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +2ms
  prisma:client query {
  prisma:client   aggregateTopic {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
  prisma:client:libraryEngine sending request, this.libraryStarted: false +57ms
  prisma:client:libraryEngine library starting +0ms
  prisma:client Prisma Client call: +5ms
  prisma:client prisma.topic.findMany({
  prisma:client   take: 100,
  prisma:client   skip: 0,
  prisma:client   select: {
  prisma:client     id: true,
  prisma:client     name: true,
  prisma:client     posts: true
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   findManyTopic(
  prisma:client     take: 100
  prisma:client     skip: 0
  prisma:client   ) {
  prisma:client     id
  prisma:client     name
  prisma:client     posts {
  prisma:client       id
  prisma:client       type
  prisma:client       title
  prisma:client       intro
  prisma:client       body
  prisma:client       source_link
  prisma:client       author_name
  prisma:client       author_email
  prisma:client       status
  prisma:client       published_on
  prisma:client       created_at
  prisma:client       updated_at
  prisma:client       posted_at
  prisma:client       is_opinion
  prisma:client       published_in
  prisma:client       truncate_content
  prisma:client       hit_count
  prisma:client       xarid
  prisma:client       newsletter_id
  prisma:client       ebulletin_issue
  prisma:client       highlighted
  prisma:client       pinned
  prisma:client       embed
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client:libraryEngine sending request, this.libraryStarted: false +8ms
  prisma:client:libraryEngine library already starting, this.libraryStarted: false +2ms
  prisma:client:libraryEngine library started +54ms
  prisma:client:fetcher Error: Can't reach database server at `localhost`:`5432`
  prisma:client:fetcher 
  prisma:client:fetcher Please make sure your database server is running at `localhost`:`5432`.
  prisma:client:fetcher     at prismaGraphQLToJSError (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\prisma-client\runtime\index.js:35992:12)
  prisma:client:fetcher     at Object.request (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\prisma-client\runtime\index.js:36358:17)
  prisma:client:fetcher     at async cb (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\prisma-client\runtime\index.js:38673:26)
  prisma:client:fetcher     at async PrismaClient._request (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\prisma-client\runtime\index.js:40859:18)
  prisma:client:fetcher     at async Object.request (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\build\index.js:106096:34)
  prisma:client:fetcher     at async Object.respond (D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\build\index.js:106266:34)
  prisma:client:fetcher     at async D:\Documents\JavascriptProjects\WindActionJS\node_modules\prisma\build\index.js:106418:26 +0ms
  prisma:client:libraryEngine library is already stopping +289ms
  prisma:client:libraryEngine library stopping +0ms
  prisma:client:libraryEngine library stopped +3ms
  prisma:client Prisma Client call: +2m
  prisma:client prisma.post.count({
  prisma:client   select: {
  prisma:client     _count: {
  prisma:client       select: {
  prisma:client         _all: true
  prisma:client       }
  prisma:client     }
  prisma:client   }
  prisma:client }) +0ms
  prisma:client Generated request: +1ms
  prisma:client query {
  prisma:client   aggregatePost {
  prisma:client     _count {
  prisma:client       _all
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +1ms
  prisma:client:libraryEngine sending request, this.libraryStarted: false +2m
  prisma:client:libraryEngine library starting +1ms
  prisma:client Prisma Client call: +2ms
  prisma:client prisma.post.findMany({
  prisma:client   take: 100,
  prisma:client   skip: 0,
  prisma:client   select: {
  prisma:client     id: true,
  prisma:client     type: true,
  prisma:client     title: true,
  prisma:client     intro: true,
  prisma:client     body: true,
  prisma:client     source_link: true,
  prisma:client     author_name: true,
  prisma:client     author_email: true,
  prisma:client     status: true,
  prisma:client     published_on: true,
  prisma:client     created_at: true,
  prisma:client     updated_at: true,
  prisma:client     posted_at: true,
  prisma:client     is_opinion: true,
  prisma:client     published_in: true,
  prisma:client     truncate_content: true,
  prisma:client     hit_count: true,
  prisma:client     xarid: true,
  prisma:client     newsletter_id: true,
  prisma:client     newsletter: true,
  prisma:client     ebulletin_issue: true,
  prisma:client     highlighted: true,
  prisma:client     pinned: true,
  prisma:client     embed: true,
  prisma:client     attachments: true,
  prisma:client     topics: true,
  prisma:client     locations: true
  prisma:client   }
  prisma:client }) +1ms
  prisma:client Generated request: +2ms
  prisma:client query {
  prisma:client   findManyPost(
  prisma:client     take: 100
  prisma:client     skip: 0
  prisma:client   ) {
  prisma:client     id
  prisma:client     type
  prisma:client     title
  prisma:client     intro
  prisma:client     body
  prisma:client     source_link
  prisma:client     author_name
  prisma:client     author_email
  prisma:client     status
  prisma:client     published_on
  prisma:client     created_at
  prisma:client     updated_at
  prisma:client     posted_at
  prisma:client     is_opinion
  prisma:client     published_in
  prisma:client     truncate_content
  prisma:client     hit_count
  prisma:client     xarid
  prisma:client     newsletter_id
  prisma:client     newsletter {
  prisma:client       id
  prisma:client       published_on
  prisma:client       created_at
  prisma:client       updated_at
  prisma:client     }
  prisma:client     ebulletin_issue
  prisma:client     highlighted
  prisma:client     pinned
  prisma:client     embed
  prisma:client     attachments {
  prisma:client       id
  prisma:client       attachable_id
  prisma:client       attachable_type
  prisma:client       attached_file_name
  prisma:client       attached_content_type
  prisma:client       attached_file_size
  prisma:client       attached_updated_at
  prisma:client       title
  prisma:client       description
  prisma:client       hidden
  prisma:client       image_width
  prisma:client       image_height
  prisma:client       attached_processing
  prisma:client     }
  prisma:client     topics {
  prisma:client       id
  prisma:client       name
  prisma:client     }
  prisma:client     locations {
  prisma:client       id
  prisma:client       continent
  prisma:client       country
  prisma:client       state
  prisma:client     }
  prisma:client   }
  prisma:client }
  prisma:client  +0ms
  prisma:client:libraryEngine sending request, this.libraryStarted: false +8ms
  prisma:client:libraryEngine library already starting, this.libraryStarted: false +1ms
  prisma:client:libraryEngine library started +41ms
  prisma:client:libraryEngine library is already stopping +90ms
  prisma:client:libraryEngine library stopping +1ms
  prisma:client:libraryEngine library stopped +3ms

[janpio]

Using the schema you posted above, I can successfully npx prisma db push it to a PostgreSQL database and then run npx prisma studio. I see in your Loom you are using the Electron version. Can you please try with the CLI one?

[linojon]

yes i've tried it with both. Please let me know if there's any more information i can provide

prisma-studio-error.mp4

.

[janpio]

You could create a small script based on https://github.com/prisma/prisma-examples/blob/latest/javascript/script/script.js and then replace the queries in there with just this one:

await prisma.topic.findMany({
  take: 100,
  skip: 0,
  select: {
    id: true,
    name: true,
    posts: true
  }
  })

This seems to be the failing one from your logs - and I wonder what happens if you execute that manually against that database. If it also fails, that would upgrade your problem from Prisma Studio to Prisma ORM overall.

[linojon]

i can run this query from my app (nextjs) and display the results in a test page. Also i can see the table data in dBeaver

const topics = await prisma.topic.findMany({
    orderBy: { name: 'asc' }
  })

[janpio]

Can you please run the exact query that Studio runs?

[linojon]

Aha! Yes that exact query generates the same error:

prisma:client:fetcher Error: Can't reach database server at `localhost`:`5432`
  prisma:client:fetcher 
  prisma:client:fetcher Please make sure your database server is running at `localhost`:`5432`.
  prisma:client:fetcher     at prismaGraphQLToJSError (D:\Documents\JavascriptProjects\WindActionJS\node_modules\@prisma\client\runtime\index.js:35992:12)
  prisma:client:fetcher     at Object.request (D:\Documents\JavascriptProjects\WindActionJS\node_modules\@prisma\client\runtime\index.js:36358:17)
  prisma:client:fetcher     at async cb (D:\Documents\JavascriptProjects\WindActionJS\node_modules\@prisma\client\runtime\index.js:38673:26)
  prisma:client:fetcher     at async PrismaClient._request (D:\Documents\JavascriptProjects\WindActionJS\node_modules\@prisma\client\runtime\index.js:40859:18)
  prisma:client:fetcher     at async ApiHandler (webpack-internal:///(api)/./pages/api/topics/index.ts:12:20)
  prisma:client:fetcher     at async Object.apiResolver (D:\Documents\JavascriptProjects\WindActionJS\node_modules\next\dist\server\api-utils.js:101:9)
  prisma:client:fetcher     at async DevServer.runApi (D:\Documents\JavascriptProjects\WindActionJS\node_modules\next\dist\server\next-server.js:320:9)
  prisma:client:fetcher     at async Object.fn (D:\Documents\JavascriptProjects\WindActionJS\node_modules\next\dist\server\base-server.js:486:37)
  prisma:client:fetcher     at async Router.execute (D:\Documents\JavascriptProjects\WindActionJS\node_modules\next\dist\server\router.js:228:32)
  prisma:client:fetcher     at async DevServer.run (D:\Documents\JavascriptProjects\WindActionJS\node_modules\next\dist\server\base-server.js:598:29) +40s
error - Error:
Invalid `prisma.topic.findMany()` invocation:


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

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

Here's the query code:

  console.log('in api/topics')
  // const topics = await prisma.topic.findMany({
  //   orderBy: { name: 'asc' }
  // })
  const topics = await prisma.topic.findMany({
    take: 100,
    skip: 0,
    select: {
      id: true,
      name: true,
      posts: true
    }
  })

[linojon]

When i comment out posts: true, it works

[linojon]

I manually dropped and recreated the relations tables , and now it seems to work :) I had migrated the database from an older framework so maybe something was corrupted. Thanks for your patience.

[janpio]

All good, happy we could figure it out 🚀

[linojon]

The problem came back when testing with my full sized database. I've recreated it in my own code. There are thousands of posts tagged with a topic. I dont know if Prisma offers a way to limit the "take" on relations, but i think that's the problem. This query works:

const topics = await prisma.topic.findMany({
    orderBy: { name: 'asc' }
})

This query fails, as above:

  const topics = await prisma.topic.findMany({
    orderBy: { name: 'asc' },
    select: {
      id: true,
      name: true,
      posts: true
    }
  })

It includes a relation query on posts with the following clause:
WHERE "public"."posts"."id" IN ($1,$2,$3,$4,$5 ... followed by thousands of ids which fails with this error:

error - Error:
Invalid `prisma.topic.findMany()` invocation:
  Can't reach database server at `localhost`:`5432`

This query works:

  const topics = await prisma.topic.findMany({
    orderBy: { name: 'asc' },
    select: {
      id: true,
      name: true,
      _count: {
        select: { posts: true }
      }
    }
  })

Maybe Prisma Studio should query for count rather than all the relation records.

[janpio]

Just to confirm: Does this also crash when you run that exact query on the production database via a script?

[linojon]

yes

[janpio]

Can you please open an issue over at https://github.com/prisma/prisma/issues/ then and describe the issue, optimally in a way that makes it reproducible for us? Does not seem to be a Studio only problem then. Thanks.

[linojon]

ok. But it is a Studio problem b/c you could/should change your query to get relation count instead of all the relation records.

[jkomyno]

Closing this in favour of prisma/prisma#8832, please see this comment.

[jkomyno]

We have closed the original issue in prisma@4.4.0, please see this comment.