Add List type support · Issue #178 · fastapi/sqlmodel · GitHub

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

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.

Are there any updates on this?

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

I think the answer of mkarbo is just perfect.

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

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

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

Bump

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

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

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

Add List type support · Issue #178 · fastapi/sqlmodel · GitHub

Repository navigation

Add List type support #178

First Check

Commit to Help

Example Code

Description

Wanted Solution

Wanted Code

Alternatives

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Add List type support · Issue #178 · fastapi/sqlmodel · GitHub

Repository navigation

Add List type support #178

Description

First Check

Commit to Help

Example Code

Description

Wanted Solution

Wanted Code

Alternatives

Operating System

Operating System Details

SQLModel Version

Python Version

Additional Context

Activity

mkarbo commented on Dec 9, 2021

antont commented on Feb 18, 2022

GussSoares commented on Feb 23, 2022

FilipeMarch commented on Mar 20, 2022

antont commented on Mar 21, 2022

0dminnimda commented on Oct 22, 2022

srausser commented on Oct 25, 2023

Matthieu-LAURENT39 commented on Oct 25, 2023

barseghyanartur commented on Jan 16, 2024

JakNowy commented on Apr 12, 2024

Nikola-Milovic commented on Oct 10, 2024

JesusMF23 commented on Mar 22, 2025

enrh commented on May 5, 2025

pauldamian commented on May 19, 2025

VGBijpuria commented on Jun 28, 2025

wide0s commented on Aug 7, 2025

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions