ValueError: <class 'pydantic.networks.EmailStr'> has no matching SQLAlchemy type

[ahaly-compass]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 pydantic import EmailStr
from sqlmodel import Field, SQLModel


class UserBase(SQLModel):
    name: str = Field(index=True)
    first_name: str
    last_name: str
    email: EmailStr  = Field(unique=True, index=True)
    active: bool = Field(default=True)


# # Database model, database table inferred from class name
class User(UserBase, table=True):
    __tablename__ = "users"
    id: int | None = Field(default=None, primary_key=True)
    hashed_password: str
    # tickets: list["Ticket"] = Relationship(back_populates="ticket")

Description

• Starting the app I get the error

ValueError: <class 'pydantic.networks.EmailStr'> has no matching SQLAlchemy type

relevant dependencies:

• fastapi = "^0.104.1"
• uvicorn = "^0.24.0.post1"
• sqlmodel = "^0.0.14"
• psycopg = {extras = ["binary"], version = "^3.1.14"}

Operating System

Linux

Operating System Details

Ubuntu 22.04 WSL

SQLModel Version

0.0.14

Python Version

3.12

Additional Context

No response

[rmasters]

I've run into this a few times. The fix is to pass the SQLAlchemy data type directly - this is sqlalchemy.String(), but in this case I'm using sqlmodel's own AutoString wrapper type:

-from sqlmodel import Field, SQLModel
+from sqlmodel import Field, SQLModel, AutoString

class UserBase(SQLModel):
    name: str = Field(index=True)
    first_name: str
    last_name: str
-   email: EmailStr  = Field(unique=True, index=True)
+   email: EmailStr  = Field(unique=True, index=True, sa_type=AutoString)
    active: bool = Field(default=True)

The root cause of this is EmailStr does not subclass a type that SQLModel knows how to convert to a SQLAlchemy type (i.e. it's not class EmailStr(str). For example, Pydantic's CountryAlpha2 does subclass str, so it works out of the box.

sqlmodel.get_sqlalchemy_type() handles conversion from primitives like int and str to Float and AutoString, as well as helpers for a few common types like IP addresses. I've proposed adding EmailStr to this list of helpers in #762.

[ahaly-compass]

I also found this solution:
email: EmailStr = Field(sa_column=Column(String, index=True, unique=True))

But yours is the SQLModel solution.
Thanks.

[salahelfarissi]

Thank you for your answer!

[ChrisNi888]

Yes, you are right. It works for HttpUrl:
avatar: Optional[HttpUrl] = Field(sa_type=AutoString)
Thanks!