Link Tables getting duplicate entries only under async? #1220
-
First Check
Commit to Help
Example Code"""Sample to show issues between Session and AsyncSession.
# Setup environment
uv init sqlmodel-link-table
cd sqlmodel-link-table
uv venv
source .venv/bin/activate
uv pip install sqlmodel aiosqlite greelet pytest pytest-asyncio
Copy this file to sqlmodel-link-table/test_session.py and run pytest.
$ pytest test_session.py -v
"""
from contextlib import asynccontextmanager, contextmanager
from typing import AsyncGenerator, Generator
from uuid import UUID, uuid4
import pytest
from sqlalchemy import UniqueConstraint
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
class LinkOrgUser(SQLModel, table=True):
"""Roles a user has in an Organization."""
__tablename__: str = "organization_user" # type: ignore
org_id: UUID | None = Field(
default=None, foreign_key="organization.id", primary_key=True
)
user_id: UUID | None = Field(default=None, foreign_key="user.id", primary_key=True)
role: int # enum.IntFlag
org: "Organization" = Relationship(back_populates="users")
user: "User" = Relationship(back_populates="orgs")
class User(SQLModel, table=True):
"""User with relationships."""
__table_args__ = (UniqueConstraint("email"),)
id: UUID = Field(primary_key=True, default_factory=uuid4)
name: str = Field(max_length=64)
email: str | None = Field(default=None, max_length=255)
# Relationships
orgs: list[LinkOrgUser] = Relationship(
back_populates="user",
)
class Organization(SQLModel, table=True):
"""Organization with users."""
id: UUID = Field(primary_key=True, default_factory=uuid4)
name: str = Field(max_length=80)
users: list[LinkOrgUser] = Relationship(back_populates="org")
###########################################################
## pytest with synchronous database session ##
@contextmanager
def memory_session() -> Generator[Session]:
"""Syncronious database session."""
engine = create_engine("sqlite://", connect_args={"check_same_thread": False})
SQLModel.metadata.create_all(bind=engine)
with Session(engine) as session:
yield session
def test_link_sync():
with memory_session() as session:
org = Organization(name="Example", id=UUID(int=1))
aa = User(name="AA", id=UUID(int=2))
org.users.append(LinkOrgUser(org=org, user=aa, role=1))
session.add(org)
session.commit()
assert org.name == "Example"
assert 1 == len(org.users)
assert aa.id in [_.user.id for _ in org.users]
###########################################################
## pytest with asynchronous database session ##
@asynccontextmanager
async def async_memory_session() -> AsyncGenerator[AsyncSession]:
"""Async database session."""
url = "sqlite+aiosqlite:///:memory:"
engine = create_async_engine(url, echo=False, future=True)
async with async_sessionmaker(engine, expire_on_commit=False)() as session:
async with engine.begin() as conn:
await conn.run_sync(SQLModel.metadata.create_all)
try:
yield session
finally:
print(f"{url} finished.")
await engine.dispose()
@pytest.mark.asyncio
async def test_link_async():
async with async_memory_session() as async_session:
org = Organization(name="Example", id=UUID(int=5))
aa = User(name="AA", id=UUID(int=6))
org.users.append(LinkOrgUser(org=org, user=aa, role=1))
async_session.add(org)
await async_session.commit()
assert org.name == "Example"
assert 1 == len(org.users)
assert aa.id in [_.user.id for _ in org.users]DescriptionI create an Organization and User objects. I then create a link table object using the two objects and the user's role. I commit and check the number of users in the Organization.users object. I expect to see one user, which I do when using a synchronous database session. However under an asynchronous database session, I see two duplicates.
There are no errors other than the assert in the failed test. I don't understand why one session works while the other doesn't. I must be doing something wrong, but what? Operating SystemLinux Operating System DetailsUp to date Arch SQLModel Version0.0.22 Python Version3.13.0 Additional ContextPackage Version aiosqlite 0.20.0 |
Beta Was this translation helpful? Give feedback.
Replies: 1 comment
-
|
The problem is in the line org.users.append(LinkOrgUser(org=org, user=aa, role=1))Creating an instance of I have no idea why it works differently with non-async session. async with async_memory_session() as async_session:
org = Organization(name="Example", id=UUID(int=5))
aa = User(name="AA", id=UUID(int=6))
LinkOrgUser(org=org, user=aa, role=1) # <- this line was changed
async_session.add(org)
await async_session.commit()
assert org.name == "Example"
assert 1 == len(org.users)
assert aa.id in [_.user.id for _ in org.users]Are there any not lazy people here who want to check how it works with pure SQLAlchemy? |
Beta Was this translation helpful? Give feedback.
The problem is in the line
Creating an instance of
LinkOrgUserwill already linkUsertoOrganization, but you append this object toorg.usersand it creates duplicates.I have no idea why it works differently with non-async session.
To fix your code example, just remove
org.users.append():