Skip to content

add comments on tables and columns, visible also on the generated DB #492

@AlekseyFedorovich

Description

@AlekseyFedorovich

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 sqlmodel import Field, Relationship, SQLModel


class Measure(SQLModel, table=True):
    t: int         = Field(primary_key=True, description='Unix epoch when the measure was performed')

Description

I want to add comments on columns and tables that can be seen also in the generated SQL DB

No 'comment' parameter could be found in the 'Field' function. I found the 'description' parameter but it has no effect on the generated schema and I couldn't find any information on what's the use of this attribute.

Operating System

Windows

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.7.3

Additional Context

No response

Activity

meirdev

meirdev commented on Nov 9, 2022

@meirdev

If your database allows comment on column you can use comment parameter:

class Measure(SQLModel, table=True):
    t: int = Field(primary_key=True, description='Unix epoch when the measure was performed', sa_column_kwargs={"comment": "test"})
tiangolo

tiangolo commented on Nov 9, 2022

@tiangolo
Member

Thanks for the help @meirdev ! 🤓☕

AlekseyFedorovich

AlekseyFedorovich commented on Nov 9, 2022

@AlekseyFedorovich
Author

it works, in analogy with SQLAlchemy I found a way to comment tables too:

class Measure(SQLModel, table=True):
    __table_args__ = dict(comment="Test")
    t: int = Field(primary_key=True, description='Unix epoch when the measure was performed', sa_column_kwargs=dict("comment": "test"))

Still I don't understand what's the use of description parameter in function Field

tiangolo

tiangolo commented on Nov 10, 2022

@tiangolo
Member

Thanks for coming back to close it! ☕

AlekseyFedorovich

AlekseyFedorovich commented on Nov 10, 2022

@AlekseyFedorovich
Author

You are welcome.
Anyway, I tested the application because it looked so promising but unfortunately I don't think I will continue using it.
The docs are to way too poor and also exploring the code there is no comment at all.
Also pydantic validation doesn't seem to work, as far as I understood for precedent issues: again no docs about that so is difficult to tell.
I'm sorry.

aptly-io

aptly-io commented on Jul 6, 2023

@aptly-io

Still I don't understand what's the use of description parameter in function Field
I use it for documenting the OpenAPI (swagger) REST API documentation.

(Interesting to learn about the comment feature for documenting the database)

