How to add BIT(1) data type to field?

[dragonballa]

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 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 datetime import datetime
from typing import Optional
from sqlmodel import Field, SQLModel

class Test(SQLModel, table=True):
    text: str
    code: bit[1]
    date: datetime

Description

First of all I don't know exactly what are all the available types are so I looked at the sqlalchemy import statements over at https://github.com/tiangolo/sqlmodel/blob/7fcd4fd7c5661d2621e89ebf3a87059ac91ea6d2/sqlmodel/__init__.py

However I do not see an equivalent for MySQL's BIT(1) so what can I do here? I am trying to connect to an existing MySQL table and it has a data type as BIT(1) which when I google it seems to be a Boolean? or TINYINT

Just not clear to me how I can make SQLModel work with this table containing BIT(1) as data type.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.8

Additional Context

No response

[oldfielj-ansto]

@dragonballa All the type mappings to the DB are hard coded right now and for the moment seem to be fairly limited.
https://github.com/tiangolo/sqlmodel/blob/7fcd4fd7c5661d2621e89ebf3a87059ac91ea6d2/sqlmodel/main.py#L378-L417

It should be possible to extend this method and add extra mappings to the column types you need assuming SQLAlchemy supports them.

[dragonballa]

@oldfielj-ansto how do i import this so I can access the other fields from here? do i do

from typing import datetime, date for instance?

if i want to add a VARCHAR type

how would I do this? i really need some assistance with this as it is basically a deal breaker for me to use SQLModel in my project or not. Really want to use this but this data type extension part im not familiar with.

 if issubclass(field.type_, varchar): 
     return varchar 

[antont]

If your bit is a boolean, you can use that SQLAlchemy type in a field declaration, https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.Boolean

from sqlalchemy import Column, String
from sqlmodel import Field

(...)
code: bool = Field(sa_column=Column(Boolean))

Not tested but should be like that - this is an actual thing we use to declare a pg string array

from sqlalchemy import Column, String
from sqlalchemy.dialects import postgresql #ARRAY contains requires dialect specific type
from sqlmodel import Field

(...)
tags: Optional[Set[str]] = Field(default=None, sa_column=Column(postgresql.ARRAY(String())))

[oldfielj-ansto]

@dragonballa SQLModel is built to be as generic as possible to ensure compatibility with a wide array of database flavors, but as @antont points out you can add a specific column type override and a better supported way of doing this would be to use a boolean.

Accessing an existing database schema does complicate things a little, but a solution like the following should work.

from sqlmodel import Field, SQLModel
from sqlalchemy import Column
from sqlalchemy.dialects import mysql
from datetime import datetime


class Test(SQLModel, table=True):
    text: str = Field(title="Text")
    code: int = Field(title="Code", ge=0, le=1, sa_column=Column("code", mysql.BIT(1)))
    date: datetime = Field(title="Date")

I don't have a MySQL database handy to test the behavior here, but I can tell you that by default SQLModel picks VARCHAR as the column type for PostgreSQL databases, so you might not need to override the column type, but if for some reason you needed to be explicit, something like this should work.

from sqlmodel import Field, SQLModel
from sqlalchemy import Column
from sqlalchemy.dialects import mysql
from datetime import datetime


class Test(SQLModel, table=True):
    text: str = Field(title="Text", sa_column=Column("text", mysql.VARCHAR()))
    code: int = Field(title="Code", ge=0, le=1, sa_column=Column("code", mysql.BIT(1)))
    date: datetime = Field(title="Date")

Also bear in mind that applying this sort of patch would mean your code would only work on MySQL databases and would need to be modified if you decided to switch to another database software such as PostgreSQL.

Another thing worth noting here is that passing sa_column will mean the "primary_key", "index" and similar parameters on your field definition will not work, so if you need to pass them you'll have to manually specify them in your column definition.

from sqlmodel import Field, SQLModel
from sqlalchemy import Column
from sqlalchemy.dialects import mysql
from datetime import datetime


class Test(SQLModel, table=True):
    text: str = Field(title="Text")
    code: int = Field(title="Code", ge=0, le=1, sa_column=Column(
        "code",
        mysql.BIT(1),
        primary_key=True,
        index=True,
    ))
    date: datetime = Field(title="Date")

My suggestion would be to install MySQL locally on your machine and start playing around creating new databases from your SQLModel's and see what column types you get, as SQLModel heavily relies on SQLAlchemy in this area.

Sorry I can't give you anything more concrete, but I primarily work with PostgreSQL these days.