Working with JSON fields in Postgres (using SQLAlchemy and Pydantic)

[navhits]

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 FastAPI documentation, with the integrated search.
• I already searched in Google "How to X in FastAPI" 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 FastAPI but to Pydantic.
• I already checked if it is not related to FastAPI but to Swagger UI.
• I already checked if it is not related to FastAPI but to ReDoc.
• After submitting this, I commit to one of:
  • Read open issues with questions until I find 2 issues where I can help someone and add a comment to help there.
  • I already hit the "watch" button in this repository to receive notifications and I commit to help at least 2 people that ask questions in the future.
  • Implement a Pull Request for a confirmed bug.

Question

I tried to create a rest API with FastAPI using Postgres. I used SQLAlchemy ORM with Alembic for migrations. With Pydantic I create a schema and I used JSON field first. When I received a 422 error I tried dict as JSON becomes a dictionary while while working on the data in Python.
Then I received an error saying that the `table' was not found and I also found the table wasn't created (I use Alembic)

Here's the ORM model (model.py)

from sqlalchemy.dialects.postgresql import JSON
class User(Base):
    __tablename__ = "test"
    id = Column(Integer, primary_key=True, index=True)
    data = Column(JSON)

And here's the schema

class User(BaseModel):
    data: dict  # Tried using Json module from pydantic.types
    class Config:
        orm_mode = True

The request body would be,

{
    "data" : {
        "key" : "value",
        "key" : ["value1", "value2"],
        "key" : {
            "key" : "value",
             ...
         }
        ...
    }
}

Description

• When I post data the body would be a JSON object.
• What I expect is to have a column in Postgres as Json field and work with that only. I was able to do this easily on Django
• With FastAPI its quite tricky for me atleast.
• The swagger docs/openAPI looks fine to me.
• The table was not being created when using Alembic, but the migration script runs without any errors.

Environment

• OS: macOS

• FastAPI Version: 0.49.2

• Python version: 3.7.9

Additional context

Since a single row will have multiple processes updating info on each API calls, the column needs to be a JSON field so that for each instance would be updated as key:value pairs. I could maybe go with with NoSQL systems like Mongo. But Postgres seems a better option to me.

Any help appreciated! Thanks!

[ArcLightSlavik]

from typing import Dict

from fastapi import FastAPI
from pydantic import BaseModel

app = FastAPI()


class Item(BaseModel):
    name: str
    other: Dict[str, float] = None


@app.post("/items")
def update_item(item: Item):
    return {"item": item}

Results in:

[navhits]

Looks like this almost solved my problem. Plus there has been another issue which I did not find out. When I do alembic revision it does not generate the migration script for me. I have no idea why. Added the upgrade script manually and it works.

Any suggestions on why this happens? (Also checking SQLAlchmy docs and issues in parallel)

PS: I used Dict[Any, Any] instead to allow nested JSON and more.

[ArcLightSlavik]

Never used alembic so can't comment on that, is not related to fastapi as well.

If you know the expected structure you can do something like Dict[Dict[str, str], str] and so on.

[navhits]

Alright, thank you @ArcLightSlavik. The structure you mentioned helps me. Though it might take a while to fix a schema if needed. Since my problem with FastAPI is done, I'm closing this issue thread. Feel free add any comments that might be helpful :)

[anoopksharma22]

@navhits Thank you so much for this post.