P
Prisma•6d ago
Uncle

Speeding up DB seeding

I'm wondering if there is a quicker way for seeding my database. I'm working on software to manage self storage. I create leases for each storage unit, then I create invoices from the leases then payment records from those invoices. The leases and invoices take 47 seconds and the payment records take 80 mins. I think this is because I have to update each invoice individually to link it to the created payment record.
6 Replies
Prisma AI Help
Prisma AI Help•6d ago
You chose to compile with the bot. One moment while I piece together your answer…
Uncle
UncleOP•6d ago
my schema: 
model Lease {
  leaseId            String        @id @unique @default(cuid(2))
  customerId         String
  employeeId         String
  addressId          String
  unitNum            String
  price              Float
  discount           Float?
  discountId         String?
  leaseCreatedAt     DateTime      @default(now())
  leaseReturnedAt    DateTime?
  leaseEffectiveDate DateTime
  leaseEnded         DateTime?
  dropboxURL         String?
  anvilEID           String?       @unique
  subscriptionId     String?
  invoices           Invoice[]
  Address            Address       @relation(fields: [addressId], references: [addressId], onDelete: NoAction, onUpdate: NoAction)
  customer           User          @relation("customer", fields: [customerId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  employee           User          @relation("employee", fields: [employeeId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  unit               Unit          @relation(fields: [unitNum], references: [num])
  discountCode       DiscountCode? @relation(fields: [discount, discountId], references: [amountOff, discountId])

  @@unique([leaseId, unitNum, price])
  @@index([leaseId, leaseCreatedAt(sort: Desc)])
}
model Lease {
  leaseId            String        @id @unique @default(cuid(2))
  customerId         String
  employeeId         String
  addressId          String
  unitNum            String
  price              Float
  discount           Float?
  discountId         String?
  leaseCreatedAt     DateTime      @default(now())
  leaseReturnedAt    DateTime?
  leaseEffectiveDate DateTime
  leaseEnded         DateTime?
  dropboxURL         String?
  anvilEID           String?       @unique
  subscriptionId     String?
  invoices           Invoice[]
  Address            Address       @relation(fields: [addressId], references: [addressId], onDelete: NoAction, onUpdate: NoAction)
  customer           User          @relation("customer", fields: [customerId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  employee           User          @relation("employee", fields: [employeeId], references: [id], onDelete: NoAction, onUpdate: NoAction)
  unit               Unit          @relation(fields: [unitNum], references: [num])
  discountCode       DiscountCode? @relation(fields: [discount, discountId], references: [amountOff, discountId])

  @@unique([leaseId, unitNum, price])
  @@index([leaseId, leaseCreatedAt(sort: Desc)])
}
model PaymentRecord {
  paymentNumber    Int           @id @unique @default(autoincrement())
  transactionId    String?       @unique
  invoiceNum       Int?
  customerId       String
  unitNum          String?
  paymentAmount    Float
  employeeId       String?
  payee            String?
  paymentCreated   DateTime      @default(now())
  paymentCompleted DateTime?
  paymentNotes     String?
  deposit          Boolean       @default(false)
  refunded         Boolean       @default(false)
  refundNumber     Int?
  paymentType      PaymentType
  invoice          Invoice?
  customer         User          @relation("customer", fields: [customerId], references: [id])
  receiver         User?         @relation("employee", fields: [employeeId], references: [id])
  RefundRecord     RefundRecord? @relation(fields: [refundNumber], references: [refundNumber])
}
model Invoice {
  invoiceNum       Int            @id @unique @default(autoincrement())
  customerId       String
  employeeId       String?
  leaseId          String?
  invoiceAmount    Float
  invoiceCreated   DateTime       @default(now())
  invoiceDue       DateTime
  paymentRecordNum Int?           @unique
  invoiceNotes     String?
  stripeId         String?        @unique
  deposit          Boolean        @default(false)
  customer         User?          @relation("customer", fields: [customerId], references: [id])
  employee         User?          @relation("employee", fields: [employeeId], references: [id])
  lease            Lease?         @relation(fields: [leaseId], references: [leaseId])
  paymentRecord    PaymentRecord? @relation(fields: [paymentRecordNum], references: [paymentNumber])
}
model PaymentRecord {
  paymentNumber    Int           @id @unique @default(autoincrement())
  transactionId    String?       @unique
  invoiceNum       Int?
  customerId       String
  unitNum          String?
  paymentAmount    Float
  employeeId       String?
  payee            String?
  paymentCreated   DateTime      @default(now())
  paymentCompleted DateTime?
  paymentNotes     String?
  deposit          Boolean       @default(false)
  refunded         Boolean       @default(false)
  refundNumber     Int?
  paymentType      PaymentType
  invoice          Invoice?
  customer         User          @relation("customer", fields: [customerId], references: [id])
  receiver         User?         @relation("employee", fields: [employeeId], references: [id])
  RefundRecord     RefundRecord? @relation(fields: [refundNumber], references: [refundNumber])
}
model Invoice {
  invoiceNum       Int            @id @unique @default(autoincrement())
  customerId       String
  employeeId       String?
  leaseId          String?
  invoiceAmount    Float
  invoiceCreated   DateTime       @default(now())
  invoiceDue       DateTime
  paymentRecordNum Int?           @unique
  invoiceNotes     String?
  stripeId         String?        @unique
  deposit          Boolean        @default(false)
  customer         User?          @relation("customer", fields: [customerId], references: [id])
  employee         User?          @relation("employee", fields: [employeeId], references: [id])
  lease            Lease?         @relation(fields: [leaseId], references: [leaseId])
  paymentRecord    PaymentRecord? @relation(fields: [paymentRecordNum], references: [paymentNumber])
}
the relevant part of seed.ts 
console.log(`🎫 ${leases.length} leases created in ${leaseEndTime.diff(unitEndTime, 'second')} seconds`);
   const invoices: PartialInvoice[] = [];
   for await (const lease of dbLeases){
      const leaseEndDate:Date | null = lease.leaseEnded ?? new Date();
      const months:Date[] = arrayOfMonths(lease.leaseEffectiveDate, leaseEndDate);
      let i = 0;
      for(const month of months) {
         let deposit = false;
         if(i === 0){
            deposit = true;
         }
         const invoice = makeInvoice(lease, month, deposit)
         invoices.push(invoice)
         i++;
      }
   }
   const dbInvoices = await prisma.invoice.createManyAndReturn({
      data: invoices,
   })
   const invoiceEndTime = dayjs();
   console.log(`💰 ${invoices.length} invoices created in ${invoiceEndTime.diff(leaseEndTime, 's')} seconds`);
console.log(`🎫 ${leases.length} leases created in ${leaseEndTime.diff(unitEndTime, 'second')} seconds`);
   const invoices: PartialInvoice[] = [];
   for await (const lease of dbLeases){
      const leaseEndDate:Date | null = lease.leaseEnded ?? new Date();
      const months:Date[] = arrayOfMonths(lease.leaseEffectiveDate, leaseEndDate);
      let i = 0;
      for(const month of months) {
         let deposit = false;
         if(i === 0){
            deposit = true;
         }
         const invoice = makeInvoice(lease, month, deposit)
         invoices.push(invoice)
         i++;
      }
   }
   const dbInvoices = await prisma.invoice.createManyAndReturn({
      data: invoices,
   })
   const invoiceEndTime = dayjs();
   console.log(`💰 ${invoices.length} invoices created in ${invoiceEndTime.diff(leaseEndTime, 's')} seconds`);
const paymentRecords:PartialPaymentRecord[]=[];
   for await (const invoice of dbInvoices){
      const paymentDate = dayjs(invoice.invoiceCreated).add(1, 'months');
      if(dayjs().diff(paymentDate, 'days') < 30) {
         continue;
      }
      const employee = employees[Math.floor(Math.random()*employees.length)];
      const randNum = Math.floor(Math.random()*3);
      const paymentType = PaymentType[Object.keys(PaymentType)[randNum]];
      const record:PartialPaymentRecord = {
            paymentType: paymentType,
            customerId: invoice!.customerId!,
            paymentAmount: invoice.invoiceAmount, 
            employeeId: employee.id,
            paymentCreated: paymentDate.toDate(),         
            paymentCompleted: paymentDate.toDate(), 
            invoiceNum: invoice.invoiceNum, 
            paymentNotes: `Payment for invoice ${invoice.invoiceNum}\n ${invoice.invoiceNotes}`,
            deposit: invoice.deposit
         }      
      paymentRecords.push(record);
   }                              
   const dbPayments = await prisma.paymentRecord.createManyAndReturn({
      data: paymentRecords
   });
   for await(const record of dbPayments){
      await prisma.invoice.update({
         where: {
            invoiceNum: record.invoiceNum!,
         },
         data: {
            paymentRecordNum: record.paymentNumber,
         }
      });
      if(record.deposit){
         const invoice = dbInvoices.find((invoice) => invoice.invoiceNum === record.invoiceNum);
         const lease = dbLeases.find((lease) => lease.leaseId === invoice?.leaseId);
         if(lease?.leaseEnded){
             await makeRefund(record)
         }
      }
   }                 
   const paymentEndTime = dayjs();
   const totalRecords = await countAll();
   console.log(`🧾 ${paymentRecords.length} payment records created in ${paymentEndTime.diff(invoiceEndTime, 'second')} seconds`);
const paymentRecords:PartialPaymentRecord[]=[];
   for await (const invoice of dbInvoices){
      const paymentDate = dayjs(invoice.invoiceCreated).add(1, 'months');
      if(dayjs().diff(paymentDate, 'days') < 30) {
         continue;
      }
      const employee = employees[Math.floor(Math.random()*employees.length)];
      const randNum = Math.floor(Math.random()*3);
      const paymentType = PaymentType[Object.keys(PaymentType)[randNum]];
      const record:PartialPaymentRecord = {
            paymentType: paymentType,
            customerId: invoice!.customerId!,
            paymentAmount: invoice.invoiceAmount, 
            employeeId: employee.id,
            paymentCreated: paymentDate.toDate(),         
            paymentCompleted: paymentDate.toDate(), 
            invoiceNum: invoice.invoiceNum, 
            paymentNotes: `Payment for invoice ${invoice.invoiceNum}\n ${invoice.invoiceNotes}`,
            deposit: invoice.deposit
         }      
      paymentRecords.push(record);
   }                              
   const dbPayments = await prisma.paymentRecord.createManyAndReturn({
      data: paymentRecords
   });
   for await(const record of dbPayments){
      await prisma.invoice.update({
         where: {
            invoiceNum: record.invoiceNum!,
         },
         data: {
            paymentRecordNum: record.paymentNumber,
         }
      });
      if(record.deposit){
         const invoice = dbInvoices.find((invoice) => invoice.invoiceNum === record.invoiceNum);
         const lease = dbLeases.find((lease) => lease.leaseId === invoice?.leaseId);
         if(lease?.leaseEnded){
             await makeRefund(record)
         }
      }
   }                 
   const paymentEndTime = dayjs();
   const totalRecords = await countAll();
   console.log(`🧾 ${paymentRecords.length} payment records created in ${paymentEndTime.diff(invoiceEndTime, 'second')} seconds`);
Prisma AI Help
Prisma AI Help•6d ago
To speed up your database seeding, especially when creating many related records (like leases, invoices, and payment records), you should leverage Prisma's bulk operations such as createMany, updateMany, and deleteMany. These operations are much faster than creating or updating records one by one, as they run in a single transaction and reduce round-trips to the database. For example, use createMany to insert multiple payment records at once instead of looping through and creating them individually. Similarly, updateMany can update multiple invoices in a single call, but note that bulk operations do not support nested writes or relations—so you may need to adjust your data model or perform some linking in a second step if necessary. Here's a basic example for bulk creation: 
await prisma.paymentRecord.createMany({
  data: [
    { /* payment record 1 */ },
    { /* payment record 2 */ },
    // ...
  ]
});
await prisma.paymentRecord.createMany({
  data: [
    { /* payment record 1 */ },
    { /* payment record 2 */ },
    // ...
  ]
});
If you need to update many invoices to link them to payment records, use updateMany, but remember it can't set relation fields directly—so you may need to batch updates by grouping invoices by the same update criteria. Also, you can wrap multiple bulk operations in a $transaction to ensure they succeed or fail together, which is efficient and safe for large seed scripts. For more details and limitations, see the Prisma docs on bulk operations and batch/bulk operations. If you need to seed with raw SQL for even more control and speed, you can use $executeRaw in your seed script, as described in the seeding docs. In summary: use createMany/updateMany for bulk inserts/updates, wrap them in $transaction if needed, and consider raw SQL for complex or highly-performant seeding tasks. This should drastically reduce your seeding time compared to individual record operations. 🤔 Alright, thanks for letting us know that there's still some help needed. A team member will reply to you shortly. In the meantime, please let us know any additional info, questions, or which parts of the answer may need clarification.
Nurul
Nurul•5d ago
How many payment records do you need to create? 80 mins is a lot 😅
Uncle
UncleOP•5d ago
I suppose I could do fewer years
Nurul
Nurul•4d ago
Yes, are you on the latest prisma version?

Did you find this page helpful?