async def upsert_record(record: Record) -> Record:
    async with async_session() as session:
        stmt = insert(Record).values(
            record.to_dict()
        ).on_conflict_do_update(
            index_elements=['id'],
            set_={
                # Just updating the values, nothing important
                ...
            }
        )
        result = await session.execute(stmt)
        await session.commit()

        record = await get_record(result.inserted_primary_key[0])
        return record

I created the following function to upsert records into the sqlite database. Once the record is upserted and commited I use get_record function which uses selectinload to load the related User object

async def get_record(id: int) -> Optional[Record]:
    async with async_session() as session:
        result = await session.execute(
            select(Record)
            .where(Record.id == id)
            .options(
                selectinload(Record.user),
            )
        )
        return result.scalars().one_or_none()

As you can see I also declared special to_dict function inside the Base class, which converts model into a dictionary, to use inside the values() function. Its worth noting that to_dict function does not include keys which values are None (to not cause any problems with columns that have default value set).

Everything seems to work but I’d like to know is there a better approach to do perform all this actions? Initially I was using session.add, but unfortunately it does not handle conflicts.

Also is it fine to use ORM models outside of the sqlalchemy sessions? As far as I know it is considered a better approach to create special dataclass (using built-in dataclasses or pydantic) and convert them to orm model and vice versa. Is it a necessary step or can it be omitted?