How to use async properly with SQLModel and FastAPI

Question

The code below is a sample code for a simple API using FastAPI and SQLModel. I have crud functions in a crud.py file. I am trying to implement async in my functions but I get this error:

town = db.execute(select(Town).where(Town.id == town_id)).first()
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'coroutine' object has no attribute 'first'

This is the function:

async def get_town(db: Session, town_id: int) -> Optional[Town]: 
     """ 
     The get_town function returns a town object from the database. 
  
     :param db: Session: Pass the database session into the function 
     :param town_id: int: Filter the town by id 
     :return: An object of the town class 
     """ 
     town = await db.execute(select(Town).where(Town.id == town_id)) 
     town_result = await town.fetchone() 
     print(town_result) 
     return town_result

Though the async implementation works when I try to get all towns:

async def get_towns(db: Session, skip: int = 0, limit: int = 10) -> List[Town]: 
     """ 
     The get_towns function returns a list of towns from the database. 
  
     :param db: Session: Pass the database session to the function 
     :param skip: int: Skip a number of rows in the database 
     :param limit: int: Limit the number of results returned 
     :return: A list of town objects 
     """ 
     query = select(Town).offset(skip).limit(limit) 
     result = await db.execute(query) 
     return result.scalars().all()

Is there a way to resolve the error? I am still new to using SQLModel.

Answer 1

Here you need to await before call first():

town = (await db.execute(select(Town).where(Town.id == town_id))).first()

And here you don't need to await, because fetchone() is not a coroutine:

town = await db.execute(select(Town).where(Town.id == town_id)) 
town_result = town.fetchone()