Create Relationships with Unique Fields (UniqueViolationError)

[njdowdy]

First Check

• I added a very descriptive title to this issue.
• I used the GitHub search to find a similar issue 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

# From the SQLModel Tutorial (https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/create-and-update-relationships/)

from typing import List, Optional
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

class Team(SQLModel, table=True):
    # id: Optional[int] = Field(default=None, primary_key=True)
    id: int = Field(default=None, primary_key=True)  # NEW
    name: str = Field(index=True)

    heroes: List["Hero"] = Relationship(back_populates="team")

class Hero(SQLModel, table=True):
    # id: Optional[int] = Field(default=None, primary_key=True)
    id: int = Field(default=None, primary_key=True)  # NEW
    name: str = Field(index=True)

    # team_id: Optional[int] = Field(default=None, foreign_key="team.id")
    # team: Optional[Team] = Relationship(back_populates="heroes")
    team_id: int = Field(default=None, foreign_key="team.id")  # NEW
    team: Team = Relationship(back_populates="heroes")  # NEW

from sqlalchemy.ext.asyncio import AsyncSession # ADDED: 2022_02_24

# def create_heroes():
async def create_heroes(session: AsyncSession, request: Hero):  # NEW, EDITED: 2022_02_24
    # with Session(engine) as session: # EDITED
        # team_preventers = Team(name="Preventers", headquarters="Sharp Tower")  # REMOVE
        # team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")  # REMOVE
        assigned_team = Team(name=request.team_to_assign)

        new_hero = Hero(
            name=request.hero_name,
            team=assigned_team
        )
        session.add(new_hero)
        await session.commit() # EDITED: 2022_02_24
        await session.refresh(new_hero) # EDITED: 2022_02_24
        return new_hero # ADDED: 2022_02_24


# Code below omitted 👇

Description

I'm following the SQLModel tutorial as I implement my own version. I have a model very similar to the above example (derived from the Hero/Team example given in the tutorial on how to implement One-To-Many relationships with SQLModel.

When I use this approach, it does write the required Team and Hero objects to my database. However, it does not check the Team table to ensure that the "team_to_assign" from the request object does not already exist. So, if I use the "create_heroes" function (in two separate commits) to create two Heroes who are on the same team, I get two entries for the same team in the Team table. This is not desirable. If the team already exists, the Hero being created should use the id that already exists for that team.

When I implement "sa_column_kwargs={"unique": True}" within the "name" Field of the Team table, I can no longer create a new Hero if they are to be connected to a Team that already exists. I get the error:

sqlalchemy.exc.IntegrityError: (sqlalchemy.dialects.postgresql.asyncpg.IntegrityError) <class 'asyncpg.exceptions.UniqueViolationError'>: duplicate key value violates unique constraint "ix_team_name" DETAIL: Key (name)=(team_name) already exists. [SQL: INSERT INTO "team" (name) VALUES (%s) RETURNING "team".id]

I was hoping that would somehow tell SQLModel to skip the insertion of a Team that already exists and get the appropriate Team id instead. Clearly it just stops it from happening. SQLModel doesn't appear to check that a Team already exists before inserting it into the Team table.

Am I missing something about how to handle this with SQLModel, or am I meant to employ my own logic to check the Team table prior to generating the Hero object to insert?

Thanks for your time!

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10

Additional Context

Using async libraries:
SQLAlchemy = {extras = ["asyncio"], version = "^1.4.31"}
asyncpg = "^0.25.0"

[byrman]

SQLModel doesn't appear to check that a Team already exists before inserting it into the Team table.

I don't know of any ORM that works this way. You'll have to write your own get_or_create logic.

[njdowdy]

SQLModel doesn't appear to check that a Team already exists before inserting it into the Team table.

I don't know of any ORM that works this way. You'll have to write your own get_or_create logic.

That's understandable. However the docs (see here: https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/define-relationships-attributes/) clearly state the results of tutorial as:

Team:

id	name	headquarters
1	Preventers	Sharp Tower
2	Z-Force	Sister Margaret’s Bar

Hero:

id	name	secret_name	age	team_id
1	Deadpond	Dive Wilson	null	2
2	Rusty-Man	Tommy Sharp	48	1
3	Spider-Boy	Pedro Parqueador	null	1

Which is what I am trying to get my data to look like.

But instead, I am seeing something like this:
Team:

id	name	headquarters
1	Preventers	Sharp Tower
2	Z-Force	Sister Margaret’s Bar
3	Preventers	Sharp Tower

Hero:

id	name	secret_name	age	team_id
1	Deadpond	Dive Wilson	null	2
2	Rusty-Man	Tommy Sharp	48	1
3	Spider-Boy	Pedro Parqueador	null	3

