sqlmodel
sqlmodel copied to clipboard
tiangolo
Add List type support
First Check
- [X] I added a very descriptive title to this issue.
- [X] I used the GitHub search to find a similar issue and didn't find it.
- [X] I searched the SQLModel documentation, with the integrated search.
- [X] I already searched in Google "How to X in SQLModel" and didn't find any information.
- [X] I already read and followed all the tutorial in the docs and didn't find an answer.
- [X] I already checked if it is not related to SQLModel but to Pydantic.
- [X] 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 List
from sqlmodel import Field, Session, SQLModel, create_engine
class Block(SQLModel, table=True):
id: int = Field(..., primary_key=True)
values: List[str]
engine = create_engine("sqlite:///test_database.db", echo=True)
SQLModel.metadata.create_all(engine)
b = Block(id=0, values=['test', 'test2'])
with Session(engine) as session:
session.add(b)
session.commit()
Description
I'm trying to store a list or similar types directly to the database.
Currently this does not seem to be the case. The above example code gives me the following error:
InterfaceError: (sqlite3.InterfaceError) Error binding parameter 1 - probably unsupported type. [SQL: INSERT INTO block (id, "values") VALUES (?, ?)] [parameters: (0, ['test', 'test2'])] (Background on this error at: https://sqlalche.me/e/14/rvf5)
Wanted Solution
I would like to directly use the List type (or similar types like Dicts) to store data to a database column. I would expect SQLModel to serialize them.
Wanted Code
Exactly as in my MWE.
Alternatives
From another thread I tried to use this:
values: List[str] = Field(sa_column=Column(ARRAY(String)))
But this results in another error.
Operating System
Linux, Windows
Operating System Details
I'm working on the WSL.
SQLModel Version
0.0.4
Python Version
3.7.12
Additional Context
I used JSON type
from typing import List
from sqlmodel import Field, Session, SQLModel, create_engine, JSON, Column
class Block(SQLModel, table=True):
id: int = Field(..., primary_key=True)
values: List[str] = Field(sa_column=Column(JSON))
# Needed for Column(JSON)
class Config:
arbitrary_types_allowed = True
engine = create_engine("sqlite:///test_database.db", echo=True)
SQLModel.metadata.create_all(engine)
b = Block(id=0, values=['test', 'test2'])
with Session(engine) as session:
session.add(b)
session.commit()
with partial success as a workaround for a small project.
With Postgres, you can use an array of e.g. strings or ints. I'm having it as a Set on Python side to verify that don't get duplicates, but List works too. I think they are not supported in Sqlite though.
from sqlalchemy.dialects import postgresql #ARRAY contains requires dialect specific type
tags: Optional[Set[str]] = Field(default=None, sa_column=Column(postgresql.ARRAY(String())))
(...)
tagged = session.query(Item).filter(Item.tags.contains([tag]))
With Postgres, you can use an array of e.g. strings or ints. I'm having it as a Set on Python side to verify that don't get duplicates, but List works too. I think they are not supported in Sqlite though.
from sqlalchemy.dialects import postgresql #ARRAY contains requires dialect specific type tags: Optional[Set[str]] = Field(default=None, sa_column=Column(postgresql.ARRAY(String()))) (...) tagged = session.query(Item).filter(Item.tags.contains([tag]))
Thank you @antont !! perfectly work for postgres database
Why this is not supported by default?
I mean, is it possible for the user to accomplish the sames as OP wants but without using values: List[str], maybe representing it on another way that SQLModel allows
I ended up doing what @mkarbo suggested
@FilipeMarch - I guess one issue is that SQLite does not have arrays, whereas Postgres does. I'm using List but it means I can't use SQLite. Which is fine in our case, we need pg support only.
This is probably not that high on the priority list as there are workarounds, but even just having a list column use a Column(JSON) under the hood and not needing arbitrary_types_allowed in the Pydantic config would be a massive upgrade.
Although of course, it would be best to use Column(ARRAY(...)) when possible, but that's probably a bit harder
It's not perfect, as it introduces code redundancy. Assume you have a model for Fastapi endpoint input validation:
class ItemBase(SQLModel):
heroes: list[Hero]
names: list[str] | None
value: int
Then you have to redeclare those in your actual SQLModel table:
class Item(ItemBase, table=True):
heroes: list[Hero] = Relationship(back_populates="item")
names: list[str] = Field(sa_column=Column(ARRAY(String), nullable=True))
@tiangolo this would be alot cleaner to have it recognized out of the box! :)
@tiangolo @antont @barseghyanartur @srausser @Spenhouet
Buuuump.
Can we make it visible to the world somehow?