Add List type support

[Spenhouet]

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

[Nikola-Milovic]

Any proposals open for this? Seems like a pretty common issue? Or is this a limitation on sqlalchemy side?

[JesusMF23]

bump, we need this, is pretty common in today's use cases

[enrh]

Bump

[pauldamian]

Bump.
I'm getting ValueError: <class 'list'> has no matching SQLAlchemy type in sqlmodel's main module.

[VGBijpuria]

If this doesn't get added soon, I'll write a PR

[wide0s]

And I need it, for my use cases. Voted.

[Spenhouet]

@tiangolo Surprised by my feature request being converted to a discussion. Could you expand on that?

[tiangolo]

Yes, all feature requests should start as discussions, then we can evaluate them from there.

Keeping issues open becomes an AI bait, and automated agents create automated AI generated PRs (that in many cases are wrong in many ways). So I'm moving existing issues to discussions to organize things.

[Spenhouet]

What does that mean for this feature request i.e. how does "evaluate from there" mean for this specific feature request?