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

[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

[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]

Thanks for the help @meirdev ! 🤓☕

[AlekseyFedorovich]

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]

Thanks for coming back to close it! ☕

[AlekseyFedorovich]

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]

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]

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]

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]

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)