Problem with SQLModel Relationship Draw

[imneedle]

Problem with SQLModel Relationship Draw

[jayqi]

tl;dr — SQLModel as an ORM represents a different kind of data and I think supporting Relationship is out of scope for erdantic. However, there are other tools for drawing ERDs from SQLAlchemy models, which SQLModel builds on.

---

Hi @imneedle,

This is a bit tricky and requires some thinking, but my current understanding is that SQLModel and erdantic work with two different kinds of thing and are therefore just incompatible.

SQLModel is an object-relational mapper (ORM) library. This is a kind of library that lets you map relational databases (SQL) to objects in object-oriented programming. You create these data models, expressed as Python classes in the ORM framework, that are used to represent data in relational database tables.

In contrast, erdantic works with frameworks that are better described as key-value models, object models, or document models.

The kind of foreign-key relationship you are trying to model with Relationship(), between a row in the B table and a row in the A table, just don't exist natively in a key-value or object data model. That's in part the reason why there's a Relationship object that gets instantiated. In a key-value data model, there are only has-a relationships, like "an instance of B contains an instance of A". These are two different kinds of relationships.

I think there are a few reasons why this case of SQLModel is confusing:

• An ORM is a translation layer between relational data and an object-oriented interface, so there is a lot of syntax overlap with object data models.
• SQLModel is implemented using Pydantic.
• SQLModel intentionally models both SQL tables ("SQL models") and object data ("non-SQL models") using the same classes.

So, this means that you can only diagram the "non-SQL models" expressed with SQLModel and not the "SQL models".

This was a long explanation for why I think it makes sense that erdantic does not support Relationship in SQLModel, and why I don't think it's in scope.

However, as an alternative, something to keep in mind is that SQLModel is also implemented with SQLAlchemy under the hood. SQLAlchemy has been around for a long time, and I think you should be able to find other tools that support drawing ERDs from SQLAlchemy models.

[imneedle]

Hi @jayqi

Thanks for the answer. I can see a lot is going on under the hood.

However, I'm wondering if maybe there would be a way to bypass/ignore the default values that are given to fields. Seen as though the following classes work:

from sqlmodel import SQLModel

class A(SQLModel):
    id: int
    name: str

class B(SQLModel):
     a_id: int
     a: A  # not specifying = Relationship()

Would it be possible to ignore the default values given to the fields. What I mean is that in my case, I simply want to get the ERD of each Model, with the list of their fields and their relationships to other models.

For example, say I were to specify the following class:

class A(SQLModel):
     id: int = 0  # default value
     name: str = "instance_a"

Well in this case, I still want to represent the class A in the same way that it was shown when the fields didn't have any default values.

So, perhaps an option such as ignore_defaults=True would suffice ? That way, no more dealing with Relationship and it's virtually the same thing as a pydantic BaseModel ?

[jayqi]

I don't think it's quite as simple as "ignore default values". The reason it behaves this way is because of SQLModel's internal implementation details. It's not just a regular Pydantic model.

from sqlmodel import SQLModel, Relationship

class A(SQLModel):
    id: int
    name: str

class B(SQLModel):
     a_id: int
     a: A = Relationship()

B.model_fields
#> {'a_id': FieldInfo(annotation=int, required=True)}

class C(SQLModel):
    a_id: int
    a: A

C.model_fields
#> { 'a': FieldInfo(annotation=A, required=True),
#>   'a_id': FieldInfo(annotation=int, required=True)}

^{Created at 2024-05-08 12:47:39 EDT by reprexlite v1.0.0}

As you can see, calling the model_fields property on B (this is the normal way to get field definitions in Pydantic) does not turn up any information about a field named a, but it does work for C.

I'm not familiar with the details of how SQLModel works, but from this, it seems like SQLModel itself treats any fields defined with Relationship as special on the definition of the model class.

I think supporting this case this would basically be adding a new plugin specifically for SQLModel. For the reasons I described in my previous comment, I don't think it makes sense to do so because the part that isn't working is a better fit for other ERD tools that are specifically intended to work with ORMs/SQL databases models.

[imneedle]

Thank you for the answer. I will try to look into other tools.

I really appreciate the help.