Set column order in alembic when adding a new column

Question

I'm struggling to find a clean way (without raw SQL) to set the column order in alembic. For example, I would like to put a new column called 'name' after the 'id' column, something like this:

from alembic import op
import sqlalchemy as sa

...

op.add_column(
    'people',
    sa.Column(
        'name',
        sa.String(),
        nullable=False
    ),
    after='id'
)

But of course, alembic does not have the 'after' parameter, therefore this code fails and I have not found an equivalent to this 'after' parameter in the docs. I'm only able to append the column at the end of the table.

Can anybody suggest how to achieve in alembic/sqlalchemy what I want? Is it possible without raw SQL?

Answer 1

alembic version 1.4.0 or higher supports this feature.

use the batch operation to achieve it.

from alembic import op
import sqlalchemy as sa

with op.batch_alter_table("people") as batch_op:
  batch_op.add_column(
    Column("name", sa.String(50)),
    insert_after="id"
  )

Reference: https://alembic.sqlalchemy.org/en/latest/ops.html?highlight=insert_after#alembic.operations.BatchOperations.add_column