🚀 SQLModelRepo - A simple CRUD util for SQLModel

SQLModelRepo is a simple and powerful repository pattern implementation for managing database interactions using the SQLAlchemy-backed SQLModel ORM. It abstracts common database tasks (CRUD - Create, Read, Update, Delete) 🛠️, making data manipulation and access easier, with support for filtering, pagination, and session management 🎯.

🎯 Features

• 🏷️ Generic Repository: Perform Create, Read (single or multiple), Update, and Delete (CRUD) operations effortlessly.
• 🔄 Session Management: Automate session reuse or creation, ensuring efficient transaction handling.
• 🔍 Filtering & Pagination: Easily filter and paginate results.
• ♻️ Partial Updates: Update records partially using SQL.

📦 Installation

pip install sqlmodel_repo

Ensure you have sqlmodel and other dependencies installed.

🚀 Usage

Define your SQLModel Repository 🏗️

Create an SQLModel class representing your database table.

...
from sqlmodel_repo import SQLModelRepo

class User(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    username: str
    email: str
    extra_metadata: dict = Field(sa_column=Column(JSON))
    
users_repo = SQLModelRepo(model=User, db_engine=engine)

Basic Operations 🛠️

Create a Record ✍️

You can easily create a new record using create.

new_user = users_repo.create(username="john_doe", email="john@example.com")

Retrieve by ID 🆔

Fetch a record by its ID using get_by_id.

user = users_repo.get_by_id(new_user.id)

Update a Record 🔄

Modify a record and call save to persist your changes.

user.email = "john_new@example.com"
users_repo.save(user)

Or, perform partial updates directly with update(id, **kwargs):

users_repo.update(user.id, email="updated_email@example.com")

Delete a Record 🗑️

Easily delete a record by passing the instance to the delete method.

users_repo.delete(user)

Reuse session 🔄

with Session(engine) as session:
    assert users_repo(session).all()

Advanced Querying 🔥

Filtering Records 🔎

Use the filter method to retrieve records meeting specific criteria.

# Filter by username 'john_doe'
users = users_repo.filter(username="john_doe").all()

# Find usernames starting with 'jo'
users = users_repo.filter(User.username.startswith('jo')).all()

Querying Inside JSON Fields 🗂️

from sqlalchemy import cast, String

users = users_repo.filter(cast(User.extra_metadata['some_num'], String) == '99').all()

Paginated Results 📄

Fetch paginated results using paginate or paginate_with_total to retrieve ordered subsets of data.

# Paginate results, sorted by username in descending order
users_paginated, total_count = users_repo.filter().paginate_with_total(
    offset=0, limit=4, order_by="username", desc=True
)

Async 🚅

sqlmodel_repo also has an async version:

pip install sqlmodel_repo[async]

from sqlmodel_repo.async_repo import AsyncSQLModelRepo

⚖️ License

This project is licensed under the MIT License.

---

Enjoy coding and happy querying with SQLModelRepo! 🎉

Author is a lazy person, so this README.md was generated by AI.