API for interactive transactions with dependencies between write-operations

[nikolasburk]

There already is a GitHub issue asking for a way to submit multiple mutations within the same HTTP request where all mutations are executed as one transaction.

However, this feature does not allow for creating a transaction where the mutations depend on each other. Here is an example where the second operation depends on the first:

prisma.transaction(async tx => {
  const user = await tx.createUser({
    name,
  })
  // compute some stuff
  await tx.updateOrder(user.id)
  await tx.commit()
})

This is currently not possible with the Prisma API as it would require having a long-running connection where the results of the operations are sent back and forth between Prisma and the database.

It should be considered whether it is helpful to add such an API to Prisma, or whether abstractions like nested mutations and the requested transactional execution of multiple independent transactions are the better approach for Prisma.

[mattwilson1024]

👍 This seems like a very useful (if not essential) feature to me and has been the only real pain point for me so far as a new Prisma user.

As you've identified, there are situations where you need to perform several mutations in sequence, making use of the response data as you go, but with the confidence that it'll be a single transaction that rolls back in the case of a failure. In my opinion, neither nested mutations or transactions for independent mutations seem quite sufficient for these use-cases.

If it helps to have example use-cases for discussion, there's a little more detail about my specific requirement here: https://www.prisma.io/forum/t/transactions-when-the-business-logic-requires-more-than-just-a-nested-insert/5904

[pantharshit00]

@matthewhuang-camelot

Yes, raw sql queries are possible see: https://www.prisma.io/docs/prisma-graphql-api/reference/raw-database-access-qwe4/

[matthewhuang-camelot]

@pantharshit00 Thanks! Just saw that too :D

[savioserra]

Hi guys, any updates on this? :(

[pantharshit00]

@savioserra

We are working on a new client which will have this.

Please check here: https://github.com/prisma/rfcs/blob/new-ts-client-rfc/text/0000-new-ts-client.md

[savioserra]

@pantharshit00 Nice :) Gonna take a look. For now, I'm using stored procedures and Prisma's executeRaw mutation as a workaround.

[arvinlorenz]

hi any update on this?

[iamclaytonray]

Will this be part of Prisma 2?

[Ilyes512]

Looking into ORM's for Node, Prism looks REALLY good (website, documentation, activity on Github, etc). But lacking/missing (manual) migrations transactions is a HUGE no-go for me. I looked at Prisma 2's change-log and article. I only see something about nested migrations transactions, which is not the same (it can't replace manual transactions (grouping)).

edit: Oops, I said migrations in place of transaction 🤦‍♂️

[nikolasburk]

Quoting "Julien Goux" from this public Slack conversation:

I want to do business logic between my write operations within the same transaction.
Business logic could be anything from data validation to third party calls.
This is a really common case of server-side code
Typically I often open a transaction for my whole request lifecycle, with PostgreSQL checkpoints if I need more granular rules (edited)
I must also point that a transaction isn't all about write operations, it's essential being able to work with a set of data frozen at a certain time.
When you do :

const userExists = await db.users.find(newUser.id)
if (!userExists) {
  await db.users.insert(newUser)
}

outside of a transaction, this is unsafe because you can't reliably know if the users are the same set from when you did the select when you're inserting.
maybe this user was created between the 15ms that separate these two statements

[nikolasburk]

I want to respond to the use case from the comment above (with points made by @sorenbs), how to handle situations like this:

const userExists = await db.users.find(newUser.id)
if (!userExists) {
  await db.users.insert(newUser)
}

If the user was created in the 15 ms that separates the two statements, then the insert statement will fail as the id is unique. So there is no need for a transaction in this case.

Further, if the id isn’t unique, then using a transaction wouldn’t solve the problem. Transactions do not block the entire database, so unrelated queries are allowed to proceed concurrently. In this case, both of the inserts would be allowed to proceed because they are unrelated, even if a transaction was used.

[sorenbs]

@Ilyes512,

could you describe your use case in detail?

[mrceperka]

Hello, can we expect this to land any time soon? Beta-9 for example? Thanks for info a your work 🙏

[maxmalov]

Further, if the id isn’t unique, then using a transaction wouldn’t solve the problem. Transactions do not block the entire database, so unrelated queries are allowed to proceed concurrently

Doesn't repeatable read / serializable isolation levels help in such cases?

[pbelbin]

Put it this way: without the ability to intermix database activity and application logic within the transaction scope, you risk the outcome becoming incoherent (ie: nonsense) data.

The point of the transaction scope is to allow the database to help ensure that what you think is happening actually ends up being correctly represented in the database.

It's really important that Prisma be able to do this.

It's well and good to be able to have a hierarchy of related data be added as part of the same transaction (as is supported now) via the 'create()'. And, being able to batch up a number of simple requests as a set, within the same transaction (eg: prisma.transction([write1, write2]). But, this last option probably prepares data to be written using data that could be changing underfoot of the code (not good, unless you can ensure there's only 1 process doing that at a time).