I am wondering if async if the culprit. I ran into a situation where I needed to add '.options(selectinload(...))' to my statements to grab the data properly. I wonder if there's something similar that needs to be added somewhere for this use case?

[byrman]

I am wondering if async if the culprit.

But you are not doing async, are you? I don't spot await anywhere, despite the AsyncSession type hint. Or am I missing something here?

[njdowdy]

I am wondering if async if the culprit.

But you are not doing async, are you? I don't spot await anywhere, despite the AsyncSession type hint. Or am I missing something here?

No, that was my mistake. I forgot to add that when I was editing the example code from the tutorial. I am using async and await on this function using SQLAlchemy with asyncio. I have added that to the example code now. Thanks for catching that.

[byrman]

So create_heroes is called 3 times and you end up with 3 teams and 3 heroes? That is to be expected from your code, because it creates 1 new team and 1 new hero on each invocation.

You will need something like this:

statement = select(Team).where(Team.name == team_to_assign)
results = await session.execute(statement)
team = results.one_or_none()
if team is None:
    team = Team(name=team_to_assign)

[njdowdy]

So create_heroes is called 3 times and you end up with 3 teams and 3 heroes? That is to be expected from your code, because it creates 1 new team and 1 new hero on each invocation.

You will need something like this:

statement = select(Team).where(Team.name == team_to_assign)
results = await session.execute(statement)
team = results.one_or_none()
if team is None:
    team = Team(name=team_to_assign)

I think that is a useful workaround, but I'm trying to not have to implement additional logic if SQLModel (and SQLAlchemy behind the scenes) can manage it for me. For some reason, my code is not creating a one-to-many relationship - it's just a bunch of one-to-one relationships between the Hero and Team table were the Team objects happen to have the same team name. I would think SQLModel would handle this, but my implementation is not functioning properly (possibly because of async and the Relationship() from SQLModel aren't playing nice?).

And from the tutorial, it seems that SQLModel is meant to handle this.

For example, in the original tutorial code:

# Code above omitted 👆

def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")

        hero_deadpond = Hero(
            name="Deadpond", secret_name="Dive Wilson", team=team_z_force
        )
        hero_rusty_man = Hero(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
        )
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador", team=team_preventers) #ADDED team here to demo my situation
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)

# Code below omitted 👇

You can see 3 heroes are added, but only 2 teams are created and get heroes attached to them. If it created 3 teams that would not be a "one-to-many" relationship, which is what the tutorial is meant to be demonstrating. There's no code here to check that Deadpond, Rusty Man, or Spider-Boy's teams already exist in the database or not.

In my implementation, if I were to try to look up heroes belonging to team = "Preventers" like this:

def update_heroes():
    with Session(engine) as session:
        hero_spider_boy = session.exec(
            select(Hero).where(Hero.name == "Spider-Boy")
        ).one()

        preventers_team = session.exec(
            select(Team).where(Team.name == "Preventers")
        ).one()

        print("Hero Spider-Boy:", hero_spider_boy)
        print("Preventers Team:", preventers_team)
        print("Preventers Team Heroes:", preventers_team.heroes)

I won't get Spider-Boy and Rusty Man (as the tutorial does), because I have two distinct teams that happen to share the name "Preventers". So my "preventers_team" statement (if the ".one()" wasn't added) will return N number of entries from the database, where N is the number of heroes that have been committed to the database with a team name of "Preventers", and all those teams named "Preventers" will only have a single hero each. That's less than useful.

I'm looking forward to the "Advanced User Guide"!

[byrman]

I'm sorry, but the tutorial makes totally sense to me. If I were you, I would (re)enable the unique constraint on Team.name, so you can't have two teams sharing the same name. Any UniqueViolationError is then an indication that you are not there yet and are trying to create a duplicate instead of retrieving an existing team. IMHO, the get_or_create idiom is not a workaround, there is just no other way to do it.

[njdowdy]

the get_or_create idiom

The get_or_create idiom is not used in the tutorial, or am I missing something? Surely this would be something handled behind the scenes by SQLModel, else it is not creating a one-to-many relationship for us.

Either way, it is clear I'll need to implement this with and without async to try to isolate whether that's really the issue at hand.

[byrman]

The get_or_create idiom is not used in the tutorial, or am I missing something?

The example create_heroes() is meant to be run once on an empty db.

Either way, it is clear I'll need to implement this with and without async to try to isolate whether that's really the issue at hand.

Yes, try to provide a minimal, complete example that can be run as-is.

[njdowdy]

I'll work on that. Thanks for your assistance @byrman!

[njdowdy]

@byrman was correct. get_or_create is the answer. I was naïve in thinking SQLModel handles this behind the scenes. The tutorial could be improved for newbies by mentioning this, imo. Thanks @byrman!