Getting field metadata for use in autogenerated model diagrams (erdantic)

[DanLipsitt]

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

See description

Description

I am trying to add SQLModel support to Erdantic, an entity relationship diagram generator. It supports Pydantic and produces diagrams that look like this:

Wanted Solution

This is hopefully more of a question than a feature request. I would like to know if it is possible to get the metadata that Erdantic needs. You can see instructions and code code showing how this is implemented for Pydantic. It is relatively short. I thought that since SQLModels are Pydantic models I might be able to reuse most of the code, but SQLModel relationship fields appear to be special, and those are crucial.

Wanted Code

The idea is as follows although the way it happens doesn't matter as long as there's a way to do it.

class Hero(SQLModel):
  ...

[(field.name, field.is_nullable, field.is_many, field.type) for field in Hero.fields]

As I understand it, is_many means it is on the many side of a many-to-one or many-to-many relationship

Alternatives

I have tried to figure out if there is a way to cast a SQLModel as a Pydantic model, but python doesn't really support the idea of casting.

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

Python 3.9.5

Additional Context

No response

[StefanBrand]

Here is the __dict__ of a model instance. Of particular interest in the context of this issue might be __sqlmodel_relationships__.

mappingproxy({'Config': <class 'geo_info_svg.models.Country.Config'>,
              '__abstractmethods__': frozenset(),
              '__annotations__': {'geometry': <class 'geoalchemy2.types.Geometry'>,
                                  'id': typing.Optional[int],
                                  'name': <class 'str'>,
                                  'population': <class 'int'>},
              '__class_vars__': {'__name__',
                                 '__sqlmodel_relationships__',
                                 '__tablename__',
                                 'metadata'},
              '__config__': <class 'sqlmodel.main.Config'>,
              '__custom_root_type__': False,
              '__doc__': None,
              '__exclude_fields__': None,
              '__fields__': {'geometry': ModelField(name='geometry', type=Geometry, required=True),
                             'id': ModelField(name='id', type=Optional[int], required=False, default=None),
                             'name': ModelField(name='name', type=str, required=True),
                             'population': ModelField(name='population', type=int, required=True)},
              '__hash__': None,
              '__include_fields__': None,
              '__init__': <function __init__ at 0x7f236f753370>,
              '__json_encoder__': <staticmethod(<cyfunction pydantic_encoder at 0x7f237d1bae90>)>,
              '__mapper__': <Mapper at 0x7f236f74a320; Country>,
              '__module__': 'geo_info_svg.models',
              '__post_root_validators__': [],
              '__pre_root_validators__': [],
              '__private_attributes__': {},
              '__schema_cache__': {},
              '__signature__': <pydantic.utils.ClassAttribute object at 0x7f236f749e70>,
              '__slots__': set(),
              '__sqlmodel_relationships__': {},
              '__table__': Table('country', MetaData(), Column('geometry', Geometry(geometry_type='POLYGON', srid=3035, from_text='ST_GeomFromEWKT', name='geometry'), table=<country>), Column('id', Integer(), table=<country>, primary_key=True), Column('name', AutoString(), table=<country>, nullable=False), Column('population', Integer(), table=<country>, nullable=False), schema=None),
              '__validators__': {},
              '__weakref__': None,
              '_abc_impl': <_abc._abc_data object at 0x7f236f759800>,
              '_sa_class_manager': <ClassManager of <class 'geo_info_svg.models.Country'> at 7f236f760950>,
              'geometry': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f236f78a700>,
              'id': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f236f78a9d0>,
              'name': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f236f78aa70>,
              'population': <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7f236f78ab10>})

[antont]

Do you need the Relationship info or would it work if you indeed just 'cast' to pydantic? I think that's possible by using the sqlmodel instances just to provide the data for vanilla pydantic instances, like the from_orm method / factory does.

[DanLipsitt]

@antont Yes, relationship info is important. Otherwise it isn't an entity relationship diagram. 😉

[kmcquade]

I just want to say that this would be SO COOL. @DanLipsitt I really hope that you come up with this at some point 😃

[hugolytics]

Hi @DanLipsitt , did you make any progress with this?
I was trying to do the same thing, I looked into generating the ERD based on the underlying sqlalchemy models, but I didn't get that far.
I would also be amazing to render the diagrams inside of mkdocs, on the other hand that would also be possible by using erdantic inside of jupyter and rendering the ipynb with mdocs-jupyter (or something like nbdev).
Anyway, id love to see whether documenting datamodels can be made a little easier :)

[martin-greentrax]

I would also like to know if you made progress @DanLipsitt :)

[austinnottexas]

this would be amazing!

[notchatbot]

Are there any news? This would be awesome! What about decomposing the SQLModel into a Dict-ish or into pydantic so erdantic works?

[GiorgioSgl]

This would be amazing

[zeehio]

I was able to create an ERD using sqlmodel through a different tool:

1. Define your models using sqlmodel
2. Create a sqlite database with your tables
3. Install npm install -g skeleton and use sqleton: sqleton -L dot -e -o erd.png database.sqlite. This tool creates an ERD from the sqlite schema.

(I have no relationship with the sqleton tool so please do your common sense validation checks)

[gnthibault]

For those who might end-up here, there's a python module to do the same thing as @zeehio suggested:

pip install eralchemy
eralchemy -i sqlite:///my_sqlite.db -o my_er_diagram.pdf