psycopg2 return missing "id" primary key column instead of generate itself

Hello I have the following model:

@dataclass
class User(Base, TimestampMixin):
    __tablename__ = "users"
    email: str
    password: int
    name: str
    id: Optional[int] = None

    id = Column(UUID(as_uuid=True), primary_key=True)
    email = Column(String(50), unique=True, nullable=False)
    password = Column(String(20), nullable=False)
    name = Column(String(15), unique=True, nullable=False)

When I try to generate a new user with the following code:

def insert_user(session: Session, name: str, email: str, password: str) -> None:
    try:
        user = User(name=name, email=email, password=password)
        session.add(user)
    except:
        session.rollback()
        raise InsertUserError
    else:
        session.commit()

I get the following error:

ERROR in handlers: (psycopg2.errors.InvalidForeignKey) missing "id" primary key column

I wasn't put the id because I guessed that psycopg2 will generate it itself, but it didn't.

How can I create that field automatically?

Solution

Just because a column is marked as a primary key, doesn't mean it has a default value. SQLAlchemy will automatically produce the correct SQL to auto-generate primary keys only when using an integer-based primary key column. For UUID primary keys, you are responsible for defining a default.

You can use a Python default:

id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)

(where you imported the uuid module). This default is used only when you create SQLAlchemy model instances, not when you insert into the table using SQL generated elsewhere.

If you have installed the uuid-osp module (e.g. create extension uuid-ossp) and want to use the uuid_generate_v4 function to produce the default on the server, you'd use server_default parameter:

id = Column(
    UUID(as_uuid=True),
    primary_key=True,
    server_default=func.uuid_generate_v4(),
)

where func is sqlalchemy.sql.expression.func (from sqlalchemy.sql import func should suffice).