Add command to dump current db schema to a SQL or Python file

[januszm]

Add a command to alembic CLI that will allow to save the current database structure to a SQL file, or one squashed migration, which will work similar to the so-called database schema / structure dump from Ruby on Rails / Active Record framework. The purpose of this is to generate a single file that allows for:

1. Fast database duplication in a new test or development environment, omitting mutually exclusive migrations (e.g. create followed by some alter statements that change the same table column), or the sequence of create-drop-recreate
2. Document the current state of the database with column types, constraints and foreign keys.

It could be something like: alembic dump or alembic structure > file.sql

It would be such a "convenience method", wrapping around the standard database dump tool, but much easier to use (alembic is already preconfigured to connect to the appropriate database, it would also use proper "dumper" depending on the database engine type).

[zzzeek]

I'd favor a cookbook recipe in the docs for this. As a builtin command for alembic it's out of scope.

[januszm]

Dumping the current database schema (type of snapshot or squashed version of all migrations) is a common task and can be performed after each migration. It is also the easiest and fastest data source to use when cloning your environment with team mate or CI / CD. Honestly, it's better to run something like this as a single alembic command rather than referring to the cookbook. Moreover, such a command will include all supported database engines and perform a database-specific dump. From my experience, I would say that it's definitely in scope of a tool such as alembic.

[zzzeek]

actually, I misspoke. When I mentioned a cookbook recipe, I was mostly thinking of something trivial which is basically how to dump the model metadata to a SQL file, which while useful, is not actually representative of what Alembic considers to be the ultimate source of truth about a schema, which is the summation of all the migration files. that recipe is pretty common and can be seen here: https://docs.sqlalchemy.org/en/14/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string

On the topic of dumping from the summation of migrations, e.g. the actual source of truth about a schema, alembic does not have the architectural capability to squash migrations into a virtual schema which could be dumped into SQL. I proposed a long term project for this in #117, but i dont have the capability to take this on myself and it would really represent a rewrite of most of Alembic to do so. so along with issues that request a similar feature here such as #792 , alembic wasnt designed this way. it simply runs the op.XYZ() commands you give it straight into a database and it doesn't really have internal knowledge of how these things build up a schema.

so in short there's no archictectural capability in Alembic to do what you propose, and adding such capability would imply that alembic is doing everything quite differently, such as autogenerate. it would actually be a great architecture but it's not something on my plate at this time and it would probably have to be like an "alembic 2" sort of thing.

without that capability, the best way to get a true "dump" is as it always has been done, which is to use the dump tools that are provided with every vendor's database.