[Feature Request] Auto-generated Transactions API endpoint

[tmax22]

Zenstack already supports automatic CRUD API with generated react-query hooks(which is insane, thanks!), so for example for Post model:

model Post {
  ...
  title String
}

we can query for Posts from the client using the generated react-query hooks such useFindManyPost()

const posts = useFindManyPost()
const totalPosts = useCountPost()

my request is a special hook, to a special endpoint /mode/$transaction on the generated CRUD API, that would allow a special react-query hook useTransaction.

The usage could be similar to:

const [posts, totalPosts] = useTransaction([
  {
    model: "Post",
    method: "findMany",
    params: {
      where: {
        title: {
          contains: "zenstack",
        },
      },
    },
  },
  {
    model: "Post",
    method: "count",
    // params // optional
  },
]);

which would be directly translated to

const [posts, totalPosts] = await prisma.$transaction([
  prisma.post.findMany({ where: { title: { contains: 'zenstack' } } }),
  prisma.post.count(),
])

our real use case is more complicated, with updates being dependent on the creation of previous records.

let me know what you thinks. and again, thank you for truly awsome framework!

[ymc9]

Hi @tmax22 , I'm glad you found ZenStack helpful and really appreciate your recognition!

I think a transaction hook will be very useful. The main challenge today is that on the backend side, ZenStack-enhanced PrismaClient doesn't support batch transactions yet (interactive ones are supported). But I want to revisit the limitation and see if it's resolveable now with refactors done in V2. If that's added, the sort of transaction you proposed should be easier to implement.

Since you said "our real use case is more complicated, with updates being dependent on the creation of previous records.", does it mean that you'll likely need interactive transactions on the hooks side as well?

[tmax22]

Indeed, our use case necessitates the use of interactive transactions due to the requirement of referencing a recordId that is generated in a subsequent operation.

However, my initial example was overly simplistic and only included read operations. I'm uncertain about the correct approach to handle mutations, those return handlers that must be invoked, as opposed to returning the data object itself.

Furthermore, I find it challenging to conceptualize the appropriate way to define the client API for client-side interactive transactions.

Let's imagine client-side Interactive transaction

looking at the Prisma interactive
transactions example:

// backend example

import {PrismaClient} from '@prisma/client'

const prisma = new PrismaClient()

function transfer(from: string, to: string, amount: number) {
    return prisma.$transaction(async (tx) => {
        // 1. Decrement amount from the sender.
        const sender = await tx.account.update({
            data: {
                balance: {
                    decrement: amount,
                },
            },
            where: {
                email: from,
            },
        })

        // 2. Verify that the sender's balance didn't go below zero.
        if (sender.balance < 0) {
            throw new Error(`${from} doesn't have enough to send ${amount}`)
        }

        // 3. Increment the recipient's balance by amount
        const recipient = await tx.account.update({
            data: {
                balance: {
                    increment: amount,
                },
            },
            where: {
                email: to,
            },
        })

        return recipient
    })
}

async function main() {
    // This transfer is successful
    await transfer('alice@prisma.io', 'bob@prisma.io', 100)
    // This transfer fails because Alice doesn't have enough funds in her account
    await transfer('alice@prisma.io', 'bob@prisma.io', 100)
}

main()

how would you call transfer-like operation from the client-side?

we can imagine transaction=useTransaction() hook that would wrap the transaction logic and provide a way to call it
from the client,
however, it's completely unclear what API calls does transaction(...) would make.
something like this:

// client example

const MyComponent = () => {
    const transaction = useTransaction()

    const handleTransfer = async () => {
        try {
            await transaction(async (tx) => {
                // 1. Decrement amount from the sender.
                const sender = await tx.account.update({
                    data: {
                        balance: {
                            decrement: amount,
                        },
                    },
                    where: {
                        email: from,
                    },

                })

                // 2. Verify that the sender's balance didn't go below zero.
                if (sender.balance < 0) {
                    throw new Error(`${from} doesn't have enough to send ${amount}`)
                }

                // 3. Increment the recipient's balance by amount
                const recipient = await tx.account.update({
                    data: {
                        balance: {
                            increment: amount,
                        },
                    },
                    where: {
                        email: to,
                    }
                })

                return recipient
            })
        } catch
            (e) {
            console.error(e)
        }
    }

    return (
        <button onClick={handleTransfer}>Transfer</button>
    )
}

One potential strategy involves initiating a new interactive transaction on the server when the transaction(...) function is called. This could be achieved by making an HTTP request to a /model/transaction endpoint, which would start a transaction and a WebSocket session and return a tx context.

The tx object would serve as a proxy to the Prisma client methods on the server (such as update, create, etc.). This proxy would understand that a call like tx.account.update(...) corresponds to a prisma.account.update(...) operation on the server.

Each method invocation on tx, such as tx.account.<method>(...), would be transmitted to the server via the WebSocket connection. The server would then execute the corresponding operation and send the result back to the client.

