Handle AUTOINCREMENT modification detection in revision auto-generation

[TheoGoudout]

Describe the use case
Unless I'm mistaken, toggling the autoincrement attribute of a table column is not detected by the revision auto-generation.

Databases / Backends / Drivers targeted
My usecase is for MySQL databases, but I guess it would be nice to have for any DB that supports this feature (although I'm not familiar with which DBs that would be).

Example Use
Migrate from

CREATE TABLE `FOO` (
  `ID` int(11) NOT NULL,
  PRIMARY KEY (`ID`)
);

to

CREATE TABLE `FOO` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
);

would generate a difference in a migration script.

Have a nice day!

[zzzeek]

this would not be very simple as SQLAlchemy considers this AUTO_INCREMENT as an implicit thing it turns on.

that is

Table("x", m, Column("id", Integer, primary_key=True))

if you generate that on MySQL, it will include AUTO_INCREMENT automatically. only if you had the column in the model like this:

Table("x", m, Column("id", Integer, primary_key=True, autoincrement=False))

does that actually mean "primary key integer column without AUTO_INCREMENT". So I guess what I'm thinking is that it's very unusual you'd have a MySQL table of this nature if it were managed by SQLAlchemy the whole time since people don't really set that flag to False without being very deliberate.

Another problem is that Alembic has no support for autogen of database specific constructs; it seeks to produce migration files that are database agnostic. In this case, I dont think we really have a migration construct that adds this flag alone to a MySQL column. We have a similar issue with ENUM datatypes as PostgreSQL has an elaborate system of types that we also don't autogenerate in an explicit way.

all of this is to say that w/ Alembic's current development profile there's not much chance of something like this happening as we don't even have the API architecture in place to properly support autogen of migrations that are conditional on certain database backends.