[SOLVED] flask-migrate / alembic: How to add a postgresql identity column to an existing table?

flask-migrate / alembic: How to add a postgresql identity column to an existing table?

I’m trying to create a migration to add a new identity column to an existing table. The table should eventually become the new primary key of that table.

class Action(db.Model):
    id = db.Column(db.Integer(), db.Identity(), primary_key=True)  # new primary key
    uuid = db.Column(sqlalchemy_utils.UUIDType, index=True, nullable=False)  # old primary key

When I generate the migration using flask db migrate I only get a new integer column:

def upgrade():
    """Upgrade from previous version."""
    op.add_column("actions", sa.Column("id", sa.Integer(), nullable=False))

For a new sequence based id-column I know that I would need to create the sequence explicitly to use it as server_default (eg as described here: https://sqlalchemy-alembic.narkive.com/U6p0nSGQ/adding-an-auto-increment-column-to-an-existing-table):

def upgrade():
    """Upgrade from previous version."""
    op.execute(sa.schema.CreateSequence(sa.Sequence("actions_id_seq")))
    op.add_column("actions", sa.Column("id", sa.Integer(), server_default=sa.text("nextval('actions_id_seq'::regclass)"), nullable=False))

This is using sqlalchemy 1.4.42 and alembic 1.8.1.

I couldn’t find any way to add an identity column with GENERATED BY DEFAULT AS IDENTITY. Any suggestions on how to accomplish this?

Solution

It seems it was as simple as adding sa.Identity():

def upgrade():
    """Upgrade from previous version."""
    op.add_column("actions", sa.Column("id", sa.Integer(), sa.Identity(), nullable=False))