Native support of @Transactional in Prisma Client Python

[sigridjineth]

Problem

Currently, Prisma Client Python lacks native support for a @Transactional decorator, which is a common and useful feature in many ORMs and database libraries. This forces developers to manually manage transactions, leading to verbose code and potential errors in transaction management.

Key issues:

1. Lack of a clean, declarative way to define transactional boundaries at the service level.
2. Difficulty in ensuring that all database operations within a method are executed within a single transaction.
3. Increased boilerplate code for managing transactions manually.
4. Potential for inconsistent transaction management across different parts of an application.

Suggested solution

Implement a native @Transactional decorator in Prisma Client Python. This decorator would:

1. Automatically start a transaction before the decorated method is executed, which wraps self.db.tx()
2. Commit the transaction if the method completes successfully.
3. Rollback the transaction if an exception occurs during method execution.

Example usage:

from prisma import Prisma, Transactional

class UserService:
    def __init__(self, db: Prisma):
        self.db = db

    @Transactional()
    async def create_user_with_posts(self, user_data: dict, posts_data: list[dict]):
        user = await self.db.user.create(data=user_data)
        for post_data in posts_data:
            post_data['authorId'] = user.id
            await self.db.post.create(data=post_data)
        return user

This implementation would handle all the transaction management internally, simplifying the developer's code and reducing the chances of transaction-related bugs.

Alternatives

1. Continue with manual transaction management using async with prisma.tx() as tx:.
2. Implement a custom decorator at the application level (current workaround for many developers, refering to here)
3. Use a third-party library for transaction management, although this may not integrate as seamlessly with Prisma.

Additional context

This feature is commonly available in other ORMs and database libraries, such as SQLAlchemy for Spring's @Transactional for Java. Adding this to Prisma Client Python would bring it more in line with developer expectations and best practices in database interaction patterns.

The implementation could potentially leverage Prisma's existing transaction capabilities, wrapping them in a more developer-friendly interface. It would be particularly useful in applications following a service-layer architecture, where transactional boundaries often align with service method boundaries.