[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?

[Eliav2]

regarding TRPC, i stopped using it and i don't want require the usage of a specific framework to enable certain feature, and also, using trpc won't enable the 'magic' client side transactions as this issue is suggeting.

marking some progress here, taking express as the first target to implement this:

• i was able to add another option to zenstack middeware enableTransaction that expects the express app:

  app.use(
      '/model',
      ZenStackMiddleware({
          getPrisma: getPrisma,
          zodSchemas: true,
          enableTransaction: { 
              app,
              // server:  // optional, if not provided, it will use the app.listen server 
          },
      }),
  );
  

  this is necessary in order to enable websockets and transactions in this app. setting this option would patch this app server to use websockets on '/model/transaction' endpoint.

  it's also important to note that now we are using 'ws' package to handle websockets, so we will need to add 'ws' as a dependency to zenstack/server. another option is to extract the websocket handling to a separate package, and use it separately from ZenstackMiddleware.

• client side usage as follows:

  function HomeComponent() {
  const transaction = sendTransaction('ws://localhost:3000/model/transaction');
  return (
          <Box sx={{ display: 'flex', flexDirection: 'column', p: 2 }}>
            <Button
                    onClick={() => {
                      transaction(async (tx) => {
                        // imagine this as prisma operations such as tx.user.create({...}) and so on.
                        await tx.create();
                        await tx.update();
                      });
                    }}
            >
              Send
            </Button>{' '}
          </Box>
  );
  }

  this is the basic usage of the client side transaction. transaction call starts an websocket session, and each tx call is async call that sends through the websocket a specific request(with optional payload). after the transaction call ends, the websockets is closed automatically. in this demo, the server returns the name of the method, but it can easily be replaced with call to prisma on the backend. sendTransaction should ideally be imported from zenstack, but currently, its unclear from what package. even if we extract the websocket handling to a separate package, we will then need to install it both on the client and the server. another option is to generate sendTransaction function on zenstack generate command, so it would be dependency in backend but the frontend could just import the generated hook without adding a dependency.

• results: we can see that implementing a sendTransaction function is certainly possible. currently the tx is dummy proxy but it could be replaced with much sophisticated typesafe object which mimics the backend types of prisma object, just on the client side. the reason sendTransaction is implemented as an function and not a hook is because we likely don't want to use caching(e.g. react-query) on transactions, and if we does, it can be easily turn it into hook with cache by using sendTransaction in the fetchFn parameter in react-query.

the current implementation for sendTransaction:

interface TransactionProxy {
    create: () => Promise<void>;
    update: () => Promise<void>;
}

function waitForResponse(socket: WebSocket): Promise<MessageEvent> {
    return new Promise((resolve) => {
        socket.onmessage = (event) => {
            resolve(event);
        };
    });
}

async function sendAndAwaitResponse(socket: WebSocket, message: string): Promise<MessageEvent> {
    // Send the message
    socket.send(message);
    // Wait for the response
    const response = await waitForResponse(socket);
    return response;
}

const sendTransaction = (url: string) => {
    return (transactionHandler: (tx: TransactionProxy) => Promise<void>) => {
        const socket = new WebSocket(url);
        socket.onopen = async (event) => {
            console.log('Connected to server');
            // socket._socket.write(Buffer.from([0xc1, 0x80]));
            await transactionHandler({
                create: async () => {
                    const messageEvent = await sendAndAwaitResponse(socket, 'Create');
                    console.log('Create response:', messageEvent.data);
                },
                update: async () => {
                    const messageEvent = await sendAndAwaitResponse(socket, 'Update');
                    console.log('Update response:', messageEvent.data);
                },
            });
            socket.close();
            console.log('closing!');
        };
    };
};

additions to server (ZenstackMiddleware):

const patchAppWithWS = (app: Application) => {
    const server = http.createServer(app);

    app.listen = function serverListen(...args) {
        return server.listen(...args);
    };
    const wss = new WebSocketServer({
        // server: server
        noServer: true,
    });
    // export const appServer = http.createServer(app);

    wss.on('connection', (ws) => {
        ws.on('message', (message) => {
            console.log('Received:', message);
            ws.send('Message received: ' + message);
        });

        ws.on('close', () => {
            console.log('WebSocket connection closed');
        });

        ws.on('error', (err) => {
            console.error('WebSocket error:', err);
        });
    });
    server.on('upgrade', (request, socket, head) => {
        console.log('upgrade event', request.url);
        if (request.url === '/model/transaction') {
            console.log('handling upgrade in /test-transaction');
            wss.handleUpgrade(request, socket, head, (ws) => {
                wss.emit('connection', ws, request);
            });
        } else {
            socket.destroy();
        }
    });
};

const factory = (options: MiddlewareOptions): Handler => {
    const { modelMeta, zodSchemas } = loadAssets(options);

    const requestHandler = options.handler || RPCApiHandler();

    if (options.enableTransaction) {
        const { app } = options.enableTransaction;
        patchAppWithWS(app);
    }
   ...

This approach comes to you after some unsuccessful(or less elegant) attempts with other approaches.

You can see more here https://github.com/Eliav2/zenstack/tree/ft/Transactions-API .

please do answer this question: do you think that this feature should be extracted into separate package?

If you think that this is something that would finally be merge into zenstack, i can keep working on this and show true typesafe prisma proxing from the client with this websocket approch.

[Eliav2]

marking progress here, typesafe client transactions actually works!!

function HomeComponent() {
    const transaction = sendTransaction('ws://localhost:3000/model/transaction');
    return (
        <Box sx={{ display: 'flex', flexDirection: 'column', p: 2 }}>
            <Button
                onClick={() => {
                    transaction(async (tx) => {
                        const res = await tx.product.findFirst();
                        console.log('res', res);
                        // await tx.create();
                    });
                }}
            >
                Send
            </Button>{' '}
        </Box>
    );
}

and in the current state, tx is completely typesafe!

few adjustments yet to be made: currently the websocket connection is created and destroyed for each operation. websocket connection should be opened in the start of transaction call and stay open until last operation ended. also, the operations on the server should be executed under a context of $transaction.

[Eliav2]

another awesome update: WORKS AS EXPECTED for expressjs after improving it more, and few hours of playing with it, it works as expected. you get magic client side typesafe API, and can apply transaction interactively. if an error is occurred the transaction is correctly aborted.

things left to do:

• changing db records with the transactions api does not automatically invalidate react-query cache.
• handle some annoying typescript complains.
• battle test this feature on various deployments environments (e.g. aws serverless lambda)
• implement this other backend environments other then express.
• implement timeout. currently, the transaction would shut down if either a server side error occurs, or if an error on the client occurs(then the client sends last ws msg to server with info about the message and the server aborts the transaction and closes the ws connection). but if the client suddenly disapear we should also throw the transaction.
• security considerations: currently I've only enabled calls with 2 chained methods such tx.user.create (but tx.user won't work) and also ignored methods starts with $.
• enhanced prisma is used so any transaction call is validated against zenstack policy engine.

please take a look at the fork! you should run the backend+frontend with dev scripts on examples/warehouse/frontend and examples/warehouse/backend in the repo. please let me know what you think! i think this feature could be awsome.

[Eliav2]

@ymc9 did you had any repeating error TS5055: Cannot write file 'dist/index.d.ts' because it would overwrite input file. errors in zenstack workspace while running pnpm run build? how did you resolved it?

[ymc9]

Hey @Eliav2 , it's great to see the progress! The result looks very neat!

I've been thinking about this problem too. I love the flexibility this offers to the frontend, but I still have reservations about proxying an entire interactive transaction. I think for most databases, transactions are resource-heavy, and it's recommended that you get in and out as fast as possible. Having the client and server synchronized on a tx can make it run for quite long time due to network latency. This can be especially bad for dbs like SQLite, which only allow a single concurrent writer.

Maybe there can be a middle ground between interactive transactions and batch ones? People need ITX for data dependency and control flow - you need the data returned in a previous step as input for the next step, or you need an if-else condition based on a previous result.

I think we can have an extended version of "batch transactions" that allows these sophistications to some extent. Let me use an example to explain the idea:

Run a transaction to transfer the current balance of accountA to accountB, then close accountA.

The frontend code can look like this (I'm using hooks-style code, but it doesn't have to be):

// `useTransaction` returns a transaction builder and its run state
const { tx, state } = useTransaction();

// build a step for getting accountA's current balance (not executed)
const getCurrent = tx.account.findUniqueOrThrow({
    where: { id: accountA },
    select: { balance: true },
});

// build a step for transferring to accountB (not executed)
// you can use the previous step's result for data dependency: `getCurrent.balance`
const transfer = tx.account.update({
    where: { id: accountB },
    data: { balance: { increment: getCurrent.balance } },
});

// build a step to close accountA (not executed)
const closeAccount = tx.account.delete({ where: { id: accountA } });

// submit the transaction for execution
tx.execute([getCurrent, transfer, close]);

The basic idea is to build a full transaction descriptor and then submit it to the server in one shot. The descriptor won't be as expressive as an ITX, but hopefully, it covers the majority of scenarios without much downside.

The server adapters need to have a new "/transaction" endpoint, as you showed in your code, but a regular HTTP endpoint, not a web socket. Under the hook, the ZenStack-enhanced PrismaClient also needs to provide an API to support this new kind of transaction.

There are still many details to think through, but I just wanted to share these wild thoughts with your and seek your feedback. Thanks!

[ymc9]

@ymc9 did you had any repeating error TS5055: Cannot write file 'dist/index.d.ts' because it would overwrite input file. errors in zenstack workspace while running pnpm run build? how did you resolved it?

No, I haven't seen this one. Is it a Windows environment?

[Eliav2]

@ymc9 it's actually a very good idea, and completely removes the need for WS. i'm still not sure how to implement it though(maybe proxies based solution?). i would try explore this idea during the week when i will have some time on separate branch. i will update soon

No, I haven't seen this one. Is it a Windows environment?

No, WSL env. i keep getting this error everytime i try to buld @zenstackhq/runtime when there is a ./dist folder already exists, and this is quite annoying and interrupting the development flow. i opened a separate discussion for it as it does not related to this feature request, take a look if you get some spair time😊.

[ymc9]

I think it's gonna be quite big a piece of work 😄. Prisma doesn't have such kind of a semi-interactive transaction, so we'll have to implement the infra. Here's what's in my mind:

• The "enhance" call extends the incoming PrismaClient's typing with a new "transaction" overload that takes the data structure as we previously discussed as input.
• At runtime, ZenStack's proxy handles the implementation of this new type of transaction and translate it into a regular interactive transaction.
• The server package extends current RPCAPIHandler to support calling the transaction api via http.
• Finally hooks plugins generate typed mutation for the api.

I feel it's viable just will involve putting many pieces together.

Just out of curiosity. Do you have an immediate need for frontend transaction in a project?

[Eliav2]

Do you have an immediate need for frontend transaction in a project?

All the time, but currently Im just creating new REST handlers on my backend and calling it with the relevant information to create the transaction on the server without zenstack queries/zenstack auto-generated API. Im also using express-typed(ts wrapper that I created) so I get full end-to-end typesafety on my express router. It could be more convenient to just call the transaction from the frontend if zenstack could support it.

The same happens for batch transactions which zenstack does not support(yet), I'm just creating another api endpoint for this request in my backend.

I think that having zenstack support such transactions could make me rely 99.5% on zenstack Middleware and auto-generated API and hooks instead of writing my own REST handlers which is beyond amazing. You define your schema and zenstack gives you a free auto-generated backend.