trouble with UUIDs using sqlalchemy with postgres

[dkress59]

i am currently working on my first python project, which is a starlite-based api. the api uses the sqlalchemy plugin to interact with (async) postgres. i started out with bare sqlalchemy+sqlite via python repl, just to get familiar with sqlalchemy in the first place.

i have been using UUIDs as primary keys, generating the UUIDs on the create/POST route handler, using VARCHAR fields (coming from sqlite). now i am having trouble to use postgres' UUID fields and generating the UUIDs by using Column(UUID, default=uuid.uuid4) as in this example.

when i create a row in a table (e.g. when i create a user), at first everything seems fine; the response to my POST request is the new user. however, if i now run a GET request to the /users route, all i get is an error (where previously i got "[]"):

{
    "status_code": 500,
    "detail": ""
}

there also seems to be a problem with validation, if i try to create the same user (where the "username" field is unique), the response i get is:

{
    "status_code": 500,
    "detail": "ValidationError(model='ExceptionResponseContent', errors=[{'loc': ('detail',), 'msg': 'str type expected', 'type': 'type_error.str'}])"
}

i am, by the way, having similar issues with DateTime fields, but maybe it's the same thing that I'm doing wrong.

here's how i am using UUID on the model, although i have tried from uuid import UUID and i have also tried from sqlalchemy import Uuid:

from datetime import datetime
from uuid import uuid4

from sqlalchemy import Column, DateTime, ForeignKey, Integer, SmallInteger, String, Text
from sqlalchemy.dialects.postgresql import UUID

class User(Base):
    """A user (e.g. admin) of the booking system."""

    __tablename__ = "users"
    id = Column(UUID, primary_key=True, default=uuid4)  # UUID
    username = Column(String(25), nullable=False, unique=True)
    email = Column(String(80), unique=True, nullable=False)
    password = Column(String(), nullable=False)

    first_name = Column(String(32), nullable=False)
    last_name = Column(String(32), nullable=False)
    role = Column(SmallInteger(), default=2, nullable=False)
    api_tokens = Column(Text())
    date_created = Column(DateTime(), default=datetime.utcnow)
    date_updated = Column(DateTime(), default=datetime.utcnow)

    def __repr__(self) -> str:
        return f"<User: {self.username} ({self.email})>"

I'll be happy to provide further information and/or to invite someone to the private repo to take a closer look.

[peterschutt]

I'll be happy to provide further information and/or to invite someone to the private repo to take a closer look.

Want to send me an invite @dkress59? Probably not enough info here to be able to work out exactly what is happening.

[dkress59]

I'll be happy to provide further information and/or to invite someone to the private repo to take a closer look.

Want to send me an invite @dkress59? Probably not enough info here to be able to work out exactly what is happening.

done. thanks in advance!

[peterschutt]

Hey @dkress59 - just trying to replicate the issue, without much luck. I've made a successful POST to /users and a followup GET from /users seems to work.

Successful POST to /users:

Successful GET from /users:

Notice that I had to send values for api_tokens and role in the POST payload, even though you don't use them to construct the user model in the controller, perhaps this has something to do with it?