Theo's Typesafe Cult•2y ago

prisma transaction api

hi, so I figured I need to use the transaction api but I cant seem to get the syntax right. heres what im trying to do: I have a board model and column model, a board can have many columns. When creating a board, I'd want the user to also be able to create columns, so the problem is getting the created board's id to create the columns (need a ref to the board) and so I read about the transaction api but I keep running into problems and I am probably getting the syntax wrong. Would love some help!

29 Replies

Keef•2y ago

await ctx.prisma.$transaction(async (tx) => {
  // In here you work with tx which is basically ctx.prisma in the outside scope.

// If it finishes it'll commit, if it fails it'll rollback

// Just do ur sql stuff in here and ur good 2 go
})

await ctx.prisma.$transaction(async (tx) => {
  // In here you work with tx which is basically ctx.prisma in the outside scope.

// If it finishes it'll commit, if it fails it'll rollback

// Just do ur sql stuff in here and ur good 2 go
})

That should be enough to go with but you can create the board first and use the id returned to create your columns

EylonOP•2y ago

Thank you very much for the reply! I forgot to mention but if I would want to create more than one column, is it okay to use map like this?

.mutation(async ({ ctx, input }) => {
      await ctx.prisma.$transaction(async (tx) => {
        const board = await tx.board.create({
          data: {
            title: input.title,
            userId: ctx.session.user.id,
          },
        });

        const columns = input.columns.map((col) =>
          tx.column.create({
            data: {
              title: col,
              boardId: board.id,
            },
          })
        );
      });
    }),

.mutation(async ({ ctx, input }) => {
      await ctx.prisma.$transaction(async (tx) => {
        const board = await tx.board.create({
          data: {
            title: input.title,
            userId: ctx.session.user.id,
          },
        });

        const columns = input.columns.map((col) =>
          tx.column.create({
            data: {
              title: col,
              boardId: board.id,
            },
          })
        );
      });
    }),

Keef•2y ago

That columns return is gonna be a Promise

EylonOP•2y ago

im wondering if columns wouldnt be an array of promises and if so, do i need to do anyhting?

Keef•2y ago

There might be a better way to create from an array but let me google this one sec With promise array you can read up on Promise.All() which acts as a "barrier" if you've done some multi processor stuff. If not it'll basically wait for it all to finish before continuing its a way to avoid waterfalls if you have non dependent async work Can you show me your schemas for these 2 models @beylon I just want to see the relation so you can pull out all the other stuff if its sensitive

EylonOP•2y ago

model Board {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    title     String
    user      User     @relation(fields: [userId], references: [id])
    userId    String
    columns   Column[]
}

model Column {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    title     String
    board     Board    @relation(fields: [boardId], references: [id])
    boardId   String
    Task      Task[]
}

model Board {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    title     String
    user      User     @relation(fields: [userId], references: [id])
    userId    String
    columns   Column[]
}

model Column {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt
    title     String
    board     Board    @relation(fields: [boardId], references: [id])
    boardId   String
    Task      Task[]
}

its very basic

Brendonovich•2y ago

You may not even need transactions for this, have you tried just using nested writes? Prisma will manage the transaction stuff for you

EylonOP•2y ago

I tried but I would need the board's id to create a column so its not possible

Brendonovich•2y ago

That’s the point - Prisma should automatically insert the board’s id when u create the columns

Keef•2y ago

https://stackoverflow.com/questions/68285222/create-or-update-one-to-many-relationship-in-prisma

Stack Overflow

Create or update one to many relationship in Prisma

I'm trying to update a one to many relationship in Prisma. My schema looks like this model A_User { id Int @id username String age Int bio Strin...

Keef•2y ago

yeah check this out

Brendonovich•2y ago

That’s the point of it being nested, the foreign keys are managed for you

EylonOP•2y ago

 return ctx.prisma.board.create({
   data: {
     title: input.title,
     userId: ctx.session.user.id,
     columns: {
       create: input.columns.map((column) => ({
         title: column,
       })),
     },
   },
 });

 return ctx.prisma.board.create({
   data: {
     title: input.title,
     userId: ctx.session.user.id,
     columns: {
       create: input.columns.map((column) => ({
         title: column,
       })),
     },
   },
 });