General practices to convert existing SQLAlchemy tables into SQLModel

priamai commented 1 year ago

First Check

[X] I added a very descriptive title to this issue.
[X] I used the GitHub search to find a similar issue and didn't find it.
[X] I searched the SQLModel documentation, with the integrated search.
[X] I already searched in Google "How to X in SQLModel" and didn't find any information.
[X] I already read and followed all the tutorial in the docs and didn't find an answer.
[X] I already checked if it is not related to SQLModel but to Pydantic.
[X] I already checked if it is not related to SQLModel but to SQLAlchemy.

Commit to Help

[X] I commit to help with one of those options 👆

Example Code

class MetricBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    fact_name: str
    dimensions: Optional[List] = Field(default=[])
    measures: Optional[List] = Field(default=[])
    params: Optional[Dict]

Description

It would be nice to show a few examples about how to model arrays and json SQL columns. In general what principles should I follow to convert from a SQLAlchemy Table definition?

Operating System

Linux

Operating System Details

Ubuntu 21.0

SQLModel Version

0.0.8

Python Version

3.8.10

Additional Context

For example I am trying to convert this existing table:


from sqlalchemy import Column, Integer, String, DateTime, BigInteger, SmallInteger,LargeBinary, ForeignKey, Table, Float,Boolean
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.postgresql import JSONB,JSON, ARRAY

class Metrics(Base):
    __tablename__ = 'metrics'
    __table_args__ = {'extend_existing':True}

    # billing item id
    id = Column(BigInteger, autoincrement=True, primary_key=True)

    # the fact name
    fact_name = Column(String,nullable=False)
    dimensions = Column(ARRAY(String))
    measures = Column(ARRAY(String))
    sql_query = Column(String)
    rest_query = Column(String)
    params_query = Column(JSON)
    chart_types = Column(JSON)
    chart_title = Column(String)

meirdev commented 1 year ago

In general, you can use any column type from sqlalchemy by specifying it in sa_column:

class MetricBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    fact_name: str
    dimensions: Optional[List] = Field(default_factory=list, sa_column=Column(ARRAY(String)))
    measures: Optional[List] = Field(default_factory=list, sa_column=Column(ARRAY(String)))
    params: Optional[Dict] = Field(default_factory=dict, sa_column=Column(JSON))

priamai commented 1 year ago

Hello thanks, that makes sense, what will happen if the user gets confused. Stupid example:

class MetricBase(SQLModel):
    id: Optional[int] = Field(default=None, primary_key=True)
    fact_name: str
    dimensions: Optional[List[int]] = Field(default_factory=list, sa_column=Column(ARRAY(String)))
    measures: Optional[List[int]] = Field(default_factory=list, sa_column=Column(ARRAY(String)))
    params: Optional[Dict] = Field(default_factory=dict, sa_column=Column(JSON))

I am actually testing it now...

meirdev commented 1 year ago

This will cause confusion between the DB and your API (if you're using fastapi for example, you won't be able to send non-int values) but it works manually.

works:

metric = Metric()
metric.fact_name = "test"
metric.dimensions = ["a", "b", "c"]  # works!
metric.measures = [4, 5, 6]
metric.params = {"foo": "bar"}

session.add(metric)
session.commit()

not works:

app = FastAPI()

@app.post("/")
async def test_post(metric: Metric):
    return metric

POST /
{
  "id": 0,
  "fact_name": "test",
  "dimensions": [
    "a",
    "b",
    "c"
  ],
  "measures": [
    4,
    5,
    6
  ],
  "params": {
    "foo": "bar"
  }
}

{
  "detail": [
    {
      "loc": [
        "body",
        "dimensions",
        0
      ],
      "msg": "value is not a valid integer",
      "type": "type_error.integer"
    },
    {
      "loc": [
        "body",
        "dimensions",
        1
      ],
      "msg": "value is not a valid integer",
      "type": "type_error.integer"
    },
    {
      "loc": [
        "body",
        "dimensions",
        2
      ],
      "msg": "value is not a valid integer",
      "type": "type_error.integer"
    }
  ]
}

priamai commented 1 year ago

Hello, thanks this makes total sense now. What happens when you don't specify the Field column, will the backend auto infer the nearest possible SQL column type? Also is it best practice to specify both default_factory (pydantic) and default for SA? Thanks again for the spoon feeding!

priamai commented 1 year ago

Responding to my own question I get this error:

ValueError: cannot specify both default and default_factory

meirdev commented 1 year ago

You can find all conversions between python types and sqlalchemy types here:

https://github.com/tiangolo/sqlmodel/blob/7b3148c0b4bba173710c774c951cee89dcc95c39/sqlmodel/main.py#L374-L414

It's preferred to use default_factory when you're dealing with mutable types.

fastapi / sqlmodel