Tanmay Naik
02/16/2021, 7:49 AMTanmay Naik
02/16/2021, 7:49 AMtry {
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)}
Tanmay Naik
02/16/2021, 7:49 AMnikolasburk
Tanmay Naik
02/16/2021, 7:55 AMmodel 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
}
nikolasburk
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
?Tanmay Naik
02/16/2021, 8:17 AMuser = 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 rolesTanmay Naik
02/16/2021, 8:20 AMnikolasburk
// 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 workspacenikolasburk
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.:
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")
}
nikolasburk
nikolasburk
$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.Tanmay Naik
02/16/2021, 10:45 AM