I have a signup flow where a user with an "owner" role can create a team, the other user values are sent as an array of objects, and a workspace is created with 1-N relationship. How do I run a transactional query for this flow? I currently have a bug where, the owner user and workspace gets created even if there is an error in any of the team users.

Hey Tanmay 👋 can you share the relevant models for this problem? Theoretically it sounds like this should be solvable with a nested write, but if you share the models we can try to figure this out together 🙂

Hey, Nikolas, sure. I'm using these three. This is the general structure

model Companies {
  company_id                                    Int                   @id @default(autoincrement())
  name                                          String                @unique
  ...
  users                                         Users[]
  workspaces_companiesToworkspaces_initiator_id Workspaces[]          @relation("companiesToworkspaces_initiator_id")
  workspaces_companiesToworkspaces_responder_id Workspaces[]          @relation("companiesToworkspaces_responder_id")
  @@map("companies")
}

model Users {
  user_id        Int            @id @default(autoincrement())
  first_name     String
  last_name      String?
  email          String         @unique
  password       String?
  role           user_role[]
  company_id     Int?
  companies      Companies?     @relation(fields: [company_id], references: [company_id])
  @@map("users")
}

model Workspaces {
  workspace_id                                 Int                  @id @default(autoincrement())
  initiator_id                                 Int
  responder_id                                 Int
 ...
  companies_companiesToworkspaces_initiator_id Companies            @relation("companiesToworkspaces_initiator_id", fields: [initiator_id], references: [company_id])
  companies_companiesToworkspaces_responder_id Companies            @relation("companiesToworkspaces_responder_id", fields: [responder_id], references: [company_id])

  @@map("workspaces")
}

enum user_role {
  CISO
  DPO
  Audit
  Security
  Owner
  Viewer
  Admin
}

Thank you! Can you maybe reformulate what exactly you're trying to do and/or show the exact code that you currently have but that's not working?

I have a signup flow where a user with an "owner" role can create a team, the other user values are sent as an array of objects, and a workspace is created with 1-N relationship. How do I run a transactional query for this flow?

What exactly do you mean with "team" in this context? Also, is it intentional that there are two one-to-many relationships from

Workspaces

to

Companies

?

Okay, so the sign up flow assumes that the user creating an account is the owner, when creating the owner, I'm doing this,

user = await prisma.users.create({
  data: {
    first_name,
    last_name,
    email,
    password: bcrypt.hashSync(password, salt),
    role: 'Owner',
    account_active: false,
    token: createToken(email),
    companies: {
      create: {
        name: company_name,
        type: company_type,
        incorporated_in: new Date(company_incorporated_in).toISOString(),
        standard_baseline: standard_baseline,
        customer_data_type: customer_data_type,
        hosted_on: company_hosted_on,
        regulated_by: company_regulated_by,
        address: company_address
      }
    }
  }
})

And workspaces are basically where teams from two companies collaborate (initiator and responder). It's 1-1. When creating an account, the first workspace created is the original company's workspace, so initiator and responder are the same company. The relation is like this,

Users N-1 Company 1-1 Workspace

When creating a team, the person creating it is the owner, and they need to create two other users (CISO and DPO), and more if they like (Audit and Security). Audit and Security are both passed as arrays of objects. I want to create a transaction where the owner creates a company, then ciso and dpo are created, connecting to the company_id created from earlier, then a workspace is created and connected via company_id, and if audit and security arrays exist, loop through them to create related users with those roles

Ha, this is definitely an interesting use case! Note that Prisma has an opinionated approach to transactions which means that you can't create "long-running transactions" with dependencies between write-operations which makes this case a bit more tricky to handle. Nonetheless, I think this case can be well handled by the Prisma Client API, I'd basically implement it as follows:

// Create owner with company
  const owner = await prisma.user.create({
    data: {
      first_name: 'Alice',
      email: '<mailto:alice@prisma.io|alice@prisma.io>',
      role: 'Owner',
      company: {
        create: {
          name: 'Prisma Corp.',
        },
      },
    },
    include: {
      company: true,
    },
  })


  // Create remaining users and workspace in a transaction
  if (owner.company) {
    const createUsers = prisma.user.createMany({
      data: [
        {
          first_name: 'Bob',
          email: '<mailto:bob@prisma.io|bob@prisma.io>',
          role: 'CISO',
          company_id: owner.company.company_id,
        },
        {
          first_name: 'Bob',
          email: '<mailto:bob@prisma.io|bob@prisma.io>',
          role: 'CISO',
          company_id: owner.company.company_id,
        },
        // you can add `Audit` and `Security` users here
      ],
    })

    const createWorkspace = prisma.workspace.create({
      data: {
        initiator_id: owner.company.company_id,
        responder_id: owner.company.company_id,
      },
    })

    await prisma.$transaction([
      createUsers,
      createWorkspace
    ])

  }

With this approach, the remaining users and the workspace get created inside of a transaction, so the bug (where the owner, company and workspace get created even if the query for other users fails) should now be eliminated. Does this fulfill your requirements? Unfortunately I don't see a way how the initial create can be wrapped in the same

$transaction

because that would introduce a "dependency" between the writes (i.e., the creation of the CISO and DPO users depend an a valid company ID, so this can't be added inside

$transaction

). You could also try to do the initial nested write starting with

company

or

workspace

but I don't think this solves the issue that at some point you depend on the

id

of a previously created record 🤔 An alternative approach might be to start the nested write with the company or the workspace

This looks much cleaner than the loop I implemented, thanks Nikolas! Yeah, the issue still exists, I will consider the UUID way.