Creating a column to store binary data (Buffer, Byte Array or BLOB)

[gsusrafael]

First Check

• I added a very descriptive title here.
• I used the GitHub search to find a similar question 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 Optional
from sqlmodel import Field, SQLModel


class ResidencialBase(SQLModel):
    id_cliente: int = Field(index=True)
    id_tipo_residencial: int = Field(index=True)
    nombre: str = Field(index=True, max_length=50)
    telefono_1: str = Field(max_length=15)
    telefono_2: Optional[str] = Field(max_length=15)
    correo: str = Field(index=True, max_length=255)
    direccion: str
    logo: bytearray
    id_usuario: int
    activo: bool


class Residencial(ResidencialBase, table=True):
    id_residencial: Optional[int] = Field(default=None, primary_key=True)


class ResidencialCreate(ResidencialBase):
    pass


class ResidencialRead(ResidencialBase):
    id_residencial: int


class ResidencialUpdate(SQLModel):
    id_cliente: Optional[int] = Field(index=True)
    id_tipo_residencial: Optional[int] = Field(index=True)
    nombre: Optional[str] = Field(index=True, max_length=50)
    telefono_1: Optional[str] = Field(max_length=15)
    telefono_2: Optional[str] = Field(max_length=15)
    correo: Optional[str] = Field(index=True, max_length=255)
    direccion: Optional[str]
    logo: Optional[bytearray]
    id_usuario: Optional[int]
    activo: Optional[bool]

Description

• Created the model with a column to store a binary file (bytearray)
• Tried to start the application, and crashed on startup

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.8

Python Version

Python 3.10.11

Additional Context

Traceback (most recent call last):
File "/home/jesus/Workspace/WebApp/portal-pass-backend/main.py", line 18, in
from api.endpoints import example, cliente, residencial
File "/home/jesus/Workspace/WebApp/portal-pass-backend/api/endpoints/residencial.py", line 14, in
from api.models.residencial import (
File "/home/jesus/Workspace/WebApp/portal-pass-backend/api/models/residencial.py", line 13, in
class ResidencialBase(SQLModel):
File "/home/jesus/.virtualenvs/portal-pass/lib/python3.10/site-packages/sqlmodel/main.py", line 272, in new
new_cls = super().new(cls, name, bases, dict_used, **config_kwargs)
File "pydantic/main.py", line 197, in pydantic.main.ModelMetaclass.new
File "pydantic/fields.py", line 506, in pydantic.fields.ModelField.infer
File "pydantic/fields.py", line 436, in pydantic.fields.ModelField.init
File "pydantic/fields.py", line 557, in pydantic.fields.ModelField.prepare
File "pydantic/fields.py", line 831, in pydantic.fields.ModelField.populate_validators
File "pydantic/validators.py", line 765, in find_validators
RuntimeError: no validator found for <class 'collections.abc.ByteString'>, see arbitrary_types_allowed in Config

[Hrabal]

This question is pretty old, but seems to (still) be the first result when looking on how to handle binary data or files with SQLModel, so I'll leave here my solution:

from sqlmodel import SQLModel, Column, Field, LargeBinary

class Document(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str
    filename: str
    data: bytes = Field(sa_column=Column(LargeBinary))

the sa_column attribute lets you define columns with SQLAlchemy entities (here, Column and LargeBinary are the original SQLALchemy classes re-imported by SQLModel).