If you can not be sure that data you read at point in time A has not changed by the time you commit the changes, there is a very good probability that eventually, the data you have will be garbage because it is no longer consistent like it should be. Instead, there will be garbage data. Data that's there, but, how it relates to other data is not able to be established.

Having garbage in the database is something we generally want to avoid.

I suppose, it could be said, that this is kind of where the database having a procedural language available sprang from. eg: stored procedures with transactional scope. By allowing us to intermix database activity and application logic in the client application, we avoid having to resort to using stored procedures to achieve the desired result.

[Sytten]

@pbelbin I personally find that having long running transactions encourages the behaviour you just described as bad. People fetch the data at the beginning, do some long operation with and external system and just save the whole object (overriding the data of other concurrent requests). The prisma.transaction is clearly needed because we sometimes need sequential DB operations (one common is to modify Many-to-Many relations where you delete existing relations and recreate new ones), but otherwise one should always use small safe UPDATE. I think having a native way to deal with increments for example is much more important than any long running transaction.

[pbelbin]

@Sytten , I guess it depends on what you think a 'long-running-transaction' is. I agree that having a transaction that is open for hours at a time is not desirable. The longer the transaction is open, the higher the chances are that the transaction will have to be abandoned anyway, because someone else changed something. My concept of a 'long-running-transaction' is much shorter than that. Even if it's just a few seconds, we still need a way to guarantee that data read at point in time A is still valid and consistent by the time the end of the transaction happens. We need the ability to read, do some logic, write/update/delete, (repeat as needed) then commit.

[jared-fraser]

A use case for myself is that I use Cockroach DB so being able to set the AS OF SYSTEM TIME {timestamp} property on a transaction would be amazing. This is to allow multiple reads across tables to be consistent

[maxerbox]

@

Hey everyone! I just want to share that we've come up with a design for this. Please let me know what you think and if you have any questions!

Also if you feel like discussing the proposal in more detail, you can schedule a short call with me here: https://calendly.com/matthewmueller/prisma-transactions.

Looks forward to hearing your thoughts on this hot topic blush

import { PrismaClient } from '@prisma/client'

async function main() {
  const prisma = new PrismaClient()

  const posts = await prisma.$transaction(async (tx) => {
    // we can do our usual queries/await them
    const user = await tx.user.findUnique({
      where: {
        email: 'john@doe.io'
      }
    })

    if (!user) {
      // we can also throw errors of our own
      throw new Error('User does not exist')
    }

    // we can process long running operations
    const copiedPost1 = await fetchData(1)

    // data can be persisted after "long" ops
    const post1 = await tx.post.create({
      data: {
        title: copiedPost1['title'],
        content: copiedPost1['content'],
        author: {
          connect: {
              email: 'john@doe.io'
          }
        }
      }
    })

    // unhandled exceptions trigger rollback
    const post2 = await tx.post.create({
      data: {
        title: undefined as any as string,
        content: undefined as any as string,
        author: {
          connect: {
              email: 'wrong@email.io'
          }
        }
      }
    })

    // we can return any custom return value
    return [post1, post2]
  })
}

// PS: we agree that this is a bad practice
function fetchData(id: string | number) {
  return fetch(`https://myservice.co/api/${id}`)
  .then(response => response.json()) as
  Promise<{title: string, content: string}>
}

