Guide to Mitsuki Repositories: From Zero to Full CRUD

Introduction

The data access layer is the foundation of web applications, sitting at the bottom of the layered architectures that power enterprise code.

Mitsuki's data layer, centered around the @CrudRepository decorator, is designed to help you prototype in early stages as well as orchestrate more complex queries down the line, maximizing your time to focus on business logic. It allows you to define a complete, high-performance data access layer by writing a simple interface, letting the framework handle the implementation.

In this guide, you'll learn how to go from a simple interface to a data access layer, starting with zero-code CRUD and progressively adding more complex logic with Query DSL, native SQL, and custom SQLAlchemy queries.

Domain Objects - @Entity

Before any repository, we need a data model for that repository (i.e. an object that maps onto a database table).

In Mitsuki, this is a @dataclass decorated with @Entity. It tells Mitsuki that this object maps to a database table:

from dataclasses import dataclass
from mitsuki import Entity, Id, Column

@Entity()
@dataclass
class User:
    id: int = Id()
    name: str = ""
    email: str = Column(unique=True, default="")
    active: bool = True

This simple class is all we need to define the users table, complete with an auto-incrementing id, a name, a unique email, and an active status.

Alternatively, you can use a UUID:

from dataclasses import dataclass
from mitsuki import Entity, UUIDv7, Column

@Entity()
@dataclass
class User:
    id: int = UUIDv7()
    name: str = ""
    email: str = Column(unique=True, default="")
    active: bool = True

With support for UUIDv1, UUIDv4, UUIDv5, UUIDv7.

Level 1: Zero-Code CRUD with @CrudRepository

To get a fully functional (basic) data access layer for our User entity, we only need to define an empty class decorated with @CrudRepository:

from mitsuki import CrudRepository
from ..models.user import User

@CrudRepository(entity=User)
class UserRepository:
    """
    A complete data access layer for the User entity.
    All methods listed below are automatically implemented.
    """
    pass

By simply defining this interface, UserRepository is instantly equipped with a full suite of asynchronous CRUD methods:

Method Signature	Description
save(entity: User) -> User	Creates or updates a user.
`find_by_id(id: int) -> User	None`
find_all(page, size, sort_by, sort_desc)	Finds all users, with optional pagination and sorting.
delete(entity: User) -> None	Deletes a user object from the database.
delete_by_id(id: int) -> None	Deletes a user by their primary key.
count() -> int	Returns the total number of users.
exists_by_id(id: int) -> bool	Checks if a user with the given ID exists.

You can inject this repository into a service and use these methods immediately, with no implementation required:

from mitsuki import Service
from ..repositories.user_repository import UserRepository

@Service()
class UserService:
    def __init__(self, repo: UserRepository):
        self.repo = repo

    async def create_new_user(self, name: str, email: str) -> User:
        new_user = User(name=name, email=email)
        # The .save() method is provided by @CrudRepository
        return await self.repo.save(new_user)

Level 2: Query DSL

What about custom queries? For common WHERE clauses, you don't need to write SQL. Mitsuki includes a Query DSL (Domain-Specific Language) that generates queries by parsing method names.

You just define the method signature in your repository; Mitsuki provides the implementation:

from typing import List, Optional
from mitsuki import CrudRepository
from ..models.user import User

@CrudRepository(entity=User)
class UserRepository:
    # Translates to: SELECT * FROM user WHERE email = ?
    async def find_by_email(self, email: str) -> Optional[User]: ...

    # Translates to: SELECT * FROM user WHERE active = ?
    async def find_by_active(self, active: bool) -> List[User]: ...

    # You can combine fields and operators
    # Translates to: SELECT * FROM user WHERE active = ? AND age > ?
    async def find_by_active_and_age_greater_than(
        self, active: bool, age: int
    ) -> List[User]: ...

    # Also works for counting and checking existence
    # Translates to: SELECT COUNT(*) FROM user WHERE active = ?
    async def count_by_active(self, active: bool) -> int: ...

This declarative approach is simple, readable, and covers a wide range of common query patterns without a single line of SQL.

Level 3: Taking Control with @Query

For queries that are too complex or long for the Query DSL, the @Query decorator gives you full control to write your own SQL.

Mitsuki supports two syntaxes:

• SQLAlchemy ORM-style
• Native SQL

SQLAlchemy ORM-style Syntax (Default)

This syntax queries your Entities and attributes, not your database tables and columns. It's the recommended approach as it's database-agnostic and less prone to errors if you happen to change your column names:

from mitsuki import Query

@CrudRepository(entity=User)
class UserRepository:
    # ... other methods

    @Query("""
        SELECT u FROM User u
        WHERE u.age BETWEEN :min_age AND :max_age
        AND u.active = :active
        ORDER BY u.name
    """)
    async def find_active_in_age_range(
        self, min_age: int, max_age: int, active: bool = True
    ) -> List[User]: ...

• FROM User u: We select from the User entity, aliased as u.
• WHERE u.age: We filter on the .age attribute of the entity.
• :min_age: Named parameters in the query are automatically mapped to the method's arguments.

Native SQL Syntax

If you need to use a database-specific feature or prefer writing raw SQL, you can use native=True. This queries the database tables and columns directly:

from mitsuki import Query

@CrudRepository(entity=User)
class UserRepository:
    # ... other methods

    # Example using a PostgreSQL-specific function
    @Query("""
        SELECT * FROM "user"
        WHERE email ILIKE :pattern
        AND created_at > NOW() - INTERVAL '30 days'
    """, native=True)
    async def find_by_email_pattern(self, pattern: str) -> List[User]: ...

• FROM "user": We select from the actual database table named user.
• ILIKE: A PostgreSQL-specific operator for case-insensitive pattern matching.

Level 4: Custom SQLAlchemy Methods

For the highest level of control, especially for dynamically constructed queries, you can simply write a complete method implementation using SQLAlchemy Core, similar to how you would in Flask or FastAPI.

Every repository provides a self.get_connection() method that gives you direct, managed access to a SQLAlchemy connection:

from sqlalchemy import select, and_
from mitsuki.data import get_database_adapter

@CrudRepository(entity=User)
class UserRepository:
    # ... other methods

    async def find_dynamically(
        self, name_pattern: Optional[str], min_age: Optional[int]
    ) -> List[User]:
        """Builds a query dynamically based on which filters are provided."""
        adapter = get_database_adapter()
        user_table = adapter.get_table(User)

        query = select(user_table)
        conditions = []

        if name_pattern:
            conditions.append(user_table.c.name.like(f"%{name_pattern}%"))
        if min_age is not None:
            conditions.append(user_table.c.age >= min_age)

        if conditions:
            query = query.where(and_(*conditions))

        async with self.get_connection() as conn:
            result = await conn.execute(query)
            # Manually map the results back to User objects
            return [User(**dict(row._mapping)) for row in result.fetchall()]

This approach gives you programmatic access to SQLAlchemy's powerful query builder, allowing for complex, conditional logic that would be difficult to express in a static string.

Conclusion

You've seen how Mitsuki's repositories provide a layered approach to data access.

You can start with zero-effort CRUD, graduate to the expressive Query DSL, and take full control with custom SQL or SQLAlchemy when you need it.

This allows you to be highly productive while never sacrificing power.

Next Steps

• Explore the docs: For a full list of supported keywords and operators, check out the official Repositories & Data Layer documentation.
• Build on your work: Now that you have a powerful repository, learn how to use it in a @RestController to expose your data via a REST API.

Happy coding! ❀