How to use HttpUrl in SqlModel? I get a SqlAlchemy error

[vinodkumars]

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

class Model(SQLModel):
  image_url: Optional[HttpUrl] = Field(default=None, sa_type=AutoString)

Description

When I enter a valid url like https://example.com I get an error from SqlAlchemy. Here's the error:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'pydantic_core._pydantic_core.Url'
[SQL: INSERT INTO "user" (id, created_at, updated_at, deleted_at, full_name, email, is_email_verified, image_url) VALUES (%(id)s, %(created_at)s, %(updated_at)s, %(deleted_at)s, %(full_name)s, %(email)s, %(is_email_verified)s, %(image_url)s)]
[parameters: {'id': '6hYri22AfrBViz1QY0gDY', 'created_at': datetime.datetime(2024, 5, 29, 22, 43, 35, 119940, tzinfo=datetime.timezone.utc), 'updated_at': datetime.datetime(2024, 5, 29, 22, 43, 35, 119947, tzinfo=datetime.timezone.utc), 'deleted_at': None, 'full_name': 'string', 'email': 'vinod@canihelp.ai', 'is_email_verified': False, 'image_url': Url('https://example.com/')}]

Operating System

macOS

Operating System Details

No response

SQLModel Version

0.0.18

Python Version

3.11

Additional Context

No response

[mike-500]

I ran into the same issue and ended up using a custom TypeDecorator from sqlalchemy.types for HttpUrl.

Full example:

from datetime import datetime
from typing import Optional

from pydantic import ConfigDict, HttpUrl
from pydantic.alias_generators import to_camel
from sqlalchemy import func
from sqlalchemy.types import String, TypeDecorator, DateTime
from sqlmodel import Field, SQLModel

from .config import settings


class HttpUrlType(TypeDecorator):
    impl = String(2083)
    cache_ok = True
    python_type = HttpUrl

    def process_bind_param(self, value, dialect) -> str:
        return str(value)

    def process_result_value(self, value, dialect) -> HttpUrl:
        return HttpUrl(url=value)

    def process_literal_param(self, value, dialect) -> str:
        return str(value)


class UrlBase(SQLModel):
    model_config = ConfigDict(
        alias_generator=to_camel,
        populate_by_name=True,
        from_attributes=True,
    )

    url: HttpUrl = Field(
        index=True,
        unique=True,
        nullable=False,
        sa_type=HttpUrlType,
    )


class Url(UrlBase, table=True):
    __tablename__ = "urls"

    id: str = Field(
        max_length=settings.url_id_length,
        min_length=settings.url_id_length,
        primary_key=True,
        nullable=False,
    )
    visits: int = Field(default=0)
    created_at: Optional[datetime] = Field(
        default=None,
        sa_type=DateTime(timezone=True),
        sa_column_kwargs={
            "server_default": func.now(),  # pylint: disable=E1102
        },
    )
    updated_at: Optional[datetime] = Field(
        default=None,
        sa_type=DateTime(timezone=True),
        sa_column_kwargs={
            "onupdate": func.now(),  # pylint: disable=E1102
        },
    )


class UrlCreate(UrlBase):
    pass


class UrlRead(UrlBase):
    id: str
    visits: int
    created_at: datetime
    updated_at: Optional[datetime]