Dear Matthew
I read your code, and you said that it is a bad practice to make API calls inside transactions.

I'm looking for documents that state how to write code transactionless, do you have some links/references that state the desavantages of LRT, and how to rewrite code bases ?

Best regards,
Maxerbox.

[felixhuttmann]

Just a comment on terminology:

The term "long-running transaction" is commonly used for transactions that are open for a long time, longer than the 5 seconds proposed here as a default timeout, (see [1, 2]). Examples for long-running transactions are aggregation or analytical queries, billing runs, or bulk data import/export.

In contrast, what is proposed in this issue as a new feature on prisma is called either "client-side transaction" (see [3, 4]) or "interactive transaction" (see [5, 6]) elswhere.

[1] https://dba.stackexchange.com/questions/273887/is-it-always-bad-to-have-long-running-transactions-or-is-this-case-ok
[2] https://www.postgresql.org/message-id/44355FEA.1060702@positivenetworks.net
[4] https://stackoverflow.com/questions/28330777/client-side-vs-server-side-database-transactions/28336342
[5] https://firebase.google.com/docs/firestore/solutions/aggregation#solution_client-side_transactions
[6] https://docs.fauna.com/fauna/current/comparisons/compare-faunadb-vs-postgres.html
[7] https://ebrary.net/64791/computer_science/encapsulating_transactions_stored_procedures

[dpetrick]

Heads-up: We have internal prototypes running and we're aiming for a preview release in 2.29, if no major blockers are uncovered along the way.

[malekjaroslav]

This is great news! How do you plan to handle isolation levels? Will there be some option to set it depending on our use-case? Thanks!

[thebiglabasky]

Just a comment on terminology:

The term "long-running transaction" is commonly used for transactions that are open for a long time, longer than the 5 seconds proposed here as a default timeout, (see [1, 2]). Examples for long-running transactions are aggregation or analytical queries, billing runs, or bulk data import/export.

In contrast, what is proposed in this issue as a new feature on prisma is called either "client-side transaction" (see [3, 4]) or "interactive transaction" (see [5, 6]) elswhere.

Thanks for pointing that out! We discussed this internally and landed on using "Interactive transactions" going forward instead. Client-side transactions felt like it could be wrongly perceived by developers as potentially be executed on the client (ie browser) side which is obviously not the case.

We'll update the materials associated with the feature in various places.

[dpetrick]

This is great news! How do you plan to handle isolation levels? Will there be some option to set it depending on our use-case? Thanks!

We're aware of additional requirements like this in the future. The first version will not expose isolation levels, but we designed transactions in a way that we can add it on top when it's clear what we want to do here.

[pkellner]

I've always thought that if you have to explain terminology, it's probably not ideal. Sounds like I maybe the only one that doesn't know about "interactive transactions", then in that case... named well. If not though, I'd still rather see them called transactions. No one needs to explain to me what a long running transaction is so my thinking is just call them "Transactions" and "Long Running Transactions". IMHO 🙂.

[matthewmueller]

Interactive Transactions are in Preview 🚀

You can enable them by upgrading to the latest Prisma (>=2.29.0) and setting the interactiveTransactions preview feature in your Prisma Schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["interactiveTransactions"]
}

Here's a basic example that shows how you can add custom logic in between two writes within a single transaction:

const video = await prisma.$transaction(async (prisma) => {
  const video = await prisma.video.create({
    data: {
      shortcode: "",
      url: "video.mp4",
    },
  })
  const shortcode = computeShortCode(video.id)
  return prisma.video.update({
    data: { shortcode },
    where: { id: video.id },
  })
})

You can learn more in the documentation. You can share your feedback in this issue.

We recommend you use interactive transactions as a last resort. The Prisma Client's nested write and atomic operations cover most of the use cases for transactions. Interactive transactions will fill in the gaps.

When you do need to reach for interactive transactions, please use them with caution. Keeping transactions open for a long time hurts database performance and can even cause deadlocks. Try to avoid performing unbound network requests and executing slow queries inside your transaction functions. As a rule of thumb: get in and out as quick as possible!

