Acceed Heroku postgresql db

General
1

Hi community, I am using node-red v2.2.2 and node.js v14.19.3.

I making a small project, and I'm trying to acceed my data stored on an Heroku postgres app.
My environnement of work is a web site using Next.js and prisma for create and managed the db and heroku for hosting the db.

I've installed node-red-contrib-postgresql and have configure the db connection as my url : user:password@host:port/db.

The issue is that when i'm simply trying to make the request SELECT * FROM user; it return me the name of the user used for the db connection and not the data present in my table named User.

Here is my prisma schema

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

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

model User {
  id       String     @id @default(uuid())
  email    String  @unique
  name     String?
  password String?
  sensors  Sensor[]
}

model Sensor {
  id       String     @id @default(uuid())
  owner    User @relation(fields: [ownerId], references: [id])
  ownerId  String // relation scalar field  (used in the `@relation` attribute above)
  dev_id   String
  nitrate  Int
  phos     Int
  potass   Int
  co2      Int
  temp     Int
  humidity Int
}
2

Hi, welcome to the forum.

Are you able to use another tool to access the database directly? If so, can you run exactly the same query using that tool to see what results you get?

I don't really know Postgress or Horuku but I wonder whether there is a different table in that database that has a similar name? For example, is the table name case-sensitive such that user and User are different tables?

3

Hi, thanks for your quick answer !

I've installed beekeeper studio and connect to my db. The connection seems to be good, my two table apear :
image

But again when I try to do SELECT * FROM User the return of the query is not my User stored in it but again my user used to connect to the db..

image
image896Ă—145 17.3 KB

Do I have maybe to add something in front of the name table ?

And I tried the case sensitivity but it's seems to be not case sensitive. I've wrote User or user and nothing change..

4

OK ! I've found the issue, I just needed to write the name of my table in quote like : SELECT * FROM "User" and it returns me the right thing...

1 Like
5

So I was right :smile_cat:

You may want to consider renaming your User to nr_user or something else to save yourself future confusion. :grinning:

1 Like
6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.