Add List type support

[Spenhouet]

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

[mkarbo]

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.

[antont]

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]))

[GussSoares]

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

[FilipeMarch]

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

[antont]

@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.

[0dminnimda]

Are there any updates on this?

[srausser]

Are there any updates on this?

[Matthieu-LAURENT39]

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

[barseghyanartur]

I think the answer of mkarbo is just perfect.

[JakNowy]

I think the answer of mkarbo is just perfect.

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! :)