Unable to map FastAPI List[], Set() through SQLModel into Postgres

Question

I'm doing my personal portfolio API with FastAPI and decided to try SQLModel. It feels so intuitive and I love it so far, but I encountered a problem that I have been struggling with for days, trying to understand how to make it right.

I have a Project model:

from datetime import datetime
from typing import List, Optional, Set
from sqlmodel import SQLModel, Field


class ProjectBase(SQLModel):
    name: Optional[str]
    summary: Optional[str]
    description: Optional[str]
    category: Set[str] = ()
    award: Optional[str] = None
    url: Optional[str] = None
    published: datetime = datetime.utcnow()
    image: str = "placeholderMainImage"
    images: List[str] = []
    learning: Optional[str]
    tech: Optional[str]
    tools: Optional[str]



class Project(ProjectBase, table=True):
    id: int = Field(default=None, primary_key=True)
    created_at: datetime = Field(default_factory=datetime.utcnow)
    updated_at: datetime = Field(default_factory=datetime.utcnow)

FastAPI works fine and I checked localhost:8000/docs and it is making the types validation correctly:

    {
  "name": "string",
  "summary": "string",
  "description": "string",
  "category": [],
  "award": "string",
  "url": "string",
  "published": "2021-10-04T20:43:26.472364",
  "image": "placeholderMainImage",
  "images": [],
  "learning": "string",
  "tech": "string",
  "tools": "string",
  "id": 0,
  "created_at": "2021-10-04T21:01:30.048Z",
  "updated_at": "2021-10-04T21:01:30.048Z"
}

When I make a POST request with the above query, I get internal server error:

invalid input for query argument $4: (expected str, got set)

Unfortunately, SQLModel by design converts any strange type into VARCHAR at table creation, making it impossible to use the List or Set functionality:

   CREATE TABLE project (
         name VARCHAR, 
         summary VARCHAR, 
         description VARCHAR, 
         category VARCHAR, 
         award VARCHAR, 
         url VARCHAR, 
         published TIMESTAMP WITHOUT TIME ZONE, 
         image VARCHAR, 
         images VARCHAR, 
         learning VARCHAR, 
         tech VARCHAR, 
         tools VARCHAR, 
         id SERIAL, 
        created_at TIMESTAMP WITHOUT TIME ZONE, 
        updated_at TIMESTAMP WITHOUT TIME ZONE, 
        PRIMARY KEY (id)
 )

I have understood that postgres has some array types like: integer[] and text[] that could handle this case scenario for the category and images fields. I tried manually changing the table columns types with same result.

I tried to post category and images as str:

    {
  "detail": [
    {
      "loc": [
        "body",
        "category"
      ],
      "msg": "value is not a valid set",
      "type": "type_error.set"
    },
    {
      "loc": [
        "body",
        "images"
      ],
      "msg": "value is not a valid list",
      "type": "type_error.list"
    }
  ]
}

It will be so sad to not be able to use such amazing features to sanitize the data I'm receiving. I looked on the internet and couldn't find anything related or an example using List and Set with SQLModel.

What can I do to support this scenario?

PD: I'm also using asyncpg

I think that you should consider implementing a non relational database for this kind of db inserts. But if you insist SQL model has a solution so check the following link: github.com/tiangolo/sqlmodel/issues/178 — Kostas Nitaf
– Kostas Nitaf, Commented Jan 29, 2023 at 14:14

JarroVGIT · Accepted Answer · 2021-10-05 17:23:41Z

7

I got it working for Lists, but not for Sets on a postgres database. The problem lies in when results are mapped back to your Project class. Here is my full code.

from datetime import datetime
from typing import List, Optional, Set
from sqlalchemy.sql.schema import Column
from sqlmodel import SQLModel, Field, create_engine, Session, select, String, ARRAY 
from fastapi import FastAPI

class ProjectBase(SQLModel):
    name: Optional[str]
    category: Set[str] = Field(default=None, sa_column=Column(ARRAY(String())))
    images: List[str] = Field(default=None, sa_column=Column(ARRAY(String())))


class Project(ProjectBase, table=True):
    id: int = Field(default=None, primary_key=True)

engine = create_engine("postgresql://postgres:mysecretpassword@localhost:5432/testdb")

new_proj = Project(
    name=f"{str(datetime.time(datetime.utcnow()))}", 
    category=("hi", "hello", "cat3"), 
    images=["img1", "img2"] 
)

app = FastAPI()

@app.on_event("startup")
def on_startup():
    SQLModel.metadata.create_all(engine)
    print("BEFORE REFRESH:", new_proj)
    with Session(engine) as session:
        session.add(new_proj)
        session.commit()
        session.refresh(new_proj)
    print("AFTER REFRESH:", new_proj)

@app.get("/", response_model=List[Project])
def home():
    with Session(engine) as session:
        projects = session.exec(select(Project)).all()
        return projects

I have simplified your Project class a bit. Upon starting uvicorn running this, I get the following output (ref to the @app.on_event("startup")):

BEFORE REFRESH: name='17:17:38.090595' category={'hoi', 'cat3', 'hello'} images=['img1', 'img2'] id=None
AFTER REFRESH: id=7 category=['hoi', 'cat3', 'hello'] images=['img1', 'img2'] name='17:17:38.090595'

Note that the difference in the category value, starting with { (denoting a set) before the object is refreshed from the database, and starting with [ after the object is refreshed from the database.

I have tried all kinds of things but to no avail. Only thing I could think of (but couldn't find anything about) was some sort of custom mapping rule for sqlalchemy. I don't know enough about that package though to really understand what I am looking for. Hope this helps at least! I learnt a lot either way :)

answered Oct 5, 2021 at 17:23

JarroVGIT

5,6042 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan David Garavito Espejo Over a year ago

Wow, thanks, i will try and let you know about.!!

Juan David Garavito Espejo Over a year ago

Thanks, it worked, so what i understand you are explicitly telling SQLModel to use the SQLAlchemy ARRAY(String()) . for the moment i will use this array. thanks Let's see if someone knows something about Set()

Collectives™ on Stack Overflow

Unable to map FastAPI List[], Set() through SQLModel into Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related