Prisma•2mo ago

Do transactions work when you nest them?

Hello, I'm trying to figure out if transactions work correctly when you nest them. In my example I've got a couple of pretty complex business-logic-required queries, and each might have their own transactions within them:

import prisma from "./db.server"
export const reorderAllChildEntities = (entity, prismaInstance?) => {
    return (prismaInstance ?? prisma).$transaction(tPrisma => {
         // do stuff with tPrisma (actual code is way to big for this)
    })
}

// in other file
export const deleteEntityAndReorderAllChildEntitiesForParent = ( entity ) => {
    return prisma.$transaction(tPrisma => {
           await tPrisma.entity.delete({where: {id: entity.id}})
           const parentEntity = await prisma.parentEntity.find(
              {where: {id: entity.parentID}}
           )
           await reorderAllChildEntities(parentEntity, tPrisma)
    })
}

import prisma from "./db.server"
export const reorderAllChildEntities = (entity, prismaInstance?) => {
    return (prismaInstance ?? prisma).$transaction(tPrisma => {
         // do stuff with tPrisma (actual code is way to big for this)
    })
}

// in other file
export const deleteEntityAndReorderAllChildEntitiesForParent = ( entity ) => {
    return prisma.$transaction(tPrisma => {
           await tPrisma.entity.delete({where: {id: entity.id}})
           const parentEntity = await prisma.parentEntity.find(
              {where: {id: entity.parentID}}
           )
           await reorderAllChildEntities(parentEntity, tPrisma)
    })
}

If deleteEntityAndReorderAllChildEntitiesForParent fails from inside the transaction within reorderAllChildEntities function, will BOTH transactions be rolled back correctly, or is this bad practice?

6 Replies

RaphaelEtim•2mo ago

Hi @Jon Higger (He / Him) We do have an open feature request to support nested transactions as shown here. I'll advise you structure your code to use a single transaction that encompasses all the operations you want to perform atomically.

export const deleteEntityAndReorderAllChildEntitiesForParent = (entity) => {
  return prisma.$transaction(async (tPrisma) => {
    await tPrisma.entity.delete({ where: { id: entity.id } });
    const parentEntity = await tPrisma.parentEntity.findUnique({
      where: { id: entity.parentID }
    });
   
    // ...
  });
};

export const deleteEntityAndReorderAllChildEntitiesForParent = (entity) => {
  return prisma.$transaction(async (tPrisma) => {
    await tPrisma.entity.delete({ where: { id: entity.id } });
    const parentEntity = await tPrisma.parentEntity.findUnique({
      where: { id: entity.parentID }
    });
   
    // ...
  });
};

This approach ensures that all operations are part of a single transaction and will be rolled back together if any part fails.

GitHub

Issues · prisma/prisma

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server, SQLite, MongoDB and CockroachDB - Issues · prisma/prisma

More Oregano Please•2mo ago

@RaphaelEtim I had the same question as OP and the follow up question is of course: if all my async function admit of an argument that is either the original prisma connection handler or transaction handler (both exposed by the top-level calling function), in theory all my async functions can be run against either the transaction API or the regular, non-transactional API, correct?

Jon Higger (He / Him)OP•2mo ago

This is what I did, I just passed the prisma instance around Here's a function I made that basically allows you to either make a transaction inside of a function, or allow you to pass a prisma instance in and use that as the transaction client

/**
 * If you pass in a prisma client, it will use that client and not create a new transaction.
 * Otherwise it will create a new transaction.
 */
export const transactionOrUseExisting =
  (
    prismaClient?: Prisma.TransactionClient,
    // you can ignore the deferContraints stuff, that's because I need to defer constraints in my transactions specifically for a reordering algorithm
    { deferConstraints = true }: { deferConstraints?: boolean } = {},
  ) =>
  async <T>(
    businessLogicCb: (defPrisma: Prisma.TransactionClient) => Promise<T>,
  ) => {
    if (prismaClient) {
      if (deferConstraints) {
        await runDeferredStatement(prismaClient);
      }
      return businessLogicCb(prismaClient);
    } else {
      return prisma.$transaction(async ($prisma) => {
        if (deferConstraints) {
          await runDeferredStatement($prisma);
        }
        return businessLogicCb($prisma);
      });
    }
  };

/**
 * If you pass in a prisma client, it will use that client and not create a new transaction.
 * Otherwise it will create a new transaction.
 */
export const transactionOrUseExisting =
  (
    prismaClient?: Prisma.TransactionClient,
    // you can ignore the deferContraints stuff, that's because I need to defer constraints in my transactions specifically for a reordering algorithm
    { deferConstraints = true }: { deferConstraints?: boolean } = {},
  ) =>
  async <T>(
    businessLogicCb: (defPrisma: Prisma.TransactionClient) => Promise<T>,
  ) => {
    if (prismaClient) {
      if (deferConstraints) {
        await runDeferredStatement(prismaClient);
      }
      return businessLogicCb(prismaClient);
    } else {
      return prisma.$transaction(async ($prisma) => {
        if (deferConstraints) {
          await runDeferredStatement($prisma);
        }
        return businessLogicCb($prisma);
      });
    }
  };

Which can then be used like this

export const dbFnA = ({
   otherParams,
   prismaInstance // can be undefined
}) => {
    return transactionOrUseExisting(prismaInstance)($prisma => {

     })
}

// That way I either get a transaction when I use dbFnA OR I can also compose it from another fn such that the transaction works across both as if it's one transaction
export const dbFnB = ({
   otherParams,
}) => {
    return prisma.$transaction(async $prisma => {
       const b = await dbFnB({ ...otherParams, prismaInstance: $prisma })
    })
}

export const dbFnA = ({
   otherParams,
   prismaInstance // can be undefined
}) => {
    return transactionOrUseExisting(prismaInstance)($prisma => {

     })
}

// That way I either get a transaction when I use dbFnA OR I can also compose it from another fn such that the transaction works across both as if it's one transaction
export const dbFnB = ({
   otherParams,
}) => {
    return prisma.$transaction(async $prisma => {
       const b = await dbFnB({ ...otherParams, prismaInstance: $prisma })
    })
}

More Oregano Please•2mo ago

Yup, that's very much what I had in mind! I need to test more thoroughly though, in my previous attempt the idea was blocked by a few failing tests. By the way I noticed that prisma: TransactionClient is checked just as prisma: Prisma.Client. Is there a way of making functions that accept only one reject the other?

Jon Higger (He / Him)OP•2mo ago

Only one way, I think you can make something that accepts a full prisma client but not a transaction client, but not vise verce.

More Oregano Please•2mo ago

Seems so indeed.

Gaming

Programming

Do transactions work when you nest them?