Create a record while selecting data from different table in prisma and both have the same relation to the parent table

Question

These are the table

model wallet {
  id             Int  @id @default(autoincrement())
  userId         Int  @unique
  balance        Int  @default(0)
  joining_bonus  Int  @default(0)
  referral_bonus Int  @default(0)
  incentive      Int  @default(0)
  user           user @relation(fields: [userId], references: [id])
}

model transaction {
  id               Int                @id @default(autoincrement())
  userId           Int
  type             String
  amount           Int
  curBalance       Int
  payment_gateway String
  payment_id      String
  status           transaction_status @default(PENDING)
  timestamp        DateTime           @default(now())
  user             user               @relation(fields: [userId], references: [id])
 
}

and i want to execute this query

const transaction = await prisma.transaction.create({
    data: {
        amount: amount,
        type: type,
        payment_gateway: payment_gateway,
        payment_id: payment_id,
        curBalance: {
            //get data from wallet table and select balance field
        },
        user: {
            connect: {
                id: parseInt(req.user.id)
            }
        }
        
    }
});

I want to get the data from wallet table while creating the of the transaction record because if i did this in different queries there can be time difference due to async calls and i can get different value of current Balance.

If this query is possible tell me how or if not how to do this so i can always the latest data.

Answer 1

Prisma currently doesn't support sub-selects or fetching data from another table while creating a record in the way that you are trying to achieve.

In order to accomplish this, you will need to use a transaction to ensure that these operations happen atomically and in the correct order. You would first fetch the current balance for the user, then use that data in the creation of the transaction record.

You can use a Database Transaction and while reading the balance value in the transaction, you could use a database level lock like SELECT FOR UPDATE to prevent it from updating while the transaction completes.

Here's an example of how it could look like:

const userId = parseInt(req.user.id);

// Start a transaction
const result = await prisma.$transaction(async (prisma) => {
  // Lock the row
  const wallet = await prisma.$queryRaw`SELECT * FROM "wallet" WHERE "userId" = ${userId} FOR UPDATE`;

  // Perform your updates...
  const transaction = await prisma.transaction.create({
    data: {
      amount: amount,
      type: type,
      payment_gateway: payment_gateway,
      payment_id: payment_id,
      curBalance: wallet.balance,
      user: {
        connect: {
          id: userId,
        },
      },
    },
  });

  // Return the results
  return transaction;
});