Note that we're tracking the following as separate feature requests:

• Controlling Isolation Levels
• Select for Update

[u007]

why am i getting this error with the samle code?

Argument of type '(prisma: any) => Promise<any>' is not assignable to parameter of type 'PrismaPromise<any>[]'.\n  Type '(prisma: any) => Promise<any>' is missing the following properties from type 'PrismaPromise<any>[]': pop, push, concat, join, and 27 more.",

[ravshansbox]

why am i getting this error with the samle code?

Argument of type '(prisma: any) => Promise<any>' is not assignable to parameter of type 'PrismaPromise<any>[]'.\n  Type '(prisma: any) => Promise<any>' is missing the following properties from type 'PrismaPromise<any>[]': pop, push, concat, join, and 27 more.",

You are providing promise where array of promises is expected.

[Filip3Dev]

Guys, i'm getting this error:

TypeError: promises is not iterable at PrismaClient.$___transactionInternal (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/@prisma/client/runtime/index.js:37853:23) at PrismaClient.$___transaction (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/@prisma/client/runtime/index.js:37881:21) at PrismaClient.$transaction (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/@prisma/client/runtime/index.js:37889:21) at /home/machado/Documents/projetos/seduc/iseduc-matricula/service.js:20:32 at dispatch (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-compose/index.js:42:32) at /home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-router/lib/router.js:372:16 at dispatch (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-compose/index.js:42:32) at /home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-compose/index.js:34:12 at dispatch (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-router/lib/router.js:377:31) at dispatch (/home/machado/Documents/projetos/seduc/iseduc-matricula/node_modules/koa-compose/index.js:42:32)

Here is my code:

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();
const Router = require('koa-router');

exports.applyViews = function(app) {
  const router = new Router({
    prefix: `/api/`,
  });

  router.get('/', async ctx => {
    try {

      const foo = await prisma.$transaction( async (_prisma) => {
        const pessoa = await prisma.pI_ACD_Pessoa.findFirst();
        const aluno = await prisma.pI_ACD_Aluno.findFirst();
        return { pessoa, aluno  };
      })
      
      ctx.body = { foo };
    } catch (error) {
      console.log(error);
      ctx.body = error;
    }
  });

  app.use(router.routes()).use(router.allowedMethods());
};

"devDependencies": {
    "@types/node": "^16.10.1",
    "koa": "^2.13.3",
    "prisma": "^3.1.1",
    "ts-node": "^10.2.1",
    "typescript": "^4.4.3"
  } 

Someone can help me?

[janpio]

@Filip3Dev Please open a new issue and fill the bug issue template. Thanks.

[scefali]

error - TypeError: promises is not iterable

I'm getting this even with version 3.6.0 and interactiveTransactions in my config

[janpio]

@scefali Please open a new issue and fill the bug issue template. Thanks.

[leandrofinger]

@matthewmueller Seems like a good start. Another proposal would be a way to get the tx without having to be in the callback. Something like:

async function main() {
  const prisma = new PrismaClient()

  const tx = await prisma.$begin()
  const user = await tx.user.findUnique({
    where: {
      email: 'john@doe.io'
    }
  })

  if (!user) {
    await tx.$rollback()
    throw new Error('User does not exist')
  }

  const post1 = await tx.post.create({
    data: {
      title: copiedPost1['title'],
      content: copiedPost1['content'],
      author: {
        connect: {
            email: 'john@doe.io'
        }
      }
    }
  })

  await tx.$commit()
}

I'd love to have that approach. It'll allow us to use transactions for tests:

describe('some test', () => {
   let trx: Transaction;

   beforeEach(async () => {
      trx = await prisma.$begin();
      jest.mock('./prisma', () => trx);
   });

   afterEach(async () => {
      await trx.$rollback();
   });

   it('.....', () => {
      // ....
   });
});

[kasir-barati]

I support fatum's comment. It can easily separate transaction implementation and business logic.

In my product which uses TypeORM, I have transaction decorator like below. I would like to achieve something like this with Prisma, too.

I hope this helps you.

