0

I have a FastAPI application and in my Item table the field id is an uuid. I'm wondering if I should handle the automatic assigning of id in my models.py or in the Alembic migration file?

At the moment this is what I have in my models:

class ItemDb(SQLMODEL, table=True):
    __tablename__ = "items"
    id: str = Field(
        default_factory=uuid.uuid4, primary_key=True, index=True, nullable=False
    )

In the migration file I have this:

sa.Column("id", sa.String(), nullable=False),

Is this a good way to handle the id or is there a better way, e.g. can I set the Postgres automatically assign an uuid when a new row is inserted?

Edit. I tried this:

Model:

class ItemDb(SQLMODEL, table=True):
        __tablename__ = "items"
        id: str = Field(
            primary_key=True, index=True, nullable=False
        )

Migration:

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

This gives me an error because the id is now None.

lr_optim
  • 299
  • 1
  • 10
  • 30
  • You can by setting a `DEFAULT` clause on the column. Consider using numeric artificial keys for increased performance. – Laurenz Albe Nov 03 '22 at 11:50
  • @LaurenzAlbe I edited what I tried into my original post. I now get an error because the `id` is `None`. – lr_optim Nov 03 '22 at 12:13
  • 1
    If you want to change the server default (i.e. DEFAULT on the column itself), you'll have to use `server_default`. Be aware that generating uuids server side as a default value on a column is only supported from MySQL 8.0.13: https://stackoverflow.com/questions/46134550/mysql-set-default-id-uuid – MatsLindh Nov 03 '22 at 12:34
  • @MatsLindh So not supported by Postgres at all? – lr_optim Nov 03 '22 at 12:38
  • Ow, sorry. Didn't notice that you have tagged it as postgresql. See https://dba.stackexchange.com/questions/122623/default-value-for-uuid-column-in-postgres for how to do it in Postgres with a plugin. If you don't have that installed or don't want to install it, I'd suggest fetching all the rows in your database as part of the migration, generating uuids and setting a unique one for each row, then using `default` with the function after that. – MatsLindh Nov 03 '22 at 12:39
  • @MatsLindh Thank you. I tried with this: `sa.Column("id", UUID(as_uuid=True), server_default=sa.text("uuid_generate_v4()"), primary_key=True),` and I get an error now saying: `sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) function uuid_generate_v4() does not exist`. Any ideas why this is not found? – lr_optim Nov 03 '22 at 12:50
  • The linked answer gives information about that function being defined in an extension, which may or may not be able to load. See the point about `CREATE EXTENSION IF NOT EXISTS "uuid-ossp";` - this extension is not available by default in base postgres afaik, but many providers bundle it. – MatsLindh Nov 03 '22 at 12:57
  • 1
    Here is the truth from the PostgreSQL side (I have no idea about Python or the ORMs in question): You don't need an extension for V4 UUIDs in recent PostgreSQL versions. All you need is to define the column like this: `colname uuid DEFAULT gen_random_uuid() NOT NULL`. – Laurenz Albe Nov 03 '22 at 13:16

0 Answers0