I'm surprised to read about the poor documentation. I'm using FastAPI and SQLModel recently and feel these come with extensive (and easy accessible) documentation/tutorials pages (I'd even say too much :-))

PaleNeutron

PaleNeutron commented on Mar 27, 2024

@PaleNeutron

Maybe it's time for us to consider this feature again. pydantic can use Python docstring as field description soon! pydantic/pydantic#6563

Currently, when user create a sqlmodel, they usually create description at 3 place.

class Hero(SQLModel, table=True):
    name: str = Field(index=True, description="the description for pydantic and openapi", sa_column_kwargs={"comment": "the description for table column"})
    """the description for IDE"""

In the pass, we cureate a custom field object to make description and sa_column_kwargs.comment one:

from sqlmodel import Field as SQLModelField


class Field(SQLModelField):
    def __init__(self, *args, **kwargs):
        if "description" in kwargs:
            description = kwargs.get("description")
            if "sa_column_kwargs" in kwargs:
                sa_column_kwargs = kwargs.get("sa_column_kwargs")
                sa_column_kwargs["comment"] = description
            else:
                kwargs["sa_column_kwargs"] = {"comment": description}
        super().__init__(*args, **kwargs)

But consider the pull request mentioned above, pydantic can use docstring as description now, but the custom field can not get it since it is done during class creation.

Can sqlmodel follow pydantic's change and provide some configuration to merge the three type of descriptions to one? @tiangolo

PaleNeutron

PaleNeutron commented on Mar 27, 2024

@PaleNeutron

I create a new basemodel, use if instead of SQLModel will automatically set comment to Column.

For anyone who want this feature

from __future__ import annotations

from ast import Dict, Tuple
from typing import TYPE_CHECKING, Any, Type
from pydantic_core import PydanticUndefined
from sqlmodel.main import SQLModelMetaclass

from sqlmodel import SQLModel

class DescriptionMeta(SQLModelMetaclass):
    def __new__(
        cls,
        name: str,
        bases: Tuple[Type[Any], ...],
        class_dict: Dict[str, Any],
        **kwargs: Any,
    ) -> Any:
        new_class = super().__new__(cls, name, bases, class_dict, **kwargs)
        fields = new_class.model_fields
        for k, field in fields.items():
            desc = field.description
            if desc:
                # deal with sa_column_kwargs
                if field.sa_column_kwargs is not PydanticUndefined:
                    field.sa_column_kwargs["comment"] = desc
                else:
                    field.sa_column_kwargs = {"comment": desc}
                # deal with sa_column
                if field.sa_column is not PydanticUndefined:
                    if not field.sa_column.comment:
                        field.sa_column.comment = desc
                # deal with attributes of new_class
                if hasattr(new_class, k):
                    column = getattr(new_class, k)
                    if hasattr(column, "comment") and not column.comment:
                        column.comment = desc
        return new_class
    
class Base(SQLModel, metaclass=DescriptionMeta):
    pass
KunxiSun

KunxiSun commented on May 22, 2024

@KunxiSun

I create a new basemodel, use if instead of SQLModel will automatically set comment to Column.

For anyone who want this feature

from __future__ import annotations

from ast import Dict, Tuple
from typing import TYPE_CHECKING, Any, Type
from pydantic_core import PydanticUndefined
from sqlmodel.main import SQLModelMetaclass

from sqlmodel import SQLModel

class DescriptionMeta(SQLModelMetaclass):
    def __new__(
        cls,
        name: str,
        bases: Tuple[Type[Any], ...],
        class_dict: Dict[str, Any],
        **kwargs: Any,
    ) -> Any:
        new_class = super().__new__(cls, name, bases, class_dict, **kwargs)
        fields = new_class.model_fields
        for k, field in fields.items():
            desc = field.description
            if desc:
                # deal with sa_column_kwargs
                if field.sa_column_kwargs is not PydanticUndefined:
                    field.sa_column_kwargs["comment"] = desc
                else:
                    field.sa_column_kwargs = {"comment": desc}
                # deal with sa_column
                if field.sa_column is not PydanticUndefined:
                    if not field.sa_column.comment:
                        field.sa_column.comment = desc
                # deal with attributes of new_class
                if hasattr(new_class, k):
                    column = getattr(new_class, k)
                    if hasattr(column, "comment") and not column.comment:
                        column.comment = desc
        return new_class
    
class Base(SQLModel, metaclass=DescriptionMeta):
    pass

@PaleNeutron I think I will probably like the style to override the Field function,👇🏻(not tested pseudocode)

from typing import Union
from sqlmodel import Field as _Field
from sqlmodel import Column

def Field(
    *,
    description: Optional[str] = None,
    sa_column: Union[Column, Select, SelectOfScalar, None] = None,
    **kwargs
) -> Any:
    if description:
        if sa_column:
            sa_column = Column(comment=description)
        else:
            sa_column.comment = description

    # pass in new sa_column and description, maybe other necessary parameters
    return _Field(description=description, sa_column=sa_column, **kwargs)
iloveitaly

iloveitaly commented on Jun 7, 2024

@iloveitaly

Has anyone figured out how to add the model docstr as a comment on the SQL table?

olisom

olisom commented on Aug 7, 2024

@olisom
class MytableBase(SQLModel):
    __table_args__ = {'comment': 'Here is the table description'}

3 remaining items

Loading
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    questionFurther information is requested

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

      Development

      Participants

      @iloveitaly@tiangolo@PaleNeutron@aptly-io@meirdev

      Issue actions

        add comments on tables and columns, visible also on the generated DB · Issue #492 · fastapi/sqlmodel