SQLALchemy support for Composite Primary Key/Different index

[jeanlst]

I was looking at the implementation for SQLAlchemy and noticed that it takes only the first primary key of the table

https://github.com/awtkns/fastapi-crudrouter/blob/master/fastapi_crudrouter/core/sqlalchemy.py#L50

What if I have a composite primary key in my table?

[awtkns]

Hi @jeanlst. Thanks for bringing this up.

You are right! This is something that is currently not possible with the SQLA implementation. Is this something that you would be interested in PRing? 🚀

[AthreyVinay]

@jeanlst Do you have a workaround for this? Thanks

[everthought]

I had already started writing a simple auto generator for Fastapi. Then I came across this project.

I created an ItemIndex pydantic class for this. This represents the primarykeys. I then use this in the function with the addition = Depends()

@self.router.delete("", response_model=Message)
def delete_product(id: ItemIndex = Depends(), db: Session = Depends(db_generator)) -> Any:

fastapi_crudrouter

fastapi_crudrouter used as query parameter e.g. in _get_one -> self._pk_type which I don't understand in detail.
Sqlalchemy also accepts a dic in .get(). which in turn can be passed to Sqlalchemy from the pydantic request model with .dict().

If you take the primary keys using the sqlalchamy model, you can certainly do this without an extra pydantic model.

Maybe it's something for a change request.

[everthought]

I took a closer look at fastapi_crudrouter.

        self._pk: str = db_model.__table__.primary_key.columns.keys()[0]
        self._pk_type: type = _utils.get_pk_type(schema, self._pk)

In the init of the SQLAlchemyCRUDRouter class the SQLalchemy model is actually used to read the primary key. Unfortunately, only the first entry from the list is passed as an attribute in _pk. So it would be possible to extract the necessary primary key here. These would then have to be dynamically transferred to the routes. I can't find any reference to this in the fastapi documentation. I'm not really familiar with pydantic yet.
There are some libraries that translate the SQLalchemy models into Pydantic schemas.

One of them is from @tiangolo the creator of fastapi. -> Link