CODEWITHSUNDEEP
postgresqlnext.jsprismanext-auth
Zoha Malik
Zoha Malik

Reputation: 509

Create a record and connect it to an existing record prisma client (1 to 1 relation)

I'm making a Next JS application with prisma and postgres.
I have 2 tables: User and Profile
Their prisma schema structure is as follows:

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?

  // foreign keys
  sessions      Session[]
  profile       Profile?
}

model Profile {
  id                Int      @id @default(autoincrement())
  isAdmin           Boolean  @default(false)
  firstName         String
  lastName          String
  email             String   @unique
  phone             String
  address           String
  gender            String
  image             Bytes
  guardianName1     String
  guardianPhone1    String
  guardianRelation1 String
  guardianName2     String?
  guardianPhone2    String?
  guardianRelation2 String?
  guardianName3     String?
  guardianPhone3    String?
  guardianRelation3 String?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  // foreign keys
  user              User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId            String   @default(cuid()) // relation scalar field (used in the `@relation` attribute above)

  requests      Request[]
}

I'm also using next-auth for the authentication part of this application. So when a user signs up then upon his email verification, next-auth itself adds the user's record to the User table. Till here, there's no issue.

Then, when the user opens his dashboard for the first time, then he's shown a form to fill, upon submission of that form, a record needs to be inserted in the Profile table. As the Profile and User table's are linked, they also need to be connected.

So when the user submits profile details form, I do this:

try {
    const newProfileData = {
        // other fields data here...
        user: {
            connect: { id: '1' } // where User table already has a record with - 'id': 1
        }
    };
    const profile = await prisma.profile.create({ data: newProfileData, include: { user: true } });
    if(profile) { 
        console.log("Created: ", profile);
        res.status(200).json({ msg: 'Successfully Created Profile!' }); 
    }
}
catch(err)
{
    console.log(err);
}

But upon running this code, I get the error:

The change you are trying to make would violate the required relation 'ProfileToUser' between the `Profile` and `User` models.
...
code: 'P2014',
clientVersion: '2.30.3',
meta: {
    relation_name: 'ProfileToUser',
    model_a_name: 'Profile',
    model_b_name: 'User'
}

How can this be solved? I even tried it the other way (i.e. updating the existing User and creating the Profile record connected to it):

const user = await prisma.user.update({
    where: {
        email: req.body.email,
    },
    data: {
        profile: {
            create: {
                // data fields here... (without the user field)
            },
        },
    },
});

But this also gives the same error...
I want to understand why the error comes. Is this not the correct way to create a record for a 1 to 1 relation using prisma-client?

Upvotes: 2

Views: 5123

Answers (2)

Zegarek
Zegarek

Reputation: 25988

The fix:

I think you need to remove @default(cuid()) from the Profile's userId field definition.

model Profile {
//...
  // foreign keys
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String   // relation scalar field (used in the `@relation` attribute above)
//...
}

And also get rid of include: { user: true }:

const profile = await prisma.profile.create({ data: newProfileData});

The explanation:

Profile's user and userId fields don't directly translate to actual columns on the db but are fields that let Prisma handle the link between the relations. It ends up translated to PostgreSQL's

create table profile(
    --...
    userId text references user (id),
    --...
);

And later Prisma will populate that field with your User's id when you issue a user:{connect:{id:'1'}}. What could've happened is when you used @default(cuid()) in userId field definition, you interfered with that process. Now the column ends up as

    userId text default gen_random_uuid() references user (id)

and whenever you create a Profile, a new row gets entered without specifying your own userId (which Prisma probably attempts to do before it'll try to link your User), a random id gets generated that doesn't correspond to any existing User, which violates the reference constraint.

It's that and/or your usage of include: { user: true } messes something up spawning a separate, new user, even though you tried to link your Profile to an existing one. But I would expect that to be just an unwanted side-effect making your code spawn a useless User object and row each time you create a Profile.

Once you get rid of the @default(cuid()) you can also just spawn a standalone, unlinked Profile and then link it to the appropriate User later with an update statement.

Upvotes: 4

Bohemian
Bohemian

Reputation: 424983

Merge the two tables into one, something like:

model User {
  id            String    @id @default(cuid())
  name          String?
  email         String?   @unique
  emailVerified DateTime?
  image         String?
  isAdmin           Boolean  @default(false)
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt
    
  // foreign keys
  sessions      Session[]
}

If you absolutely must have a Profile relation, create a database view:

create view Profile as
select
  id,
  isAdmin,
  name,
  email,
  createdAt,
  updatedAt,
  userId
from user

and map it as a read only relation, but I can’t see the point.

Upvotes: -1

Related Questions