Support for auto-generated SQL migrations in online mode

[basvandriel]

Describe the use case
Not everyone wants to use an ORM to add and edit database migrations. For enterprise graded production databases, reading raw SQL might even be more useful for database administrators. Alembic does currently not support that, other then the offline mode.

Databases / Backends / Drivers targeted
To start with, PostgreSQL

Example Use

After configuration, I foresee something like the following steps.

1. A new revision should be created alembic revision --autogenerate --sql -m "Added account table", which will create one more SQL file containing the code for upgrading and downgrading the database. Similar to the regular migration files, this will also hold metadata.
2. Then, using the metadata, we can simply run alembic upgrade head, which will take the files into account.

[zzzeek]

you can put raw SQL in a migration file like this:

def upgrade():
    op.get_bind().exec_driver_sql("""

CREATE TABLE ....

""")

however, the ask here seems to be that autogenerate would run normally, and create migration files, but instead of using alembic migration ops, would create a new .sql file with some migration that reads it.

We have had requests for migration files placed in .sql mode, for which it's easy enough to create migration files that consume the SQL files, but for autogenerate to create these files in the first place which are generated in any case from alembic's own operators, I dont see the point of that.

reading raw SQL might even be more useful for database administrators

absolutely, which is what --sql mode is for, run alembic upgrade base:head --sql > myfile.sql and hand off myfile.sql to your database administrators. your DBAs are either reading your source repository directly, in which case it's pretty trivial to read what an alembic op does, or they're not, in which case you just give them the file.

[zzzeek]

it's likely that this use could also be produced using a custom rewriter function. it would be a little involved since it would have to run the ops to generate the SQL into a separate file, but should be doable. this can be rolled custom as a recipe and we can support adding such a recipe to our docs

[zzzeek]

here's that:

import pathlib

from alembic.operations import Operations
from alembic.migration import MigrationContext


def write_sql_files(context, revision, directives):
    migrations = directives[0]

    for ops, op in [
        (migrations.upgrade_ops, "upgrades"),
        (migrations.downgrade_ops, "downgrades"),
    ]:
        version_path = context.environment_context.script.versions
        norm_path = pathlib.Path(version_path)
        with open(
            norm_path / f"{migrations.rev_id}_{op}.sql", "w"
        ) as output_file:
            opts = context.opts.copy()
            opts["as_sql"] = True
            opts["output_buffer"] = output_file
            sql_migration_context = MigrationContext.configure(
                context.connection, opts=opts
            )
            operations = Operations(sql_migration_context)

            stack = [ops]
            while stack:
                elem = stack.pop(0)

                if hasattr(elem, "ops"):
                    stack.extend(elem.ops)
                else:
                    operations.invoke(elem)

set this as the rewriter:

def run_migrations_online() -> None:
    connectable = engine_from_config(
        config.get_section(config.config_ini_section, {}),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            process_revision_directives=write_sql_files,
        )

        with context.begin_transaction():
            context.run_migrations()

this traversal is based on the traverse we see at https://alembic.sqlalchemy.org/en/latest/cookbook.html#run-alembic-operation-objects-directly-as-in-from-autogenerate

[zzzeek]

the above just writes the files for readability purposes. it could be extended to also rewrite the MigrationScript object in place to be code that reads the .sql files instead, or you could simply alter the script.py.mako script to hardcode to this routine.

[basvandriel]

While the above gives the user the expected output, I still think we should just use the .sql files instead of also generating the Python script. This so don't have any duplication and won't cause confusion which files are being used. Regarding the offline mode script generation, this could be handy when we want a full database SQL export of that specific revision. In this case, the export should be per revision. I'm open to work on this to also add it to the cookbook. Should we re-open the issue?

[zzzeek]

you can use the .sql files. change your script.py.mako template to be hardcoded to reading the files, rather than using the ${upgrades} or ${downgrades} tokens.

there's no issue to be opened for adding doc recipes