igorbenav / fastcrud Goto Github PK

Describe the bug or question
Handling something like this scenario:

router = APIRouter(prefix="/user/{user_id}")


@router.get("/posts/{post_id}")
def get_post(user_id: int, post_id: int):
    ...
    return {"user": user_id, "post:": post_id, "text": post_text}

curl localhost:8000/user/1/posts/2

{
  "user": 1,
  "post:": 2
  "text": "sample text"
}

Thank you for the recent update! We appreciate the enhancements and improvements made. It's not critical, but I think it's worth discussing.

Describe the bug or question
The problem lies in encountering an error due to duplicate table names when writing SQL queries using SQLAlchemy. This occurs when there's ambiguity in the query because the same table name is used multiple times without explicitly specifying aliases. As a result, SQLAlchemy fails to process the query correctly, leading to a query execution error.

To Reproduce

booking_join_config = [
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.owner_id == models.UserModel.id,
        join_prefix="owner_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
    JoinConfig(
        model=models.UserModel,
        join_on=models.Booking.user_id == models.UserModel.id,
        join_prefix="user_",
        schema_to_select=schemas.UserBase,
        join_type="inner",
    ),
]

Description
Output: table name "users" specified more than once

FROM bookings 
JOIN users ON bookings.owner_id = users.id 
JOIN users ON bookings.user_id = users.id 

Additional context

FROM bookings 
JOIN users AS owner_users ON bookings.owner_id = owner_users.id 
JOIN users AS user_users ON bookings.user_id = user_users.id 

First of all: I love FastCrud

Is your feature request related to a problem? Please describe.
Using the automatic crud_router functionality I'm missing the option to filter (all) routes on a specific column.
For example the automatic crud router works great when using a Todo model with

id: int
name: str
description: str
completed: bool

However, when I add a user_id to the todo model I cannot find any (advanced) option to filter the automatically generated routes on a value (like the user_id of the currently logged-in user). Currently, I'm manually creating e.g. a fastcrud.get route that manually adds user_id kwarg.

Describe the solution you'd like
I'm not sure what the best option would be but maybe the following crud_router arguments: read_filter, create_filter, update_filter, delete_filter can be added.

Example:

todo_router = crud_router(
       ...
       read_filter={ 'user_id': lambda: request.state.user_id }
       ...
)

Would do something like this under the hood:

todo_crud.get(db, schema_to_select=TodoRead, return_as_model=False, id=id, **read_filter)

Describe alternatives you've considered
Currently manually creating each router with a filter variables. So while this is still very doable it still feels like a lot of repetitive code of you have to add this filter for all CRUD routes. For example:

@todos_fastcrud.get("/{id}", response_model=TodoRead)
async def get_todo_fastcrud(id: int, request: Request, db: AsyncSession = Depends(get_session)):
    user_id = request.state.user_id
    return await todo_crud.get(db, schema_to_select=TodoRead, return_as_model=False, id=id, user_id=user_id)

Additional context
Maybe I'm missing something and this is already implemented?
I see a request for filters in the multi get, but this request is still different I think:
#15

@igorbenav I have more a question about get multi_joined. When i joined 2 table many to one. I can't not create response object in object. Example: I only create like that

"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template_title": "Example text for encryption",
      "template_description": "Example description for encryption template"
    }
  ]

This is response that i want

"data": [
    {
      "id": 0,
      "title": "string",
      "template_id": 0,
      "description": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "created_at": "2024-04-14T16:28:01.403Z",
      "created_by": "string",
      "template":{
              "title": "Example text for encryption",
              "description": "Example description for encryption template"
       }
    }
  ]

