Migrations with UUID and server_default fail.

[kristjanvalur]

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

import uuid
from typing import Optional
from sqlmodel import SQLModel, Field
from sqlalchemy.sql.expression import func
class Event(SQLModel, table=True):

    event_uuid: Optional[uuid.UUID] = Field(
        primary_key=True, nullable=False, sa_column_kwargs=dict(server_default=func.gen_random_uuid())
    )

# a PostgreSQL database with this model cannot have a migration created.

Description

Models which use sqlmodel.sql.sqltypes.GUID and a server_default cannot be upgraded by Alembic, using PostgreSQL

Problem

1. create a column with UUID and server default, e.g.

   event_uuid: Optional[uuid.UUID] = Field(
        primary_key=True, nullable=False, sa_column_kwargs=dict(server_default=func.gen_random_uuid())
    )
   

2. alembic creates a table like this:

   sa.Column(
            'event_uuid', sqlmodel.sql.sqltypes.GUID(), server_default=sa.text('gen_random_uuid()'), nullable=False
        ),
   

3. update the database. Use the database. All is fine.
4. Create another migration with alembic. The analysis and comparison of the current model with the current database will result in the following error:

   sqlalchemy.exc.DataError: (psycopg2.errors.InvalidTextRepresentation) invalid input syntax for type uuid: "gen_random_uuid()"
   LINE 1: SELECT gen_random_uuid() = 'gen_random_uuid()'
   

Workaround

Replacing the Field specification in the model with

Field(
     sa_column=sa.Column(sqlalchemy.dialects.postgresql.UUID(), primary_key=True, server_default=func.gen_random_uuid()),
 )

gets rid of the error. But this bypasses SqlModel's type inference with a more lengthy and explicit sa.Column.

It would appear that sqlmodel.sql.sqltypes as a type in a sa.Column somehow does not behave the same as the type class from sqlalchemy.

Operating System

Linux

Operating System Details

No response

SQLModel Version

0.0.6

Python Version

3.10

Additional Context

alembic 1.8.1
sqlalchemy 1.4.35

[ThirVondukr]

Try using sqlalchemy.func.gen_random_uuid() 🤔

[yinziyan1206]

there are also some other bugs when using sa_column_kwargs and alembic. it is better to use sa_column now

[jpmckinney]

there are also some other bugs when using sa_column_kwargs and alembic. it is better to use sa_column now

What bugs? I prefer not to use sa_column. Developers can accidentally do things like Field(sa_column=Column(JSON), nullable=False), where sa_column causes nullable to be ignored, and sa_column's default value of nullable=True is used instead. (0.0.11 added an error for this scenario.) That said, if there are worse issues with sa_column_kwargs, it'd be good to know.