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 toPydantic.
• I already checked if it is not related to SQLModel but toSQLAlchemy.

Commit to Help

• I commit to help with one of those options 👆

Example Code

# current workaround: force extending sqlmodel.main.get_sqlalchemy_typefromtypingimportAny,Callableimportsqlmodel.mainfrompydanticimportBaseModel,ConstrainedStrfrompydantic.fieldsimportModelFieldfromsqlalchemyimportStringfromtyping_extensionsimportTypeAlias_GetSqlalchemyTypeProtocol:TypeAlias=Callable[[ModelField],Any]def_override_get_sqlalchemy_type(original_get_sqlalchemy_type:_GetSqlalchemyTypeProtocol,)->_GetSqlalchemyTypeProtocol:def_extended_get_sqlalchemy_type(field:ModelField)->Any:ifissubclass(field.type_,BaseModel):# TODO use sqlalchemy.JSON or CHAR(N) for "known to be short" modelsraiseNotImplementedError(field.type_)ifissubclass(field.type_,ConstrainedStr):# MAYBE add CHECK constraint for field.type_.regexlength=field.type_.max_lengthiflengthisnotNone:returnString(length=length)returnString()returnoriginal_get_sqlalchemy_type(field)return_extended_get_sqlalchemy_typesqlmodel.main.get_sqlachemy_type=_override_get_sqlalchemy_type(sqlmodel.main.get_sqlachemy_type)# MAYBE get_sqlachemy_type -> get_sqlalchemy_type (sqlmodel > 0.0.8)# cf. <https://github.com/tiangolo/sqlmodel/commit/267cd42fb6c17b43a8edb738da1b689af6909300>

Description

Problem:

• We want to decide database column types deterministically by model field.
• SometimesSQLModel does not provide expected column type, and it is (in some cases) impossible because requirements ofSQLModel users are not always the same (e.g. DBMS dialects, strictness of constraints, choice of CHAR vs VARCHAR vs TEXT vs JSON, TIMESTAMP vs DATETIME)

Wanted Solution

Allow user to use customizedget_column_from_field |get_sqlalchemy_type function to fit with their own requirements.

Add parameter to model config likesa_column_builder: Callable[[ModelField], Column] = get_column_from_field.

Functionget_column_from_field would be better split by the following concerns, to be used as a part of customizedsa_column_builder implementation:

1. Deciding the column type (currently done inget_sqlalchemy_type)
2. Applying pydantic field options to column type (e.g. nullable, min, max, min_length, max_length, regex, ...)
3. Applying column options (e.g. primary_key, index, foreign_key, unique, ...)

Possible effects on other issues/PRs:

• May become explicitly extendable bysqlmodel users:
  • JSON Fields for Nested Pydantic Models? #63
  • Add support for deferred column loading #97
  • How PostgreSQL sequence can be mapped with a column using SqlModel? #137
  • Add List type support #178
  • How to set check constraint in sql model? #292
  • How to add BIT(1) data type to field? #298
  • Support discriminated union #356
  • How to use EncryptedType of SQLAlchemy #447
• May expecting a similar thing:
  • Specifying SQL Server column data type, similar to dtype in pd.to_sql() #319

p.s-1

Conversion rule between Field/column value may become necessary, mainly to serialize field value to column value.
(e.g. Classes inheriting BaseModel cannot be stored directly intosqlalchemy.JSON because it is not JSON or dict. We avoid this by addingjson_serializer tocreate_engine. Deserialize part has no problem because JSONstr ->BaseModel will be done by pydantic validation for now (pydantic v1))

def_json_serializer(value:Any)->str:ifisinstance(value,BaseModel):returnvalue.json()returnjson.dumps(value)

p.s-2

IMO usingsqlmodel.sql.sqltypes.AutoString() in alembic revision file is not good from the sight of future migration constancy, and this is one of the reason I overriddenget_sqlalchemy_type function.

Wanted Code

################################################################# expected: any of the following `Foo` / `Bar`def_custom_sa_column_builder(field:ModelField)->Column:    ...classFoo(SQLModel,table=True):classSQLModelConfig:sa_column_builder:Callable[[ModelField],Column]=_custom_sa_column_builder    ...classBar(SQLModel,table=True,sa_column_builder=_custom_sa_column_builder):    ...

Alternatives

• Write a function that returnssa_column and call it insqlmodel.Field declaration
  • -> Not easy to apply pydantic-side constraints (e.g. nullable,ConstrainedStr, ...)

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

3.10.7

Additional Context

No response