Flowing large hash values to Postgres BigInt

[cworkschris]

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 sqlalchemy import BigInteger
from typing import Optional


from sqlmodel import Field, Session, SQLModel, create_engine


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    #case A
    normhash: Optional[int] = Field(default=None, index=True)       
    #case B
    #normhash: Optional[BigInteger] = Field(default=None, index=True)           


hero_1 = Hero(normhash=1559512409891417611)

engine = create_engine("postgresql://app:newbpw@somehost:5400/some_db)


SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    session.add(hero_1)
    session.commit()
    # in case A: DataError: (psycopg2.errors.NumericValueOutOfRange) integer out of range
    # (case B the code won't even finish coming up - no validator error)
    session.refresh(hero_1)
    print(hero_1)

Description

Using your default Hero example.
Replace the fields with a hash field.
Using postgres, I'm unable to set up the field for big integers. Case A: using standard int results in NumericValueOutOfRange at the psycopg2 level.

So, case B: trying to force a postgres BIGINT, using sqlalchemy BigInteger, I get:
File "pydantic/validators.py", line 715, in find_validators
RuntimeError: no validator found for <class 'sqlalchemy.sql.sqltypes.BigInteger'>, see arbitrary_types_allowed in Config

I know it involves all the different levels, but it seems like a model of use problem (and I had validator problems before that ended up being a change in the way I use sqlmodel.)

Thanks for your creation of sqlmodel - so far I've really enjoyed it along with fastapi!

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.4

Python Version

3.8.12

Additional Context

No response

[yinziyan1206]

    class Hero(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        normhash: Optional[int] = Field(default=None, index=True, sa_column=Column(BigInteger())) 

UPD YuriiMotov: to avoid conflicts it's now better to use sa_type:

    class Hero(SQLModel, table=True):
        id: Optional[int] = Field(default=None, primary_key=True)
        normhash: Optional[int] = Field(default=None, index=True, sa_type=BigInteger) 

[unidesigner]

@yinziyan1206 This works if the field is not the primary key. If I want to create a primary key of type BigInteger, I get the exception:

sqlalchemy.exc.ArgumentError: Mapper mapped class Hero->hero could not assemble any primary key columns for mapped table 'hero'

e.g. with id: int = Field(default=None, index=True, sa_column=Column(BigInteger()))
Any idea how to solve this?

[yinziyan1206]

@unidesigner uhhhh, you can make it a column in sqlalchemy just like this:
id: int = Field( default_factory=next_val, sa_column=Column(BigInteger(), primary_key=True, autoincrement=False) )
and also, you can use sa_column_kwargs to set a column in sqlalchemy. i think it is flexible

[unidesigner]

thanks @yinziyan1206 - this did the trick!

[tobiasfeil]

If you want to reference an id field created in this manner as a foreign key, make sure to do it like this:

customer_id: int = Field(None, foreign_key='customer.id', sa_column=Column(BigInteger()))

[yudytskiy]

If you want to reference an id field created in this manner as a foreign key, make sure to do it like this:

customer_id: int = Field(None, foreign_key='customer.id', sa_column=Column(BigInteger()))

This doesn't create foreign key constrant because if sa_column is not undefined it is returned before any parameters are parsed.
And
customer_id: int = Field(None, sa_column=Column(BigInteger(), foreign_key='customer.id'))
the same behavor...

[yudytskiy]

To make it work you should do:
customer_id: int = Field(sa_column=Column(BigInteger(), ForeignKey('customer.id')))

[mrexodia]

Just FYI, to get the bigserial column like I wanted, I had to modify @yinziyan1206's solution a bit:

from typing import Optional
from sqlalchemy import Column, BigInteger
from sqlmodel import Field, SQLModel

class Users(SQLModel, table=True):
    id: Optional[int] = Field(default=None, sa_column=Column(BigInteger(), primary_key=True, autoincrement=True))
    name: str

This generates the following in postgres:

CREATE TABLE public."users" (
	id bigserial NOT NULL,
	name varchar NOT NULL,
        PRIMARY KEY (id)
);

[lee-cq]

I searched for a long time in the document, but did not find any relevant processing methods.

I wonder if the document can introduce the data types of custom data databases for fields and the meaning of uncommon parameters in Field().

This is very helpful for beginners.

[sebpretzer]

sqlmodel: v0.0.22
sqlalchemy: v2.0.36

I am getting the following error when trying this:

from sqlalchemy import Column, BigInteger
from sqlmodel import Field, SQLModel

class Users(SQLModel, table=True):
    id: int = Field(primary_key=True, sa_column=Column(BigInteger()))

RuntimeError: Passing primary_key is not supported when also passing a sa_column

so I switched to this and now things seem to work:

from sqlalchemy import BigInteger
from sqlmodel import Field, SQLModel

class Users(SQLModel, table=True):
    id: int = Field(primary_key=True, sa_type=BigInteger)