Heavily customizing (dialect) what's generated for migration

[asyschikov]

Hi alchemists!

I want to figure out and ideally release support for a new database called Amazon Aurora DSQL . It is a somewhat compatible Postgres DB. But yeah it doesn't support enough to be supported by Postgres dialect Alembic in multiple key ways (Unsupported PostgreSQL features in Aurora DSQL). I managed to overcome the blockers but there is still too much friction, and I need some ideas on how to remove them.

For example, DSQL doesn't support "drop column", so I want migration scripts not to have it (I understand it breaks the symmetry). I looked into inheriting the PostgresImpl class and playing with "@compiles" but didn't get very far. (the source of inspiration: https://github.com/cockroachdb/sqlalchemy-cockroachdb/blob/master/sqlalchemy_cockroachdb/base.py ) . How can I either disable the emission of certain statements? Or is it better to make sure they are there, but rendered to no-op SQL down the wire?

Thanks

[zzzeek]

There's a bunch of things to cover here.

Firstly, when you do your own alembic dialect, alembic has a dialect system not too unlike the one SQLAlchemy has, it's much simpler, you've probably seen it by looking at alembic's source. you want to make your own AuroraImpl, that could subclass either alembic.ddl.postgresql.PostgresqlImpl if it's really similar, or alembic.ddl.DefaultImpl to have all your own hooks. So right there you will have a lot more control over things than just using @compiles hooks.

Next, if you look at DefaultImpl, the gateway to drop_column is right here: https://github.com/sqlalchemy/alembic/blob/b54431b15534b924c6ef06428c49d18423d9e75f/alembic/ddl/impl.py#L387C1-L400C10 . So no need to override @compiles, which wouldn't work anyway because it still would be seeking to invoke a statement; just override that method to do nothing, or warn, or raise if some its_ok_just_skip_drops flag is not set, whatever you want there.

Next thing, for databases that have insufficient ALTER, the worst offender is of course SQLite. After many years of my really avoiding doing it, i eventually relented to pressure and built the batch migrations system. this uses table copy + drop + rename to migrate tables, and this is also a technique you'll see large scale shops doing with mainstream DBs like MySQL and PostgreSQL to minimize downtime, which is why we support it for all backends. Depending on best practices for your database, you may want to pursue having this be available as well.

Finally, there is a hook that allows complete control over the way an alembic template is laid out which is the migration rewriter hook, however this hook right now is set up via user-defined directives in the env.py file - there's no straightforward direct hook for a backend "impl" to set these up, nor do I think you'd want to do that since migration files typically seek to be backend agnostic. But having the impl in use take advantage of this hook or something similar is theoretically possible, I'd have to poke around to the plumbing to see if there's an automatic way to do it or you could tell users to use a pre-defined hook in their env.py to get a customized effect.

So depending on how much ALTER DDL is not supported here, you want to look into some combination of the above approaches. hope this helps