Achieving Consistency in Object Equality Checks with SQLModel for Data and Database Models

[timo0]

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

from typing import Optional

from sqlmodel import SQLModel, Field, create_engine, Session, select


class HeroBase(SQLModel):
    name: str = Field(index=True)
    age: Optional[int] = Field(default=None, index=True)

class Hero(HeroBase, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)

sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

connect_args = {"check_same_thread": False}
engine = create_engine(sqlite_url, echo=True, connect_args=connect_args)

# Some code to compose the HeroBase object from CSV file

from_data = HeroBase(name='ABC', age=12)

with Session(engine) as session:
    statement = select(Hero).where(Hero.name == from_data.name)
    from_db = session.exec(statement).one_or_none()
    # Assumption:
    #   from_db = Hero(id=1, name='ABC', age=12)

if from_data == from_db:
    print("Success!")
else:
    print("Fail!")

Description

In our project, we read data from files or APIs and use it to create objects. Then, we check whether we already have that data in our database by comparing some key data points. If we find a match, we want to check if the new object is equal to the one in the database.

Currently, we use a SQLAlchemy class and a Pydantic model that have similar fields for this task. However, we recently discovered SQLModel and hoped to simplify the process by using it for both the data and database models, with inheritance.

However, when we tried this approach, we noticed that the objects are never considered equal. We're not sure if we made a mistake or if there's something we need to change to achieve the desired behavior.

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.9

Additional Context

No response

[antont]

It would be wrong to say that those are equal, by default, as from_data does not have id, whereas from_db does have it.

If you want, you can however define the equality check how you want, by overriding __eq__,https://docs.python.org/3/reference/datamodel.html#object.__eq__

It might be confusing to make non-equal objects equal, though, so perhaps for your purpose you want some new method, like from_data.has_same_data(from_db) or maybe better has_same_data(from_data, from_db) .. perhaps content_equals() ?

[martinezpl]

I ended up here because I've expected comparing SQLModel objects to work like comparing Pydantic objects and well, they don't.

I do a query, as a result I end up with a list of SQLModel objects.

I compare them against external database - if the corresponding object has been modified in the external database, I have the object in my database.

So I create a new object using data from external database, and compare it to the object returned from querying my database.

I expected it to act like pydantic models, but it seems each SQLModel object is different because of internal properties.