Prisma2: use user id in related table in create function

Question

I am trying to create a user that is the child of another user. I am managing this relationship by having a User table where all users, parents and children, are stored. There is a seperate table that just has the id of the child and the id of the parent.

My problem is that when I create a child account I want to create an entry in the Relationship table using the user id that would be created. I am not sure at all how I should go about this.

// schema.sql

CREATE TABLE "public"."Relationship" (
    id SERIAL PRIMARY KEY NOT NULL,
    parent_id INT NOT NULL,
    FOREIGN KEY (parent_id) REFERENCES "User" (id),
    child_id INT NOT NULL,
    FOREIGN KEY (child_id) REFERENCES "User" (id)
)

CREATE TABLE "public"."User" (
    id SERIAL PRIMARY KEY NOT NULL,
    name VARCHAR(128) NOT NULL,
    email VARCHAR(128) UNIQUE,
    password VARCHAR(128) NOT NULL,
    isChild BOOLEAN NOT NULL DEFAULT false
    created_at TIMESTAMP NOT NULL DEFAULT NOW();
);

// CreateChild User mutation

export const createChildAccount = mutationField('createChildAccount', {
  type: 'User',
  args: {
    name: stringArg({ required: true }),
    password: stringArg({ required: true }),
  },
  resolve: async (_parent, { name, password }, ctx) => {
    const userId = getUserId(ctx);
    if (!userId) {
      // TODO -think I might need to throw an error here
      return;
    }
    const user = await ctx.prisma.user.create({
      data: {
        name,
        password,
        ischild: true,
        child: {
          create: { child_id: ???????? },
        },
        parent: {
          connect: {id: userId}
        }
      },
    });
    return user;
  },
});

Should I actually be creating a Relationship and then using that to connect the parent and create the child?

Ryan · Answer 1 · 2020-04-28T07:32:51.093

2

If you are just storing the id of the child and the parent, I would suggest using a self-relation to the same table hainv something like this in the schema

model User {
  id        Int      @default(autoincrement()) @id
  name      String
  parent    User?    @relation("UserToUser", fields: [parent_id], references: [id])
  parent_id Int?     @unique
  createdAt DateTime @default(now())
}

For the same in SQL, it would be as follows

create table "User" (
    createdAt timestamp default now(),
    "id" serial primary key,
    "name" varchar not null,
    parent_id int unique,
    foreign key (parent_id) references "User"("id") on delete set null on update cascade
)

Then your create/update call would be quite simple in the following manner

const parent = await prisma.user.create({
  data: {
    name: 'abc',
  },
})

await prisma.user.create({
  data: {
    name: 'def',
    parent: {
      connect: {
        id: parent.id,
      },
    },
  },
})

edited Apr 28 '20 at 07:32

answered Apr 27 '20 at 15:39

Ryan

5,229
1
20
31

Would that work if a child had multiple parents? Im using this as a practice run for another larger app I am planning and the child will be able to have multiple parents and the parents will be able to have multiple children. Would I just be able to add multiple parent fields? Like `parent1`, `parent2` etc? – Tristan Apr 27 '20 at 23:26
Yes it would be the same. In the schema the parent would be changed as follows `parents User[]` And you could have a M-N self-relation there and fetch all the `parents` – Ryan Apr 28 '20 at 07:33
`M-N self-relation` do you mean a many to many relation? I am VERY new to database stuff. Learning a lot very quickly! – Tristan Apr 28 '20 at 11:08

score 0 · Accepted Answer · answered May 01 '20 at 08:54

In hindsight it was an easy solution. I created the entry in the User and then created an entry in the Relationship table where I connected the parent and child accounts

export const createChildAccount = mutationField('createChildAccount', {
  type: 'User',
  args: {
    name: stringArg({ required: true }),
    password: stringArg({ required: true }),
  },
  resolve: async (_parent, { name, password }, ctx) => {
    const userId = getUserId(ctx);
    if (!userId) {
      return;
    }
    const user = await ctx.prisma.user.create({
      data: {
        name,
        password,
        ischild: true,
      },
    });
    await ctx.prisma.relationship.create({
      data: {
        parent: {
          connect: {
            id: userId,
          },
        },
        child: {
          connect: {
            id: user.id,
          },
        },
      },
    });
    return user;
  },
});

Prisma2: use user id in related table in create function

2 Answers2