Declarative mode

[francipvb]

Hello all,

Is there a possibility to make a declarative migrations mode like those found in django?

I found that in most situations declarative approach is better and less error-prone than imperative.

For example a migration script could look like this:

from alembic.declarative import op
import sqlalchemy as sa

steps = [
    ops.NewTable(
        sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
        name='tbl1',
    ),
]

This will give us a one-time up/down migration declaration with a single source of truth.

Thanks,

[francipvb]

Implementation details should be like this:

classDiagram
class Operation{
    <<abstract>>
    +upgrade()
    +downgrade()
}
  
class NewTable
NewTable <|-- Operation

Loading

This is a simple object-oriented way to define the model, but more functionality can be added.

[zzzeek]

well there already is a declarative structure of your schema in SQLAlchemy , which is your Table metadata and/or ORM model. So a system that looks at a "declared" state of truth and figures out on the fly what migrations that would require, we already have that declared state of truth.

the given code example you gave doesn't seem to be substantively different from op.create_table(), just a different name and casing convention, and that it looks more like data than functions; Alembic's migration scripts are in fact based on a data structure so we in fact have that also, and an end-user recipe to achieve what you want could be bult using these directives.

What i'd assume is that you are looking for a Ruby-esque "idempotent" approach that I would assume looks at a given databases state on a per-operation basis and tries to bring it up to date based on individual comparison operations.

I'm not sure what Django's approach is, my vague understanding was that their migrations were fully automatic and that they have some kind of storage that can produce migrations on the fly based on model comparison alone. I'm not sure how this relates to your proposal. But in any case we don't have any model versioning system in Alembic, our only "comparison" is against a live database. It works only at the whole model level and not at the level of individual operations. Alembic's individual operations are one-directional; they are DDL statements only that can be run as a flat SQL script without any introspection. This allows them to be efficient. if you've ever used an "idempotent" system like Ansible you'd know that all the "checking if each thing is already there" is really slow and cumbersome.

If django has somehow figured out a way to make it all fast and easy, that's good for them, it's not a competency we have on this end nor do we have development /maintenance resources for a new migration paradigm. Alembic is however very modular and open ended and a new kind of migration system could certainly be built on top of it. As an example, check out the recipe Run Alembic Operation Objects Directly which illustrates how to have a command that determines migrations on the fly and runs them directly. that's not something we can support as the reflection / comparison process is not designed to be foolproof in all cases (django here benefits by the fact that django models have fairly rigid structural requirements compared to SQLAlchemy that allows anything). But it does show how something like this can be done perhaps for the benefit of a 3rd party Alembic extension.

[francipvb]

Hello,

I will clarify your points:

1. The difference is that the NewTable construct encapsulates both the upgrade and the downgrade steps to perform the operation itself. In the case of the NewTable constructed instance, the upgrade method will create the table and the downgrade method will perform the exact reverse operation.
2. You talk here about the autogenerate feature, not the migration script itself. The links you provide are about an internal feature of alembic, intended for plugin developers.
3. The last point is about autogenerate feature too and is not the thing I am talking about.

Thanks!

[zzzeek]

I think what needs to be appreciated here is that you are proposing an entirely new way that migration scripts would be constructed. As Alembic seeks to apply Python's suggestion that there should be "only one way to do it", at least within the scope of Alembic itself (third party libraries can of course build on Alembic to propose other kinds of APIs) this inherently implies that if Alembic were to devise a new way of writing migrations that is to be part of Alembic itself, it would need to be enough of an improvement that we could in fact deprecate Alembic's entire existing API.

So at the outset, that is an extremely high bar, and to be clear it's not realistic to approach a project with a brief five line code fragment and assume that from there, we'd take over and replace the whole API with something resembling that, or that we'd go look at Django and decide to replace most of Alembic with an all new approach that looks like Django. That's more like, a new migration tool, that people should be enthused to go out there and create, whether or not it's based on Alembic or SQLAlchemy or whatever. For us, we have a substantial user base that's doing things the autogenerate / op.do_thing() way, and replacing all that inline with something new would be a massive undertaking.

All of that is independent of how much of an improvement the idea you are thinking of might be. So while it's probably frustrating that I'm not "getting it" as to how this proposal is a much superior approach to Alembic's existing API, even if I did "get it", there's really not room on the roadmap right now to wholesale replace Alembic's API with some new django-esque paradigm, as nice as that may be. it's a lot easier for a new project to start up and produce that, which is why i suggested someone could use Alembic's internals to do all the heavy lifting, if they just wanted to focus on the front-facing API.

[francipvb]

It is not needed to replace the current API.

In fact this idea can be implemented on top of the current features and the current API can be documented as a low-level way while this could be a high level API.

Good night,

[CaselIT]

Hi @francipvb

I too don't see what the big difference between the current api and what you are proposing. Other than objects vs functions, that's mostly semantic, is the main difference you are proposing that a single list would give you the list of operations do to in upgrade or downgrade?

[francipvb]

Hello,

Yes, that is correct. For small migrations this can be a small difference, but suppose you have a big migration with a lot of table changes, index definitions, etc., you can miss something in the upgrade or downgrade step and you'll end with an asymmetric migration script.

I plan to submit a PR with some POC for demonstration if I can find the time to look at the architecture and if I can find the place where migration scripts are called and how.

In the proposed example, you have both steps for the NewTable operation. You add a NewTable instance to the operation list and you have up and down migration steps for a single line of code. Now imagine that you have some indexes, foreign key definitions and you want to ensure that the migration script is symmetric, this could be a good solution.