SQLAlchemy: Adding an UUID column to a MySQL database

Question

I need to add a new UUID column to an existing SQLAlchemy / MySQL table where.

For doing so I added in my database model:

class MyTable(db.Model):
    uid = db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4)

Doing so generates the following alembic upgrade code which of course does not work as the default value if the new column is null:

op.add_column('my_table', sa.Column('uid', sa.BINARY(length=16), nullable=True))
op.create_unique_constraint(None, 'my_table', ['uid'])

I tried to extend the db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4) definition with an appropriate server_default=... parameter but wasn't able to find a parameter that generates for each row a new random UUID.

How to add a new column and generate for all existing rows a new random and unique UUID value?

A solution that uses sa.String instead of sa.BINARY would also be acceptable.

score 0 · Answer 1 · edited Jan 16 '23 at 16:19

0

As per mysqlalchemy's documentation on server_default:

A text() expression will be rendered as-is, without quotes: Column('y', DateTime, server_default = text('NOW()'))

y DATETIME DEFAULT NOW()

Based on this, your server_default definition should look like this:

server_default=text('(UUID_TO_BIN(UUID())))')

However, if your mysql version is earlier than v8.0.12, then you cannot use the server side default like this, you need to use either the default with setting uuid from python or you need a trigger as specified in the following SO question: MySQL set default id UUID

edited Jan 16 '23 at 16:19

snakecharmerb

47,570
11
100
153

answered Jan 16 '23 at 11:40

Shadow

33,525
10
51
64

Thanks for your answer. In theory this may work, unfortunately if you really execute it it throws an error `MySQLdb._exceptions.OperationalError: (1674, 'Statement is unsafe because it uses a system function that may return a different value on the slave.')` using SQLALchemy 1.4.67 on an MySQL 8.0.31. BTW: the value inside `text()` has one closing bracket too much. – Robert Jan 16 '23 at 12:12
Is replication an issue for you? If so, then you must use trigger on the server side or must use the python side default. – Shadow Jan 16 '23 at 12:13
Just a stand-alone server so no replication. From my understanding TRIGGER is only for new records, and I want to update the existing records... – Robert Jan 16 '23 at 12:18
Do you use binary logs for anything (e.g. backups)? If not, you can disable the binary log and the error message will go away. If you cannot disable the binary log, then use trigger or python to set the default value. – Shadow Jan 16 '23 at 12:34

score 0 · Answer 2 · answered Jan 16 '23 at 12:50

In the end I manually created the necessary UPDATE statements in the alembic update file so existing rows are assigned a unique UID.

For new entries default=uuid.uuid4 in the SQLAlchemy column definition is sufficient.

Note that the MySQL UUID() function generates timestamp based UUID values for the existing records, and new records created via Python/SQLAlchemy use uuid.uuid4 which generates a v4 (random) UUID. So both are UUIDs but you will see which UUIDs were generated by UUID() as they only differ in the first block when generating them using the UPDATE statement.

Using binary column type

class MyTable(db.Model):
    uid = db.Column(db.BINARY(16), nullable=False, unique=True, default=uuid.uuid4)

def upgrade():
    op.add_column('my_table', sa.Column('uid', sa.BINARY(length=16), nullable=False))
    op.execute("UPDATE my_table SET uid = (SELECT(UUID_TO_BIN(UUID())))")
    op.alter_column('my_table', 'uid', existing_type=sa.BINARY(length=16), nullable=False)
    op.create_unique_constraint(None, 'my_table', ['uid'])

Using String/varchar column type

class MyTable(db.Model):
    uid = db.Column(db.String(36), nullable=False, unique=True, default=uuid.uuid4)

def upgrade():
    op.add_column('my_table', sa.Column('uid', sa.String(length=36), nullable=False))
    op.execute("UPDATE my_table SET uid = (SELECT(UUID()))")
    op.alter_column('my_table', 'uid', existing_type=sa.String(length=36), nullable=False)
    op.create_unique_constraint(None, 'my_table', ['uid'])

SQLAlchemy: Adding an UUID column to a MySQL database

2 Answers2

Using binary column type

Using String/varchar column type