Cannot update a table - code: 'P2021'

[fergusmeiklejohn]

Bug description

I cannot update a table in my postgres database with Prisma. I can read it, create but cannot update this particular table.
I can update this table with an SQL query. So I don't believe that the problem is the table or the schema. This seems to be a bug in Prisma.

unknown database error:  { [Error:  
Invalid `prisma.user.update()` invocation: 
 
 
  The table `(not available)` does not exist in the current database.] 
  code: 'P2021', 
  clientVersion: '3.10.0', 
  meta: { table: '(not available)' } } 

How to reproduce

This prisma query fails with the above error:

await db.user.update({
      where: { id: uid },
      data: {
        avatar: uuid,
        images: {
          create: [
            {
              id: uuid,
            },
          ],
        },
      },
    });

This prisma query succeeds (provided I've already created this row):

await db.image.update({
    where: { id: uuid },
    data: {
      userId: uid,
    },
  });

Other queries on the user table succeed: db.user.create etc

Expected behavior

I expect an update query to succeed. And if I does fail I expect an error message that can tell me why it has failed. This error message is telling me the table '(not available)' doesn't exist. Which is just false.

Prisma information

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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


model User {
  insertedAt       DateTime            @default(now()) @map("inserted_at")
  updatedAt        DateTime            @updatedAt @map("updated_at")
  id               String              @id @default(cuid()) 
  firstName        String              @map("first_name") @db.VarChar(40) 
  lastName         String              @map("last_name") @db.VarChar(40) 
  email            String              @unique @db.VarChar(255) 
  title            String?             @db.VarChar(100) 
  bio              String?             @db.VarChar(600) 
  avatar           String?             @db.VarChar(36) 
  images           Image[]
  
  @@map("users")
}


model Image {
  insertedAt DateTime @default(now()) @map("inserted_at")
  updatedAt  DateTime @updatedAt @map("updated_at")
  id         String   @id 
  userId     String   @map("user_id")
  user       User     @relation(fields: [userId], references: [id], onDelete: Restrict, onUpdate: Cascade)

  @@index([userId])
  @@map("images")
}

Environment & setup

• OS: Mac OS 12.2.1
• Database: PostgreSQL
• Node.js version: 16.14.0

Prisma Version

prisma                  : 3.10.0
@prisma/client          : 3.10.0
Current platform        : darwin-arm64
Query Engine (Node-API) : libquery-engine 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/libquery_engine-darwin-arm64.dylib.node)
Migration Engine        : migration-engine-cli 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/migration-engine-darwin-arm64)
Introspection Engine    : introspection-core 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/introspection-engine-darwin-arm64)
Format Binary           : prisma-fmt 73e60b76d394f8d37d8ebd1f8918c79029f0db86 (at node_modules/@prisma/engines/prisma-fmt-darwin-arm64)
Default Engines Hash    : 73e60b76d394f8d37d8ebd1f8918c79029f0db86
Studio                  : 0.458.0
Preview Features        : referentialIntegrity

[fergusmeiklejohn]

seems similar to #10771
The fixes mentioned in that issue: npx prisma db push have not solved this issue

[janpio]

Seems the nested create fails here. Can you maybe enable query logging so we know what queries it tries to run?
https://pris.ly/d/logging + https://pris.ly/d/debugging

Can you please check if it works if you do not set referentialIntegrity = "prisma"? (Can you elaborate why you are using that with a PostgreSQL database btw? Am interested.)

[fergusmeiklejohn]

Thanks! The problem is actually simpler than a nested create. I've simplified things:
This fails:

await db.user.update({
    where: { id: uid },
    data: {
      avatar: uuid,
    },
  });

While the same query as raw sql succeeds:

await db.$queryRaw(
    Prisma.sql`UPDATE public.users SET avatar = ${uuid} WHERE id = ${uid}`
  );

[fergusmeiklejohn]

Logs for failed query:

createMemoryUploadHandler called 
 
prisma:info Starting a postgresql pool with 17 connections. 

prisma:query BEGIN 
 
prisma:query SELECT "public"."users"."id" FROM "public"."users" WHERE "public"."users"."id" = $1 OFFSET $2 
 
prisma:query SELECT "public"."images"."id", "public"."images"."user_id" FROM "public"."images" WHERE (1=1 AND "public"."images"."user_id" IN ($1)) OFFSET $2 
 
prisma:query SELECT "public"."poll_votes"."user_id", "public"."poll_votes"."poll_id" FROM "public"."poll_votes" WHERE (1=1 AND "public"."poll_votes"."user_id" IN ($1)) OFFSET $2 
 
