I have a signup flow where a user with an "owner" ...
# prisma-client
t
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.
I'm using something like this,
Copy code
try {
 await prisma.$transaction([
  user = ...prisma create with nested write,
  user2withdifferentrole = ...prisma create nested connect from previous write,
  async function call to run looped query,
  async function call to create workspace
])
return res.json({success: true})
catch(err){throw Error(error)}
It runs the query successfully but prisma returns an error saying that the transaction must have a prisma client promise
n
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 🙂
t
Hey, Nikolas, sure. I'm using these three. This is the general structure
Copy code
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
}
n
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
?
t
Okay, so the sign up flow assumes that the user creating an account is the owner, when creating the owner, I'm doing this,
Copy code
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
Right now all these queries are independent, which has introduced a bug, where the owner, company and workspace get created even if the query for other users fails
n
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:
Copy code
// 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
side-note: I slightly adjusted your data model to make the models singular and made the relation fields shorter so that they're more "human-readable":
Copy code
model Company {
  company_id            Int         @id @default(autoincrement())
  name                  String      @unique
  users                 User[]
  workspacesAsInitiator Workspace[] @relation("InitiatedWorkspaces")
  workspacesAsResponder Workspace[] @relation("ResponderWorkspaces")

  @@map("companies")
}

model User {
  user_id    Int         @id @default(autoincrement())
  first_name String
  last_name  String?
  email      String      @unique
  password   String?
  role       user_role[]
  company_id Int?
  company    Company?    @relation(fields: [company_id], references: [company_id])

  @@map("users")
}

model Workspace {
  workspace_id Int     @id @default(autoincrement())
  initiator_id Int
  initiator    Company @relation("InitiatedWorkspaces", fields: [initiator_id], references: [company_id])
  responder_id Int
  responder    Company @relation("ResponderWorkspaces", fields: [responder_id], references: [company_id])

  @@map("workspaces")
}
The singular form is important because otherwise it feels a bit strange if you have a field that's not a list but the type is written as a plural form, e.g.:
Copy code
model Users {
  user_id    Int        @id @default(autoincrement())
  company_id Int?
  companies  Companies? @relation(fields: [company_id], references: [company_id]) // this feels inaccurate

  @@map("users")
}
I also just posted your question as a GitHub discussion to share it internally with my colleagues and get their thoughts on how to approach this: https://github.com/prisma/prisma/discussions/5680
Added a quick comment to the Discussion on GitHub: As I hunched, it seems like it's not possible to wrap all the calls inside the same
$transaction
call because there are dependencies on the ID that's generated by the DB 😞 a potential workaround would be to use UUIDs or CUIDs, generate the IDs on the application level and supply it to the
create
calls inside of
$transaction
manually.
t
This looks much cleaner than the loop I implemented, thanks Nikolas! Yeah, the issue still exists, I will consider the UUID way.
🙌 1