pathlib.Path is probably an unsupported type

[matutter]

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 pathlib import Path
from typing import Optional

import pytest
from sqlmodel import VARCHAR, Column, Field, Session, SQLModel, create_engine


class MyFile(SQLModel, table=True):
  id: Optional[int] = Field(default=None, primary_key=True)
  path: Path = Field(sa_column=Column(VARCHAR))


@pytest.fixture
def session() -> Session:
  engine = create_engine(
    'sqlite://', connect_args={'check_same_thread': False})
  SQLModel.metadata.create_all(engine)
  with Session(engine) as session:
    yield session


def test_myfile(session: Session, tmp_path: Path):
  session.add(MyFile(path=tmp_path / 'test.txt'))
  session.commit()

Description

• Create a model with a field of type pathlib.Path.
• Create a table where the field is mapped as a VARCHAR.
• Insert into the table.
• Select from table and have the field mapped back as a pathlib.Path.

Operating System

Linux

Operating System Details

Ubuntu 22.04.

SQLModel Version

0.0.6

Python Version

3.10

Additional Context

Logs from the test case:

2022-08-10 10:56:17,187 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("myfile")
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("myfile")
2022-08-10 10:56:17,188 INFO sqlalchemy.engine.Engine [raw sql] ()
2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine 
CREATE TABLE myfile (
        path VARCHAR, 
        id INTEGER, 
        PRIMARY KEY (id)
)


2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine [no key 0.00012s] ()
2022-08-10 10:56:17,189 INFO sqlalchemy.engine.Engine COMMIT
-------------------------------------------------------------------------- Captured stderr setup ---------------------------------------------------------------------------
sqlalchemy.engine.Engine - BEGIN (implicit)
sqlalchemy.engine.Engine - PRAGMA main.table_info("myfile")
sqlalchemy.engine.Engine - [raw sql] ()
sqlalchemy.engine.Engine - PRAGMA temp.table_info("myfile")
sqlalchemy.engine.Engine - [raw sql] ()
sqlalchemy.engine.Engine - 
CREATE TABLE myfile (
        path VARCHAR, 
        id INTEGER, 
        PRIMARY KEY (id)
)


sqlalchemy.engine.Engine - [no key 0.00012s] ()
sqlalchemy.engine.Engine - COMMIT
---------------------------------------------------------------------------- Captured log setup ----------------------------------------------------------------------------
INFO     sqlalchemy.engine.Engine:base.py:953 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1772 PRAGMA main.table_info("myfile")
INFO     sqlalchemy.engine.Engine:base.py:1777 [raw sql] ()
INFO     sqlalchemy.engine.Engine:base.py:1772 PRAGMA temp.table_info("myfile")
INFO     sqlalchemy.engine.Engine:base.py:1777 [raw sql] ()
INFO     sqlalchemy.engine.Engine:base.py:1772 
CREATE TABLE myfile (
        path VARCHAR, 
        id INTEGER, 
        PRIMARY KEY (id)
)


INFO     sqlalchemy.engine.Engine:base.py:1777 [no key 0.00012s] ()
INFO     sqlalchemy.engine.Engine:base.py:1013 COMMIT
--------------------------------------------------------------------------- Captured stdout call ---------------------------------------------------------------------------
2022-08-10 10:56:17,194 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine INSERT INTO myfile (path) VALUES (?)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
2022-08-10 10:56:17,195 INFO sqlalchemy.engine.Engine ROLLBACK
--------------------------------------------------------------------------- Captured stderr call ---------------------------------------------------------------------------
sqlalchemy.engine.Engine - BEGIN (implicit)
sqlalchemy.engine.Engine - INSERT INTO myfile (path) VALUES (?)
sqlalchemy.engine.Engine - [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
sqlalchemy.engine.Engine - ROLLBACK
---------------------------------------------------------------------------- Captured log call -----------------------------------------------------------------------------
INFO     sqlalchemy.engine.Engine:base.py:953 BEGIN (implicit)
INFO     sqlalchemy.engine.Engine:base.py:1772 INSERT INTO myfile (path) VALUES (?)
INFO     sqlalchemy.engine.Engine:base.py:1777 [generated in 0.00029s] (PosixPath('/tmp/pytest-of-vscode/pytest-187/test_myfile0/test.txt'),)
INFO     sqlalchemy.engine.Engine:base.py:981 ROLLBACK
========================================================================= short test summary info ==========================================================================
FAILED tests/test_misc.py::test_myfile - sqlalchemy.exc.InterfaceError: (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.

[antont]

I don't know about this, but would not be surprising as a Path is not a string, but has also attributes like parents. It can be made from a string though, so that would at least work with the varchar in db.

Searched for the docs and happened to see this, might be a different thing though as is not with db code there. fastapi/fastapi#563

Ah the fix there is to make it jsonable:
"This PR adds a case to jsonable_encoder so that pathlib.PurePath objects are serializable."

fastapi/fastapi#978

Other folks here use json db fields for some things I think, we haven't so far but just have strings and ints and such in the db like usually.

Am using strings for paths in the sqlmodel db models. Well or don't have paths but just filenames and paths come from runtime logic.

[dd-ssc]

Any update on this ?

I think I can live with modeling Path as str for the moment and cast it back to Path when I use the data, but something like Field(sa_column=Column(TEXT)) would obviously be much more elegant.

Can I somehow add the FastAPI fix to my code locally ?

[strfx]

Any update on this ?

I think I can live with modeling Path as str for the moment and cast it back to Path when I use the data, but something like Field(sa_column=Column(TEXT)) would obviously be much more elegant.

Can I somehow add the FastAPI fix to my code locally ?

You maybe could use a SQLAlchemy Custom Type to convert Path to / from string:

from pathlib import Path
from sqlalchemy.types import TypeDecorator, String

class PathType(TypeDecorator):
    impl = String

    def process_bind_param(self, value, dialect):
        if isinstance(value, Path):
            return str(value)
        return value

    def process_result_value(self, value, dialect):
        if value is not None:
            return Path(value)
        return value

And use it like this:

class MyFile(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    path: Path = Field(sa_column=Column(PathType))

Stumbled on this thread by accident while researching a similar issue. Hope it helps!