Is there a way to define a column (primary key) as a UUID in SQLAlchemy if using PostgreSQL (Postgres)?
Best Answer
The sqlalchemy postgres dialect supports UUID columns. This is easy (and the question is specifically postgres) -- I don't understand why the other answers are all so complicated.
Here is an example:
from sqlalchemy.dialects.postgresql import UUIDfrom flask_sqlalchemy import SQLAlchemyimport uuiddb = SQLAlchemy()class Foo(db.Model):id = db.Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)Be careful not to miss passing the callable uuid.uuid4 into the column definition, rather than calling the function itself with uuid.uuid4(). Otherwise, you will have the same scalar value for all instances of this class. More details here:
A scalar, Python callable, or ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert.
I wrote this and the domain is gone but here's the guts....
Regardless of how my colleagues who really care about proper database design feel about UUID's and GUIDs used for key fields. I often find I need to do it. I think it has some advantages over autoincrement that make it worth it.
I've been refining a UUID column type for the past few months and I think I've finally got it solid.
from sqlalchemy import typesfrom sqlalchemy.dialects.mysql.base import MSBinaryfrom sqlalchemy.schema import Columnimport uuidclass UUID(types.TypeDecorator):impl = MSBinarydef __init__(self):self.impl.length = 16types.TypeDecorator.__init__(self,length=self.impl.length)def process_bind_param(self,value,dialect=None):if value and isinstance(value,uuid.UUID):return value.byteselif value and not isinstance(value,uuid.UUID):raise ValueError,'value %s is not a valid uuid.UUID' % valueelse:return Nonedef process_result_value(self,value,dialect=None):if value:return uuid.UUID(bytes=value)else:return Nonedef is_mutable(self):return Falseid_column_name = "id"def id_column():import uuidreturn Column(id_column_name,UUID(),primary_key=True,default=uuid.uuid4)# Usagemy_table = Table('test',metadata,id_column(),Column('parent_id',UUID(),ForeignKey(table_parent.c.id)))I believe storing as binary(16 bytes) should end up being more efficient than the string representation(36 bytes?), And there seems to be some indication that indexing 16 byte blocks should be more efficient in mysql than strings. I wouldn't expect it to be worse anyway.
One disadvantage I've found is that at least in phpymyadmin, you can't edit records because it implicitly tries to do some sort of character conversion for the "select * from table where id =..." and there's miscellaneous display issues.
Other than that everything seems to work fine, and so I'm throwing it out there. Leave a comment if you see a glaring error with it. I welcome any suggestions for improving it.
Unless I'm missing something the above solution will work if the underlying database has a UUID type. If it doesn't, you would likely get errors when the table is created. The solution I came up with I was targeting MSSqlServer originally and then went MySql in the end, so I think my solution is a little more flexible as it seems to work fine on mysql and sqlite. Haven't bothered checking postgres yet.
If you are happy with a 'String' column having UUID value, here goes a simple solution:
def generate_uuid():return str(uuid.uuid4())class MyTable(Base):__tablename__ = 'my_table'uuid = Column(String, name="uuid", primary_key=True, default=generate_uuid)I've used the UUIDType from the SQLAlchemy-Utils package.
Since you're using Postgres this should work:
from app.main import dbfrom sqlalchemy.dialects.postgresql import UUIDclass Foo(db.Model):id = db.Column(UUID(as_uuid=True), primary_key=True)name = db.Column(db.String, nullable=False)Here is an approach based on the Backend agnostic GUID from the SQLAlchemy docs, but using a BINARY field to store the UUIDs in non-postgresql databases.
import uuidfrom sqlalchemy.types import TypeDecorator, BINARYfrom sqlalchemy.dialects.postgresql import UUID as psqlUUIDclass UUID(TypeDecorator):"""Platform-independent GUID type.Uses Postgresql's UUID type, otherwise usesBINARY(16), to store UUID."""impl = BINARYdef load_dialect_impl(self, dialect):if dialect.name == 'postgresql':return dialect.type_descriptor(psqlUUID())else:return dialect.type_descriptor(BINARY(16))def process_bind_param(self, value, dialect):if value is None:return valueelse:if not isinstance(value, uuid.UUID):if isinstance(value, bytes):value = uuid.UUID(bytes=value)elif isinstance(value, int):value = uuid.UUID(int=value)elif isinstance(value, str):value = uuid.UUID(value)if dialect.name == 'postgresql':return str(value)else:return value.bytesdef process_result_value(self, value, dialect):if value is None:return valueif dialect.name == 'postgresql':return uuid.UUID(value)else:return uuid.UUID(bytes=value)In case anyone is interested, I've been using Tom Willis answer, but found useful to add a string to uuid.UUID conversion in the process_bind_param method
class UUID(types.TypeDecorator):impl = types.LargeBinarydef __init__(self):self.impl.length = 16types.TypeDecorator.__init__(self, length=self.impl.length)def process_bind_param(self, value, dialect=None):if value and isinstance(value, uuid.UUID):return value.byteselif value and isinstance(value, basestring):return uuid.UUID(value).byteselif value:raise ValueError('value %s is not a valid uuid.UUId' % value)else:return Nonedef process_result_value(self, value, dialect=None):if value:return uuid.UUID(bytes=value)else:return Nonedef is_mutable(self):return FalseI encountered the same issue, this should work, it works for me:
from sqlalchemy import Column, textfrom sqlalchemy.dialects.postgresql import UUIDColumn("id", UUID(as_uuid=True),primary_key=True,server_default=text("gen_random_uuid()"),)If you use PostgreSQL < 14, I think you need to add this EXTENSION pack:
CREATE EXTENSION IF NOT EXISTS "pgcrypto";You can use uuid_generate_v4() as well, you'd need to add the EXTENSION pack then:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";SQLAlchemy 2.0 adds the UUID type, the SQL-native form of the database agnostic type which is backwards compatible with the previous PostgreSQL-only version of UUID.
Example:
import sqlalchemy as safrom sqlalchemy.orm import DeclarativeBase, Mappedclass Base(DeclarativeBase):passclass MyModel(Base):my_field: Mapped[sa.UUID]We can use UUIDType,
from sqlalchemy_utils import UUIDTypefrom sqlalchemy import Stringclass User(Base):id = Column(UUIDType(binary=False), primary_key=True, default=uuid.uuid4)name = Column(String)For more details we can refer to the official documentation.
You could try writing a custom type, for instance:
import sqlalchemy.types as typesclass UUID(types.TypeEngine):def get_col_spec(self):return "uuid"def bind_processor(self, dialect):def process(value):return valuereturn processdef result_processor(self, dialect):def process(value):return valuereturn processtable = Table('foo', meta,Column('id', UUID(), primary_key=True),)