On 09/22/2016 02:51 AM, Tom Walter wrote:
I work in a team of people collaborating on database development and we'd like to start version controlling and deploy our changes with Alembic. I was wondering though if there is a way to generate the DDL for the current state of the whole schema as of a given revision... something like the 'offline' SQL generation, however rather than every intermediate revision, just output the CREATE statements of the final states of all the objects. You might ask why anyone would want to do this... the reason is that we generally like to do code reviews or pull requests when someone deploys a change. If the only artifacts stored in source control are alembic migrations, it can be difficult for a reviewer to get an idea of the overall context and impact of a given migration or set of migrations.
this is also a common use case because of the desire to "squash" all the migrations up to a certain point that are no longer needed individually.
If we could use alembic to update a file or set of files representing the current state of the entire DB, then that could be checked into version control along with the migrations. Granted it would be useless for migrating a given instance of a database from one state to another, but it would be a useful and more readable way for a dev to understand all the objects in a database, short of logging into a live instance of one and poking around.
If the purpose of the file here is strictly one of documentation, I would first note that a traditional SQLAlchemy application has the tables also expressed in Python, either as Table objects or as ORM mapped objects that will show off the table names, columns, and general constraints. Just from that level, there are tools that can generate ER diagrams from a SQLAlchemy model. As well as, you can take any collection of SQLAlchemy tables and just use metadata.create_all() to emit the CREATE statements, which can be directed to string output. So even without a database, the structure of your schema is apparent just from the Python code in the normal case. It would not be hard to write a short script to generate this from the state of the model.
To document the current state of the database itself without consulting the Python structure, IMO that's not even an Alembic thing, most simply you can use your database's dump feature to just dump out the CREATE statements to a text file. All relational databases have such a tool available, such as pg_dump or mysqldump. Then there are also an endless number of both free and commercial database inspection / diagramming tools as well that can generate all kinds of views of a relational database schema.
If, as a third case, you want to generate from the database schema itself, *and* use only SQLAlchemy / Alembic, you would create a MetaData object, do a metadata.reflect() of that engine, then run a metadata.create_all() into a string buffer. This would not be nearly as complete as that of using the database dumper tools, however you would see the basic table structures as well as foreign key constraints.
The create_all() into a string buffer is described at http://docs.sqlalchemy.org/en/latest/faq/metadata_schema.html#how-can-i-get-the-create-table-drop-table-output-as-a-string.
Usually, people want this "state of the database" to be squashed into one big Alembic migration, which would be an additional step to the techniques above, but if you just want a readable text file, running metadata.create_all() into a text file is the most expedient way, it's just that you won't see any special directives or constraints that are present in your migrations only. To get a fully accurate view I
-- You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alembic+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
