Custom Root Types are detected as NullType by SQLAlchemy

[jedieaston]

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 typing import Optional

from sqlmodel import Field, SQLModel, create_engine

class MyNewCustomRootType(SQLModel):
  __root__ : str

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    my_new_attribute: MyNewCustomRootType 


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

Description

1. Add a type that uses the technique described in the Custom Root Types section of the Pydantic docs.
2. Use this type for a type hint in a model that creates a SQLModel table.
3. Run metadata creation.
4. SQLAlchemy will error out claiming that you didn't set a type (but I did!)

This library looks really cool, but my Pydantic models use custom root types in a few places and if I can't use them I'll have to replicate a ton of code over and over again. Is this intentional behavior, or is it possible support for them can be added?

Operating System

Linux, macOS

Operating System Details

No response

SQLModel Version

0.0.3

Python Version

3.9.4, 3.8

Additional Context

The full traceback from trying to run the example in a Repl.it instance:

2021-08-25 02:42:46,495 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2021-08-25 02:42:46,495 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("hero")
2021-08-25 02:42:46,495 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-08-25 02:42:46,496 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("hero")
2021-08-25 02:42:46,496 INFO sqlalchemy.engine.Engine [raw sql] ()
2021-08-25 02:42:46,497 INFO sqlalchemy.engine.Engine ROLLBACK
Traceback (most recent call last):
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 4031, in visit_create_table
    processed = self.process(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 4065, in visit_create_column
    text = self.get_column_specification(column, first_pk=first_pk)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/dialects/sqlite/base.py", line 1424, in get_column_specification
    coltype = self.dialect.type_compiler.process(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 522, in process
    return type_._compiler_dispatch(self, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 4612, in visit_null
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Can't generate DDL for NullType(); did you forget to specify a type on this Column?

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "main.py", line 21, in <module>
    SQLModel.metadata.create_all(engine)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 4739, in create_all
    bind._run_ddl_visitor(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 342, in _run_ddl_visitor
    conn._run_ddl_visitor(visitorcallable, element, **kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 2081, in _run_ddl_visitor
    visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 485, in traverse_single
    return meth(obj, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 846, in visit_metadata
    self.traverse_single(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 485, in traverse_single
    return meth(obj, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 890, in visit_table
    self.connection.execute(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 77, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1349, in _execute_ddl
    compiled = ddl.compile(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 517, in compile
    return self._compiler(dialect, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py", line 29, in _compiler
    return dialect.ddl_compiler(dialect, self, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 455, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 490, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/visitors.py", line 81, in _compiler_dispatch
    return meth(self, **kw)
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/sql/compiler.py", line 4041, in visit_create_table
    util.raise_(
  File "/opt/virtualenvs/python3/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
sqlalchemy.exc.CompileError: (in table 'hero', column 'my_new_attribute'): Can't generate DDL for NullType(); did you forget to specify a type on this Column?

[5cat]

The issue is with this function which does not support pydantic models with root types.

One hacky way to fix it, is to edit the the function and replace the original one

from typing import Optional

import sqlmodel
from pydantic.fields import ModelField
from pydantic import BaseModel
from sqlmodel import Field, SQLModel, create_engine

original_get_sqlachemy_type = sqlmodel.main.get_sqlachemy_type


def with_pydantic_root_sqlachemy_type(field: ModelField):
    if issubclass(field.type_, BaseModel):
        if field.type_.__custom_root_type__:
            assert len(field.type_.__fields__) == 1
            assert '__root__' in field.type_.__fields__
            return with_pydantic_root_sqlachemy_type(field.type_.__fields__['__root__'])
    return original_get_sqlachemy_type(field)


sqlmodel.main.get_sqlachemy_type = with_pydantic_root_sqlachemy_type


class MyNewCustomRootType(SQLModel):
    __root__: str


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    secret_name: str
    age: Optional[int] = None
    my_new_attribute: MyNewCustomRootType


sqlite_url = f"sqlite://"

engine = create_engine(sqlite_url, echo=True)

SQLModel.metadata.create_all(engine)

which gives us the correct table

INFO sqlalchemy.engine.Engine 
CREATE TABLE hero (
	id INTEGER, 
	name VARCHAR NOT NULL, 
	secret_name VARCHAR NOT NULL, 
	age INTEGER, 
	my_new_attribute VARCHAR NOT NULL, 
	PRIMARY KEY (id)
)

but it still fails when you try to add and commit to the session

i will try to submit a pull request to add the solution.

[tiangolo]

Thanks @jedieaston! Help me understand, what use case would be solved by a root model that is not solved by standard types with parameters in Field()?

I think custom root models don't make much sense when used as fields (e.g. in SQLModel or FastAPI). And they changed quite a bit in Pydantic v2 as well. But still, I haven't seen a convincing reason to support them with their complexity, but I want to hear the arguments.

[github-actions]

Assuming the original need was handled, this will be automatically closed now. But feel free to add more comments or create new issues or PRs.