export const TypeORMTransaction = (): ((
  target: unknown,
  propKey: string,
  descriptor: PropertyDescriptor
) => PropertyDescriptor) => {
  return (target: unknown, propKey: string, descriptor: PropertyDescriptor): PropertyDescriptor => {
    const original: Function = descriptor.value;

    descriptor.value = async (...args: Record<string, unknown>[]): Promise<unknown> => {
      const queryRunner = await someFunctionGetQueryRunner(); // This function returns cached QueryRunner
      // Start transaction
      await queryRunner.startTransaction();
      let result: any;
      try {
        result = await original.apply(target, args);
        // Commit
        await queryRunner.commitTransaction();
      } catch (e) {
        // Rollback
        await queryRunner.rollbackTransaction();
        throw e;
      } finally {
        // Release query runner
        await queryRunner.release();
      }
      return result;
    };
    return descriptor;
  };
};

export class Foo {
  @TypeORMTransaction()
  static async SomeBusinessLogic(input: Input): Promise<Output> {
    ...
  }
}

did you found any proper solution for this situation, Separating transactions from business logic. Or a good enough compromised solution?

Thanks regardless

[dougecomp]

@matthewmueller Seems like a good start. Another proposal would be a way to get the tx without having to be in the callback. Something like:

async function main() {
  const prisma = new PrismaClient()

  const tx = await prisma.$begin()
  const user = await tx.user.findUnique({
    where: {
      email: 'john@doe.io'
    }
  })

  if (!user) {
    await tx.$rollback()
    throw new Error('User does not exist')
  }

  const post1 = await tx.post.create({
    data: {
      title: copiedPost1['title'],
      content: copiedPost1['content'],
      author: {
        connect: {
            email: 'john@doe.io'
        }
      }
    }
  })

  await tx.$commit()
}

I'd love to have that approach. It'll allow us to use transactions for tests:

describe('some test', () => {
   let trx: Transaction;

   beforeEach(async () => {
      trx = await prisma.$begin();
      jest.mock('./prisma', () => trx);
   });

   afterEach(async () => {
      await trx.$rollback();
   });

   it('.....', () => {
      // ....
   });
});

As soon as the prisma allows the possibility of getting the transaction reference, that would be great! Just like @leandrofinger exemplified.

On my case, i can't open a database transaction on my API endpoint without coupling my interface and prisma implementations. On Knex or TypeORM was very simple. Just like this snippet: https://github.com/rmanguinho/advanced-node/blob/aa69e98291b9f63c4ea0f7c19a551818b4208a7e/src/infra/repos/postgres/helpers/connection.ts

[capaj]

the default timeout of 2000 ms is quite low. I suspect many people who are reporting issues like this: #13713

might actually be running into timeouts.
Would be worth increasing to something like 30 seconds IMHO.
Also printing some kind of log/warning when timeout happens would be amazing-at least on dev.

[capaj]

for now, I've worked around this using this snippet:

// helper, because the default prisma transaction timeouts are too small
export const prismaTransaction = <R>(fn: (prisma: Prisma.TransactionClient) => Promise<R>) => {
  return prismaClient.$transaction(fn, {
    timeout: 30000,
    maxWait: 60000
  })
}

but we need a way to set these timeouts when we instantiate a prisma client

[millsp]

Hey @capaj, I think it's a nice idea would you mind opening a feature request?

[terryli0095]

@matthewmueller Seems like a good start. Another proposal would be a way to get the tx without having to be in the callback. Something like:

async function main() {
  const prisma = new PrismaClient()

  const tx = await prisma.$begin()
  const user = await tx.user.findUnique({
    where: {
      email: 'john@doe.io'
    }
  })

  if (!user) {
    await tx.$rollback()
    throw new Error('User does not exist')
  }

  const post1 = await tx.post.create({
    data: {
      title: copiedPost1['title'],
      content: copiedPost1['content'],
      author: {
        connect: {
            email: 'john@doe.io'
        }
      }
    }
  })

  await tx.$commit()
}

This syntax allows for easier testing and more freedom with business logic.
the concern with "transactions being left open" can be mitigated in the context of an HTTP request or a worker session by doing a clean-up at the end of request or session