Add support for preserving field order?

[msaysell]

Describe the use case

As far as I can tell, there doesn't seem to be a way to specify field order when adding a new column? Ive got an inherited model where it would be nice if any new fields that I create via an autogenerated revision would be entered into the database before the base classes fields.

I know I could manually add an execute after adding it to alter the table to insert it after another field but that sounds tedious to enforce and a way to preserve field order would be useful.

Databases / Backends / Drivers targeted

mysql in this example

Example Use

from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.sql import func
from sqlalchemy.dialects.mysql import TIMESTAMP

class MyModel(MyBase):
   existing_field = ...
   new_field = ...

class MyBase(DeclarativeBase):
    created = Column(TIMESTAMP(fsp=2), server_default=func.now(), nullable=False)
    modified = Column(TIMESTAMP(fsp=2), onupdate=func.now(), nullable=False)

The new_field will be created at the end of the table

existing_field|created               |modified              |new_field|
-----------+----------------------+----------------------+------------+
          0|2017-02-08 16:38:14.65|2017-02-08 16:38:14.65|            |

where I'd like to have a way to insert it before a field

existing_field|new_field|created               |modified              |
-----------+------------+----------------------+----------------------+
          0|            |2017-02-08 16:38:14.65|2017-02-08 16:38:14.65|

Have a nice day!

[CaselIT]

Hi,

I'm not sure how portable would be such a solution. Looking fairly quickly at some db, mysql seems the only one that supports it, postgresql, oracle and sql server do not support it.

I guess we could add mysql_after/mysql_first (and the mariadb equivalents) support to add_column but it would be a manual option I think

[msaysell]

Thanks, I did think it was probably going to have to be manual unless there was something that could be done with get_columns but without looking at the code to that it felt like that would end up messy too

[CaselIT]

it may be automated I guess, but first we would need to support it.

Since db support seems only mysql, if we were to support some sort automation that would be behind a config parameter, since it would be a bit annoying having that rendered by default for people who never plan on using mysql.

@zzzeek do you have any consideration about this?

@msaysell a PR to add manual support for it would be accepted, let's wait for mike regarding whatever we should also thing about automating something.
If you want an example what it this mysql options would look like, take a look at the postgresql_using that does a comparable thing

[zzzeek]

there's a bunch going on if we are taking this to be automated all the way from the ORM model with mixins as in the top example, there are strict rules about how the ORM orders columns in these cases, which can be affected manually using mapped_column.sort_order.

once we get a Table out of the ORM mapping with a certain column order (all outside of Alembic), then considering the autogen diffs / render to include special MySQL FIRST/AFTER directives (apparently, just looked it up) would sort of be something we'd likely publish as a recipe based on Rewriter . publishing things as recipes takes some of the pressure off of us to get it exactly right the first time and also reveals the behavior as something the user can opt into in a transparent way.

so yes in this case we would add mysql_first andmysql_after directives to add_column()

[CaselIT]

yes, the automation here would just be looking at the position in the sqlalchemy table.

A recipe is likely a better first automation step