Search code examples
node.jspostgresqlprisma

Select from multiple tables gives me an error

I have a query

const a = await prisma.$queryRaw`
SELECT r.name as name, r.profileId as profile, o.lastName as lastName
FROM UserSetting r , User o
WHERE r.userId=o.id
`

When I run that I get relation "userSetting" does not exist even though it does in prisma's model... Even though i have the following relationship

UserSetting

user   Owner @relation(fields: [userId], references: [id])
userId Int

User

UserSetting UserSetting[]

What am I doing wrong and how can I fix it?

Solution

  • Most likely you created your tables and columns with double-quoted identifiers preserving upper-case letters. Now you have to double-quote those for the rest of their lives:

    SELECT r.name, r."profileId" as profile, o."lastName"
FROM   "UserSetting" r, "User" o
WHERE  r."userId" = o.id

    See:

    Oh, and a table named "User" is an accident waiting to happen, as user is also a reserved word.