Unable tu 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 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 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 made the 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 imposible 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 have some array types like: integer[] and text[] that could handle this case scenario for the category and images fields. Tried manually change the table columns types with same result.

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 internet and can't find yet anything related or an example using List and Set with SQLModel

¿What can I do to support this case scenario?

PD: I'm also using asyncpg

Answer 1

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: https://github.com/tiangolo/sqlmodel/issues/178

Answer 2

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