The tx object on the client side would also be responsible for handling any errors that occur during the transaction on the server. If an error is detected, it would throw an exception on the client side and cancel the transaction.

Once the asynchronous function passed to transaction(...) completes, the WebSocket session would be closed.

While using a WebSocket might pose challenges when deploying on serverless platforms, it could be a viable approach in this case. The WebSocket session would only remain open for the duration of the transaction, and would be closed once the asynchronous function finishes execution.

Implementing this approach would certainly be complex and would require further research to make the best design decisions. However, it appears to be a feasible solution for handling interactive transactions from the client side.

Let me know your thoughts, thanks!

[ymc9]

Hi @tmax22 , sorry for the late response, and thanks for the detailed elaboration of your thoughts! I agree having interactive transactions in the frontend will be really cool (with a magical feeling too 😄).

I see we'll have to use WebSocket to proxy back and forth. My main concern is this can potentially leave a server-side transaction hanging for a long time due to connection interruption, which can in turn cause unexpected database performance degradation or locking.

I'm wondering if it's simpler to just implement the logic as an API router or server action (if you're using Next.js) and use the ZenStack-enhanced PrismaClient in it for access control. Is it feasible for you? I understand there's no strong-typed frontend hooks support for this approach, but maybe we can continue exploring some options along this route if it sounds sensible.

[tmax22]

what do you mean by API router?

your concern about server-side transactions hanging for a long time due to connection interruption can be handled by some kind of timeout limit.

I'm not saying my WebSocket implementation suggestion should be the best one, and maybe there are better approaches.
I think this issue can be left open for now until further research is done, and the best design choices are made.

[ymc9]

what do you mean by API router?

your concern about server-side transactions hanging for a long time due to connection interruption can be handled by some kind of timeout limit.

I'm not saying my WebSocket implementation suggestion should be the best one, and maybe there are better approaches. I think this issue can be left open for now until further research is done, and the best design choices are made.

Sorry, I meant to say API route (or something equivalent if you don't use Next.js). I'm basically thinking whether a complex transaction should be better contained in a real backend routine instead of implemented from the frontend. I guess you've probably already considered such an alternative, but I'd like to understand your considerations,

[Eliav2]

hey @ymc9 I want to play around with this idea and check out how feasible it is.

I've cloned zenstack monorepo and i have a demo of zenstack app (backend+frontend in separate packages).
could you please explain how you are testing zenstack on another app live in development?
usually what i do is that i add the demo packages as another packages in the workspace but it looks like you don't use this approach. also, any important notices when suggesting contributions to zenstack?

[ymc9]

hey @ymc9 I want to play around with this idea and check out how feasible it is.

I've cloned zenstack monorepo and i have a demo of zenstack app (backend+frontend in separate packages). could you please explain how you are testing zenstack on another app live in development? usually what i do is that i add the demo packages as another packages in the workspace but it looks like you don't use this approach. also, any important notices when suggesting contributions to zenstack?

Sorry that I missed this comment @Eliav2 .

For full-stack experiments, I usually have a standalone project aside and copy over updated js files or make a symlink. I think it's a good idea to have a set of demo packages in the repo for both experimenting and learning. I'll find some time to try it out.

There's a very simple contributing guide here, which only covers the basics: https://github.com/zenstackhq/zenstack/blob/main/CONTRIBUTING.md

Do you plan to implement a transaction API at the server adapter layer? I'm very interested in knowing more about your thoughts. Thanks!

[Eliav2]

I've already taken the approach of a "nested" pnpm workspace with a demo project containing git sub module for my zenstack fork, and including zenstack workspaces in the upper demo workspace. It provides awesome development environment and immidiate reflection of changes in zenstack(besides typescript which causes me some problems just yet). You can check it out here.
https://github.com/Eliav2/zenstack-warehouse-demo/tree/zenstack-dev

I haven't had the time to seriously implement it yet, by my approach is to add another endpoint to the zenstack server adapter at /transaction that starts a websocket connection and wait for transaction operations, and generate react query hook which responsible to initiate this socket and passing requests through this socket and terminating the connection at the end of async handler passed at the client side.

I would update when I would have time to work on it. I would also would love to hear your thoughts !

[ymc9]

Nice, the demo structure looks pretty cool!

Got it. I still have the worry that we'll probably allow the frontend to do "too much" 😄. Besides the risk of leaving dangling transactions, I feel non-trivial CRUD is also traditionally considered as backend implementation details, which maybe people often won't feel comfortable to leak to the frontend code? I may be too conservative though.

Alternatively, what do you think about introducing something like "stored procedures"? Basically a function declared in ZModel (so hooks generator knows it), and the implementation registered to the server adapter (so it becomes part of the auto CRUD API). If you use Supabase, the idea is close to its edge functions.

[ymc9]

Btw, for your use case, have you considered using trpc to achieve implementing a backend transaction and call it directly from the frontend?