Can you override what SQLModel does to convert from the Python type to the SQLAlchemy type?

[nicklockhart-fullfibre]

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 documentation, with the integrated search.
• I already searched in Google "How to X" and didn't find any information.
• I already read and followed all the tutorials in the docs and didn't find an answer.

Example Code

from ipaddress import IPv4Address, IPv6Address

from pydantic.networks import IPvAnyAddress
from sqlmodel import Field, SQLModel
from sqlmodel.sql.sqltypes import AutoString


# This model won't work as-is, you'll need to pick just one of the ip_ fields,
# and for this issue, you probably want ip_masquerade.
class ModelWithIpAddress(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)

    # this works - the IP address is converted to a string (addr.exploded)
    # when you INSERT an instance of this model (session.add) into a database
    ipv4_working: IPv4Address = Field()
    # v6 should also work:
    ipv6_working: IPv6Address = Field()

    # neither of these work, for different reasons:

    # "ValueError: <class 'pydantic.networks.IPvAnyAddress'> has no matching
    # SQLAlchemy type"
    ip_v_any: IPvAnyAddress = Field()

    # "ValueError: Cannot have a (non-optional) union as a SQLAlchemy field"
    ip_union: IPv4Address | IPv6Address = Field()

    # now, if I override the sa_type to AutoString, the model can be created...
    ip_masquerade: IPvAnyAddress = Field(sa_type=AutoString)
    # ...but when I try to INSERT this into the database, I get the following:
    #
    # DBAPIError: <class 'asyncpg.exceptions.DataError'>: invalid input for
    # query argument $6: IPv6Address('xxxx::xxxx:xxxx:xxxx:xxxx') (expected str,
    # got IPv6Address)
    #
    # so, is it possible to override how a field is converted before being
    # INSERTed into the database?

Description

Replicating

• Create a model with a field where the type is an ipaddress union - either pydantic.networks.IPvAnyAddress or a manual ipaddress.IPv4Address | IPv6Address
• Specify the type is sqlmodel.sql.sqltypes.AutoString, which is what sqlmodel.get_sqlalchemy_type returns for IPv4Adress and IPv6Address
• Create an instance of the model, and INSERT it into the database (get Session, then session.add(ip_model_instance))
• SQLAlchemy throws a "invalid input for query argument" error

Question

Is it possible to override how a given field is converted to (for example) an AutoString before being written to the database?

SQLModel seems to already do this for IPv4Address and IPv6Address if a field is an IPvXAddress and an IPvXAddress only. It uses the sa_type of AutoString and converts it to a string before writing it to the database.

However, if you use IPv4Address | IPv6Address or pydantic.networks.IPvAnyAddress , then set the sa_type to AutoString, you run into a weird bug where you can create the model and the table, but not insert into it, as SQLModel passes the raw ipaddress object to SQLAlchemy, instead of converting it to a string first.

I can work around this by specifying the field type as str, then writing a class method that takes an IPvAnyAddress object for that field, and converts it to a string before creating the instance, but this is a bit clunky, and I'm not the biggest fan of this solution. I'd prefer to just have the field typed as IPvAnyAddress and have the conversion handled internally somehow.

Is this currently possible in SQLModel?

Operating System

macOS

Operating System Details

macOS Golden Gate 27.0.1, aarch64

Project Version

SQLModel 0.0.39

Python Version

Python 3.14.3

Additional Context

If this is resolved in a newer version of SQLModel, I am able to update to a newer version.

[kasparovabi]

Yes, with a SQLAlchemy TypeDecorator passed as sa_type. SQLModel never converts the value itself. For IPv4Address / IPv6Address it only picks AutoString as the column type (get_sqlalchemy_type), and AutoString has no bind step, so the ipaddress object goes to the driver as is. Whether that works then depends on the driver. On SQLite, a plain ip: IPv4Address field fails the same way once the attribute holds an IPv4Address object:

sqlite3.ProgrammingError: Error binding parameter 1: type 'IPv4Address' is not supported

A decorator that does the conversion in both directions:

from ipaddress import IPv4Address, IPv6Address, ip_address

from pydantic.networks import IPvAnyAddress
from sqlalchemy.types import TypeDecorator
from sqlmodel import Field, SQLModel
from sqlmodel.sql.sqltypes import AutoString


class IPAddressString(TypeDecorator):
    impl = AutoString
    cache_ok = True

    def process_bind_param(self, value, dialect):
        return None if value is None else str(value)

    def process_result_value(self, value, dialect):
        return None if value is None else ip_address(value)


class Host(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    ip_any: IPvAnyAddress = Field(sa_type=IPAddressString)
    ip_union: IPv4Address | IPv6Address = Field(sa_type=IPAddressString)

sa_type also gets past the "Cannot have a (non-optional) union" error, because SQLModel returns it before looking at the annotation. process_bind_param runs on INSERT and on bound values in WHERE clauses, so select(Host.id).where(Host.ip_any == IPv6Address("2001:db8::1")) matches the stored row (the where: line below). process_result_value gives you ipaddress objects back on load.

Run with sqlmodel 0.0.39, SQLAlchemy 2.0.55, pydantic 2.14.1, Python 3.12, SQLite in memory. Your Field(sa_type=AutoString) model with an IPv6Address value:

ProgrammingError (sqlite3.ProgrammingError) Error binding parameter 1: type 'IPv6Address' is not supported

The model above, one row built from objects and one through Host.model_validate({...}) from strings:

IPv6Address('2001:db8::1') IPv4Address('192.0.2.7')
IPv4Address('192.0.2.8') IPv6Address('2001:db8::2')
raw columns: [('2001:db8::1', '192.0.2.7', 'text'), ('192.0.2.8', '2001:db8::2', 'text')]
where: [1]

I did not run it against PostgreSQL with asyncpg. The bind step happens before the driver sees the value, so asyncpg should get a str there, but that part is not tested.

[nicklockhart-fullfibre]

Thank you, this works perfectly! For anyone else coming across this thread, yes, this works fine with PostgreSQL/asyncpg.

I'd gotten very caught up in a thought process of "there must be an option to solve this somewhere in SQLModel..." and didn't even think of checking the SQLAlchemy docs 😅 I was also under the vague impression that sa_type was 1-to-1 with SQL types, and didn't know you could define custom SQLAlchemy types like that, so I'll try to keep that in mind for future use.