prisma:query SELECT "public"."users"."id", "public"."poll_votes"."user_id", "public"."poll_votes"."poll_id" FROM "public"."poll_votes" WHERE 1=0 OFFSET $1 
 
prisma:query ROLLBACK 
 
catch Error:  
Invalid `prisma.user.update()` invocation: 
 
 
  The table `(not available)` does not exist in the current database. 

[fergusmeiklejohn]

Logs for succeeding $queryRaw:

createMemoryUploadHandler called 
 
prisma:query SELECT 1 
 
prisma:query UPDATE public.users SET avatar = $1 WHERE id = $2 

[janpio]

Thanks - I highly suspect this is a sideeffect of referentialIntegrity = prisma. Can you please check if it works if you do not set referentialIntegrity = "prisma"? (Can you elaborate why you are using that with a PostgreSQL database btw? Am interested.)

[fergusmeiklejohn]

thanks as you suggest,
the fix is to remove referentialIntegrity = "prisma" rebuild the client then put it back in and rebuild the client again :-)

I'm using this is in a postgres DB because I'm in the process of migrating to PlanetScale and I want to confirm things work before migrating

[fergusmeiklejohn]

I wonder if a quick note in the docs about this quick fix would be useful?

[janpio]

No, this a) should not be happening and b) does not really make sense. The Client is now in the same state as before, right? That should behave the same.

[fergusmeiklejohn]

I agree! It's a bug for sure. For the record I did this and it solved the issue:

generator client {
  provider        = "prisma-client-js"
  // previewFeatures = ["referentialIntegrity"]
}

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

npx prisma generate

Then I undid the changes:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["referentialIntegrity"]
}

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

npx prisma generate

So maybe the bug is in the generate process

[janpio]

Can you still reproduce this in any way?

[fergusmeiklejohn]

I can't sorry, it fixed itself by doing the above. If it breaks again in the same way I'll let you know

[fergusmeiklejohn]

Okay so the same problem happened again: I activated logging and get this:

unknown database error:  { [Error:  
Invalid `prisma.user.update()` invocation: 
 
 
  The table `(not available)` does not exist in the current database.] 
  code: 'P2021', 
  clientVersion: '3.11.0', 
  meta: { table: '(not available)' } } 

I can confirm that removing referentialIntegrity = "prisma" solves the problem

[pantharshit00]

Can you please let us know where this database is hosted? Are you using something like Citus under the hood?

[janpio]

ping @fergusmeiklejohn

[fergusmeiklejohn]

sorry! It's Supabase

[pantharshit00]

I wasn't able to reproduce this again. @fergusmeiklejohn are you seeing reoccurrence of this again? Just make sure you are on latest version of Prisma

[fergusmeiklejohn]

Sorry, I'm not using Prisma on the current project so can't check.

[ibrahimhozhun]

I've faced the same issue today with PlanetScale and I saw that my database schema and all the records have been deleted. I don't know if Prisma did that but it was working completely fine last night. Luckily it was just a side project in development but if this is related to Prisma that's a major bug. Also in my case prisma and prisma/client versions weren't matching.

[janpio]

I don't think this issue describes deletion of a full schema and tables, but just an unfortunate error message that says (not available) instead of the actual table name.

What you describe would be a desaster for Prisma, but honestly Prisma Client itself does not even have any code that would drop a schema. The only command that can do this is prisma migrate reset but that needs explicit confirmation.

Did you maybe create a new branch in Planetscale and then not merge that back to your main branch? That is a common oversight with the new and uncommon branching model of PlanetScale.

[avaryx]

I wanted to leave a comment here for others who might end up in this situation, as I found myself here today after everything was working just yesterday. I'm using PM2 to run my dev environment, and after looking over the notes here and trying a few things myself, I found that simply executing pm2 restart <name> did the trick. I've had to do this before when making schema changes, and thankfully it did the trick here as well.

I do not have any logging or other indications as to why this happened. Hopefully this solution (as simple as it might be) works for others as well.

[ctsstc]

I'm trying to do a relational upsert, as mentioned here:

• https://www.prisma.io/docs/orm/prisma-client/queries/relation-queries#update-or-create-a-related-record

Mine isn't much different. Part of me wonders if this is related to mapping in the schema (tables or rows)?

After removing the where on my relationship upsert for the update things seem to be working now 🎉
I'm leaving this here if the above rebuilding doesn't work for people.

I was getting this error btw:

The table (not available) does not exist in the current database.

Now I'm getting a P2002: Unique constraint failed on the fields: (id) on the relational upsert

[janpio]

Create a new discussion please @ctsstc and share the information it is asking for. Thank you.