Please help me :(

When deleting an object, the following occurs: can't subtract offset-naive and offset-aware datetimes.
Below I wrote the model and parameters that are passed in the SQL query.

Here is model with fields for soft delete

class UserModel(Base):
...
    created_at = Column(DateTime(), default=datetime.now())
    updated_at = Column(
        DateTime(),
        default=datetime.now(),
        onupdate=datetime.now(),
    )
    deleted_at = Column(DateTime())
    is_deleted = Column(Boolean, default=False)

user_crud = FastCRUD(UserModel)
user_router = crud_router(
    session=get_session,
    model=UserModel,
    crud=user_crud,
    create_schema=UserSchema.UserCreate,
    update_schema=UserSchema.UserUpdate,
    path="/users",
    tags=["Users"]
)

parameters:

[SQL: UPDATE users SET updated_at=$1::TIMESTAMP WITHOUT TIME ZONE, deleted_at=$2::TIMESTAMP WITHOUT TIME ZONE, is_deleted=$3::BOOLEAN WHERE parking.id = $4::INTEGER]
[parameters: (datetime.datetime(2024, 2, 22, 2, 54, 42, 666156), datetime.datetime(2024, 2, 22, 2, 55, 1, 607450, tzinfo=datetime.timezone.utc), True, 1)]

I don't quite understand what the problem could be. Could you help me?

Describe the bug or question
Consider the following models:

class Ability(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "abilities"

    name: Mapped[str] = mapped_column(nullable=False)
    strength: Mapped[int] = mapped_column(nullable=False)
    heroes: Mapped[list["Hero"]] = relationship(back_populates="ability")

class Hero(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "heroes"

    name: Mapped[str] = mapped_column(nullable=False)
    ability_id: Mapped[int] = mapped_column(ForeignKey("abilities.id"))
    ability: Mapped["Ability"] = relationship(back_populates="heroes")

When running a nested join query like this:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="ability_", nest_joins=True)

The returned values are:

{
    "data": [
        {
            "name": "Diana",
            "ability": {
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
                "name": "Superstrength",
                "strength": 10,
                "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
        },
    ],
    "total_count": 2,
}

Note how the nested set contains the id twice, once as id and once as id_1 and note that the original field of Hero called ability_id is missing, which is failing when returning the data as the Pydantic model will complain about a missing field.

When setting the join_prefix to something stupid, the attribute is in place correctly but obviously that also changes the name of the nested dict:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="xyz_", nest_joins=True)

{
    "data": [
        {
            "name": "Diana",
            "ability_id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
            "xyz": {
                "name": "Superstrength",
                "strength": 10,
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
        },
    ],
    "total_count": 2,
}

Get Joined methods (get_joined and get_multi_joined) should support multiple models to be joined.

Additional context
First mentioned in #24:

Hello,

car_models = await car_crud.get_multi_joined(
    db,
    join_model=models.CarBrand,
    join_prefix="brand_",
    user_id=credentials["id"],
)

is working well. but what i need to do if i have 2 joined models ?
not only models.CarBrand, but also models.CarBodyType

can you give a short code example?

Thank you!

Error initializing database
Here is a piece of code that relates to the error

async def init_db(engine = engine):
    async with async_session() as session:
        Base.metadata.create_all(engine)

AttributeError: 'AsyncEngine' object has no attribute '_run_ddl_visitor'

How can I resolve this error?

Does FastCRUD work with relationship in SQLAlchemy?

class Car(Base):
    brand_id = Column(Integer, ForeignKey("car_brands.id"), nullable=False)
    body_type_id = Column(Integer, ForeignKey("car_body_types.id"), nullable=False)
    body_type = relationship("CarBodyType", backref="cars")
    brand = relationship("CarBrand", backref="cars")

Description
If you use get_multi, then a list is displayed without support for models that belong to this model

Additional context
The expectation was that if you add a relationship to the SQLalchemy model, then this relationship will also appear in the list after get_multi

Is your feature request related to a problem? Please describe.
I can't find a way to annotate number of related objects using the fastcrud.get_multi(). This is a raw sqlalchemy query I have instead: [note m2m relationship in this specific case]

    searches = await db.execute(
        select(Search, func.count(Video.id))
        .outerjoin(VideoSearchAssociation)
        .outerjoin(Video, VideoSearchAssociation.video_id == Video.id)
        .group_by(Search.id)
    )

Describe the solution you'd like
I'd like to be able to retrieve videos_count from search_crud.get_multi() method and potentialy others. Extending to_select with func.count(Video.id) seems relatively easy, but whole query must be wrapped in a GROUP_BY clause, which seems to be a bit harder to implement in an elegant way. Maybe some CountConfig resembling JoinConfig would be a way to go?

Is your feature request related to a problem? Please describe.
In some cases it is reduntant to calculate a total_count of objects in database. Especially if the database is huge and there are multiple filters in the query, it would make sense to ommit it.

Describe the solution you'd like
Add a flag to get_multi and get_multi_joned methods (possibly something else?) to optionally disable count call to database. The flag should be True by default, to maintain backwards compatibility. I am willing to contribute to that in PR, if getting approved.

Describe the bug or question
As far as I can tell, the soft delete columns are not taken into account when returning entries from the database using any of the get methods.

Description
When using the soft delete columns, they are set correctly when using the FastCRUD.delete() method. When retrieving objects however, it is not taken into account whether the object was soft deleted or not. Maybe this is an intentional design choice and wrong expectations on my side, but I was assuming that by default soft deleted objects would not be returned, potentially with an option to return them by using a parameter.

Describe the bug or question
Image is white and transparent, doesn't work on pypi properly

To Reproduce
Just access https://pypi.org/project/fastcrud/

Is your feature request related to a problem? Please describe.
I would like to be able to filter get_multi endpoint.

Describe the solution you'd like
I want to be filtering and searching in the get_multi endpoint. This is such a common feature in most CRUD system that I consider relevant. This is also available in flask-muck.

Describe alternatives you've considered
Some out-of-the-box solution are out there:
https://github.com/arthurio/fastapi-filter (sqlalchemy + mongo)
https://github.com/OleksandrZhydyk/FastAPI-SQLAlchemy-Filters (specific sqlalchemy)

Additional context
Add any other context or screenshots about the feature request here.
Ifnot being implemented could an example be provided?

Describe the bug or question
When you try running code from examples in documentation, all created endpoints have mandatory query parameter local_kw.

To Reproduce
Just create and run app, using the examples from documentation.

Description
The problem is that in the examples async_session object is used as a session dependency. The __call__ method of this object has **local_kw: Any as a parameter. And FastAPI treats it as mandatory query parameter for that endpoint.

Screenshots

Additional context

Describe the bug or question

The FastCRUD class expects that the model that's passed to it is a subclass of SQLAlchemy's DeclarativeBase. SQLModel, meanwhile, derives a metaclass from SQLAlchemy's DeclarativeMeta and uses that as its base. It therefore isn't a subclass of DeclarativeBase and passing a class derived from SQLModel to FastCRUD fails type checking:

$ pyright mve.py
.../mve.py
  .../mve.py:12:22 - error: Argument of type "type[Store]" cannot be assigned to parameter "model" of type "type[ModelType@FastCRUD]" in function "__init__"
    Type "Store" is incompatible with type "DeclarativeBase"
      "Store" is incompatible with "DeclarativeBase" (reportArgumentType)
1 error, 0 warnings, 0 informations

To Reproduce

The MVE used to produce the above error message. The documentation says this should work, and it probably does actually run, but again, it fails type checking (using "standard" typeCheckingMode):

from typing import Annotated

from fastcrud import FastCRUD
from sqlmodel import Field, SQLModel


class Store(SQLModel, table=True):
    id: Annotated[str, Field(primary_key=True)]
    name: Annotated[str, Field(unique=True)]


StoreCRUD = FastCRUD(Store)

In order for EndpointCreator to work, "id" must be the primary_key column name. It should be in the docs

Describe the bug or question
As far as I can tell, the join documentation is out of date

For example, it shows:

user_tier = await user_crud.get_joined(
    db=db,
    model=Tier,
    join_on=User.tier_id == Tier.id,
    join_type="left",
    join_prefix="tier_",,
    id=1
)

While the code suggests it should be join_model instead of model:

Describe the bug or question
I have 2 models, A and B, in M2M relationship linked via an ABAssociation table. The documentation explains a way to perform left and outer joins using JoinConfig. It lacks details on how to proceed with M2M relationships. Is that possible?

To Reproduce
Please provide a self-contained, minimal, and reproducible example of your use case

class A(SQLModel, table=True):
    id: int | None = Field(primary_key=True, index=True)

class B(SQLModel, table=True):
    id: int | None = Field(primary_key=True, index=True)

class VideoSearchAssociation(SQLModel, table=True):
    a_id: int | None = Field(primary_key=True, foreign_key="a.id")
    b_id: int | None = Field(primary_key=True, foreign_key="b.id")

Description

Screenshots

Additional context

Is your feature request related to a problem? Please describe.
When writing multiple records, if some fail, there is no option to roll back the changes.

Describe the solution you'd like
It should be possible to omit db.commit() within write methods, allowing users to call session commit or rollback externally. This would enable multiple records to be written within the same transaction.

created_record = await crud_xyz.create(db=db, object=record, skip_commit=True)

Is there any specific reason why FastCRUD depends on FastAPI <0.11.0 and is it easy to fix? The FastAPI changelog doesn't outline any breaking changes except for dependencies with "yield" and "except".

Thanks!

Describe the bug or question
In the docs, we have something like

router = crud_router(
    ...
    read_deps=[get_current_user],
    update_deps=[get_current_user],
    ...
)

When actually the correct way would be:

from fastapi import Depends

router = crud_router(
    ...
    read_deps=[Depends(get_current_user)],
    update_deps=[Depends(get_current_user)],
    ...
)

I'd like some opinion if possible on whether we should fix the docs or change the way it works. The latter is more explicit, but I think the former might be simpler for the end user.

If the former is used, stuff like getting non-callable objects or dependencies that are already wrapped must be handled.

Describe the bug or question
Attributes from a joined model are not nested if no join_prefix is set

To Reproduce

Consider two simple models, Hero and Ability

class Ability(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "abilities"

    name: Mapped[str] = mapped_column(nullable=False)
    strength: Mapped[int] = mapped_column(nullable=False)
    heroes: Mapped[list["Hero"]] = relationship(back_populates="ability")

class Hero(Base, UUIDMixin, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "heroes"

    name: Mapped[str] = mapped_column(nullable=False)
    ability_id: Mapped[int] = mapped_column(ForeignKey("abilities.id"))
    ability: Mapped["Ability"] = relationship(back_populates="heroes")

Then, this code does not work as expected:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, nest_joins=True)

Returns this (unrelated columns are removed):

{
    "data": [
        {
            "name": "Diana",
            "ability_id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
            "name_1": "Superstrength",
            "strength": 10,
            "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
        },
    ],
    "total_count": 2,
}

When adding a prefix, it (kinda) works:

heroes = await crud_hero.get_multi_joined(db, join_model=Ability, join_prefix="ability_", nest_joins=True)

Result is looking better

{
    "data": [
        {
            "name": "Diana",
            "ability": {
                "id": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
                "name": "Superstrength",
                "strength": 10,
                "id_1": UUID("6e52176e-8a92-4a8d-b0b3-1fcd55acc666"),
            },
            "id": UUID("8212bccb-ce20-489a-a675-45772ad60eb8"),
        },
    ],
    "total_count": 2,
}

Thanks for this awesome package.

Is your feature request related to a problem? Please describe.
It should be possible to filter records using the IN and NOT IN operators in the get and get_multi functions.

Describe the solution you'd like

db_asset = await crud_users.get(
        db=db, 
        schema_to_select=User, 
        return_as_model=True, 
        id=id, 
        filter=User.id.not_in(ids),
        is_deleted=False,
)

It would be great to utilize logical operators such as and_ and or_.

Describe alternatives you've considered
Currently, one must rely on SQLAlchemy methods to execute such filtering operations.

smt = select(User).where(User.reference_id == ref_id).filter(User.id.not_in(ids))

As you can see on the swagger definition attached to this description, The crud_router create automatically routes with suffix get, create or get_multi...

I would like to implement a more "clean" and standard way of route suffix creation like

• create => POST /api/todos/
• get -> GET /api/todos/{id}
• get_multi -> GET /api/todos/

etc...

But I don't know if there is a way to specify it easily

Anyway, if it's not the case I think it could be a good feature and I will be happy to help on it

Is your feature request related to a problem? Please describe.

I understand that the ability to do pagination with get_multi etc. is considered a feature, but I'd like to be able to get all rows without dealing with that, and there's no simple way to do it.

Describe the solution you'd like

I'd like to be able to specify that I don't want to set a limit on the number of rows to fetch. (Actually, ideally unlimited/unpaginated should've probably been the default, IMO, but I understand that doing that now would be a breaking change since the current default is 100, so I'll settle for it being on by default and being able to turn it off.)

Describe alternatives you've considered

The first thing I tried was setting limit to 0, since I'd noticed that the code checks for limit < 0 as an error but not limit == 0. Instead, what I got was no rows whatsoever, which I suppose is technically what one should maybe expect from saying you want at most zero rows, but also feels useless enough to not be intentional.

I've been able to work around the situation by using count to get the total number of rows and setting limit to the result, but that feels like a gruesome hack that I'd rather avoid.

Additional context

I'm willing to work on a PR for this, and am filing this issue at least in part to see a. whether this would even be welcomed, and b. whether you'd prefer:

1. Turning off pagination in the existing methods by setting limit=0
2. Turning off pagination in the existing methods by setting limit=None
3. Splitting off and having a separate set of methods for unpaginated multi-row gets (regular and joined).

(I suppose an additional benefit of option 3 would be that the methods would just return the rows instead of wrapping them in a dict that the ["data"] needs to get dug out of.)

Is your feature request related to a problem? Please describe.
First of all, thanks for your work and sorry for opening a million issues. This one is more of a debate I assume, as there is no "right" way of doing it. I do have the feeling that the current standard set of endpoints I suboptimal. Basically using the example:

router = crud_router(
    session=get_db,
    model=Ability,
    create_schema=AbilityCreateSchema,
    update_schema=AbilityUpdateSchema,
    path="/abilities",
    tags=["abilities"],
)

Yields these endpoints:

One example is: DELETE /api/v1/abilities/delete/{id} - we already know its deleting because of the HTTP verb - why does it need to be in the path again? The same applies to create (POST), update (PATCH) and get (GET) as well.

Describe the solution you'd like
I'd love to be able to configure something "cleaner" (although I'm fully aware that's very opinionated) like this:

The HTTP Verb here together with the endpoint is quite descriptive in itself (and obviously I should change the default endpoint description, but its just for the demo).

Its not possible to achieve this with customised endpoint names either, as it would yield a double slash even if setting the endpoint name for delete to an empty string.