Native SQLModel Support

[awtkns]

Discussed in https://github.com/awtkns/fastapi-crudrouter/discussions/108

^{Originally posted by **voice1** September 29, 2021} Is there any intent to support SQLModel? [https://github.com/tiangolo/sqlmodel](https://github.com/tiangolo/sqlmodel)

[nuno-andre]

Hi! FWIW, SQLModel replicates both SQLAlchemy and Pydantic. It seems that supporting it may be very straightforward. This just works (cc #108):

from sqlmodel import SQLModel, Field, func, create_engine
from sqlalchemy.orm import sessionmaker

from fastapi import FastAPI
from fastapi_crudrouter import SQLAlchemyCRUDRouter

# models / db

engine = create_engine(
    'sqlite:///./app.db',
    connect_args={"check_same_thread": False},
)

SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
)

async def get_db():
    session = SessionLocal()
    try:
        yield session
        session.commit()
    finally:
        session.close()

class UpdatePotato(SQLModel):
    color: str

class CreatePotato(UpdatePotato):
    mass: float

class Potato(CreatePotato, table=True):
    # this idiom is no longer needed
    # id: Optional[int] = Field(default=None, primary_key=True)
    id: int = Field(primary_key=True)
    created_at: datetime = Field(default_factory=func.now)
    updated_at: datetime = Field(default_factory=func.now, sa_column_kwargs={'onupdate': func.now()})

SQLModel.metadata.create_all(bind=engine)

# api

router = SQLAlchemyCRUDRouter(
    schema=Potato,
    create_schema=CreatePotato,
    update_schema=UpdatePotato,
    db_model=Potato,
    db=get_db,
)

app = FastAPI()
app.include_router(router)

Regards!

[ChuckMoe]

class Potato(SQLModel, table=True):
    # this idiom is no longer needed due to CreatePotato
    # id: Optional[int] = Field(default=None, primary_key=True)
    id: int = Field(primary_key=True)
    color: str
    mass: float
    created_at: datetime = Field(default_factory=func.now)

class CreatePotato(BaseModel):
    color: str
    mass: float

class UpdatePotato(BaseModel):
    color: str

You don't even need to use the pydantic BaseModel, you could also just use the SQLModel as it also doubles as pydantic model. This enables us to reuse our classes like so:

class BasePotato(SQLModel):
    color: str

class CreatePotato(BasePotato):
    mass: float

class Potato(CreatePotato, table=True):
    id: int = Field(primary_key=True)
    created_at: datetime = Field(default_factory=func.now)

[nuno-andre]

Good point, @ChuckMoe! It's truly impressive the amount of boilerplate that can be reduced with SQLModel and FastAPI-CRUDRouter. I've updated the example with the UpdateModel > CreateModel > BaseModel approach.

[russdot]

Finding this post made my day! I've been able to integrate FastAPI with FastAPI-Users (using SQLAlchemy + asyncio) and now fastapi-crudrouter 👍

Using the Potato schema/models above, I can observe an entire new Potato section in the /docs. However, when I attempt to create a new potato via POST, I'm seeing the following:

pydantic.error_wrappers.ValidationError: 3 validation errors for Potato
response -> id
  none is not an allowed value (type=type_error.none.not_allowed)
response -> created_at
  invalid type; expected datetime, string, bytes, int or float (type=type_error)
response -> updated_at
  invalid type; expected datetime, string, bytes, int or float (type=type_error)

I'm still learning Pydantic - but these fields are all defined in the Potato model and expected to be filled upon creation? Maybe I'm missing something? Any help is greatly appreciated. This extension is fantastic - thank you!

-- edit I just realized I'm using (from FastAPI Users SQLAlchemy full example app/db.py)

engine = create_async_engine(DATABASE_URL, **engine_args)
async_session_maker = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    async with async_session_maker() as session:
        yield session

router = SQLAlchemyCRUDRouter(
    schema=Potato,
    create_schema=CreatePotato,
    update_schema=UpdatePotato,
    db_model=Potato,
    db=get_async_session, # <-- is this a problem?
)

-- edit 2 Darn, I see https://github.com/awtkns/fastapi-crudrouter/pull/121 - I guess that means I can't feed in the AsyncSession just yet..