How can I create a table with a UUID column in a Postgres db using SQLAlchemy?

Question

I have created some tables using SQLALchemy 2.0's MappedAsDataclass and DeclarativeBase models, creating my tables like this:

class Base(MappedAsDataclass, DeclarativeBase):
    """subclasses will be converted to dataclasses"""

class Post(Base):
    __tablename__ = 'allposts'
    post_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    collection_id: Mapped[int] = mapped_column(ForeignKey("collections.collection_id"), default=None, nullable=True)
    user: Mapped[str] = mapped_column(default=None, nullable=True)
    title: Mapped[str] = mapped_column(default=None, nullable=True)
    description: Mapped[str] = mapped_column(default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    date_modified: Mapped[Optional[datetime.datetime]] = mapped_column(default=None, nullable=True)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    views: Mapped[int] = mapped_column(default=0, nullable=True)

Post.__table__

Base.metadata.create_all(engine)

I want to use a GUID as the PK, but I am having trouble creating a UUID type column via SQLALchemy using DeclarativeBase/MappedAsDataclass

I was trying stuff like this to make a UUID column, but no luck:

from sqlalchemy.dialects.postgresql import UUID

class Post(Base):
    __tablename__ = 'allposts'
    post_id: Mapped[int] = mapped_column(init=False, primary_key=True)
    guid: Mapped[uuid.uuid1] = mapped_column(default=None, nullable=True)
    collection_id: Mapped[int] = mapped_column(ForeignKey("collections.collection_id"), default=None, nullable=True)
    user: Mapped[str] = mapped_column(default=None, nullable=True)
    title: Mapped[str] = mapped_column(default=None, nullable=True)
    description: Mapped[str] = mapped_column(default=None, nullable=True)
    date_added: Mapped[datetime.datetime] = mapped_column(default=None, nullable=True)
    date_modified: Mapped[Optional[datetime.datetime]] = mapped_column(default=None, nullable=True)
    tags: Mapped[list]  = mapped_column(ARRAY(TEXT, dimensions=1), default=None, nullable=True)
    views: Mapped[int] = mapped_column(default=0, nullable=True)

I tried different varients of uuid.uuid1, but if that's the issue I didn't guess the right one.

There are some SQLAlchemy examples of UUID columns here, but I can't seem to figure how to translate it into the model I am using.

Answer 1

Here is an example of how you can do it on a PostgreSQL server:

import uuid

from sqlalchemy import create_engine, select, text, types
from sqlalchemy.orm import (DeclarativeBase, Mapped, MappedAsDataclass,
                            mapped_column, sessionmaker)

DATABASE_URL = "postgresql+psycopg2://test_username:test_password@localhost:5432/test_db"
engine = create_engine(
    DATABASE_URL,
    echo=True,
)
Session = sessionmaker(
    bind=engine,
)


class Base(MappedAsDataclass, DeclarativeBase):
    pass


class Post(Base):
    __tablename__ = 'post'
    guid: Mapped[uuid.UUID] = mapped_column(
        types.Uuid,
        primary_key=True,
        init=False,
        server_default=text("gen_random_uuid()") # use what you have on your server
    )


if __name__ == "__main__":
    Base.metadata.create_all(engine)

    with Session() as session:
        post1 = Post()
        post2 = Post()
        session.add_all([post1, post2])
        session.commit()

    with Session() as session:
        for post in session.scalars(select(Post)):
            print(post)

Here, it's using a sever default to have the server generate the UUID for us.

CREATE TABLE post (
        guid UUID DEFAULT gen_random_uuid() NOT NULL,
        PRIMARY KEY (guid)
)

The result will be:

Post(guid=UUID('b8b01f06-8daf-4c2c-9a7b-c967c752a7bf'))
Post(guid=UUID('7b94c8a3-ff0e-4d09-92e9-caf4377370e1'))