Link Tables getting duplicate entries only under async?

[jidn]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question and didn't find it.
• I searched the SQLModel documentation, with the integrated search.
• I already searched in Google "How to X in SQLModel" and didn't find any information.
• I already read and followed all the tutorial in the docs and didn't find an answer.
• I already checked if it is not related to SQLModel but to Pydantic.
• I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

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]

Description

I 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.

assert 1 == len(org.users)
E AssertionError: assert 1 == 2
E + where 2 = len([LinkOrgUser(role=1, >org_id=UUID('...005'), user_id=UUID('...006')), LinkOrgUser(role=1, >org_id=UUID('...005'), user_id=UUID('...006'))])
E + where [LinkOrgUser(role=1, org_id=UUID('...005'), user_id=UUID('...006')), LinkOrgUser(role=1, org_id=UUID('...005'), user_id=UUID('...006'))] = Organization(id=UUID('...005'), name='Example').users

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 System

Linux

Operating System Details

Up to date Arch

SQLModel Version

0.0.22

Python Version

3.13.0

Additional Context

Package Version

---

aiosqlite 0.20.0
annotated-types 0.7.0
greenlet 3.1.1
iniconfig 2.0.0
packaging 24.2
pluggy 1.5.0
pydantic 2.9.2
pydantic-core 2.23.4
pytest 8.3.3
pytest-asyncio 0.24.0
sqlalchemy 2.0.36
sqlmodel 0.0.22
typing-extensions 4.12.2

[YuriiMotov]

The problem is in the line

        org.users.append(LinkOrgUser(org=org, user=aa, role=1))

Creating an instance of LinkOrgUser will already link User to Organization, but you append this object to org.users and 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():

    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?