Prisma uses a PostgreSQL advisory lock with the magic number ID 72707369 that blocks a new migration if the previous one is still connected and idle. This kind of lock only releases when the connection is fully closed and removed from the table pg_stat_activity (an internal PostgreSQL table that shows all active and idle connections in the current instance).

There is another table, pg_lock that contains all the locks, and with a simple SELECT query, you can find in the objid column the lock with the magic number 72707369.

So, the following query must be run every time you do a migration with Prisma (specifically, after the migration step):

SELECT pg_terminate_backend(PSA.pid)
FROM pg_locks AS PL
    INNER JOIN pg_stat_activity AS PSA ON PSA.pid = PL.pid
WHERE PSA.state LIKE 'idle'
    AND PL.objid IN (72707369);

pg_terminate_backend drops the connection and releases every associated lock. This is hardcoded to the magic number of the Prisma lock, because we don't want to drop every current idle connection.

For more information:

• Prisma Migrate: Advisory Locking
• PostgreSQL 9.27.2: Server Signaling Functions
• PostgreSQL 13.3.5: Advisory Locks
• PostgreSQL 54.12: pg_locks

I realized, that for your specific usecase you could also increment your incrementalReportNumber with an atomic number operation

This avoids the need for a stricter transaction isolation level, because you only run update queries and avoid all read queries:

 const report = await prisma.$transaction(async (prisma) => {
      const school = await prisma.school.update({
        where: { id: schoolId },
        data: {
          incrementalReportNumber: {
            // increment the report number here
            increment: 1,
          },
        },
      });

      const newReport = await prisma.report.create({
        data: {
          publicId: school.incrementalReportNumber,
          schoolId: school.id,
          name: reportName,
        },
      });

      return newReport;
    });

Transactions in Prisma aren't implemented yet. However there is some issues opened in their github about this:

• Transactions for multiple operations (#74)
• Transactions for long running operations with dependencies between write operations (#3846)

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;
});