Add autoincrement column that is not primary key Alembic

Question

I want to add a column that is autoincrement that is not primary key to an existing MySQL database.

The command issued on the server required for this operation is the following:

ALTER TABLE `mytable` ADD `id` INT UNIQUE NOT NULL AUTO_INCREMENT FIRST

The issue I face is how to replicate this table change through an Alembic migration. I have tried:

from alembic import op
import sqlalchemy as sa

def upgrade():
    op.add_column('mytable', sa.Colummn('id', sa.INTEGER(), 
                  nullable=False, autoincrement=True)

but when I try to insert a row with the following command:

INSERT INTO `mytable` (`col1`, `col2`) VALUES (`bar`);

where col1, col2 are non nullable columns. When I insert this record I expect the table to generate automatically the id for me.

ERROR 1364 (HY000): Field 'id' doesn't have a default value

If I inspect the sql autogenerated by Alembic using the following command:

alembic upgrade 'hash-of-revision' --sql

it spits out, for the given revision:

ALTER TABLE mytable ADD COLUMN id INTEGER NOT NULL;

Which means that either Alembic or SQLAlchemy is ignoring the autoincrement field when generating the sql of the migration.

Is there a way I can solve this? Or can I establish a migration based on a custom sql command?

Answer 1

You can pass the parameter recreate="always" to the method batch_alter_column so that a new table is created and the old one is deleted. This way, you won't have problems with prior constraints. This can be helpful with the migrations of not so big tables (as otherwise the performance would be bad). Check Alembic's docs here.

Answer 2

Unfortunately, @Nelson's answer does not work if the table has data in it

Not sure how it allows me to add a primary key on an empty column but i guess it is because it is in a transaction block.

I believe that this is because there is no NULL values in the column if there are no rows in the database.

Here's what I got to work with MySQL 8.0.34:

The upgrade:

# add NOT NULL `id` column, for now is not autoincrement
op.add_column('table_name', sa.Column('id', mysql.INTEGER(unsigned=True), nullable=False, autoincrement=False))
# drop current primary key so that can create a new one
op.drop_constraint(None, 'table_name', 'primary')
# add new primary key with the same columns as the old one + the `id` column
op.create_primary_key(None, "table_name", ["id", "previous_pk_1", "previous_pk_2"])
# alter the `id` column to be autoincrement (can't make an autoincrement column that is not part of a primary key)
# this will assign autoincrement values to the column (before this it is all 0s)
op.alter_column('table_name', 'id',
                existing_type=mysql.INTEGER(unsigned=True),
                existing_nullable=False,
                autoincrement=True)
# remove the autoincrement from the column to allow dropping the primary key
op.alter_column('table_name', 'id',
                existing_type=mysql.INTEGER(unsigned=True),
                existing_nullable=False,
                autoincrement=False)
# drop the primary key
op.drop_constraint(None, 'table_name', 'primary')
# recreate the primary key, now with just the `id` column
op.create_primary_key(None, "table_name", ["id"])
# re-add autoincrement to the column
op.alter_column('table_name', 'id',
                existing_type=mysql.INTEGER(unsigned=True),
                existing_nullable=False,
                autoincrement=True)

The downgrade is simpler:

# remove autoincrement from `id` column
op.alter_column('table_name', 'id',
                existing_type=mysql.INTEGER(unsigned=True),
                existing_nullable=False,
                autoincrement=False)
# can now drop primary key since no autoincrement column
op.drop_constraint(None, 'table_name', 'primary')
# create original primary key
op.create_primary_key(None, "table_name", ['previous_pk_1', 'previous_pk_2'])
# drop `id` column
op.drop_column('table_name', 'id')

Answer 3

As of SQLAlchemy 1.4+ and Alembic 1.9 you can use the Identity type, which according to docs, supersedes the Serial type.

This Declarative ORM:

class ProductOption(Base):
    __tablename__:str = 'product_options'

    id:Mapped[int] = mapped_column(Integer, server_default=Identity(start=1, cycle=True), primary_key=True)
    uuid:Mapped[UUID] = mapped_column(UUID, nullable=False, unique=True)
    name:Mapped[str] = mapped_column(String(50), nullable=False)
    price:Mapped[Decimal] = mapped_column(Numeric(16, 4), nullable=False)
    cost:Mapped[Decimal] = mapped_column(Numeric(16, 4), nullable=False)
    unit:Mapped[str] = mapped_column(String(8), nullable=False)
    duration:Mapped[int] = mapped_column(Integer)

Results in the following Alebic --autogenerate migration:

op.create_table(
    "product_options",
    sa.Column(
        "id",
        sa.Integer(),
        sa.Identity(always=False, start=1, cycle=True),
        nullable=False,
    ),
    sa.Column("uuid", sa.UUID(), nullable=False),
    sa.Column("name", sa.String(length=50), nullable=False),
    sa.Column("price", sa.Numeric(precision=16, scale=4), nullable=False),
    sa.Column("cost", sa.Numeric(precision=16, scale=4), nullable=False),
    sa.Column("unit", sa.String(length=8), nullable=False),
    sa.Column("duration", sa.Integer(), nullable=False),
    sa.PrimaryKeyConstraint("id"),
    sa.UniqueConstraint("uuid"),
)

Answer 4

First we add the column and allow it to have null values

op.add_column('abc', Column('id', BIGINT(unsigned=True), comment='This column stores the type of phrase') )

Then we create the primary key

op.create_primary_key( 'abc_pk', table_name='abc', columns=['id'] )

Not sure how it allows me to add a primary key on an empty column but i guess it is because it is in a transaction block.

Then we alter the column to have the autoincrement column

op.alter_column( existing_type=BIGINT(unsigned=True), table_name='abc', column_name='id', autoincrement=True, existing_autoincrement=True, nullable=False)